こんにちは！ITライターのもり(@moripro3)です！
クラウド会計ソフトfreeeのAPIを使って経理業務を自動化するネタをシリーズでお届けしています。
このシリーズでは、freeeとチャットワークを連携して「当日入金予定の取引一覧を自動でチャットワークに送信するツール」を作成します。
成果物のイメージがこちら！入金予定のある日に、自動でチャットワークに通知が届きます。
前回の記事では、freeeAPIのレスポンスをオブジェクトに変換して「必要なデータのみを取り出す方法」を紹介しました。
今回の記事では、オブジェクトからデータを取り出し、スプレッドシートに書き出す方法を紹介します。このようなイメージです。
シリーズを通してfreeeAPIを使いこなし、経理業務の自動化を進めていきましょう！
（この記事はfreee株式会社さまとのコラボ企画です。シリーズを通して、皆さんの働くの価値を上げられるようサポートしていきます！）
（前回のおさらい）オブジェクトからデータを取り出す
前回の記事では、freeeAPIからのレスポンス（JSON形式の文字列）を、GASで扱いやすい「オブジェクト」に変換し、ドット記法でオブジェクトからデータを取り出しました。
ドット記法とは、オブジェクトから値を取り出す記述方法です。オブジェクト名に対して、ドット(.)でプロパティ名を指定します。
取引1件目の金額を取り出す方法です。「dealsプロパティ（配列）」→「amountプロパティ」の順にアクセスします。
Logger.log(obj.deals[0].amount); //86400
このドット記法を今回の記事でも使っていきますよ！
スプレッドシートの準備
このツールでは、下記の6項目をスプレッドシートに書き出します。
- 発生日
- 支払期日
- 金額
- 取引先ID
- 勘定科目ID
- 品目ID
シートの1行目にヘッダーの項目名を入力します。ヘッダー入力は最初の1回のみです。スクリプト実行の都度、2行目以降を書き換えていきます。
また、この記事では、シート名を「取引」としておきます。
オブジェクトの受け渡しは必要データのみとする
この項では、取引データをスプレッドシートに書き出す関数を作成します。引数として取引データを受け取ります。
- 関数名：OutputDeals
- 引数：取引データ（オブジェクト）
OutputDeals関数は、取引データが存在する場合のみmain関数から呼び出されます。
main関数がOutputDeals関数を呼び出すときのポイントは、freeeAPIからのレスポンスを丸ごと渡すのではなく、dealsプロパティのみを渡すことです。
function main() {
//当日入金予定の取引一覧を取得する
var response = getDeals();
//JSON形式の文字列をオブジェクトに変換する
var obj = JSON.parse(response);
//取引件数の確認
var total_count = obj.meta.total_count;
//取引データが存在する場合のみ、後続の処理を行う
if (total_count > 0) {
OutputDeals(obj.deals); //※dealsプロパティのみを渡す
}
}
受け取る側の変数名は、dealsプロパティのみであることがわかるよう objDeals としておきます。
/**
* freeeAPIから取得した取引データをスプレッドシートに書き出す
*
* @param {object} 取引データ
*/
function OutputDeals(objDeals) {
//スプレッドシートに書き出す処理
}
オブジェクト（変数obj）を丸ごと渡すのではなく、dealsプロパティを指定して渡す理由をお伝えします。
freeeAPIのレスポンスの中身は下記の構造になっていて、取引データはすべてdealsプロパティに格納されています。
{
"deals":[
{
//取引1件目=要素番号[0]
},
{
//取引2件目=要素番号[1]
},
{
//取引3件目=要素番号[2]
}
],
"meta":{
"total_count":3
}
}
オブジェクト（変数obj）を丸ごと受け取った場合、取引データはすべてdealsプロパティに入っているにも関わらず、値を取り出すたびに、dealsプロパティを指定しなければなりません。
//取引1件目の6項目を取得する Logger.log(obj.deals[0].issue_date); //2019-07-01 Logger.log(obj.deals[0].due_date); //2019-08-15 Logger.log(obj.deals[0].amount); //86400 Logger.log(obj.deals[0].partner_id); //18092592 Logger.log(obj.deals[0].details[0].account_item_id); //296536450 Logger.log(obj.deals[0].details[0].item_id); //165676671
あらかじめdealsプロパティのみを受け取っておけば、ドット(.)の連結をひとつ減らすことができます。
//取引1件目の6項目を取得する Logger.log(objDeals[0].issue_date); //2019-07-01 Logger.log(objDeals[0].due_date); //2019-08-15 Logger.log(objDeals[0].amount); //86400 Logger.log(objDeals[0].partner_id); //18092592 Logger.log(objDeals[0].details[0].account_item_id); //296536450 Logger.log(objDeals[0].details[0].item_id); //165676671
このように、関数を呼び出すときの引数には、オブジェクトを丸ごと渡すのではなく、必要なプロパティのみを指定しましょう。
人間同士で何らかのデータをやりとりする場合も、「とりあえず全部渡すから、必要な部分だけ使ってね」よりも、相手が必要なデータだけに絞って渡してあげるほうが親切ですね。
オブジェクトのデータをスプレッドシートに書き出す
この項ではOutputDeals関数の処理を作成していきます。
支払期日2019-08-15に該当する取引は3件ありました。この3件について、必要な6項目をスプレッドシートに書き出します。
実装のポイントは、「どのようにスプレッドシートに書き込むか？」です。
単純に「オブジェクトから値を取り出して、1つずつスプレッドシートに書き込む」と、3件×6項目 = 18 回の書き込み処理が必要です。これでは、取引件数が増えるほど、書き込み回数が増えてしまいます。
当ブログでは、GASの高速化テクニックとして「スプレッドシートへの書き込み回数を減らすこと」を推奨しています。
そこで、下記の2ステップで処理を作成します。
- ①オブジェクト（連想配列）から、必要なデータのみを配列に格納する
- ②配列をスプレッドシートに書き出す
こうすることで、取引件数がどんなに多くても、スプレッドシートへの書き込み処理はたったの1回で済むのです！
それではコードを書いていきましょう。
オブジェクト（連想配列）から配列を作成する
まずは①の処理を作成します。「キー」と「値」がペアになったオブジェクト（連想配列）から「値」のみを取り出して配列を作成します。
オブジェクト（連想配列）とは、「キー」と「値」がペアになった集合体です。
オブジェクトから必要な「値」のみを取り出して、配列を作成します。
1件目の取引データから6項目を取得してみます。
取引データのdealsプロパティは配列形式であり、取引件数の分、オブジェクトが並んでいることを前回の記事でお伝えしました。
（配列の要素番号は0から始まるので、1件目の取引データを取得する場合、dealsプロパティに要素番号[0]を指定します）
取引データをすべて取得するためには、要素番号の値を変数にして、取引件数分ループ処理します。
処理の流れは下記の3ステップです。
- 空の配列を用意
- 取引データの件数分、6項目を取得（value1～value6）
- value1～value6を、pushメソッドで配列arrに追加
※勘定科目ID・品目IDについて
detailsプロパティは取引の「明細」です。1件の取引に複数明細が存在する場合、明細の数だけオブジェクトが並びます。ただし、全明細を取得する処理は複雑になってしまうので、このツールでは、details[0]を指定して「最初の1明細」のみを取得することにします。
var arr = []; //空の配列を用意
for(var i = 0; i < objDeals.length; i++) {
var value1 = objDeals[i].issue_date; //発生日
var value2 = objDeals[i].due_date; //支払期日
var value3 = objDeals[i].amount; //金額
var value4 = objDeals[i].partner_id; //取引先ID
var value5 = objDeals[i].details[0].account_item_id; //勘定科目ID ※明細行1番目
var value6 = objDeals[i].details[0].item_id; //品目ID ※明細行1番目
//二次元配列を作成
arr.push([value1,value2,value3,value4,value5,value6]);
Logger.log(arr);
}
ポイントはArrayオブジェクトのpushメソッドです。
pushメソッドとは、配列の末尾に値を追加するメソッドです。追加する値に[配列]を指定することで、配列の追加も可能です。
変数arrのログ出力結果です。
合計3回ループして、ループ処理のたびに、末尾に配列が格納されていく様子がわかりますね。
配列をスプレッドシートに書き出す
取引3件×6項目の値を格納した二次元配列arrが完成しました。
[[2019-07-01, 2019-08-15, 86400, 18092592, 296536450, 165676671], [2019-06-15, 2019-08-15, 54000, 18092591, 296536450, 165676702], [2019-06-01, 2019-08-15, 108000, 18092590, 296536450, 165676665]]
この変数arrをスプレッドシートに書き出します。②の処理です。
まず、getRangeメソッドで配列を書き込むセル範囲を取得します。
つぎに、セル範囲に対して配列をセットするsetValuesメソッドを使用します。
A2セルを起点として、3行×6列の配列をセットします。
配列arrのタテ・ヨコの長さ（length）を、変数i, j に格納しています。
var i = arr.length; //タテ（行数）= 3
var j = arr[0].length; //ヨコ（列数）= 6
//スプレッドシートのA2セルを起点として、配列arrをセットする
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('取引');
sheet.getRange(2, 1, i, j).setValues(arr);
実行結果です。1回の書き込み処理で、目的のデータをすべて書き出すことができました。
前回データをクリアする
さて、無事に取引データを書き出せて一安心ですが、最後にひとつ処理を加えます。
このツールは、トリガーで毎日実行してスプレッドシートの2行目以降を上書きします。 そこで、データを書き出す前に、いったん前回データをクリアする処理が必要ですね。
OutputDeals関数の冒頭部に、値のクリア処理を加えます。
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('取引');
var lastRow = sheet.getLastRow();
sheet.getRange(2, 1, lastRow, 6).clearContent();//A列～F列の2行目以降をクリアする
スクリプトまとめ
ここまでで作成したスクリプトのまとめです。getDeals関数についてはシリーズ第3回目の記事をご覧ください。
function main() {
//当日入金予定の取引一覧を取得する
var response = getDeals();
//JSON形式の文字列をオブジェクトに変換する
var obj = JSON.parse(response);
//取引件数の確認
var total_count = obj.meta.total_count;
//取引データが存在する場合のみ、後続の処理を行う
if (total_count > 0) {
OutputDeals(obj.deals); //※dealsプロパティのみを渡す
}
}
/**
* freeeAPIから取得したデータをスプレッドシートに書き出す
*
* @param {object} 取引データ
*/
function OutputDeals(objDeals) {
//前回データのクリア
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('取引');
var lastRow = sheet.getLastRow();
sheet.getRange(2, 1, lastRow, 6).clearContent();//A列～F列の2行目以降をクリアする
var arr = []; //空の配列を用意
for(var i = 0; i < objDeals.length; i++) {
var value1 = objDeals[i].issue_date; //発生日
var value2 = objDeals[i].due_date; //支払期日
var value3 = objDeals[i].amount; //金額
var value4 = objDeals[i].partner_id; //取引先ID
var value5 = objDeals[i].details[0].account_item_id; //勘定科目ID ※明細行1番目
var value6 = objDeals[i].details[0].item_id; //品目ID ※明細行1番目
//二次元配列を作成
arr.push([value1,value2,value3,value4,value5,value6]);
}
//二次元配列をスプレッドシートに書き出す
var i = arr.length; //タテ（行数）
var j = arr[0].length; //ヨコ（列数）
//スプレッドシートのA2セルを起点として、配列arrをセットする
sheet.getRange(2, 1, i, j).setValues(arr);
}
まとめ・次回予告
「当日入金予定の取引一覧を自動でチャットワークに送信する」シリーズの第5回目では、これらを紹介しました。
- 関数を呼び出すときの「引数」には、オブジェクトのプロパティを指定して必要データのみを渡す方法
- オブジェクトから値を取り出してスプレッドシートに書き出す方法
→処理高速化のため、スプレッドシートへの書き込み処理は1回のみとする。値を1つずつ書き込むのではなく、必要な値のみを配列に格納し、配列ごとスプレッドシートに書き込むのがポイント！
次回は、スプレッドシートのデータからチャットワーク通知用の本文を作成します。
連載目次：【GAS×freeeAPI】当日入金予定の取引一覧を自動でチャットワークに送信する
「クラウド会計ソフトfreee」のAPIをGASで操作して経理業務を自動化するシリーズ。「当日入金予定の取引一覧を自動でチャットワークに送信するツール」の作成を目標とします。
- ノンプログラミングでOK！「会計freee連携アドオン」でスプレッドシートにデータを取得する
- 【GAS×freeeAPI】指定条件の取引一覧を取得する「リクエストURL」の作り方と仕組み
- 【GAS×freeeAPI】GETリクエストで当日入金予定の取引一覧を取得する
- 【GAS×freeeAPI】APIリクエストのレスポンスから必要データのみを取り出す
- 【GAS×freeeAPI】オブジェクトからデータを取り出してスプレッドシートに書き出す
- 【GAS×freeeAPI】スプレッドシートのデータからチャットワーク通知用の本文を作成する
- 【GAS×freeeAPI】当日入金予定の取引一覧を毎日自動でチャットワークに送信する
