Power AutomateにてAPI呼出しによるデータ検索とOffice Scriptの実行を行い、Excelファイルに検索結果を反映する方法を紹介します。データはSharePointリスト、APIはMicrosoft Graph Rest APIを使用します。Excel VBAで同じことを実施した記事もありますので、よろしければそちらも参考にしてください。
・操作環境：
・OS：Windows 11
・Webブラウザー：Edge
・使用プラン：Microsoft 365 Business Premium
- この記事で使用するデータとAPIについて
- Office ScriptからのAPI呼出しについて
- 検索結果を反映するExcelファイルとOffice Scriptを用意する
- Power AutomateでAPI周りの処理を実装する
- 動作確認する
- おわりに
この記事で使用するデータと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について、今後実行できるようになることを期待します(対応の優先度や技術的な問題ではなく、セキュリティポリシーにより実行できないようにしている可能性もありそうですが・・・)。
