Google スプレッドシートと LINE ボットで生活費管理を効率化した話

この記事は [デジタルキューブ ＆ ヘプタゴン Advent Calendar 2024](https://qiita.com/advent-calendar/2024/digitalcube-heptagon) の 12月3日分の記事として執筆しています。

ぜひ他のメンバーの記事もご覧ください！

# 自己紹介
今年の4月より、デジタルキューブからヘプタゴンに出向中の麻植（おえ）です。
クラウドオペレーショングループで、 WordPress のホスティングサービス「Amimoto」のインフラ保守を主に担当しています。

# LINE ボット作成のきっかけ
パートナーとの同棲生活で、生活費の割り勘計算が面倒に感じたのがきっかけです。

精算用の LINE グループに、払った人がいくら何に払ったかをコメントして記録し、定期的に合計して精算するという運用をしていましたが、かなり面倒でした。
（気がつくと半年分溜まっていることも......）

家計簿アプリ「B/43」のようなサービスの利用も検討しましたが、現在の方法を大きく変えるのは気が進みません。

そこで、Google スプレッドシートと LINE ボットで割り勘の仕組みを作れたら面白そうと思い、作成してみることにしました。

# 構想と狙い
![構成図.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1488939/7835709e-107a-cbca-d844-c3d885207124.png)

- スプレッドシートに支出を記録できれば、割り勘計算が簡単になりそう
- LINE ボットを使えば LINE グループにコメントするだけでスプレッドシートに記録できるのでは

# スプレッドシートの作成
支出を記録する Google スプレッドシートおよび操作自動化のための Google App Script (GAS) は、手間なく作成できました。
シートは以下のような構成になっています。
- 支払った金額と用途を登録する「支払い分登録」
- 精算のための計算を行う「貸し借り計算」
- 精算が終わった支払いの履歴が記録される「アーカイブ」

スプレッドシートへ組み込んだ GAS では、精算完了した際の以下の動作を行っています。
- 精算が完了したデータを「アーカイブ」シートに移動
- 精算完了を記録するため、日付や計算結果を「アーカイブ」シートに追加

GAS の作成には ChatGPT を使用しました。以下のようなスクリプトで、簡単に使えるコードが生成でき、とても便利です。
以下が ChatGPT に入れたスクリプトの原文そのままになります。
一部修正は行いましたが、これでほぼ完璧なコードが作成できました。

```
スプレッドシートで以下の操作をクリック１つでできるような仕組みを作りたいです。
- シート1の A2:A と B2:B を シート3 の A2:A と B2:B にコピー
- なおコピーする位置はシート3の A 列か B 列の値が入っているセルの１つした（例えばシート3のA列はA24まで値があり、B列はB20まで値がある場合、シート1からコピーしてくる値はシート3の A25 B25 を先頭にする。）
- 上記作業を行った日時をコピーしてきた値の行のC列に記録する
```

# LINE ボットの作成
## 準備
次は LINE ボットの作成ですが、そもそも、LINE ボットの動かし方が全く分かっていませんでした。

そこで、LINE ボットを業務で触っていた社内のメンバーに聞いたところ、以下のことが分かりました。
- ボットの作成には「LINE Messaging API」なるものを使う必要がある
- LINE Messaging API は AWS Lambda のような REST API へアクセスできるサービスで外部から呼び出せる
- GAS には外部 API へアクセスできる命令 (メソッド) がある

スプレッドシートで既に GAS は使ってるので、これで LINE Messaging API を動かすことにしました。

## 実装
スプレッドシートのときと同じく、ChatGPT にコードを作ってもらいました。ただ、生成したコードをそのまま使うと動かなかったので、以下の手順で修正を加えながら完成させています。

1. ChatGPT にコードを作成してもらう
1. エラーが出たら再度 ChatGPT に報告して修正してもらう
1. 必要に応じて自分で調整

正直 LINE Messaging API も GAS もよく分からないまま数時間で LINE ボットが完成してしまい、驚きです。

## LINE ボットのコード
ここからは LINE メッセージの内容をスプレッドシートに登録する LINE ボットのコードを、役割ごとに解説します。

### 1. アクセストークンの設定
LINE Messaging API を操作するためのアクセストークンを定数として定義します。
このコードではメッセージ送信機能を使っていないため、アクセストークンの使用は最小限です。

```
const ACCESS_TOKEN = 'ACCESS_TOKEN'; // LINE Messaging APIのアクセストークン
const SHEET_NAME = '支払い分登録'; // スプレッドシートのシート名
```

### 2. Webhook の受信と処理 (doPost 関数)
LINE Messaging API で発行した Webhook URL に POST リクエストが届くと、この関数が実行されます。

JSON 形式で POST されたデータからユーザー ID とメッセージ内容を取得し、ユーザー ID に応じてスプレッドシートの特定の列にデータを追加する関数 appendToSheet を呼び出します。

```
// Webhookを受信する
function doPost(e) {
  const json = JSON.parse(e.postData.contents);
  const events = json.events;

  events.forEach(event => {
    if (event.type === 'message' && event.message.type === 'text') {
      const userMessage = event.message.text; // 受け取ったメッセージ
      const userId = event.source.userId;

      // ユーザー1と2を判別する（userIdで判定）
      if (userId === 'USER1_ID') {
        appendToSheet(userMessage, 'B', 'A'); // ユーザー1: 偶数行をA列、奇数行をB列に追加
      } else if (userId === 'USER2_ID') {
        appendToSheet(userMessage, 'D', 'C'); // ユーザー2: 偶数行をC列、奇数行をD列に追加
      }
    }
  });

  return ContentService.createTextOutput(JSON.stringify({ status: 'success' })).setMimeType(ContentService.MimeType.JSON);
}
```

### 3. データの登録 (appendToSheet 関数)
メッセージ内容を改行で分割し、奇数行を用途、偶数行を金額として、指定された列に登録します。

その後、各列で最後に空いている行を取得する getLastRow 関数を使い、データを適切な位置に記録します。

列の指定にはアルファベットを使用しますが、コード内では数値で扱うため、getColumnIndex 関数で変換を行います。

```
// メッセージをシートに追加する
function appendToSheet(message, oddColumn, evenColumn) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_NAME);
  const rows = message.split('\n'); // メッセージを改行で区切る

  // 各列で最後の空いている行を取得する
  const lastRowOdd = getLastRow(sheet, oddColumn);
  const lastRowEven = getLastRow(sheet, evenColumn);
  
  // 最後の空いている行を決定（奇数列と偶数列のうち大きい方の行を使用）
  const startRow = Math.max(lastRowOdd, lastRowEven) + 1;

  // メッセージの行を処理して、奇数列と偶数列に追加
  for (let i = 0; i < rows.length; i++) {
    const currentRow = startRow + Math.floor(i / 2);

    if (i % 2 === 0) { 
      // 奇数行（1, 3, 5...）を奇数列に追加
      sheet.getRange(currentRow, getColumnIndex(oddColumn)).setValue(rows[i]);
    } else {
      // 偶数行（2, 4, 6...）を偶数列に追加
      sheet.getRange(currentRow, getColumnIndex(evenColumn)).setValue(rows[i]);
    }
  }
}
```

### 4. 補助関数
そのほか、スプレッドシートの操作を補助する関数をいくつか作成しています。

#### getLastRow 関数
指定された列の最終行を取得し、その次の行番号を返します。
```
// 指定された列の最後の空いている行を取得する
function getLastRow(sheet, column) {
  const columnIndex = getColumnIndex(column);
  const values = sheet.getRange(1, columnIndex, sheet.getLastRow()).getValues();
  for (let i = values.length - 1; i >= 0; i--) {
    if (values[i][0] !== '') {
      return i + 1; // 最後の非空行の次の行を返す
    }
  }
  return 1; // 列が空の場合は1行目を返す
}
```

#### getColumnIndex 関数
列名（アルファベット）を列番号（数値）に変換します。
スプレッドシートでは、列の指定にアルファベットを使用しますが、GAS で列を扱う際には数で指定しなければいけないため、変換する必要があります。
```
// 列名を列番号に変換する
function getColumnIndex(column) {
  return column.charCodeAt(0) - 'A'.charCodeAt(0) + 1;
}
```

# LINE ボットの動作概要
こうして完成した割り勘 LINE ボットの動きを紹介します。

## LINE グループの作成
まず割り勘の対象となる人と LINE ボットが一緒になるグループを作成します。

## 登録
支払った人が LINE グループに対して、奇数行が用途、偶数行が金額になるようにメッセージを送ります。
（スクリーンショットは開発途中のもので、値は適当です）
![スクリーンショット 2024-11-25 17.39.01.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1488939/8e530974-d50b-e701-3004-cd7fed6518f6.png)

メッセージを送ると LINE ボットと GAS が動き、スプレッドシートの「支払い分登録」シートにユーザーごとの金額と用途が記載されます。
![スクリーンショット 2024-11-25 17.46.35.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1488939/3fd07b7f-8917-4a0c-bba5-cf1c9a91fd39.png)

## 精算
「貸し借り計算」シートに誰が誰にいくら払えばいいか、自動的に計算されます。
精算が完了したら「精算完了報告セル」のプルダウンを「精算完了」に変更します。
![スクリーンショット 2024-11-25 17.52.26.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1488939/b3649bf9-5192-f13f-00d0-9284abc1d78e.png)

精算が完了したら「支払い分登録」シートに記載されていた内容が「アーカイブ」シートに移動します。
同時に、精算した日付や精算結果が「アーカイブ」シートにも記載されます。
![スクリーンショット 2024-11-25 17.50.38.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1488939/9cef0c4a-9dff-ffec-037d-505a27d88681.png)

# 感想
- 事前知識ゼロでも数時間で LINE ボットが完成できた
- ChatGPT のおかげで、試行錯誤の時間を大幅に短縮できた
- 普段触れない GAS や LINE Messaging API の勉強になった
- 現状、登録は LINE ボットでできるようになったが、精算はスプレッドシートを見に行く必要があるので、機能追加もしていきたい
- ソースコードにユーザー ID をベタ書きするのは美しくないのでやめたい
- この記事もほとんど ChatGPT に書いてもらいました、ほんと便利すぎる