Google Apps ScriptでAPI呼出しによるデータ検索を実施し、Googleスプレッドシートに反映する例を紹介します。APIはクラウド会計システム「freee会計」が提供しているfreee APIを使用します。
- 操作環境：
- OS：Windows 11
- Webブラウザー：Edge
- 使用プラン：Google Workspace Business Starter(個人プランも可)、 freee会計スタンダードプラン(スタータープランも可)
- 使用APIについて
- 検索結果反映用Googleスプレッドシート用意
- 検索結果反映用Google Apps Script用意
- Google Apps Scriptによるデータ取得・加工実装
- 動作確認
- おわりに
使用APIについて
以前の記事でExcel VBAによるfreee会計APIを使用したデータ取得を行いました。
今回はこれとほぼ同じ処理をGoogle Apps ScriptとGoogleスプレッドシートで実装してみます。
freee APIの利用開始手順は以前の記事の工程2、freee会計APIのアプリ設定手順は以前の記事の工程3、PostmanによるAPI動作確認の紹介は以前の記事の工程4をご参照ください。
以降、この記事ではGoogle App ScriptをGASと表記します。
検索結果反映用Googleスプレッドシート用意
この例では、検索結果出力用のGoogleスプレッドシートをGoogleドライブのマイドライブに用意します([新規＞Googleスプレッドシート＞空白のスプレッドシート]から新規作成できます)。
ファイルを開き、出力先シートとして[Search]、[deals]、[partners]、[account_items]、[taxes]、[items]、[sections]シートを用意します。各セルは値なしで問題ありません。
*[deals]～[sections]シートに取引データや各マスターのデータを出力し、それらを結合した結果を[Search]シートに出力します
出力元シートとして[Main]シートを用意します。
B1,B2セルを発生日の開始・終了日欄とします。
検索実行ボタンは図形を挿入して工程3のスクリプトを割り当てる形で用意します。
上メニューの[挿入＞図形描画]を選択します。
[図形描画]画面が表示されたら、図形アイコンを選択し、[図形]からボタンに見立てた図形を選択します。
挿入した図形にテキストを追加するには、図形を右クリックして[テキストを編集する]を選択します。
「検索実行」と入力します。
[図形描画]画面右上の[保存して閉じる]を選択します。
スプレッドシート内に挿入されたボタンを選択し、三点マークが表示されたらこれを選択し、[スクリプトを割り当て]を選択します。
工程3で作成するスクリプトの関数名を指定し、[確定]を選択します。
検索結果反映用Google Apps Script用意
上メニューの[拡張機能＞Apps Script]を選択します。
画面右側のスクリプトエディターにスクリプトを記述していきます(スクリプトの内容は工程4で紹介します)。
上メニューのプルダウンから実行したいスクリプトを選択し、[実行]を選択すると、動作確認できます。行番号の左側を選択すると、ブレークポイントを設定でき、デバッグもできます。
APIキーのような、スクリプトやGoogleスプレッドシート内に埋め込みたくないセキュアな値は、スクリプトプロパティに設定します。
左端の歯車アイコン(プロジェクトの設定)を選択し、画面の下の方にある[スクリプト プロパティを追加]を選択します。
freeeAPIのトークン取得等で使用する項目(取得方法は以前の記事の工程3参照)を定義し、[スクリプトプロパティを保存]を選択します。
Google Apps Scriptによるデータ取得・加工実装
関数がやや多いため、呼び出し構造を図示します。
スクリプトの基本的な設計は以前紹介したExcel VBA版とほぼ同じですので、そちらも参考にしてください。異なる点を以下に示します。
- Googleスプレッドシート上ではダイアログボックスを使用できないため、トースト通知を表示する設計とした(67,70行目)
- company_id,access_tokenなどは、Googleスプレッドシート上ではなく、図表3-3で定義したスクリプトプロパティから取得している(93,99,166,171,210,216,316-318,320行目)
- Googleスプレッドシート上ではテキストボックスを使用できないため、B1,B2セルに入力された発生日の開始・終了日を取得する設計とした(167-168,211-212行目)
- 更新後のaccess_token,refresh_tokenは、 Googleスプレッドシート上ではなく、図表3-3で定義したスクリプトプロパティに反映している(333-334行目)
- VBAと異なり数値変数の参照渡し(変数を関数の引数として渡して値を更新し、呼び出し元でも更新後の値を使用する)が不可のため、returnで値を返している(399行目)
- GASではGoogleスプレッドシートをデータベースのテーブルと見立てて複雑なSELECT文を発行することが難しいため、Map型オブジェクトを用いてデータ結合する形とした(234-290,403-431行目)
メイン関数
/**
* 【メイン】今年一年ボタン:入力欄の発生日(開始/終了)に今年一年を設定
*/
function setThisPastYear() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const today = new Date();
const current_year = today.getFullYear();
ss.getSheetByName("Main").getRange("B1").setValue(current_year + "-01-01");
ss.getSheetByName("Main").getRange("B2").setValue(current_year + "-12-31");
}
/**
* 【メイン】検索実行ボタン：freee会計の取引データを取得しSearchシートに出力
*/
function getfreeeAPI() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
let keys = [];
try {
//取引先マスタ出力(20項目(zipcode)以降はaddress_attributes等の子項目)
keys = ["id", "code", "company_id", "name", "update_date", "available",
"shortcut1", "shortcut2", "org_code", "country_code", "long_name",
"name_kana", "default_title", "phone", "contact_name", "email",
"payer_walletable_id", "transfer_fee_handling_side", "qualified_invoice_issuer",
"invoice_registration_number",
"zipcode", "prefecture_code", "street_name1", "street_name2",
"bank_name", "bank_name_kana", "bank_code", "branch_name", "branch_kana",
"branch_code", "account_type", "account_number", "account_name",
"long_account_name"];
writeMstData(ss, keys, "partners");
//勘定科目マスタ出力(categoriesは複数値)
keys = ["id", "name", "tax_code", "shortcut", "shortcut_num",
"code", "default_tax_code", "account_category", "account_category_id", "categories",
"available", "walletable_id", "group_name", "group_id", "corresponding_income_name",
"corresponding_income_id", "corresponding_expense_name", "corresponding_expense_id"];
writeMstData(ss, keys, "account_items");
//税区分マスタ出力
keys = ["code", "name", "name_ja"];
writeMstData(ss, keys, "taxes");
//品目マスタ出力
keys = ["id", "company_id", "name", "update_date", "available",
"shortcut1", "shortcut2", "code"];
writeMstData(ss, keys, "items");
//部門マスタ出力
keys = ["id", "company_id", "name", "long_name", "shortcut1", "shortcut2"];
writeMstData(ss, keys, "sections");
//取引データ出力(13項目(id)以降はdetailsの子項目)
keys = ["id", "company_id", "issue_date", "due_date", "amount",
"due_amount", "type", "partner_id", "partner_code", "ref_number",
"status", "deal_origin_name", "id", "account_item_id", "tax_code",
"item_id", "section_id", "amount", "vat", "description", "entry_side"];
writeDealsData(ss, keys, "deals");
//取引データと各マスタを結合して出力(取引先&品目&部門は未設定時を考慮)
writeSearchData(ss);
//出力完了
ss.toast("取引データの出力が正常終了しました。:", "正常終了", 3);
} catch (error) {
ss.toast("取引データの出力が異常終了しました。:" + error.message, "異常終了", -1);
}
}
API実行関数
/**
* 【API実行】APIから各マスタデータを取得し、Excelシートに出力する
* @param {SpreadsheetApp.Sheet} ss 出力先ワークシート
* @param {string[]} keys 出力データ項目名
* @param {string} label データ識別ラベル
*/
function writeMstData(ss, keys, label) {
//トークン更新
refreshTokens();
//データ検索用の設定
let apiMethod = "GET";
let apiUrl = "https://api.freee.co.jp/api/1/" + label;
if (label == "taxes") {
apiUrl = apiUrl + "/codes";
}
let apiParams = "?company_id=" + PropertiesService.
getScriptProperties().getProperty("company_id");
if (label == "partners" || label == "items") {
apiParams = apiParams + "&limit=3000";
}
apiHeaders = {
"Authorization":"Bearer " + PropertiesService.
getScriptProperties().getProperty("access_token")
};
//ワークシートの設定&初期化
let ws_data = ss.getSheetByName(label);
ws_data.clear();
//データ検索
let response = UrlFetchApp.fetch(apiUrl + apiParams, {
method: apiMethod,
headers: apiHeaders,
});
let apiJson = JSON.parse(response.getContentText());
//データ出力(タイトル行)
for (let cnt_col = 0; cnt_col < keys.length; cnt_col++) {
ws_data.getRange(1, cnt_col + 1).setValue(keys[cnt_col]);
}
//データ出力(データ行)
for (let cnt_row = 0; cnt_row < apiJson[label].length; cnt_row++) {
for (cnt_col = 0; cnt_col < keys.length; cnt_col++) {
//account_itemsのcategories(複数値項目)はカンマ区切りデータを出力
if (label == "account_items" && keys[cnt_col] == "categories") {
ws_data.getRange(cnt_row + 2, cnt_col + 1).setValue(
concatString(apiJson, label, "categories", cnt_row, cnt_col));
//partnersの20-24項目はaddress_attributesのサブ項目を出力
} else if (label == "partners" && 19 <= cnt_col && cnt_col <= 23) {
ws_data.getRange(cnt_row + 2, cnt_col + 1).setValue(
apiJson[label][cnt_row]["address_attributes"][keys[cnt_col]]);
//partnersの25項目以降はpartner_bank_account_attributesのサブ項目を出力
} else if (label == "partners" && 24 <= cnt_col) {
ws_data.getRange(cnt_row + 2, cnt_col + 1).setValue(
apiJson[label][cnt_row]["partner_bank_account_attributes"][keys[cnt_col]]);
//その他の項目を出力
} else {
ws_data.getRange(cnt_row + 2, cnt_col + 1).setValue(
apiJson[label][cnt_row][keys[cnt_col]]);
} //end if
}//end for cnt_col
}//end for cnt_row
}
/**
* 【API実行】APIから取引データを取得し、Excelシートに出力する
* @param {SpreadsheetApp.Sheet} ss 出力先ワークシート
* @param {string[]} keys 出力データ項目名
* @param {string} label データ識別ラベル
*/
function writeDealsData(ss, keys, label) {
let ws_main = ss.getSheetByName("Main");
let cnt_res = 0; //出力するExcelの行数カウンタ
//トークン更新
refreshTokens();
//データ検索用の設定
let apiMethod = "GET";
let apiUrl = "https://api.freee.co.jp/api/1/" + label;
let apiParams = "?company_id=" + PropertiesService.
getScriptProperties().getProperty("company_id") + "&limit=100" +
"&start_issue_date=" + Utilities.formatDate(ws_main.getRange(1, 2).getValue(), 'JST','yyyy-MM-dd') +
"&end_issue_date=" + Utilities.formatDate(ws_main.getRange(2, 2).getValue(), 'JST', 'yyyy-MM-dd');
let apiHeaders = {
"Authorization":"Bearer " + PropertiesService.
getScriptProperties().getProperty("access_token")
};
//ワークシートの設定&初期化
let ws_data = ss.getSheetByName(label);
ws_data.clear();
//データ出力(タイトル行)
for (let cnt_col = 0; cnt_col < keys.length; cnt_col++) {
if (cnt_col <= 11) { //12項目以下は基本情報
ws_data.getRange(1, cnt_col + 1).setValue(keys[cnt_col]);
} else { //13項目以降は明細情報
ws_data.getRange(1, cnt_col + 1).setValue("dtl_" + keys[cnt_col]);
}
}//end for cnt_col
//データ検索(1ページ目)
let response = UrlFetchApp.fetch(apiUrl + apiParams, {
method: apiMethod,
headers: apiHeaders,
});
let apiJson = JSON.parse(response.getContentText());
//データ出力(データ行:1ページ目)
cnt_res = writeDealsDataValueAndIncrement(apiJson, ss, label, keys, cnt_res);
//ページ数のカウント(ページ跨ぎ考慮)
const max_row = Number(apiJson.meta.total_count); //全データ行数
const max_page = Math.ceil(max_row / 100); //max_row÷ページ上限件数(100)
//2ページ目以降の処理
if (max_row > 100) {
for (let cnt_page = 2; cnt_page <= max_page; cnt_page++) {
//トークン更新
refreshTokens();
//データ検索用の設定(apiMethodとapiUrlは1ページ目と同じため割愛)
apiParams = "?company_id=" + PropertiesService.
getScriptProperties().getProperty("company_id") + "&limit=100" +
"&start_issue_date=" + Utilities.formatDate(ws_main.getRange(1, 2).getValue(), 'JST','yyyy-MM-dd') +
"&end_issue_date=" + Utilities.formatDate(ws_main.getRange(2, 2).getValue(), 'JST', 'yyyy-MM-dd') +
"&offset=" + String((cnt_page - 1) * 100);
apiHeaders = {
"Authorization":"Bearer " + PropertiesService.
getScriptProperties().getProperty("access_token")
};
//データ検索
response = UrlFetchApp.fetch(apiUrl + apiParams, {
method: apiMethod,
headers: apiHeaders,
});
apiJson = JSON.parse(response.getContentText());
//データ出力
cnt_res = writeDealsDataValueAndIncrement(apiJson, ss, label, keys, cnt_res);
} //end for cnt_page
}//end if
}
データ結合関数
/**
* 【データ結合】各シートのデータを結合し、Searchシートに出力する
* @param {SpreadsheetApp.Sheet} ss 出力先ワークシート
*/
function writeSearchData(ss) {
const keys = ["取引ID", "発生日", "支払期日", "収支", "取引先ID",
"取引先CD", "取引先名", "決済状況", "取引明細ID", "勘定科目ID",
"勘定科目", "科目分類", "税区分CD", "税区分", "品目ID", "品目",
"部門ID", "部門", "税込金額", "消費税", "備考", "貸借"];
//出力先ワークシートの設定&初期化
const ws_search = ss.getSheetByName("Search");
ws_search.clear();
//各シートのデータを一括取得
const dt_deals = ss.getSheetByName("deals").getDataRange().getValues();
const dt_partners = ss.getSheetByName("partners").getDataRange().getValues();
const dt_items = ss.getSheetByName("items").getDataRange().getValues();
const dt_sections = ss.getSheetByName("sections").getDataRange().getValues();
const dt_accounts = ss.getSheetByName("account_items").getDataRange().getValues();
const dt_taxes = ss.getSheetByName("taxes").getDataRange().getValues();
//検索用のMapを作成(0列目をIDとして紐付け)
const mp_partner = createMap(dt_partners, 0);
const mp_item = createMap(dt_items, 0);
const mp_section = createMap(dt_sections, 0);
const mp_account = createMap(dt_accounts, 0);
const mp_tax = createMap(dt_taxes, 0);
//データ結合処理(forループの開始1はタイトル行を除くため)
const result = [];
for (let cnt_row = 1; cnt_row < dt_deals.length; cnt_row++) {
const dl = dt_deals[cnt_row];
//データ結合:勘定科目,税区分(INNER JOINに相当)
const acc = mp_account.get(String(dl[13]).trim());
const tax = mp_tax.get(String(dl[14]).trim());
//データ結合:取引先,品目,部門(LEFT JOINに相当:nullの場合は○○なしを渡す)
const prt = mp_partner.get(String(dl[7]).trim()) || [null, "取引先CDなし", null, "取引先名なし"];
const itm = mp_item.get(String(dl[15]).trim()) || [null, "品目なし"];
const sec = mp_section.get(String(dl[16]).trim()) || [null, "部門なし"];
//データ結合結果の配列を作成
result.push([
dl[0], dl[2], dl[3], dl[6], // 取引ID, 発生日, 支払期日, 収支
dl[7], prt[1], prt[3], // 取引先ID, 取引先CD, 取引先名
dl[10], dl[12], dl[13], // 決済状況, 取引明細ID, 勘定科目ID
acc[1], acc[7], // 勘定科目, 科目分類
dl[14], tax[1], // 税区分CD, 税区分
dl[15], itm[2], // 品目ID, 品目
dl[16], sec[2], // 部門ID, 部門
dl[17], dl[18], dl[19], dl[20]// 税込金額, 消費税, 備考, 貸借
]);
}
//Searchシートに出力
if (result.length > 0) {
ws_search.getRange(1, 1, 1, keys.length).setValues([keys]);//タイトル行
ws_search.getRange(2, 1, result.length, result[0].length).setValues(result);//データ行
}
//Searchシートのソート
ws_search.getRange(2, 1, ws_search.getLastRow() - 1, ws_search.getLastColumn()).sort([
{column: 2, ascending: true}, //キー1：B列 (発生日) 昇順
{column: 1, ascending: true}, //キー2：A列 (取引ID) 昇順
{column: 9, ascending: true} //キー3：I列 (取引明細ID) 昇順
]);
}
サブ関数
/**
* 【サブ】freeeのトークンを更新する
*/
function refreshTokens() {
//アクセストークン取得用の設定
let apiMethod = "POST"
let apiUrl = "https://accounts.secure.freee.co.jp/public_api/token"
let apiParams = "?grant_type=refresh_token" +
"&redirect_uri=" + PropertiesService.getScriptProperties().getProperty("callback_url") +
"&client_id=" + PropertiesService.getScriptProperties().getProperty("client_id") +
"&client_secret=" + PropertiesService.getScriptProperties().getProperty("client_secret");
apiParams = apiParams + "&refresh_token=" +
PropertiesService.getScriptProperties().getProperty("refresh_token");
let apiHeaders = {
"Content-Type":"application/x-www-form-urlencoded"
};
//アクセストークン取得
let response = UrlFetchApp.fetch(apiUrl + apiParams, {
method: apiMethod,
headers: apiHeaders,
});
let apiJson = JSON.parse(response.getContentText());
//アクセストークンをスクリプトプロパティに設定
PropertiesService.getScriptProperties().setProperty("access_token", apiJson.access_token);
PropertiesService.getScriptProperties().setProperty("refresh_token", apiJson.refresh_token);
}
/**
* 【サブ】複数値項目をカンマ区切りデータで返す
* @param {Object} apiJson APIデータ検索結果
* @param {string} key 出力データ項目名
* @param {string} label データ識別ラベル
* @param {number} cnt_row データ出力行
* @return {string} tmp_dtl カンマ区切りデータ
*/
function concatString(apiJson, label, key, cnt_row) {
tmp_dtl = "";
for (let cnt_dtl = 0; cnt_dtl < apiJson[label][cnt_row][key].length; cnt_dtl++) {
if (cnt_dtl > 0) {
tmp_dtl = tmp_dtl + ",";
} else {
tmp_dtl = "";
}//end if
tmp_dtl = tmp_dtl + apiJson[label][cnt_row][key][cnt_dtl];
} //end for cnt_dtl
return tmp_dtl;
}
/**
* 【サブ】取引データの明細部分を出力し、出力行カウンタcnt_resをインクリメントして返す
* @param {Object} apiJson APIデータ検索結果
* @param {SpreadsheetApp.Sheet} ss 出力先ワークシート
* @param {string} label データ識別ラベル
* @param {string[]} keys 出力データ項目名
* @param {number} cnt_res データ出力行
* @return {number} cnt_res データ出力行(更新後)
*/
function writeDealsDataValueAndIncrement(apiJson, ss, label, keys, cnt_res) {
let ws_data = ss.getSheetByName(label);
for (let cnt_row = 0; cnt_row < apiJson[label].length; cnt_row++) {
for (let cnt_col = 0; cnt_col < keys.length; cnt_col++) {
if (cnt_col < 12) { //12項目までは基本情報
ws_data.getRange(cnt_res + 2, cnt_col + 1).
setValue(apiJson[label][cnt_row][keys[cnt_col]]);
} else { //13項目以降は明細情報
for (let cnt_dtl = 0; cnt_dtl < apiJson[label][cnt_row]["details"].length; cnt_dtl++) {
ws_data.getRange(cnt_res + 2 + cnt_dtl, cnt_col + 1).setValue(
apiJson[label][cnt_row]["details"][cnt_dtl][keys[cnt_col]]);
//複数明細存在し明細が2行目以降の場合、前行の基本情報をコピペする
if (cnt_col == 12 && apiJson[label][cnt_row]["details"].length > 1 && cnt_dtl > 0) {
let copy_range = ws_data.getRange(
cnt_res + 1 + cnt_dtl, 1, cnt_res + 1 + cnt_dtl, 12);
let paste_range = ws_data.getRange(
cnt_res + 2 + cnt_dtl, 1, cnt_res + 2 + cnt_dtl, 12);
copy_range.copyTo(paste_range);
} //end if
} //end for cnt_dtl
}//end if
} //end for cnt_col
cnt_res = cnt_res + apiJson[label][cnt_row]["details"].length;
} //end for cnt_row
return cnt_res;//数値変数は参照渡し不可のため、returnで値を返す
}
/**
* 【サブ】データをMap型オブジェクトに格納して返す
* @param {string[][]} data - シートから取得した2次元配列
* @param {number} keyIdx - キーにする列番号 (0=A列, 1=B列...)
* (IDを文字列として扱うことで、数値/文字列の型違いによる不一致を防ぐ)
*/
function createMap(data, keyIdx) {
//データが空の場合は、空のMapを返す
if (data == null || data.length == 0) {
return new Map();
}
let map = new Map();
//データ格納(forループの開始1はタイトル行を除くため)
for (var cnt_row = 1; cnt_row < data.length; cnt_row++) {
let row = data[cnt_row];
//キーを取得し、念のため文字列にして空白を除去
let key = String(row[keyIdx]).trim();
//マップ型オブジェクトに格納
map.set(key, row);
}
return map;
}
動作確認
[Main]シートのB1,B2セルに発生日の開始・終了日を入力し([今年一年]ボタンを押して今年の日付期間を設定でも良い)、[検索実行]ボタンを選択します。
右下の処理終了のトースト通知が表示されるのを待ちます。
[Search]シートを開いて結果を確認します。
おわりに
GASではGoogleスプレッドシートをデータベースのテーブルと見立てて複雑なSELECT文を発行することが難しいため、その部分だけExcel VBA版と大きく変えました。SQLに慣れているとこれは扱いづらく感じるかもしれませんが、GASを用いたクラウドサービス間のデータ連携・データ結合への応用に期待できます。
当ブログ内の関連記事
