Empfangen und Verarbeiten von Daten mit benutzerdefinierten Funktionen

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.

Wichtige Punkte

• Gibt einen JavaScript-Code Promise aus Funktionen zurück, die externe Daten abrufen.
• Verwenden Sie Streamingfunktionen, um Zellwerte kontinuierlich ohne Benutzerinteraktion zu aktualisieren.
• Streamingfunktionen verwenden das Tag und CustomFunctions.StreamingInvocation den @streaming Parameter.
• Der onCanceled Rückruf verarbeitet die Bereinigung, wenn eine Funktion abgebrochen wird.
• WebSockets ermöglichen Echtzeitaktualisierungen von Daten von Servern mit persistenten Verbindungen.

Funktionen, die Daten aus externen Quellen zurückgeben

Benutzerdefinierte Funktionen, die Daten aus externen Quellen wie REST-APIs oder Webdiensten abrufen, sind von Natur aus asynchron. Excel muss warten, bis die Daten eintreffen, bevor ergebnisse in der Zelle angezeigt werden. Um dies zu behandeln, muss Ihre Funktion:

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.

Excel wartet automatisch auf die Auflösung der Zusage, bevor das Ergebnis in der Zelle angezeigt wird. Dieses Muster funktioniert für einmalige Datenanforderungen. Verwenden Sie für fortlaufende Updates stattdessen Streamingfunktionen.

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.

Erwägen Sie beim Entwickeln eigener Funktionen das Ausführen einer Aktion, wenn die Webanforderung nicht rechtzeitig abgeschlossen wird, oder die Batchverarbeitung mehrerer API-Anforderungen.

/**
 * 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 ist nützlich für die Anzeige von Livedaten aus Diensten, z. B. Aktienkursen, Sensorwerten oder Echtzeitanalysen, wie die Funktion im Tutorial zu benutzerdefinierten Funktionen.

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

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

Streamingfunktionen unterscheiden sich von regulären asynchronen Funktionen darin, dass sie mehrmals aufrufen setResult können, um den Zellwert kontinuierlich zu aktualisieren, anstatt ein einzelnes Ergebnis zurückzugeben.

Beispiel für einfaches Streaming

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 im AutoVervollständigen-Menü 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);
  };
}

Streamen von Daten aus einem Webdienst

Das folgende Beispiel zeigt eine Streamingfunktion, die alle 10 Sekunden Aktienkurse von einem Webdienst abruft.

/**
 * Streams stock price updates.
 * @customfunction
 * @param {string} ticker Stock ticker symbol.
 * @param {CustomFunctions.StreamingInvocation<number>} invocation
 */
function stockPrice(ticker, invocation) {
  const updateInterval = 10000; // Update every 10 seconds.

  const timer = setInterval(() => {
    // Replace with your actual API endpoint.
    fetch(`https://api.example.com/stock/${ticker}`)
      .then(response => response.json())
      .then(data => {
        invocation.setResult(data.price);
      })
      .catch(error => {
        // Return the #N/A error if stock price is unavailable.
        invocation.setResult(
          new CustomFunctions.Error(CustomFunctions.ErrorCode.notAvailable)
        );
      });
  }, updateInterval);

  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 automatisch 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.

Eine ordnungsgemäße Bereinigung im onCanceled Rückruf ist wichtig, um unnötige Netzwerkanforderungen zu vermeiden. Timer immer löschen, Verbindungen schließen und ausstehende Anforderungen abbrechen, wenn eine Funktion abgebrochen wird. 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. WebSockets sind nützlich für Echtzeitdaten, die häufig aktualisiert werden, z. B. Finanzticker, Chatnachrichten oder Sensordaten. 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.

WebSocket-Streamingbeispiel

Das folgende Codebeispiel zeigt eine Streamingfunktion, die WebSockets verwendet, um Echtzeitupdates zu empfangen.

/**
 * Streams real-time data via WebSocket.
 * @customfunction
 * @param {string} symbol Data symbol to monitor.
 * @param {CustomFunctions.StreamingInvocation<string>} invocation
 */
function streamWebSocket(symbol, invocation) {
  const ws = new WebSocket('wss://example.com/data');

  ws.onopen = () => {
    // Subscribe to updates for the specified symbol.
    ws.send(JSON.stringify({ subscribe: symbol }));
  };

  ws.onmessage = (event) => {
    const data = JSON.parse(event.data);
    invocation.setResult(data.value);
  };

  ws.onerror = (error) => {
    // Return the #N/A error if connection fails.
    invocation.setResult(
      new CustomFunctions.Error(CustomFunctions.ErrorCode.notAvailable)
    );
  };

  invocation.onCanceled = () => {
    ws.close();
  };
}

Nächste Schritte

• Lernen Sie die verschiedenen Parametertypen kennen, die Ihre Funktionen verwenden können.
• Entdecken Sie, wie Sie mehrere API-Aufrufe im Batch verarbeiten.

Siehe auch

• Lernprogramm: Erstellen von benutzerdefinierten Funktionen in Excel
• Parameteroptionen für benutzerdefinierte Funktionen
• Batchaufrufe von benutzerdefinierten Funktionen für einen Remotedienst
• Zurückgeben mehrerer Ergebnisse aus Ihrer benutzerdefinierten Funktion
• Veränderliche Werte in Funktionen
• Erstellen von JSON-Metadaten für benutzerdefinierte Funktionen
• Erstellen von benutzerdefinierten Funktionen in Excel