カスタム関数でデータを受信して​​処理する

カスタム関数が Excel の機能を強化する方法の 1 つは、( WebSocket を介して) Web やサーバーなどのブック以外の場所からデータを受信することです。 外部データを要求するには、 Fetch などの API を使用するか、サーバーと対話するための HTTP 要求を発行する標準 Web API である XmlHttpRequest(XHR)を使用します。

重要な点

• 外部データをフェッチする関数から JavaScript Promise を返します。
• ストリーミング関数を使用して、ユーザー操作なしでセル値を継続的に更新します。
• ストリーミング関数では、 @streaming タグと CustomFunctions.StreamingInvocation パラメーターを使用します。
• onCanceled コールバックは、関数が取り消されたときにクリーンアップを処理します。
• WebSocket を使用すると、永続的な接続を持つサーバーからリアルタイムのデータ更新が可能になります。

外部ソースからデータを返す関数

REST API や Web サービスなどの外部ソースからデータを取得するカスタム関数は、本質的に非同期です。 Excel では、セルに結果を表示する前に、データが到着するのを待つ必要があります。 これを処理するには、関数で次の操作を行う必要があります。

1. JavaScript Promiseを Excel に返します。
2. コールバック関数を使用して、最終的な値で Promise を解決します。

Excel は、セルに結果を表示する前に、promise が解決されるまで自動的に待機します。 このパターンは、1 回限りのデータ要求に対して機能します。 継続的な更新の場合は、代わりにストリーミング関数を使用します。

Fetch の使用例

次のコード サンプルでは、 webRequest 関数は、現在国際宇宙ステーションにいるユーザーの数を追跡する架空の外部 API に到達します。 関数は JavaScript Promise を返し、 fetch を使用して架空の API から情報を要求します。 結果のデータは JSON に変換され、 names プロパティは文字列に変換され、promise を解決するために使用されます。

独自の関数を開発するときは、Web 要求がタイムリーに完了しない場合や 、複数の API 要求をバッチ処理する場合は、アクションを実行することを検討してください。

/**
 * 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));
      })
  })
}

XHR の使用例

次のコード サンプルでは、 getStarCount 関数は Github API を呼び出して、特定のユーザーのリポジトリに与えられた星の量を検出します。 これは、JavaScript Promiseを返す非同期関数です。 Web 呼び出しからデータが取得されると、データをセルに返す promise が解決されます。

/**
 * 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();
  });
}

ストリーミング関数を作成する

ストリーム カスタム関数を使用すると、繰り返し更新されるセルにデータを出力でき、ユーザーが明示的に何かを更新する必要ありません。 これは、 カスタム関数チュートリアルの関数のように、株価、センサーの測定値、リアルタイム分析などのサービスからのライブ データを表示する場合に便利です。

ストリーミング関数を宣言するには、次の 2 つのオプションのいずれかを使用できます。

• @streaming JSDoc タグ。
• CustomFunctions.StreamingInvocation呼び出しパラメーター。

ストリーミング関数は、単一の結果を返すのではなく、 setResult を複数回呼び出してセル値を継続的に更新できる点で、通常の非同期関数とは異なります。

基本的なストリーミングの例

以下のコード サンプルは、毎秒ごとに結果に数値を追加するカスタム関数です。 このコードについては、次の点に注意してください。

• Excel は、setResult メソッドを使用して自動的に新しい値を表示します。
• 2 つ目の入力パラメーター invocationは、オートコンプリート メニューから関数を選択しても、Excel のエンド ユーザーには表示されません。
• onCanceled コールバックは、関数が取り消されたときに実行される関数を定義します。
• ストリーミングは、必ずしも Web 要求の作成に関連付けられているわけではありません。 この場合、関数は Web 要求を行いませんが、設定された間隔でデータを取得するため、ストリーミング invocation パラメーターを使用する必要があります。

/**
 * 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);
  };
}

Web サービスからのデータのストリーミング

次の例は、Web サービスから 10 秒ごとに株価をフェッチするストリーミング関数を示しています。

/**
 * 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);
  };
}

関数を取り消す

Excel では、次の状況で関数の実行が自動的に取り消されます。

• ユーザーが、関数を参照するセルを編集または削除した場合。
• 関数の引数 (入力) の 1 つが変更されたとき。 この場合、キャンセルに続いて、関数の新しい呼び出しがトリガーされます。
• ユーザーが手動で再計算をトリガーしたとき。 この場合、キャンセルに続いて、関数の新しい呼び出しがトリガーされます。

不要なネットワーク要求を防ぐには、 onCanceled コールバックでの適切なクリーンアップが重要です。 タイマーを常にクリアし、接続を閉じ、関数が取り消されたときに保留中の要求を中止します。 また、要求が発生したときに、オフラインの場合でも、ケースを処理する既定のストリーミング値を設定することもできます。

呼び出しパラメーターを使用する

invocation パラメーターは、既定ではカスタム関数の最後のパラメーターです。 invocation パラメーターは、セルに関するコンテキスト (アドレスや内容など) を提供し、setResult メソッドと onCanceled イベントを使用して、関数がストリーム処理 (setResult) または取り消されたとき (onCanceled) を定義できます。

呼び出しハンドラーは、web 要求を処理するために CustomFunctions.StreamingInvocation 型であるか、 CustomFunctions.CancelableInvocation である必要があります。

invocation引数のその他の潜在的な使用方法と、それが Invocation オブジェクトに対応する方法については、「呼び出しパラメーター」を参照してください。

WebSocket 経由のデータ受信

カスタム関数内で、WebSocket を使用して、サーバーとの固定接続経由でデータを交換することができます。 WebSocket は、財務ティッカー、チャット メッセージ、センサー データなど、頻繁に更新されるリアルタイム データに役立ちます。 WebSocket を使用すると、カスタム関数はサーバーとの接続を開き、特定のイベントが発生したときにサーバーからメッセージを自動的に受信できます。サーバーでデータを明示的にポーリングする必要はありません。

WebSocket ストリーミングの例

次のコード サンプルは、WebSocket を使用してリアルタイム更新プログラムを受信するストリーミング関数を示しています。

/**
 * 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();
  };
}

次の手順

• 関数で使用できるさまざまなパラメーターのタイプについての詳細。
• 複数の API の呼び出しをバッチする方法を探す。

関連項目

• Excel カスタム関数のチュートリアル
• カスタム関数パラメーター オプション
• リモート サービスのバッチ カスタム関数呼び出し
• カスタム関数から複数の結果を返す
• 関数の揮発性の値
• カスタム関数の JSON メタデータを作成する
• Excel でカスタム関数を作成する