LINE Messaging API×GASで家計簿管理用のLINE botを作成する：スプレッドシートからのデータ参照

LINE家計簿bot作成シリーズもこれで最後です。

今回は、Googleスプレッドシートに記録した家計簿の内容を参照する機能を作成します。

「食費」とか「交通費」と入れると対象の費目の合計金額を返すようにしたいと思います。

全体像は以下のとおりです。赤枠の部分が今回の実装箇所になります。なので、ほぼほぼGASをいじっていくかんじになります。

今回も、手順通りに進めれば初心者の方でも作成できる内容となっております。GASのプログラムを編集する箇所がありますが、コピペで出来るようになっていますのでプログラミング未経験者の方でも安心して進めていただければと思います。

GASに費目の合計を求めるスクリプトを作成する

Google Apps Scriptからスクリプトを編集します。前回までにLINEから購入品とその金額を投稿するとスプレッドシートに記録する処理が設定できていれば、以下のスクリプトをコピペするだけでOKです。

もし、本記事から見ていただいている方でスクリプトをコピペして動かしたいという方は、前回までの記事にLINEとGASの連携、スプレッドシートの用意手順を記載していますのでご参照ください。

GASにスクリプトを作成する（コピペでOK）

Google Apps Scriptを開きます。

前回までにすでにスクリプトを作成済の方は該当のプロジェクトを、そうでない方は「新しいプロジェクト」を押下します。

コード.gsに記載されているものをいったん全て削除して、

以下のソースコードを貼り付けてください。

/*
  LINE家計簿用bot
*/
// ▼LINE関係
// LINE developerで登録したチャネルアクセストークン
const ACCESS_TOKEN = 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX';
// LINEへ応答メッセージを送るAPI
const LINE_ENDPOINT = "https://api.line.me/v2/bot/message/reply";
// ▼スプレッドシート関係
const SHEET_ID = "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX";
var spreadSheet = SpreadsheetApp.openById(SHEET_ID);
var sheetName = Utilities.formatDate(new Date(), 'Asia/Tokyo', 'yyyyMM');
const targetSheet = spreadSheet.getSheetByName(sheetName);               // 対象となるシート(サマリー)
const targetSheetUchi = spreadSheet.getSheetByName(sheetName + "内訳");   // 対象となるシート(内訳)

// LINEからPOSTリクエストが渡されてきたときに実行される処理
function doPost(e) {

  // LINEからPOSTされるJSON形式のデータをGASで扱える形式(JSオブジェクト)に変換
  var json = JSON.parse(e.postData.contents);

  // LINE側へ応答するためのトークンを作成(LINEからのリクエストに入っているので、それを取得する)
  var reply_token= json.events[0].replyToken;
  if (typeof reply_token === 'undefined') {
    return;
  }
  // LINEから送られてきたメッセージを取得
  var user_message = json.events[0].message.text;
  // 改行で区切って配列にする
  var user_msgarray = user_message.split(/\r\n|\n/);

  // 応答メッセージ本文
  var resultMsg = "";

  // LINEから受け取ったメッセージの内容が形式通りかチェック
  var validate = isValid(user_msgarray);
  if (validate != "OK"){
    var sum = calcItemSum(user_msgarray);
    if (Number.isInteger(sum) & Math.sign(sum) == 1){
      // 合計をかえす
      resultMsg = sum;
    }
    else{
      resultMsg = validate;
    }
  }
  else if(validate == "OK"){
    // スプレッドシートへの書き込み
    resultMsg = registData(user_msgarray);
  }
  
  // 応答用のメッセージを作成
  var message = {
                  "replyToken"  : reply_token,
                  "messages"    : [{"type": "text",      // メッセージのタイプ(画像、テキストなど)
                                    "text" : resultMsg}] // メッセージの内容
                };

  // LINE側へデータを返す際に必要となる情報
  var options = {
    "method" : "post",
    "headers" : {
      "Content-Type" : "application/json; charset=UTF-8",  // JSON形式を指定、LINEの文字コードはUTF-8
      "Authorization" : "Bearer " + ACCESS_TOKEN           // 認証タイプはBearer(トークン利用)、アクセストークン
    },
    "payload" : JSON.stringify(message)                    // 応答文のメッセージをJSON形式に変換する
  };

  // LINEへ応答メッセージを返す
  UrlFetchApp.fetch(LINE_ENDPOINT, options);
}

