こんにちは!ITライターのもり(@moripro3)です!
クラウド会計ソフトfreeeのAPIを使って経理業務を自動化するネタをシリーズでお届けしています。
このシリーズでは、freeeとチャットワークを連携して「当日入金予定の取引一覧を自動でチャットワークに送信するツール」を作成します。
成果物のイメージがこちら!入金予定のある日に、自動でチャットワークに通知が届きます。
前回の記事では、オブジェクトからデータを取り出し、スプレッドシートに書き出す方法を紹介しました。
![](https://tonari-it.com/wp-content/uploads/3041405419df3a7665a2e11c7b4d170d-160x90.png)
今回の記事では、スプレッドシートのデータからチャットワーク通知用の本文を作成します。ツール完成まであと一歩ですよ!
シリーズを通してfreeeAPIを使いこなし、経理業務の自動化を進めていきましょう!
(この記事はfreee株式会社さまとのコラボ企画です。シリーズを通して、皆さんの働くの価値を上げられるようサポートしていきます!)
取引データの「ID」を「名称」に変換する
前回の記事で、freeeAPIのレスポンスをスプレッドシートに書き出しました。オブジェクト(連想配列)から必要データのみを配列に格納し、1回でスプレッドシートに書き込む方法でした。
わざわざスプレッドシートに書き込まなくても、レスポンスのデータを直接チャットワークに送信できるのでは・・・?と思うかもしれませんね。
いったんスプレッドシートに書き込んだのは、下記3つの項目を変換するためです。数値形式のIDがチャットワークに通知されても、何の科目かわかりませんからね。
- 取引先ID
- 勘定科目ID
- 品目ID
そこで、この3つの「ID」を「名称」に変換します。スプレッドシートのG列~I列に、名称用のヘッダーを準備します。
取引先・勘定科目・品目それぞれについて、「IDと名称が存在するマスタ」を用意し、「ID」をキーにして「名称」に変換します。
シリーズ初回の記事「会計freee連携アドオン」でスプレッドシートにデータを取得するで、3つの情報を取得しました。
- 取引先情報
- 勘定科目情報
- 品目情報
これらの情報をマスタとして使い、名称を取得します。
アドオンで取得した情報をマスタ形式に整形
スプレッドシートのVLOOKUP関数を使用して、「ID」をキーとして、「名称」に変換します。
VLOOKUP関数は「キー」を「値」の左側に配置する必要があるので、「キー」をA列に移動します。
取引先情報では、「システムID」が「キー」になります。
3つのシートそれぞれについて、同じ作業をします。
- 取引先情報
- 勘定科目情報
- 品目情報
A列に「ID」・B列に「名称」を配置します。
VLOOKUP関数でIDを名称に変換する
VLOOKUP関数で「取引先ID」をキーにして、「取引先情報」のシートから「取引先」を取得します。
IFERROR関数も組み合わせて、IDが見つからない場合は”-“(ハイフン)表示にします
G2セルの入力値がこちらです。
=IFERROR(VLOOKUP(D2,'取引先情報'!A:B,2,false),"-")
他の2項目も同様の処理をします。
「勘定科目ID」をキーにして、「勘定科目情報」のシートから「勘定科目」を取得します。
「品目ID」をキーにして、「品目情報」のシートから「品目」を取得します。
これで、3つの項目をすべて名称に変換できました。実行日によって取引件数が変わるので、関数式は任意の行までコピーしておきましょう。
これでスプレッドシート上のデータが完成しました。ここからチャットワーク通知用の本文を作成していきましょう!
チャットワーク通知用の本文を作成する
このツールでは、スプレッドシートの5項目をチャットワークに送信します。
5項目を、このような形式でチャットワークに通知します。
チャットワーク通知用の本文を作る関数を作成していきます。取引件数の分、データを作成するので、引数として取引件数を受け取ります。
- 関数名:createBody
- 引数:取引件数
createBody関数は、取引データが存在する場合のみ、main関数から呼び出されます。
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); //チャットワーク通知用の本文を作成する createBody(total_count); } }
これから作成するcreateBody関数です。取引件数(total_count)を引数として受け取ります。
/** * スプレッドシートのデータからチャットワーク通知用の本文を作成する * * @param {number} 取引件数 */ function createBody(total_count) { //処理 }
それではcreateBody関数の中身を書いていきましょう。
スプレッドシートの取引データを二次元配列に格納
スプレッドシートの取引データを二次元配列に格納します。
- 行数:total_count(ここでは3件)
- 列数:A列~I列(9列)
RangeオブジェクトのgetValuesメソッドで、セル範囲を変数valuesに格納します。
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('取引'); var values = sheet.getRange(2, 1, total_count, 9).getValues();//A列~I列の値を格納
二次元配列の値を変数に格納
まずは、1件目の取引からチャットワーク通知用の文字列を作ってみます。チャットワークに通知するのは赤枠の項目のみです。(配列の要素番号0,2,6,7,8)
コードです。二次元配列の1件目なので、行番号をvalues[0]にしています。5項目をパイプラインで結合します。
var issue_date,amount,partner,account_item,item; var body = ''; //二次元配列の値を変数に格納 issue_date = values[0][0]; //発生日 amount = values[0][2]; //金額 partner = values[0][6]; //取引先 account_item = values[0][7]; //勘定科目 item = values[0][8]; //品目 //パイプライン(|)で結合する body = issue_date + ' | ' + partner + ' | ' + amount + ' | ' + account_item + ' | ' + item; Logger.log(body);
ログ出力結果です。5項目を取得して、1行に結合できました。
あとは、取引件数分のデータを取得して文字列を作成すればよいですね!
取引件数分のデータから文字列を作成する
ループ処理で、取引件数の分、データを結合します。文字列結合のポイントは2つあります。
①末尾に改行コード ’\n’ を付与する
②結合代入演算子の「+=」で結合する(※下記の2つは、どちらも同じ結果になります)
- body = body + str
- body += str
5項目のヘッダーも付与しておきましょう。
var issue_date,amount,partner,account_item,item; var body = ''; for (var i = 0; i < values.length; i++) { //二次元配列の値を変数に格納 issue_date = values[i][0]; //発生日 amount = values[i][2]; //金額 partner = values[i][6]; //取引先 account_item = values[i][7]; //勘定科目 item = values[i][8]; //品目 //パイプライン(|)で結合する body += issue_date + ' | ' + partner + ' | ' + amount + ' | ' + account_item + ' | ' + item + '\n'; } //ヘッダー項目を付与 var header = '[発生日] | [取引先] | [金額] | [勘定科目] | [品目等]\n'; body = header + body; Logger.log(body);
ログ出力結果です。ヘッダー項目と取引3件分の本文を作成できました。
あとは、この文字列をチャットワークに通知すれば完成です!
スクリプトまとめ
ここまでで作成したスクリプトのまとめです。
※下記2つの関数は過去記事をご参照ください。
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); //チャットワーク通知用の本文を作成する createBody(total_count); } } /** * スプレッドシートのデータからチャットワーク通知用の本文を作成する * * @param {number} 取引件数 */ function createBody(total_count) { var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('取引'); var values = sheet.getRange(2, 1, total_count, 9).getValues();//A列~I列の値を格納 var issue_date,amount,partner,account_item,item; var body = ''; for (var i = 0; i < values.length; i++) { //二次元配列の値を変数に格納 issue_date = values[i][0]; //発生日 amount = values[i][2]; //金額 partner = values[i][6]; //取引先 account_item = values[i][7]; //勘定科目 item = values[i][8]; //品目 //パイプライン(|)で結合する body += issue_date + ' | ' + partner + ' | ' + amount + ' | ' + account_item + ' | ' + item + '\n'; } //ヘッダー項目を付与 var header = '[発生日] | [取引先] | [金額] | [勘定科目] | [品目等]\n'; body = header + body; Logger.log(body); /* [発生日] | [取引先] | [金額] | [勘定科目] | [品目等] 2019-07-01 | テスト会社5 | 86400 | 売上高 | ポスターデザイン 2019-06-15 | テスト会社4 | 54000 | 売上高 | サービス料収入 2019-06-01 | テスト会社3 | 108000 | 売上高 | デザイン素材 */ }
まとめ・次回予告
「当日入金予定の取引一覧を自動でチャットワークに送信する」シリーズの第6回目では、スプレッドシートのデータからチャットワーク通知用の本文を作成する方法を紹介しました。
- VLOOKUP関数を使用して、「ID」を「名称」に変換する
- スプレッドシートの値を二次元配列に格納し、ループ処理で値を結合する
- 取引1件ごとに、末尾に改行コード \n を付与
- 結合代入演算子 += で文字列を結合
さて次回はいよいよ最終回!チャットワークに送信しますよ。
![](https://tonari-it.com/wp-content/uploads/9505a50163ff3d013706e8b377b18eba-160x90.png)
連載目次:【GAS×freeeAPI】当日入金予定の取引一覧を自動でチャットワークに送信する
「クラウド会計ソフトfreee」のAPIをGASで操作して経理業務を自動化するシリーズ。「当日入金予定の取引一覧を自動でチャットワークに送信するツール」の作成を目標とします。
- ノンプログラミングでOK!「会計freee連携アドオン」でスプレッドシートにデータを取得する
- 【GAS×freeeAPI】指定条件の取引一覧を取得する「リクエストURL」の作り方と仕組み
- 【GAS×freeeAPI】GETリクエストで当日入金予定の取引一覧を取得する
- 【GAS×freeeAPI】APIリクエストのレスポンスから必要データのみを取り出す
- 【GAS×freeeAPI】オブジェクトからデータを取り出してスプレッドシートに書き出す
- 【GAS×freeeAPI】スプレッドシートのデータからチャットワーク通知用の本文を作成する
- 【GAS×freeeAPI】当日入金予定の取引一覧を毎日自動でチャットワークに送信する