Google Apps ScriptでBoard APIを使ってデータの入出力してみた【GAS】
2019年くらいにVBAで案件・請求管理サービスであるBoardのAPIを叩いて案件データを取得したことがありました。VBAで捌くにはなかなか骨が折れた記憶があります。
時は流れてまたちょっと使う機会があったので、今回はおなじみのGoogle Apps Scriptでやってみようと請求データの取得を行ってみました。ドキュメントもAPIも対して中身は変わっていないようだったので、チャレンジです。
リンク
今回利用するツール等
- Board請求データ取得 - Google Spreadsheet
- Board API Reference
基本的な流れはVBAの時と全く同じです。ただJSONの扱いの大変なVBAと比較して、JavaScriptで走査する点はとても楽ちんです。しかし、今回は前回の案件データではなく請求データ。期間指定する部分があり、ページネーション処理も当然あります。
いくつか注意ポイントもあります。
事前準備
APIトークンとAPIキーの取得
Board APIはよく見かけるOAuth2.0認証を利用した方法ではなく、あらかじめ用意しておいたAPIトークンとAPIキーの２つを送り付けることでAPIの操作を行う事が出来ます。以下にその取得手順を示します。
- Boardにログインする
- 右上の歯車アイコンをクリックして「API設定」を開く
- APIキーが表示されているので控えておく
- 新規トークン生成をクリックして、新しいAPIトークンを作成します。
- 用途説明を入力、このトークンへ与える権限をチェックして、登録ボタンを押します。権限は必要最低限にて。自分の場合、「案件リストの取得」「案件の取得」「請求リストの取得」の3つとしてます。
- APIトークンは設定時の1度しか表示されませんので注意が必要です。この２つを控えておきます。
図：Board API Token取得
スクリプトプロパティに格納
取得したAPIキーおよびAPIトークンは以下のスクリプトプロパティに格納しておき、GAS側で呼び出して取り出します。
- boardapikey : ここにAPIキーを格納する
- boardapitoken : ここにAPIトークンを格納する
図：スクリプトプロパティに格納しておく
リクエスト制限
API自体追加料金なしで利用は可能ですが、連続リクエストには制限があります。この制限を理解せずにコードを組んでしまうと、場合によっては制限を超えた分について処理されずにエラーとなってしまいます。上手に制限を回避するようにコードを書く必要があります。主な注意すべきリクエスト制限は以下の通りです。
- １日3000リクエストまで。なるべく一発で処理できるようにAPIの利用回数には気を付ける必要があります。
- 3リクエスト/秒まで。但し、100リクエストまでは制限なく呼び出し可能。ただし、この100は全部で100であり、１回あたりではありません。よって、頻繁に3リクエスト/秒を超えるリクエストを投げてしまうと、この100を消費してしまいます。なるべく、ウェイトで調整して超えないようにしましょう。
- 上記の100リクエストは一定期間利用されないと100まで補充される仕組みです。
- リクエスト基準の1日は、UTC基準でありJSTでないので注意。
- 制限をオーバーすると、秒間リクエストを超えると429 - Too Many Requestsが返ってきます。１日の制限リクエストを超えるとLimit Exceededが返ってきます。エラー処理も実装しておくと良いでしょう。
- データの取得などで、１回のリクエストで取得できる件数は最大100件まで。それを超えた場合、ページネーションされ、ページ指定を繰り返してデータを取得する必要があります。なるべく取得する範囲を絞ってリクエストを投げましょう。
ソースコード
Board APIはGETでリクエストするので、URLに対してパラメータをつなげてリクエストする必要があります。この時に必要な最低限のパラメータが
- invoice_date_gteq : 開始日付。yyyy-mm-ddの形式で指定する
- invoice_date_lteq : 終了日付。同じ形式で指定する
- per_page : 1ページあたりのレコード数。最大100件までなので、100を指定しておく。
- page : 開始ページ番号。1固定指定する。
リクエスト時のオプションでx-api-keyとしてAPIキーを割り当て、Authorization BearerにAPIトークンを指定します。
一番の重要ポイントは最初のリクエスト時の返り値のヘッダにx-total-countという総レコード数を取得しておくこと。これをper_pageで割った数値をもってループの上限回数とし、回します。1リクエスト1秒のウェイトを入れて429エラーを回避する。
最後に返り値のレコードの中から必要な項目だけを配列にして、スプシに洗替えで書き出しをしています。この時、管理番号の無いものは今回は処理をスルーするようにしています。
//Board APIを叩いて請求データを取得する
function getBoardApiSeikyu(){
//APIkeyとTokenを取得
const prop = PropertiesService.getScriptProperties();
const apikey = prop.getProperty("boardapikey");
const apitoken = prop.getProperty("boardapitoken");
//key, tokenの設定チェック
if (!apitoken || !apikey) {
console.log('エラー: APIトークンまたはAPIキーが設定されていません。');
console.log('スクリプトプロパティに「boardapitoken」と「boardapikey」を設定してください。');
return false;
}
//取得期間の設定
const fromDate = '2024-01-01';
const today = new Date();
const toDate = Utilities.formatDate(today, 'JST', 'yyyy-MM-dd');
//リクエストエンドポイントを構築
const baseUrl = boardpoint + "invoices";
//取得用の変数
let allInvoices = []; //請求データ格納用
let page = 1; //現在のページ
let totalPages = 1; //初期化の為の仮の値
const perPage = 100; //1リクエストあたり取得する件数
//ページネーションを考慮して全データを取得
try {
do {
//GETクエリパラメータを構築
const queryParams = {
invoice_date_gteq: fromDate,
invoice_date_lteq: toDate,
per_page: perPage,
page: page
};
//オブジェクトの各キーと値をエンコードしながらクエリ文字列を生成
const queryString = Object.keys(queryParams)
.map(key => `${encodeURIComponent(key)}=${encodeURIComponent(queryParams[key])}`)
.join('&');
//リクエストURLを構築
const url = `${baseUrl}?${queryString}`;
//リクエストオプション
const options = {
'method': 'get',
'headers': {
'Authorization': `Bearer ${apitoken}`,
'x-api-key': apikey,
'Content-Type': 'application/json'
},
'muteHttpExceptions': true
};
//HTTPリクエストを実行
console.log(`リクエストを送信中... Page: ${page}`);
const response = UrlFetchApp.fetch(url, options);
const responseCode = response.getResponseCode();
//ステータス200なら続けて値を取得
if (responseCode === 200) {
//レスポンスデータを取り出し
const data = JSON.parse(response.getContentText());
//ヘッダから総ページ数を取得する
if (page === 1) {
//レスポンスヘッダを取り出す
const headers = response.getAllHeaders();
//レコード数を取得する
const totalCount = parseInt(headers['x-total-count'], 10) || 0;
//totalPagesを算出する
if (totalCount > 0) {
// 全件数を1ページあたりの件数で割り、小数点以下を切り上げて総ページ数を計算
totalPages = Math.ceil(totalCount / perPage);
} else {
totalPages = 0; // データが0件ならループは1回で終了
}
console.log(`総件数: ${totalCount}, 総ページ数: ${totalPages}`);
}
//配列にデータを結合
if (Array.isArray(data) && data.length > 0) {
//配列データを結合する
allInvoices = allInvoices.concat(data);
console.log(`レコード数合計: ${allInvoices.length}`)
}
//ページカウントアップ
page++;
} else {
console.log(`エラーが発生しました。 Status: ${responseCode}, Body: ${response.getContentText()}`);
return false;
}
//1ページリクエスト1秒のウェイトを入れる
if (page <= totalPages) {
Utilities.sleep(1000);
}
} while (page <= totalPages); //ページネーションが残ってる場合は処理を継続
//ページ取得完了
console.log(`データの取得が完了しました。合計 ${allInvoices.length} 件の請求データを取得しました。`);
//書き出しデータのチェック
if (allInvoices.length > 0) {
//シート書き出し指示
let result = writeDataToSheet(allInvoices);
//終了処理
return result;
} else {
console.log('書き出すデータがありませんでした。');
return false;
}
} catch (e) {
console.log(`予期せぬエラーが発生しました: ${e.message}`);
return false;
}
}
//スプシに書き出し
function writeDataToSheet(invoices) {
try {
//取得するシートとシートクリアの実行
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("board");
sheet.getRange("A2:S").clearContent();
//書き出し用配列を用意
const dataForSheet = []; // 書き出し用の空の配列を準備
for (let i = 0; i < invoices.length; i++) {
//レコードを一個取り出す
const inv = invoices[i]; // 現在処理中の請求書データを取得
//管理番号が未入力のものがスルーする
if(inv.management_no == "" || inv.management_no == "タスク登録なし" || inv.management_no == undefined){
continue;
}
//1行分のデータを配列として作成
const rowData = [
Utilities.getUuid(),
inv.management_no,
inv.project_id,
inv.id,
inv.name,
inv.client.name,
inv.total,
inv.tax,
Number(inv.total) + Number(inv.tax), //合計金額算出
inv.invoice_date,
inv.paid_date,
];
//書き出し用配列にpush
dataForSheet.push(rowData); // 書き出し用の配列に行データを追加
}
//シートへ書き出し
let lastColumn = dataForSheet[0].length; //カラムの数を取得する
let lastRow = dataForSheet.length; //行の数を取得する
sheet.getRange(2,1,lastRow,lastColumn).setValues(dataForSheet);
console.log(`${dataForSheet.length} 件のデータをシートに書き出しました。`);
return true;
} catch (e) {
console.log(`シートへの書き込み中にエラーが発生しました: ${e.message}`);
return false;
}
}
