情シス仕事の備忘録

自身の備忘録を兼ねて、情シス仕事で役に立ちそうな情報を掲載しています

Office Script : Power AutomateからのAPI呼出しによるデータ検索

Power AutomateにてAPI呼出しによるデータ検索とOffice Scriptの実行を行い、Excelファイルに検索結果を反映する方法を紹介します。データはSharePointリスト、APIはMicrosoft Graph Rest APIを使用します。Excel VBAで同じことを実施した記事もありますので、よろしければそちらも参考にしてください。

・操作環境:
 ・OS:Windows 11
 ・Webブラウザー:Edge
・使用プラン:Microsoft 365 Business Premium

 

 

この記事で使用するデータとAPIについて

以前の記事で、Excel VBAからMicrosoft Graph Rest APIを実行し、書籍管理SharePointリストに接続してデータ検索・更新を行う例を紹介しました。参考比較のため、この記事でも同じデータとAPIを使用してみます。
このデータとAPIの設定方法を紹介した、以前の記事の該当箇所へのリンクを貼っておきます。

 

Office ScriptからのAPI呼出しについて

Office Scriptから直接呼び出せるAPIもありますが(こちらの記事で事例紹介)、Microsoft Graph Rest APIのようにOAuth2認証を要するAPIは使用できないようです(公式記事の解釈が非常に難しいですが…)。
※外部API呼出しサポートに関する公式記事:Office スクリプトでの外部 API 呼び出しのサポート - Office Scripts | Microsoft Learn

 

試しにOffice Scriptから直接API呼出しを行うと、以下画面(Webブラウザーの開発者ツールで確認)のようにアクセストークン取得時にCORSエラーやCSPエラーが出て機能しませんでした。
アクセス許可したいOffice ScriptのOriginは固定値ではないため、AzureのAPI Managementサービスでの対応も難しいようです(レスポンスヘッダーへの設定でワイルドカードが使用できない)。

 

Power AutomateにてAPI呼出しを行い(アクセストークン取得とデータ検索を実施)、指定したOffice Scriptに検索結果データを渡し、Excelファイルに出力させる形としました。

 

実運用において、SharePointリストのデータをExcel出力するのにわざわざAPIを使うことはないと思いますが、この後の内容がPower AutomateからOffice Scriptにデータを渡して処理を行う場合の参考になれば幸いです。

 

検索結果を反映するExcelファイルとOffice Scriptを用意する

検索結果出力用のExcelファイルは、SharePointサイトのドキュメントの下に配置しました。

 

ファイルを開き、出力先シートとして[Search]シートを用意します。各セルは値なしで問題ありません。
[自動化]タブを選択し、[新しいスクリプト]を選択します。

 

コードエディターを左側に広げてスクリプトを記述していきます。
ちなみに、Office Scriptのファイルは保存先を指定しなければ各自のOneDrive内に保存されます。

 

スクリプトの中身です。ポイント事項は以下の通りです。

  • Office ScriptはAny型が使用できないため、検索結果のJsonデータはInterfaceで定義した型で成形している。
  • Main関数の第二引数は検索結果のJsonデータで、Power Automateから値を受け取る想定としている。
  • 検索結果にエラーがあった場合は、エラー内容をコンソール出力して処理を終了している。
interface ApiJsonBook {
  value: [{
    id: string;
    fields: {
      category: string;
      status: string;
      borrower?: string;
      borrowed_date?: string;
      isbn_code: string;
      published_date: string;
      book_name: string;
      author0: string;
      publisher: string;
      memo: string;
      thumbnail?: {
        Description: string;
        Url:string;
      };
    };
  }];
  error?: {
    code: string;
    message: string;
  };
}

