【GAS×freeeAPI】スプレッドシートのデータからチャットワーク通知用の本文を作成する

こんにちは！ITライターのもり(@moripro3)です！

クラウド会計ソフトfreeeのAPIを使って経理業務を自動化するネタをシリーズでお届けしています。

このシリーズでは、freeeとチャットワークを連携して「当日入金予定の取引一覧を自動でチャットワークに送信するツール」を作成します。

成果物のイメージがこちら！入金予定のある日に、自動でチャットワークに通知が届きます。

前回の記事では、オブジェクトからデータを取り出し、スプレッドシートに書き出す方法を紹介しました。

【GAS×freeeAPI】オブジェクトからデータを取り出してスプレッドシートに書き出す

「当日入金予定の取引一覧を自動でチャットワークに送信する」シリーズの第5回目。キーと値がペアになった「オブジェクト」から必要な値のみを取り出して「配列」に格納し、スプレッドシートに書き出す方法を紹介しています。

今回の記事では、スプレッドシートのデータからチャットワーク通知用の本文を作成します。ツール完成まであと一歩ですよ！

シリーズを通してfreeeAPIを使いこなし、経理業務の自動化を進めていきましょう！

（この記事はfreee株式会社さまとのコラボ企画です。シリーズを通して、皆さんの働くの価値を上げられるようサポートしていきます！）

取引データの「ID」を「名称」に変換する
1. アドオンで取得した情報をマスタ形式に整形
2. VLOOKUP関数でIDを名称に変換する
チャットワーク通知用の本文を作成する
スクリプトまとめ
まとめ・次回予告
連載目次：【GAS×freeeAPI】当日入金予定の取引一覧を自動でチャットワークに送信する

取引データの「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」をキーにして、「取引先情報」のシートから「取引先」を取得します。

VLOOKUP(検索キー, 範囲, 番号, [並べ替え済み])

IFERROR関数も組み合わせて、IDが見つからない場合は”-“（ハイフン）表示にします

IFERROR(値, [エラー値])

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つの関数は過去記事をご参照ください。

getDeals関数（第3回目参照）
OutputDeals関数（第5回目参照）

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 | テスト会社５ | 86400 | 売上高 | ポスターデザイン
  2019-06-15 | テスト会社４ | 54000 | 売上高 | サービス料収入
  2019-06-01 | テスト会社３ | 108000 | 売上高 | デザイン素材
  */

  
}