- 操作環境：
- OS：Windows 11
- Webブラウザー：Google Chrome
- 使用プラン：Google Workspace Business Starter(個人プランも可)
- 使用APIについて
- 検索結果反映用Googleスプレッドシート用意
- 検索結果反映用Google Apps Script用意
- Google Apps Scriptによるデータ検索実装
- 動作確認
- おわりに
使用APIについて
以前の記事でExcel OnlineからOffice Scriptを呼出し、Google Books APIsでボリューム検索(リスト)を行いました。今回はこれとほぼ同じ処理をGoogle Apps ScriptとGoogleスプレッドシートで実装してみます。
このAPIの説明は以前の記事(Office Script版)の工程1をご参照ください。
以降、この記事ではGoogle App ScriptをGASと表記します。
検索結果反映用Googleスプレッドシート用意
この例では、検索結果出力用のGoogleスプレッドシートをGoogleドライブのマイドライブに用意します([新規＞Googleスプレッドシート＞空白のスプレッドシート]から新規作成できます)。
ファイルを開き、出力先シートとして[Book_Search_Output]シートを用意します。各セルは値なしで問題ありません。
出力元シートとして[Book_Search_Input]シートを用意します。
A1セルをキーワード入力欄とします。
検索実行ボタンは図形を挿入して工程3のスクリプトを割り当てる形で用意します。
上メニューの[挿入＞図形描画]を選択します。
[図形描画]画面が表示されたら、図形アイコンを選択し、[図形]からボタンに見立てた図形を選択します。
挿入した図形にテキストを追加するには、図形を右クリックして[テキストを編集する]を選択します。
「検索実行」と入力します。
[図形描画]画面右上の[保存して閉じる]を選択します。
スプレッドシート内に挿入されたボタンを選択し、三点マークが表示されたらこれを選択し、[スクリプトを割り当て]を選択します。
工程3で作成するスクリプトの関数名を指定し、[確定]を選択します。
検索結果反映用Google Apps Script用意
上メニューの[拡張機能＞Apps Script]を選択します。
スクリプトエディターが表示されたら、画面右側にスクリプトを記述していきます(スクリプトの内容は工程4で紹介します)。
上メニューのプルダウンから実行したいスクリプトを選択し、[実行]を選択すると、動作確認できます。行番号の左側を選択すると、ブレークポイントを設定でき、デバッグもできます。
Google Apps Scriptによるデータ検索実装
スクリプトの基本的な設計は以前紹介したOffice Script版とほぼ同じですので、そちらも参考にしてください。異なる点を以下に示します。
- GASはGoogleのサーバー上での処理になるため、API実行時にawaitの記述は不要である(1,32行目)
- Office ScriptでExcelを操作する場合の開始行は0だが、GASでGoogleスプレッドシートを操作する場合の開始行は1である(18行目)
- GASでGoogle Books APIsを使用する際、国の自動判定ができないため、URLにcountry=JPを明記している(19,26行目)
- GASでGoogle Books APIsを使用する際、キーワードにスペースを含むとエラーが発生するため、encodeURIComponent()で文字コードの処理を明記している(20,27行目)
- Any型を使用できるため、検索結果のJsonデータはInterfaceで成形していない(33行目)
- Googleスプレッドシート上ではコンソールログを確認できないため、トースト通知を表示する設計とした(44,49,103,106行目)
function getGoogleBooksAPIs() {
let ss = SpreadsheetApp.getActiveSpreadsheet();
try{
// 出力先ワークシートの設定 、データ初期化
let ws = ss.getSheetByName("Book_Search_Output");
ws.clear();
//検索キーワードの取得
let keyword = ss.getSheetByName("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 = 1; //出力シートの行数カウンタ
let url = "https://www.googleapis.com/books/v1/volumes?country=JP&q=intitle:" +
encodeURIComponent(keyword) + "&maxResults=" + num_per_page.toString();
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?country=JP&q=intitle:" +
encodeURIComponent(keyword) + "&maxResults=" + num_per_page.toString() +
"&startIndex=" + (cnt_row_xlsx - 1).toString();
}
//データ取得の実施
let response = UrlFetchApp.fetch(url);
let apiJson = JSON.parse(response.getContentText());
//初回ページの場合、全体件数からページ数を算出
if (cnt_page === 0) {
if (apiJson.totalItems > num_per_page) {
max_page = Math.floor(apiJson.totalItems / num_per_page);
}
}
//取得合計件数がゼロの場合、メッセージを出力して終了
if (apiJson.totalItems == 0 && cnt_page === 0) {
ss.toast("該当データがありませんでした。:", "正常終了", 3);
return;
}
//最終ページでapiJson.Itemsがない場合、メッセージを出力して終了
if (!apiJson.items) {
ss.toast("書籍データの出力が正常終了しました。:", "正常終了", 3);
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.getRange(cnt_row_xlsx, cnt_col + 1).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.getRange(cnt_row_xlsx + 1, cnt_col + 1).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.getRange(cnt_row_xlsx + 1, cnt_col + 1).setValue("'" + tmp_array);
}
//データ行(その他の列)
} else {
if (apiJson.items[cnt_row].volumeInfo[keys[cnt_col]]) { //値があれば書き出し
ws.getRange(cnt_row_xlsx + 1, cnt_col + 1).setValue
("'" + apiJson.items[cnt_row].volumeInfo[keys[cnt_col]]);
}
}
}
//出力シートの行数カウンタをインクリメント
cnt_row_xlsx = cnt_row_xlsx + 1;
}
}
//出力完了
ss.toast("書籍データの出力が正常終了しました。:", "正常終了", 3);
} catch (error) {
ss.toast("書籍データの出力が異常終了しました。:" + error.message, "異常終了", -1);
}
}
動作確認
[Book_search_Input]シートのA1セルに書籍名のキーワードを入力し、[検索実行]ボタンを選択します。
右下の処理終了のトースト通知が表示されるのを待ちます。
[Book_search_Output]シートを開いて結果を確認します。
最後の方のデータもきちんと出力されていることを確認します。
おわりに
とてもシンプルな例ですが、GASによるAPI呼出しやGoogleスプレッドシートの操作を行う基本形がつかめるかと思います。GASで初めてこれらを扱う際の参考になれば幸いです。
当ブログ内の関連記事
