Office ScriptでのAPI呼出しによるデータ検索の例を紹介します。APIはGoogle Books APIsのボリューム検索(リスト)を使用します。
以前の記事で触れた通り、Office ScriptからのAPI呼出しについては、OAuth2認証を要するAPIは使用できないようなのですが、今回使用するAPIはOAuth2認証を使用しないため、Office Scriptからの実行が可能です。
・操作環境：
・OS：Windows 11
・Webブラウザー：Edge
・使用プラン：Microsoft 365 Business Premium
- この記事で使用するAPIについて
- PostmanでAPIの動作確認を行う(推奨)
- 検索結果を反映するExcelファイルとOffice Scriptを用意する
- Office ScriptでAPIに接続しデータ検索する
- 動作確認する
- おわりに
この記事で使用するAPIについて
以前の記事で、書籍のISBNコードをバーコードリーダーで読み取り、Google Books APIsで詳細情報を取得し、登録するアプリの作成例を紹介しました。そのアプリではボリューム検索のGETを使用しましたが、今回は複数データを取得できるリストの方を使用します。
※Google Books APIs 公式リファレンス>ボリューム検索(リスト)：
ボリューム: list | Google Books APIs | Google for Developers
また、この例で使用するパラメーターは以下の通りです。
・q：全文検索クエリ(書籍名や説明等のキーワード)
・maxResults：一度に返される結果の上限件数。今回は40とします
・startIndex：返される最初の結果のインデックス(0から始まる)。
Google Books APIsに限らず、リスト検索では一度に返すデータ件数の上限が設けられているのが一般的です。
例えば、200件該当データがあり、maxResults=40とした場合、インデックスは以下の通りとなります。
・一回目：startIndex=0(設定なしでもよい) ⇒ インデックスが0～39のデータを取得
・二回目：startIndex=40 ⇒ インデックスが40～79のデータを取得
・三回目：startIndex=80 ⇒ インデックスが80～119のデータを取得
・四回目：startIndex=120 ⇒ インデックスが120～159のデータを取得
・五回目：startIndex=160 ⇒ インデックスが160～199のデータを取得
PostmanでAPIの動作確認を行う(推奨)
いきなりOffice Scriptで実装するのではなく、先にAPI開発ツール「Postman」でデータのやりとりを確認することをおススメします。Postmanの入手方法は以前の記事を参考にしてください。
以下画面はapiというキーワードで、検索結果のインデックス80から40件取得する例です。
検索結果を反映するExcelファイルとOffice Scriptを用意する
検索結果出力用のExcelファイルは、SharePointサイトのドキュメントの下に配置しました。
ファイルを開き、出力先シートとして[Book_Search_Output]シートを用意します。各セルは値なしで問題ありません。
[自動化]タブを選択し、[新しいスクリプト]を選択します。
コードエディターを左側に広げてスクリプトを記述していきます。
ちなみに、Office Scriptのファイルは保存先を指定しなければ各自のOneDrive内に保存されます。
出力元シートとして[Book_Search_Input]シートを用意します。
キーワード入力欄を作成します。
先ほどのスクリプトについて、[ブックに追加]を選択し、実行ボタンを用意します。
Office ScriptでAPIに接続しデータ検索する
スクリプトの中身です。ポイント事項は以下の通りです。
- Office ScriptはAny型が使用できないため、検索結果のJsonデータはInterfaceで定義した型で成形している
- [全件数]÷[一度に返す件数の上限]の商をページ数とし、ページ数分APIを実行し、全件数分のデータを出力している。全件数は、APIレスポンスの[totalItems]で分かる
- APIの初回実行時に該当データがなかった場合、コンソールにその旨を出力して処理を終了している
- API実行はfetch関数を使用し、検索結果のJsonデータをスクリプトの最初に定義したInterfaceの型に格納している。これらはawaitにより結果が返されるまで待機するようにしている
- [authors]や[industryIdentifiers]のような複数データを持つ項目は、カンマ区切りの値として出力している
interface ApiJsonBook {
totalItems:number;
items: [
{
volumeInfo:{
title:string; //書籍名
authors?:string[]; //著者
publisher:string; //出版社
publishedDate:Date; //出版日
description?:string;//説明
industryIdentifiers?: [//ISBNコード
{
type: string;
identifier:string;
}
]
}
}
]
error?: {
code: string;
message: string;
};
}
async function main(workbook: ExcelScript.Workbook) {
try{
// 出力先ワークシートの設定 、データ初期化
let ws = workbook.getWorksheet("Book_Search_Output");
if (ws.getUsedRange() != null) {
ws.getUsedRange().clear();
}
//検索キーワードの取得
let keyword = workbook.getWorksheet("Book_Search_Input").getRange("A1").getValue();
//定数および最初から使う変数の定義
const keys = ["title", "authors", "publisher", "publishedDate", "description", "industryIdentifiers"];
const num_per_page = 40; //APIで読み込める最大件数(超えたらページ送り)
let max_page = 1; //ページ数の初期値
let cnt_row_xlsx = 0; //出力シートの行数カウンター
let url = "https://www.googleapis.com/books/v1/volumes?q=intitle:" +
keyword + "&maxResults=" + num_per_page.toString(); //APIのurlの初期値
for (let cnt_page = 0; cnt_page < max_page; cnt_page++) {
//2ページ目以降の場合、urlにページ送り用のインデックスを追加
if (max_page > 1) {
url = "https://www.googleapis.com/books/v1/volumes?q=intitle:" +
keyword + "&maxResults=" + num_per_page.toString() +
"&startIndex=" + (cnt_page * num_per_page).toString();
}
//データ取得の実施
let res = await fetch(url);
let apiJson: ApiJsonBook = await res.json();
//初回ページの場合、全件数からページ数を算出
if (cnt_page === 0) {
if (apiJson.totalItems > num_per_page) {
max_page = Math.floor(apiJson.totalItems / num_per_page);
}
}
//全件数がゼロの場合、メッセージを出力して終了
if (apiJson.totalItems == 0) {
console.log("該当データがありませんでした。");
return;
}
// 取得したデータのシート書き出し
for (let cnt_row = 0; cnt_row < apiJson.items.length; cnt_row++) {
for (let cnt_col = 0; cnt_col < keys.length; cnt_col++) {
//タイトル行
if (cnt_row === 0 && cnt_page === 0) {//タイトル行
ws.getCell(cnt_row_xlsx, cnt_col).setValue(keys[cnt_col]);
}
//データ行(authors列)
if (keys[cnt_col] === "authors") {
let tmp_array = "";
if (apiJson.items[cnt_row].volumeInfo[keys[cnt_col]]) {//値があれば書き出し
for (let cnt_array = 0; cnt_array < apiJson.items[cnt_row].volumeInfo[keys[cnt_col]].length; cnt_array++) {
if (cnt_array > 0) {
tmp_array = tmp_array + ",";
}
tmp_array = tmp_array + apiJson.items[cnt_row].volumeInfo[keys[cnt_col]][cnt_array];
}
ws.getCell(cnt_row_xlsx + 1, cnt_col).setValue("'" + tmp_array);
}
//データ行(industryIdentifiers)
} else if (keys[cnt_col] === "industryIdentifiers") {
let tmp_array = "";
if (apiJson.items[cnt_row].volumeInfo[keys[cnt_col]]) {//値があれば書き出し
for (let cnt_array = 0; cnt_array < apiJson.items[cnt_row].volumeInfo[keys[cnt_col]].length; cnt_array++) {
if (cnt_array > 0) {
tmp_array = tmp_array + ",";
}
tmp_array = tmp_array + apiJson.items[cnt_row].volumeInfo[keys[cnt_col]][cnt_array]["identifier"];
}
ws.getCell(cnt_row_xlsx + 1, cnt_col).setValue("'" + tmp_array);
}
//データ行(その他の列)
} else {
if (apiJson.items[cnt_row].volumeInfo[keys[cnt_col]]) { //値があれば書き出し
ws.getCell(cnt_row_xlsx + 1, cnt_col).setValue
("'" + apiJson.items[cnt_row].volumeInfo[keys[cnt_col]]);
}
}
}
//出力シートの行数カウンターをインクリメント
cnt_row_xlsx = cnt_row_xlsx + 1;
}
}
//出力完了
console.log("書籍データの出力が正常終了しました。");
} catch (error) {
console.log("書籍データの出力が異常終了しました。", error);
}
}
動作確認する
スクリプトの実行ボタンを押し、処理終了のメッセージが表示されたら、[Book_search_Output]シートを開いて結果を確認します。
下スクロールして全件出力されていることを確認します。
おわりに
とてもシンプルな例ですが、Office ScriptでAPI呼出しを行う基本形がつかめるかと思います。Office Scriptで初めてAPI呼出しを行う際の参考になれば幸いです。
※Office ScriptでのAPI呼出しのサポートに関する公式記事：Office スクリプトでの外部 API 呼び出しのサポート - Office Scripts | Microsoft Learn
