- 操作環境：
- OS：Windows 11
- Webブラウザー：Google Chrome
- 使用プラン：Google Workspace Business Starter(個人プランも可)
- 使用APIについて
- 検索結果反映用Googleスプレッドシート用意
- 検索結果反映用Google Apps Script用意
- Google Apps Scriptによるデータ取得・加工実装
- 動作確認
- おわりに
使用APIについて
以前の記事でExcel VBAによるe-Stat APIを使用したデータ取得(CSV形式)を行いました。
今回はこれとほぼ同じ処理をGoogle Apps ScriptとGoogleスプレッドシートで実装してみます。
e-Stat APIの使用開始手順(appId取得を含む)は以前の記事の工程1、API実行用URL取得手順は以前の記事の工程2、PostmanによるAPI動作確認の紹介は以前の記事の工程3をご参照ください。
以降、この記事ではGoogle App ScriptをGASと表記します。
検索結果反映用Googleスプレッドシート用意
この例では、検索結果出力用のGoogleスプレッドシートをGoogleドライブのマイドライブに用意します([新規＞Googleスプレッドシート＞空白のスプレッドシート]から新規作成できます)。
ファイルを開き、出力先シートとして[eStat_Search_Output]シートを用意します。各セルは値なしで問題ありません。
出力元シートとして[eStat_Search_Input]シートを用意します。
B1セルをAPI実行用URL欄(取得方法は以前の記事の図表2-10参照)とします。
検索実行ボタンは図形を挿入して工程3のスクリプトを割り当てる形で用意します。
上メニューの[挿入＞図形描画]を選択します。
[図形描画]画面が表示されたら、図形アイコンを選択し、[図形]からボタンに見立てた図形を選択します。
挿入した図形にテキストを追加するには、図形を右クリックして[テキストを編集する]を選択します。
「検索実行」と入力します。
[図形描画]画面右上の[保存して閉じる]を選択します。
スプレッドシート内に挿入されたボタンを選択し、三点マークが表示されたらこれを選択し、[スクリプトを割り当て]を選択します。
工程3で作成するスクリプトの関数名を指定し、[確定]を選択します。
検索結果反映用Google Apps Script用意
上メニューの[拡張機能＞Apps Script]を選択します。
画面右側のスクリプトエディターにスクリプトを記述していきます(スクリプトの内容は工程4で紹介します)。
上メニューのプルダウンから実行したいスクリプトを選択し、[実行]を選択すると、動作確認できます。行番号の左側を選択すると、ブレークポイントを設定でき、デバッグもできます。
APIキーのような、スクリプトやGoogleスプレッドシート内に埋め込みたくないセキュアな値は、スクリプトプロパティに設定します。
左端の歯車アイコン(プロジェクトの設定)を選択し、画面の下の方にある[スクリプト プロパティを追加]を選択します。
e-Stat APIのappId(取得方法は以前の記事の工程1参照)を定義し、[スクリプトプロパティを保存]を選択します。
Google Apps Scriptによるデータ取得・加工実装
スクリプトの基本的な設計は以前紹介したExcel VBA版とほぼ同じですので、そちらも参考にしてください。異なる点を以下に示します。
- appIdは、Googleスプレッドシート上ではなく、図表3-3で定義したスクリプトプロパティから取得している(15行目)
- Googleスプレッドシート上ではテキストボックスを使用できないため、B1セルに入力されたURLを取得する設計とした(18-19行目)
- API実行の記述はシンプルなため、サブ関数を作成せず、メイン関数内で実行している(29-33行目)
- Googleスプレッドシート上ではダイアログボックスを使用できないため、トースト通知を表示する設計とした(38-39,47,50行目)
/**
* 【メイン】eStatAPIのデータ検索結果をスプレッドシートに出力する
*/
function geteStatAPI() {
let ss = SpreadsheetApp.getActiveSpreadsheet();
try {
// 出力先ワークシートの設定 、データ初期化
const ws = ss.getSheetByName("eStat_Search_Output");
ws.clear();
// appIdの取得
const appId = PropertiesService.getScriptProperties().getProperty("appId");
// URLの取得(appId=で分割)
const arrayUrl = ss.getSheetByName("eStat_Search_Input").
getRange("B1").getValue().split("appId=");
// データ検索
const apiMethod = "GET";
const apiUrl = arrayUrl[0] + "appId=" + appId + arrayUrl[1];
const apiHeaders = {
"Content-Type": "application/json",
};
// データ取得の実施
const response = UrlFetchApp.fetch(apiUrl, {
method: apiMethod,
headers: apiHeaders,
});
const apiResponse = response.getContentText();
const data_row = apiResponse.split("\n");//LF改行は\n
// データ検索でエラーが発生した場合、ダイアログを表示する
if (data_row.length < 6) {
ss.toast("統計データの取得でエラーが発生しました。:" +
data_row[1] + "," + data_row[2], "データ取得エラー", -1);
return;
}
// 取得したデータのシート書き出し
writeSearchData(ws, data_row);
//出力完了
ss.toast("統計データの出力が正常終了しました。:", "正常終了", 3);
} catch (error) {
ss.toast("統計データの出力が異常終了しました。:" + error.message, "異常終了", -1);
}
}
/**
* 【サブ】eStatAPIのデータ検索結果をスプレッドシートに出力する
* @param {SpreadseetApp.Sheet} ws 出力先ワークシート
* @param {Object[]} data_row データ検索結果の配列
*/
function writeSearchData(ws, data_row) {
let data_col = []; //data_rowを,で分割格納した変数
let start_row = 0; //タイトル行の行番号(行が"tab_code"で始まる)
let value_col = 0; //value列の列番号(タイトルが"value")
for (let cnt_row = 0; cnt_row < data_row.length; cnt_row++) {
//タイトル行
if (data_row[cnt_row].includes('"tab_code"')) {
start_row = cnt_row; //"tab_code"で始まる行はタイトル行であり行番号を格納
}
if (cnt_row >= start_row) { //タイトル行以降を書き出し対象とする
data_col = data_row[cnt_row].split(',');
for (let cnt_col = 0; cnt_col < data_col.length; cnt_col++) {
if (cnt_row == start_row && data_col[cnt_col] == '"value"') {
value_col = cnt_col; //タイトル行かつvalue列の場合、列番号を格納
}
if (cnt_row > start_row && cnt_col == value_col) { //データ行かつvalue列の場合
if (data_col[cnt_col] != '"-"') { //値が"-"でない場合、"を除き出力
ws.getRange(cnt_row - start_row + 1, cnt_col + 1).
setValue(data_col[cnt_col].replaceAll('"', ''));
}
} else {//データ行でないかvalue列でない場合、先頭に'を付加し、"を除き出力
ws.getRange(cnt_row - start_row + 1, cnt_col + 1).
setValue("'" + data_col[cnt_col].replaceAll('"', ''));
}
}//end for cnt_col
}
}//end for cnt_row
}
動作確認
[eStat_Search_Input]シートのB1セルにAPI実行用URL (取得方法は以前の記事の図表2-10)を入力し、[検索実行]ボタンを選択します。
右下の処理終了のトースト通知が表示されるのを待ちます。
[eStat_Search_Output]シートを開いて結果を確認します。
おわりに
スクリプトプロパティを使用したため、Google Books APIsのデータ検索(前の記事)よりわずかに複雑になりましたが、それでもとても簡単に実装できます。
また、 Excel Online × Office Scriptの場合と違い、GASはサーバーサイドで実行されるため、API実行時にfetchエラーも発生しません。
JavaScriptを少し知っていれば、簡単に実装できるかと思います。
当ブログ内の関連記事
