Empfangen und Verarbeiten von Daten mit benutzerdefinierten Funktionen

  • Artikel

Eine der Möglichkeiten, mit denen benutzerdefinierte Funktionen die Leistungsfähigkeit von Excel verbessern, besteht darin, Daten von anderen Speicherorten als der Arbeitsmappe zu empfangen, z. B. aus dem Web oder einem Server (über WebSockets). Sie können externe Daten über eine API wie Fetch oder mithilfe XmlHttpRequest von (XHR) anfordern, einer Standardweb-API, die HTTP-Anforderungen für die Interaktion mit Servern ausgibt.

Wichtig

Beachten Sie, dass benutzerdefinierte Excel-Funktionen auf den folgenden Plattformen verfügbar sind.

  • Office unter Windows
    • Microsoft 365-Abonnement
    • retail unbefristete Office 2016 und höher
  • Office für Mac
  • Office im Web

Benutzerdefinierte Excel-Funktionen werden derzeit in den folgenden Artikeln nicht unterstützt:

  • Office auf dem iPad
  • Volumenlizenzierte unbefristete Versionen von Office 2019 oder früheren Versionen

GIF einer benutzerdefinierten Funktion, die die Zeit von einer API streamt.

Funktionen, die Daten aus externen Quellen zurückgeben

Wenn eine benutzerdefinierte Funktion Daten aus einer externen Quelle (etwa aus dem Web) abruft, muss sie folgende Aktionen ausführen:

  1. Zurückgeben eines JavaScript-Codes Promise in Excel.
  2. Lösen Sie den Promise mit dem endgültigen Wert auf, indem Sie die Rückruffunktion verwenden.

Beispiel für Abrufen

Im folgenden Codebeispiel wendet sich die webRequest Funktion an eine hypothetische externe API, die die Anzahl der Personen nachverfolgt, die sich derzeit auf der Internationalen Raumstation befinden. Die Funktion gibt ein JavaScript Promise zurück und verwendet fetch , um Informationen von der hypothetischen API anzufordern. Die resultierenden Daten werden in JSON transformiert, und die names Eigenschaft wird in eine Zeichenfolge konvertiert, die zum Auflösen der Zusage verwendet wird.

Wenn Sie eigene Funktionen entwickeln, möchten Sie möglicherweise eine Aktion ausführen, wenn die Webanforderung nicht zeitnah ausgeführt wird, oder Sie erwägen, mehrere API-Anforderungen zu stapeln.

/**
 * Requests the names of the people currently on the International Space Station.
 * Note: This function requests data from a hypothetical URL. In practice, replace the URL with a data source for your scenario.
 * @customfunction
 */
function webRequest() {
  let url = "https://www.contoso.com/NumberOfPeopleInSpace"; // This is a hypothetical URL.
  return new Promise(function (resolve, reject) {
    fetch(url)
      .then(function (response){
        return response.json();
        }
      )
      .then(function (json) {
        resolve(JSON.stringify(json.names));
      })
  })
}

Hinweis

Die Verwendung von fetch vermeidet geschachtelte Rückrufe und ist in einigen Fällen möglicherweise XHR vorzuziehen.

XHR-Beispiel

Im folgenden Codebeispiel ruft die getStarCount Funktion die GitHub-API auf, um die Menge der Sterne zu ermitteln, die einem bestimmten Benutzer im Repository zugewiesen werden. Dies ist eine asynchrone Funktion, die ein JavaScript Promisezurückgibt. Wenn Daten aus dem Webaufruf abgerufen werden, wird die Zusage aufgelöst, wodurch die Daten an die Zelle zurückgegeben werden.

/**
 * Gets the star count for a given Github organization or user and repository.
 * @customfunction
 * @param userName string name of organization or user.
 * @param repoName string name of the repository.
 * @return number of stars.
 */
async function getStarCount(userName: string, repoName: string) {

  const url = "https://api.github.com/repos/" + userName + "/" + repoName;

  let xhttp = new XMLHttpRequest();

  return new Promise(function(resolve, reject) {
    xhttp.onreadystatechange = function() {
      if (xhttp.readyState !== 4) return;

      if (xhttp.status == 200) {
        resolve(JSON.parse(xhttp.responseText).watchers_count);
      } else {
        reject({
          status: xhttp.status,

          statusText: xhttp.statusText
        });
      }
    };

    xhttp.open("GET", url, true);

    xhttp.send();
  });
}

Erstellen einer Streamingfunktion

Mithilfe von benutzerdefinierten Streamingfunktionen können Sie Daten, die wiederholt aktualisiert werden, an Zellen ausgegeben, ohne dass der Benutzer etwas explizit aktualisieren muss. Dies kann nützlich sein, um Livedaten eines Dienstes online zu überprüfen, z. B. die Funktion im Lernprogramm für benutzerdefinierte Funktionen.

