[GAS][会計freee]スプレッドシートへ出力する関数

どうも。つじけ（tsujikenzo）です。このシリーズでは 「会計freeeで100件以上の取引を取得しよう」 ということで全3回でお送りします。今日は3回目です。

前回のおさらい
1. アジェンダ
レスポンスの受け取りと2次元配列化
見出し行の設定
1. 見出し行の生成
スプレッドシートへの出力と重複削除
1. 重複削除
2. 完成系
まとめ
このシリーズの目次

前回のおさらい

前回は、「レコードの開始値（オフセット値）を操作しよう」 ということで「期間範囲」「最大取得レコード数」「オフセット値」をお届けしました。

[GAS][会計freee]レコードの開始値（オフセット値）を操作しよう

どうも。つじけ（tsujikenzo）です。このシリーズでは「会計freeeで100件以上の取引を取得しよう」ということで全3回でお送りします。今日は2回目です。前回のおさらい前回は、「会計freeeで100件以上の取引を取得しよう...

今回は、最終回で 「スプレッドシートへ出力する関数」 をお届けします。

アジェンダ

レスポンスの受け取りと2次元配列化
見出し行の設定
スプレッドシートへの出力と重複削除

レスポンスの受け取りと2次元配列化

スプレッドシートへの出力は、関数を分けました。まず、引数にレスポンスを渡します。

setAllDeals_(response);

受け取ったレスポンスをJSONオブジェクトに変換し、1行ずつレコードを分解するサブ関数getFullArray_()を使って2次元配列を整形します。

//JSONオブジェクトに変換
const jsonObj = JSON.parse(response);

//取引（収入／支出）の配列を取得する
const deals = jsonObj['deals'];

//全ての項目を2次元配列化する（サブ関数を使用）
const values = deals.map(deal => getFullArray_(deal)).flat();

見出し行の設定

どんなデータにも、見出し行となる1行目は必要だと思います。セルの中身が何を示しているのか、分かりやすく管理するためです。

あらかじめ、シートの1行目に手で入力しておいて、2行目以降にデータを流し込むことが多いと思います。

しかし、列数が変更になったり順番が変わったりすると、管理が大変です。できれば 取得したレコードから見出し行を作成したい ものです。

注意が必要な点は、プロパティを見出し行に使用するさいは、プロパティだけでは、名前が重複してしまう点です。

なので、[details]と[payments]については、見出しにそれぞれのプロパティを付与します。

見出し行の生成

[deals直下]、[detailsプロパティ]、[paymentsプロパティ]の3種類なので、ベタ書きしました。

//見出しを先頭に追加
const upperPropertyKeys = Object.keys(deals[0]);

const detailPropertyKeys = Object.keys(deals[0]['details'][0]);
const detailKeys = detailPropertyKeys.map(detailPropertyKey => 'details_' + detailPropertyKey);

const paymentPropertyKeys = Object.keys(deals[0]['payments'][0]);
const paymentKeys = paymentPropertyKeys.map(paymentPropertyKey => 'payments_' + paymentPropertyKey);

最後はスプレッド構文で、headersという2次元配列を作成したものを、valuesの先頭行に足しています。

const headers = [...upperPropertyKeys, ...detailKeys, ...paymentKeys];
values.unshift(headers);

繰り返し処理になるので、必要に応じて、関数化した方がいいかもしれませんね。

スプレッドシートへの出力と重複削除

スプレッドシートへ出力をするときは、まず、貼り付け位置の基点となるセル位置を、getLastRow()メソッドで取得します。

そして下記のように、常に 最終行の次の行に貼り付ける ようにします。

//スプレッドシートシートへ出力
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('シート1');

const lastRowNext = sheet.getLastRow() + 1;
sheet.getRange(lastRowNext, 1, values.length, values[0].length).setValues(values);

valuesには、見出し行も含まれていますので、毎回、見出し行も貼り付けてしまいますが、レコードの重複削除 をしますので問題ありません。

重複削除

重複削除は、スプレッドシートのメソッドを使う のが最もコスパがいいと思います。（「2次元配列の段階で重複削除する」「集合論に基づいてCRUD操作に準拠する」など、さまざまな考察をした後の個人的な感想です）

スプレッドシートの重複削除メソッドは2種類しかありません。2種類を見比べると、どのようなロジックでレコードの重複を判定（および削除）しているのか伝わると思います。

//引数無し。行全体を比較する
removeDuplicates()

//使用例
range.removeDuplicates()

//引数に、重複を検討するインデックスを指定する
removeDuplicates(columnsToCompare)

//使用例
range.removeDuplicates([2,4])

完成系

すべてをつなげた完成系のコードはこちらです。

/**
* 取引（収入／支出）一覧をスプレッドシートへ出力する関数
*
* @param {object} レスポンス
* @return none
*/
function setAllDeals_(response) {

//レスポンスをJSONオブジェクトに変換
const jsonObj = JSON.parse(response);

//取引（収入／支出）の配列を取得する
const deals = jsonObj['deals'];

//全ての項目を2次元配列化する（サブ関数を使用）
const values = deals.map(deal => getFullArray_(deal)).flat();

//見出しを先頭に追加
const upperPropertyKeys = Object.keys(deals[0]);

const detailPropertyKeys = Object.keys(deals[0]['details'][0]);
const detailKeys = detailPropertyKeys.map(detailPropertyKey => 'details_' + detailPropertyKey);

const paymentPropertyKeys = Object.keys(deals[0]['payments'][0]);
const paymentKeys = paymentPropertyKeys.map(paymentPropertyKey => 'payments_' + paymentPropertyKey);

const headers = [...upperPropertyKeys, ...detailKeys, ...paymentKeys];
values.unshift(headers);

//スプレッドシートシートへ出力
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('シート1');

const lastRowNext = sheet.getLastRow() + 1;
sheet.getRange(lastRowNext, 1, values.length, values[0].length).setValues(values);

//重複削除
sheet.getDataRange().removeDuplicates();
}