// スプレッドシートへの書き込み
function registData(user_msgarray){

  var msg = "";

  // 対象のスプレッドシートを取得
  var spreadSheet = SpreadsheetApp.openById(SHEET_ID);

  // 本日の日付を取得
  var today = Utilities.formatDate(new Date(), 'Asia/Tokyo', 'yyyy/MM/dd');

  // 書き込む対象のシートを取得
  var sheetName = Utilities.formatDate(new Date(), 'Asia/Tokyo', 'yyyyMM内訳');
  var targetSheet = spreadSheet.getSheetByName(sheetName);

  if (targetSheet != null){

    // 最終行を取得
    var lastRow = targetSheet.getLastRow();
    var targetRow = lastRow + 1;

    // 書き込み
    targetSheet.getRange(targetRow, 1).setValue(today);  // 日付
    targetSheet.getRange(targetRow, 2).setValue(user_msgarray[0]);  // 費目
    targetSheet.getRange(targetRow, 3).setValue(user_msgarray[1]);  // 金額
    msg = "書き込み完了";
  }
  else{
    // 対象のシートが見つからない場合
    msg = "シート名：" + sheetName + "\nが見つかりません";
  }
  return msg;
}

// 値のチェック
function isValid(user_msgarray){

  var result;

  // 入力された費目が存在するかチェック
  var values = targetSheet.getRange("A13:A22").getValues();
  for (var i in values) {
    var iNum = parseInt(i);
    if (values[iNum][0] == user_msgarray[0]){
      result = "OK";
      break;
    }
  }
  if (result != "OK"){
    result = "費目に存在しません： " + user_msgarray[0];
    return result;
  }
  // 入力された金額が正しいかチェック
  if(user_msgarray[1] != null & Number.isInteger(Number(user_msgarray[1]))){
    result = "OK";
  }
  else{
    result = "金額を正しく入力してください";
  }
  return result;
}

// スプレッドシートのデータの参照
function calcItemSum(user_msgarray){

  var result = 0;
  
  // 「費目＋合計」だったら費目別合計を返す
  if (user_msgarray[0].includes("合計")){

    // 「合計」の部分を削除
    var targetItem = user_msgarray[0].replace("合計","");

    // サマリーシートの費目を取得
    var values = targetSheet.getRange("A13:A22").getValues();

    // 内訳シートの値が入っている最終行を取得
    var lastRow = targetSheetUchi.getLastRow();
    // 値がある範囲を取得
    var itemVal = targetSheetUchi.getRange(2,1,lastRow,3).getValues();

    // 対象の費目の合計を求める
    for (var i in itemVal){
      var iNum = parseInt(i);
      if (itemVal[iNum][1] == targetItem){
        result = result + Number(itemVal[iNum][2]);
      }
    }
  }
  else{
    result = -1;
  }
  return result;
}

自分の環境に合わせて変数の値を変更する

LINEのアクセストークンやスプレッドシートのIDなどを、ご自身の環境に合わせて変更します。

変更する箇所は以下になります。

• 6行目：アクセストークンの値を、LINE Developers ＞作成したチャネル＞Messaging API設定かの一番下にある「チャネルアクセストークン」の値をコピーして、「’ ‘」の中に入れてください。
• 10行目：スプレッドシートのID（https://docs.google.com/spreadsheets/d/【スプレッドシートのID】/edit#gid=XXXXXXX）を設定します

スクリプトをデプロイする

作成したスクリプトをデプロイします。

デプロイ＞新しいデプロイを押下

デプロイタイプは「ウェブアプリ」を選択

• ウェブアプリ：自分
• アクセスできるユーザーを「全員」

に設定して、「デプロイ」を押下します。

デプロイ後に発行されるウェブアプリのURLをコピーします。

警告が出た場合には、以下の通りに承認を行ってください。

GASでデプロイしたURLをLINE Developersに貼り付ける

GASでデプロイしたURLを、LINE Developers ＞作成したチャネル＞Messaging API設定のWebhook設定に貼り付けます。

LINEでメッセージを送り、対象の費目の合計金額が返ってくるか確認

今回の目的である、「食費」とか「交通費」と入れると対象の費目の合計金額を返すという処理がきちんと実装されているか確認してみます。

「202302内訳」シートには、以下のように値が入っています。

LINEから「外食合計」と送ってみると、

外食の合計金額が返ってきました。

スプレッドシートのサマリーシート（202302）の費目に存在しない場合はエラーメッセージを返します。このメッセージは前回作成した、スプレッドシートに書き込む際の処理と使いまわし共通のものです。

費目＋（改行）＋ 金額を入力することで、前回同様に内訳シートに使った金額が記録されます。

スプレッドシートに投稿した金額が記録されました。

まとめ

今回LINE家計簿botを作成してみて、LINEは連絡手段として日常的に使っているものなので、新たにスマホにアプリをインストールしたり、ウェブからサイトを開いたりといった手間がないので、家計簿入力のハードルを下げられるのではないかと思いました。

実際、私も主婦として日々の買い物記録をノートにつけてみたり、アプリで管理してみたりと色々やってきましたが、現在ではスプレッドシートで管理しています。内容としては今回使用したスプレッドシートとほぼほぼ同じです。（内訳の項目がもう少し細かいですが）

今回は、LINE、GAS、スプレッドシートの連携により書き込み＆参照を行うという必要最低限の内容でしたので、月末に当月の収支をつぶやいてくれる機能とか、予算額を超えそうなときに知らせてくれる機能とかを実装できたらなと思っています。