Um eine Streamingfunktion zu deklarieren, können Sie eine der folgenden beiden Optionen verwenden.

  • Das @streaming JSDoc-Tag.
  • Der CustomFunctions.StreamingInvocation Aufrufparameter.

Das folgende Codebeispiel zeigt eine benutzerdefinierte Funktion, die jede Sekunde eine Zahl zu dem Ergebnis addiert. Beachten Sie die folgenden Aspekte in diesem Code.

  • Excel zeigt jeden neuen Wert automatisch mithilfe der Methode setResult an.
  • Der zweite Eingabeparameter (invocation) wird Endbenutzern in Excel nicht angezeigt, wenn sie die Funktion über das Menü „AutoVervollständigen“ auswählen.
  • Der onCanceled Rückruf definiert die Funktion, die ausgeführt wird, wenn die Funktion abgebrochen wird.
  • Streaming ist nicht unbedingt an eine Webanforderung gebunden. In diesem Fall stellt die Funktion keine Webanforderung, sondern immer noch Daten in festgelegten Intervallen, sodass die Verwendung des Streamingparameters invocation erforderlich ist.
/**
 * Increments a value once a second.
 * @customfunction INC increment
 * @param {number} incrementBy Amount to increment
 * @param {CustomFunctions.StreamingInvocation<number>} invocation
 */
function increment(incrementBy, invocation) {
  let result = 0;
  const timer = setInterval(() => {
    result += incrementBy;
    invocation.setResult(result);
  }, 1000);

  invocation.onCanceled = () => {
    clearInterval(timer);
  };
}

Hinweis

Ein Beispiel für die Rückgabe eines dynamischen Überlaufarrays aus einer Streamingfunktion finden Sie unter Zurückgeben mehrerer Ergebnisse aus Ihrer benutzerdefinierten Funktion: Codebeispiele.

Abbrechen einer Funktion

Excel bricht die Ausführung einer Funktion in den folgenden Situationen ab.

  • Wenn der Benutzer eine Zelle bearbeitet oder löscht, die auf die Funktion verweist.
  • Wenn sich eines der Argumente (Eingaben) für die Funktion ändert. In diesem Fall wird nach dem Abbruch ein neuer Funktionsaufruf ausgelöst.
  • Wenn der Benutzer manuell eine Neuberechnung auslöst. In diesem Fall wird nach dem Abbruch ein neuer Funktionsaufruf ausgelöst.

Sie können auch die Festlegung eines Standardstreamingwerts in Betracht ziehen, um Fälle zu behandeln, in denen eine Anforderung erfolgt, während Sie offline sind.

Hinweis

Es gibt auch eine Kategorie von Funktionen, die als abbrechbare Funktionen bezeichnet werden, die das @cancelable JSDoc-Tag verwenden. Abbrechbare Funktionen ermöglichen das Beenden einer Webanforderung in der Mitte der Anforderung.

Eine Streamingfunktion kann das @cancelable Tag nicht verwenden, aber Streamingfunktionen können eine onCanceled Rückruffunktion enthalten. Nur asynchrone benutzerdefinierte Funktionen, die einen Wert zurückgeben, können das @cancelable JSDoc-Tag verwenden. Weitere Informationen zum Tag finden Sie Autogenerate JSON metadata: @cancelable unter @cancelable .

Verwenden eines Aufrufparameters

Der invocation-Parameter ist standardmäßig der letzte Parameter einer benutzerdefinierten Funktion. Der invocation Parameter gibt Kontext zur Zelle (z. B. Adresse und Inhalt) an und ermöglicht es Ihnen, die -Methode und onCanceled das setResult -Ereignis zu verwenden, um zu definieren, was eine Funktion beim Streamen (setResult) oder abgebrochen wird (onCanceled).

Der Aufrufhandler muss vom Typ CustomFunctions.StreamingInvocation oder CustomFunctions.CancelableInvocation zum Verarbeiten von Webanforderungen sein.

Weitere Informationen zu anderen möglichen Verwendungsmöglichkeiten des Arguments und seiner Entsprechung mit dem invocationInvocation-Objekt finden Sie unter Aufrufparameter.

Empfangen von Daten mithilfe von WebSockets

Innerhalb einer benutzerdefinierten Funktion können Sie WebSockets verwenden, um Daten über eine permanente Verbindung mit einem Server auszutauschen. Mithilfe von WebSockets kann Ihre benutzerdefinierte Funktion eine Verbindung mit einem Server herstellen und dann automatisch Nachrichten vom Server empfangen, wenn bestimmte Ereignisse auftreten, ohne dass der Server explizit Daten abfragen muss.

WebSockets-Beispiel

Das folgende Codebeispiel richtet eine WebSocket-Verbindung ein und protokolliert dann jede eingehende Nachricht vom Server.

let ws = new WebSocket('wss://bundles.office.com');

ws.onmessage(message) {
    console.log(`Received: ${message}`);
}

ws.onerror(error){
    console.err(`Failed: ${error}`);
}

Nächste Schritte

Siehe auch