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;
}
}た
他のリストを取得する汎用関数
前述では請求のリストを取得することに特化していましたが、他の案件リストや顧客リストも取得が必要となったら、汎用のリスト取得関数があると便利です。
引数として、本体の関数からapikey ,apitoken, fromDate, toDateそしてendnameはエンドポイントの名前なので、前述で言えばinvoicesがそれに該当し、顧客ならclients, 案件ならprojectsが該当します。
デフォルトでBoard APIはリストのうち最小限の項目だけ返す仕様なので、全部欲しい場合にはクエリパラメータのresponse_groupでlargeを指定しておきます。
あとはページネーション処理をして配列で呼び出し元に返すだけ。
//Boardの各リスト取得を取得する
function getBoardList(apikey,apitoken,fromDate,toDate,endname){
//リクエストエンドポイントを構築
const baseUrl = boardpoint + endname;
//取得用の変数
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,
response_group : "large"
};
//オブジェクトの各キーと値をエンコードしながらクエリ文字列を生成
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); //ページネーションが残ってる場合は処理を継続
//値を返す
return allInvoices;
} catch (e) {
console.log(`予期せぬエラーが発生しました: ${e.message}`);
return false;
}
}
複数リストの値を結合したい場合
Boardのウェブ上から請求に関するCSVを取得すると、請求以外の項目もCSVに入っています。しかし、APIの場合には請求以外の項目は請求リストには入っていません。これらは案件リストや顧客リストに含まれており、APIで同じinvoices.csvを構築しようと思ったら、最低この3つのリストを特定のキーを元に結合して１つの配列データを作り上げる必要があります。
そうなるとロジックが複雑になるので、ここでは高速化するためにMap化しておき特定のIDの値で簡単に取り出せるようにしておくと便利です。listMapというMap化したものからlistMap.get(特定のID)で簡単に該当レコードを取り出せます。
また、データによっては値が空だと要素自体が存在しない事があり、参照時にエラーになってしまうのでオプショナルチェーンとNull合体演算子という特殊な数式を使うと良いです。対象の要素が無い場合には指定の文字列を返すようにしてくれます。
※但し、CSVにする場合には改行コードを除去したり、区切り記号がカンマのものは別の記号に置き換えるなどのデータクリーニングも必要です。
//BigQueryインポート用のcsv作成(3つのリストを引数で受け取る）
function writeDataToCsv(allInvoices,allCustomers,allCases){
//csv格納先を取得
let target = DriveApp.getFolderById(folderid);
//書き出し用の配列を用意
let array = [];
//Mapにしておくと他のリスト読み取りが高速化（id要素を基準にする）
const customerMap = new Map(
allCustomers.map(item => [item.id, item])
);
const projectMap = new Map(
allCases.map(item => [item.id, item])
);
for(let i = 0;i<allInvoices.length;i++){
//請求レコードを一個取り出す
let rec = allInvoices[i];
//管理番号が無いものは処理をスルーする
if(rec.management_no == "" || rec.management_no == "タスク登録なし" || rec.management_no == undefined){
continue;
}
//顧客IDとプロジェクトIDを取得する
let customerId = rec.client.id;
let projectId = rec.project_id;
//customerIdとprojectIdで対象レコードを取得する
const customer = customerMap.get(customerId);
const project = projectMap.get(projectId);
//タグのデータを区切り記号で結合する
let tags = rec.tags
if(tags == null || tags == undefined || tags == ""){
tags = "";
}else{
tags = tags.join("|");
}
//要素がNullかチェックする
let namedisp = project?.client?.name_disp ?? "";
let payment_period = project?.periodical_invoice_interval ?? "";
//社内メモで改行入力されてるケースがあるので対応する
let memo = project?.in_house_memo ?? "";
memo = memo.replace(/\r?\n/g, '|');
//一時配列を作成する
const temparr = [
//・・・・中略・・・・
]
//arrayに追加する
array.push(temparr);
}
//配列データをカンマ区切りへ変換
const csvContent = array.map(row => row.join(',')).join('\r\n');
//CSVのファイル名
let csvfilename = setBaseFileName();
//ドライブにファイルを生成する（Shift-JIS指定）
const blob = Utilities.newBlob("", "text/comma-separated-values", csvfilename).setDataFromString(csvContent, "Shift_JIS");
let targetfileid = target.createFile(blob).setName(csvfilename).getId();
//処理完了
return targetfileid;
}
//ベースファイル名
function setBaseFileName(){
//ベース名
let base = "invoices"
//日時データを取得する
const now = new Date();
const year = now.getFullYear();
const month = String(now.getMonth() + 1).padStart(2, '0'); // 月は0から始まるため+1
const day = String(now.getDate()).padStart(2, '0');
const hours = String(now.getHours()).padStart(2, '0');
const minutes = String(now.getMinutes()).padStart(2, '0');
const seconds = String(now.getSeconds()).padStart(2, '0');
//ファイル名を生成する
let filename = base + `${year}${month}${day}${hours}${minutes}${seconds}` + ".csv";
//ファイル名を返す
return filename;
}