async function main(workbook: ExcelScript.Workbook, jsonData: string) {

  try{

    // 出力先ワークシートの設定 、データ初期化
    let ws = workbook.getWorksheet("Search");
    if (ws.getUsedRange() != null) {
      ws.getUsedRange().clear();
    }

    // 取得データ項目を定義する
    let keys = ["id", "category", "status", "borrower", "borrowed_date", "isbn_code", 
      "published_date", "book_name", "author0", "publisher", "memo", "thumbnail"];

    // 取得データを成型する
    let apiJson: ApiJsonBook = JSON.parse(jsonData);

    // 取得データにエラーがあった場合
    if (apiJson.error) {
      console.log("データ検索でエラーが発生しました。:" + 
        apiJson.error.code + ":" + apiJson.error.message);
      return;
    }

    // 取得データをsearchシートに書き出す
    for (let cnt_row = 0; cnt_row < apiJson.value.length; cnt_row++) {
      for (let cnt_col = 0; cnt_col < keys.length; cnt_col++) {
        if (cnt_row === 0) {
          ws.getCell(cnt_row, cnt_col).setValue(keys[cnt_col]); // タイトル行
        }
        if (keys[cnt_col] === "id") { // データ行(id列)
          ws.getCell(cnt_row + 1, cnt_col).setValue
            ("'" + apiJson.value[cnt_row][keys[cnt_col]]);
        } else if (keys[cnt_col] === "thumbnail") { // データ行(thumbnail列)
          if (apiJson.value[cnt_row].fields[keys[cnt_col]]) { // 値がある時だけ書き出し
            ws.getCell(cnt_row + 1, cnt_col).setValue
              ("'" + apiJson.value[cnt_row].fields[keys[cnt_col]].Description);
          }
        } else { // データ行(その他の列)
          if (apiJson.value[cnt_row].fields[keys[cnt_col]]) { // 値がある時だけ書き出し
            ws.getCell(cnt_row + 1, cnt_col).setValue
              ("'" + apiJson.value[cnt_row].fields[keys[cnt_col]]);
          }
        }
      }
    }
  } catch (error) {
    console.log("Failed to get book data:", error);
  }
}

 

Power AutomateでAPI周りの処理を実装する

WebブラウザーよりPower Automateの画面にアクセスし、左メニューの[マイフロー]を選択し、[新しいフロー>インスタントクラウドフロー]を選択します。
※Power Automate画面:Microsoft Power Automate

 

フロー名を適宜入力し、[フローを手動でトリガーする]を選択し、[作成]を選択します。

 

先にフロー全体を示します。

  • access-token取得に必要な変数の初期化(tenant-id~scope)
  • access-token取得・解析・変数への格納
  • book-data(SharePointリストデータ)取得に必要な変数の初期化(site-id,list-id)
  • book-data取得・解析
  • Officeスクリプトの実行(ここでbook-data解析結果も渡す)


各ステップの設定箇所を示します。

 

JSONの解析に関しては、[サンプルのペイロードを使用して]スキーマを生成する]からPostmanでのリクエスト実行結果を貼り付けると楽です。
変数access-tokenの関数の設定内容は以下の通りです。
concat(body('JSON_の解析_access-token')?['token_type'], ' ',body('JSON_の解析_access-token')?['access_token'])

 

前のステップまででaccess-tokenが取得できたので、ここからはbook-dataの取得を行います。

 

access-token取得時と同様、JSONの解析に関しては、[サンプルのペイロードを使用して]スキーマを生成する]からPostmanでのリクエスト実行結果を貼り付けると楽です。
スクリプトの実行の一番下のパラメーターは、前の工程で設定したOffice Scriptの第二引数で、book-dataの検索結果を渡しています。

 

動作確認する

Power Automateの画面で、左メニューの[マイフロー]を選択し、前の工程で作成したフローの実行アイコンを選択します。

 

少し時間が経ったら前の画面でフローを選択し、実行履歴を確認します。実行が完了し、[成功]と表示されれば問題ありません。

 

工程3で用意したExcelファイルを確認すると、SearchシートにSharePointリストのデータが出力されています。

 

おわりに

Office ScriptからのAPI呼出しに関するポイントは以下の通りです。

  • OAuth2認証が必要なAPIは直接呼出しができない(と思われる)
  • 上記の場合、Power AutomateのHTTPコネクタを使用すればよい
  • HTTPコネクタはプレミアムコネクタなので、365の標準では使用できない(追加料金が必要)

追加料金を払いたくないなら、現状はOffice Script & Power Automateよりも、従来の仕組みですがExcel VBAの方が良いのかなと思いました。
現状Office Scriptで直接実行できないAPIについて、今後実行できるようになることを期待します(対応の優先度や技術的な問題ではなく、セキュリティポリシーにより実行できないようにしている可能性もありそうですが・・・)。