見出し画像

LINEからGAPを通じてスプレッドシートに入力する共有家計簿

aglio26

はじめに

　これまで一人生計だったのでお金の管理がかなり適当だったのですが、妻と共同生活を始めるにあたってお金の管理をしっかりしようと思い、家計簿を作成しようと考えました。現在の生活拠点であるオーストリアのチロル地方では現金およびチップ文化が根強くクレジットカードによる一括管理が難しいため、当初スプレッドシートに個々人がレシート内容を入力することを考えました。しかし、意外に手間がかかることや、妻がスプレッドシートの扱いに慣れていないことからよりよい方法を模索していました。

そんな中上記記事をNoteで見かけ、便利そうに思ったので自分(が少しと大部分は生成AI)で実装してみました。完成したLINEでの実際の応答は以下の通りで、LINEのMessage APIを通じて家計簿への書き込みと統計データの出力を行います。

実際の家計簿アプリの応答

GAPには画像を直接的に扱うライブラリが存在しないようなので、LINEへのグラフの送信は一旦Google Driveに画像として保存し、そこにLINE APIでアクセスすることで実装しています。

本記事ではゼロ知識から上記家計簿を実装する方を対象に、その方法について説明します。


LINE Developers でのMessage API の設定

LINE Developers で 家計簿BOTを作成

　まずLINE Developersにアクセスし、自身のLINE IDでログインします。そのあとコンソールにアクセスし、Createボタンを押してLINE APIのプロバイダとして登録します。名前は特に重要ではないので、任意の名前をつけます(画面ではtestとしました)。すると、下図のような画面が表示されるので、Create Messaging API channel を選択します。必要事項を記入し、チャンネルを作成します。

チャンネルの作成

チャンネルを生成すると、コンソールから下図のように各チャンネルにアクセスできるようになります。下図ではチャンネルの名前を家計簿にしています。

チャンネルの選択画面

Message APIの設定

　チャンネルを選択し、Messaging APIのタブを選択します。QR codeが表示されるので、LINE アプリで友達追加を行います。ここでは、Use WebhookとAllow bot to join group chatsを有効にします。後ほどGAPのスクリプトを実装してデプロイするのですが、このデプロイ時に表示されるスクリプトのURLをWebhook URLに入力することでGAPがMessage APIにアクセスできるようになります。
　また、Channel access token (long-lived) にAPIへのアクセストークンが記述されているので、記録しておきます。

GAP Script

スプレッドシート作成とアクセス設定

　まずスプレッドシートを作成し、下図を参考に拡張機能->Apps ScriptからGAPの管理画面を開きます。

スプレッドシートのGAP編集

　エディタのコード.gsを開き(デフォルトで選択されていると思います)、プリアンブルとして以下のコードを記述します。

const ACCESS_TOKEN ='LINE Message APIのアクセストークン';
const URL = 'https://api.line.me/v2/bot/message/reply'; // 応答メッセージ用のAPI URL
const SHEET_ID = 'SpreadsheetsのID';
const FOLDER_ID = 'Google DriveのフォルダID';

スプレッドシートのIDは、スプレッドシートのURL中次のように表示されます。
docs.google.com/spreadsheets/d/'スプレッドシートのID'/edit#gid=???????

同様に、Google DriveのフォルダIDはフォルダURLの末尾に次のように表示されます。
drive.google.com/drive/u/1/folders/'Google DriveのフォルダID'

このドライブは画像生成の一時フォルダとしてのみ用いられるので適当なフォルダで問題ありませんが、APIがアクセスできるように共有権限を変更する必要があります。該当のフォルダを右クリックし、共有->共有から一般的なアクセスをリンクを知っている全員にして、さらに編集権限を付与します。

Google Driveの共有設定

GAPでのコード実装

　次に以下のコードをプリアンブルの下にコピー＆ペーストします。コードについては、私よりも生成AIに聞いた方が良い説明が帰ってくるので、分からない部分があればその関数をコピペして聞いてみてください。
　
　基本的な動作としては、ユーザーが該当ボットにメッセージを送信すると、先ほどMessaging APIで設定したWebhookによってAPIからGAPにHTTPPostリクエストが送信されます。GAPではPostリクエストを受信した際に実行される関数doPost()が用意されているので、この関数内に処理のすべてを記述します。詳しい情報に関しては以下のレファレンスを参照してください。

Web Apps | Apps Script | Google for Developers
Messaging API reference | LINE Developers

以下のコードでは、
"日付
 カテゴリ
 金額
 メモ"
という入力フォーマットで家計簿の入力を受け付けています。フォーマット外の入力で適正フォーマットの案内文をLINE に返信します。

  • 日付はmm/ddか、あるいは今日、昨日、一昨日による指定が可能で、省略された場合は今日の日付が入力されます。

  • カテゴリは['食費', '日用品', '交通費', '家賃', 'その他']の中から選択する必要があります。カテゴリを増やしたい場合、validCategories配列の中身を修正してください。

  • 金額は正の数字で入力を受け付けています。

  • メモは省略可能で、必要があれば入力します。

また、"いくら"の入力を受けた際には当月のカテゴリ別合計値を返信し、"統計"という入力を受けた際には各月のカテゴリ別合計値をグラフにしてその画像を返信します。


**
 * doPost
 * ユーザーがLINEにメッセージ送信した時の処理
 **/

function doPost(e) {
    var json = JSON.parse(e.postData.contents);
    var reply_token = json.events[0].replyToken;
    if (typeof reply_token === 'undefined') {
        return;
    }

    var user_message = json.events[0].message.text;
    var message_parameter = user_message.split(/\r\n|\n/);
    var post_message = "";

    // "いくら"の入力で該当月カテゴリ別合計値を返信
    if (user_message === 'いくら') {
        post_message = getCategoryTotals();
        replyWithMessage(reply_token, post_message);
        return ContentService.createTextOutput(JSON.stringify({ 'content': 'sum ok' })).setMimeType(ContentService.MimeType.JSON);
    }

    // "統計"の入力で統計データを更新
    if (user_message === '統計') {
    updateStatisticsSheet(reply_token);
    return ContentService.createTextOutput(JSON.stringify({ 'content': 'statistics updated' })).setMimeType(ContentService.MimeType.JSON);
    }

    // 家計簿入力
    var dateObj = new Date(); // Assume today's date by default
    var dateProvided = false; // Track if a date is explicitly provided

    // Check if the first parameter is a date
    if (message_parameter[0].match(/^(\d{1,2})\/(\d{1,2})$/)) {
        var [month, day] = message_parameter[0].split('/');
        dateObj = new Date(new Date().getFullYear(), parseInt(month) - 1, parseInt(day));
        dateProvided = true;
    } else if (['昨日', '一昨日', '今日'].includes(message_parameter[0])) {
        var offset = (message_parameter[0] === '昨日') ? -1 : (message_parameter[0] === '一昨日') ? -2 : 0;
        dateObj.setDate(dateObj.getDate() + offset);
        dateProvided = true;
    }

    if (dateProvided) {
        message_parameter.shift(); // Remove the date info if a date was provided
    }

    if (isNaN(dateObj.getTime())) {
        post_message = '無効な日付です。';
        replyWithMessage(reply_token, post_message);
        return ContentService.createTextOutput(JSON.stringify({ 'content': 'invalid date' })).setMimeType(ContentService.MimeType.JSON);
    }

    // Ensure there's at least category and amount
    if (message_parameter.length < 2) {
        post_message = 'フォーマット:\n日付(mm/dd, 昨日, 一昨日, 今日の場合は省略可)\nカテゴリ(食費, 日用品, 交通費, 家賃,その他)\n金額\nメモ(オプション)';
        replyWithMessage(reply_token, post_message);
        return ContentService.createTextOutput(JSON.stringify({ 'content': 'missing information' })).setMimeType(ContentService.MimeType.JSON);
    }

    var category = message_parameter[0];
    var amount = message_parameter[1];
    var memo = message_parameter[2] || ""; // Optional memo, default to empty if not provided

    post_message = recordExpense(dateObj, category, amount, memo);
    replyWithMessage(reply_token, post_message);
    return ContentService.createTextOutput(JSON.stringify({ 'content': 'post ok' })).setMimeType(ContentService.MimeType.JSON);
}

/**
 * replyWithMessage
 * メッセージをラインで返信
 **/
function replyWithMessage(reply_token, message) {
    UrlFetchApp.fetch(URL, {
        'headers': {
            'Content-Type': 'application/json; charset=UTF-8',
            'Authorization': 'Bearer ' + ACCESS_TOKEN,
        },
        'method': 'post',
        'payload': JSON.stringify({
            'replyToken': reply_token,
            'messages': [{
                'type': 'text',
                'text': message,
            }]
        }),
    });
}

/**
 * recordExpense
 * 入力された情報をスプレッドシートに記録
 **/
function recordExpense(dateObj, category, amount, memo) {
    try {
        var validCategories = ['食費', '日用品', '交通費', '家賃', 'その他'];
        if (!validCategories.includes(category)) {
            // カテゴリがリストに含まれていない場合はエラーメッセージを返す
            return 'カテゴリは以下のいずれかで入力してください: ' + validCategories.join(', ');
        }
        
        if (isNaN(amount) || amount <= 0) {
            return '金額は正の数で入力してください。';
        }

        var spreadsheet = SpreadsheetApp.openById(SHEET_ID);
        var sheetName = Utilities.formatDate(new Date(), 'Asia/Tokyo', 'yyyy年MM月');
        var sheet = spreadsheet.getSheetByName(sheetName);

        if (!sheet) {
            sheet = spreadsheet.insertSheet(sheetName);
            sheet.appendRow(['日付', 'カテゴリ', '金額', 'メモ']);
        }

        // 日付の時間をリセットしてフォーマット
        var formattedDate = Utilities.formatDate(resetTime(dateObj), 'GMT+09:00', 'yyyy/MM/dd');

        var data = sheet.getDataRange().getValues();
        sheet.appendRow([formattedDate, category, amount, memo]);

        sortSheetByDate(sheet);

        return category + 'の' + amount + ' €を' + formattedDate + 'に記録しました。メモ: ' + memo;
    } catch (e) {
        return "エラーが発生しました: " + e.toString();
    }
}

/**
 * resetTime
 * 書き込む際の時間をリセット
 **/
function resetTime(dateObj) {
    dateObj.setHours(0, 0, 0, 0); // 時間、分、秒、ミリ秒を0に設定
    return dateObj;
}

/**
 * sortSheetByDate
 * シート内を日付でソート
 **/
function sortSheetByDate(sheet) {
    // データが存在する範囲を取得
    var range = sheet.getDataRange();
    var numRows = range.getNumRows();

    // ヘッダー行を除いた範囲をソート対象とする
    if (numRows > 1) {
        // 2行目から始まる範囲を取得（1行目はヘッダーなので除外）
        var sortRange = sheet.getRange(2, 1, numRows - 1, range.getNumColumns());
        sortRange.sort({column: 1, ascending: true}); // 1列目（日付）を基準に昇順でソート
    }
}

/**
 * getCategoryTotals
 * コールされた月内のカテゴリ別合計値を出力
 **/
function getCategoryTotals() {
    var today = new Date();
    var sheetName = Utilities.formatDate(today, 'Asia/Tokyo', 'yyyy年MM月');
    var spreadsheet = SpreadsheetApp.openById(SHEET_ID);
    var sheet = spreadsheet.getSheetByName(sheetName);

    if (!sheet) {
        return '今月のデータはまだありません。';
    }

    var data = sheet.getDataRange().getValues();
    var totals = {};
    for (var i = 1; i < data.length; i++) {
        var category = data[i][1];
        var amount = parseFloat(data[i][2]);
        if (totals[category]) {
            totals[category] += amount;
        } else {
            totals[category] = amount;
        }
    }

    var result = '今月のカテゴリ別合計:\n';
    for (var key in totals) {
        var roundedTotal = totals[key].toFixed(2); // 各カテゴリの合計値をtoFixed()メソッドで丸める
        result += key + ': ' + roundedTotal + '€\n';
    }
    return result;
}

/**
 * updateStatisticsSheet
 * 統計情報を専用シートに出力
 **/
function updateStatisticsSheet(reply_token) {
    var spreadsheet = SpreadsheetApp.openById(SHEET_ID);
    var sheets = spreadsheet.getSheets();
    var validCategories = ['食費', '日用品', '交通費', '家賃', 'その他'];
    var statistics = {};

    // 既存のシートをループしてデータを集計
    sheets.forEach(sheet => {
        if (sheet.getName() !== '統計') {
            var data = sheet.getDataRange().getValues();
            for (var i = 1; i < data.length; i++) {
                var month = sheet.getName();
                var category = data[i][1];
                var amount = parseFloat(data[i][2]);
                if (!statistics[month]) {
                    statistics[month] = {total: 0}; // 合計値を保持するためのキーを追加
                }
                if (!statistics[month][category]) {
                    statistics[month][category] = 0;
                }
                statistics[month][category] += amount;
                statistics[month].total += amount; // 各カテゴリの合計を加算
            }
        }
    });

    // 統計シートを更新または作成
    var statsSheet = spreadsheet.getSheetByName('統計') || spreadsheet.insertSheet('統計');
    statsSheet.clear(); // 既存のデータをクリア
    statsSheet.appendRow(['月', ...validCategories, '合計']); // ヘッダー行に「合計」カラムを追加

    // 統計データをシートに書き込み前に、月を時間順にソート
    var sortedMonths = Object.keys(statistics).sort(function(a, b) {
        // "yyyy年MM月"の形式をDateオブジェクトに変換して比較
        var dateA = new Date(a.replace('年', '/').replace('月', '/01'));
        var dateB = new Date(b.replace('年', '/').replace('月', '/01'));
        return dateA - dateB;
    });

    // ソートされた月のデータをシートに書き込み
    sortedMonths.forEach(function(month) {
        var row = [month];
        validCategories.forEach(category => {
            row.push(statistics[month][category] || 0);
        });
        row.push(statistics[month].total); // 合計値を行に追加
        statsSheet.appendRow(row);
    });

    // 棒グラフの作成
    createStackedBarChart(reply_token,statsSheet, validCategories);
}

/**
 * createStackedBarChart
 * 統計情報に基づいて棒グラフを生成
 **/
function createStackedBarChart(reply_token,sheet, categories) {
    var lastRow = sheet.getLastRow();
    var range = sheet.getRange(1, 1, lastRow, categories.length + 0); // 1行目を含むデータ範囲

    var chart = sheet.newChart()
        .setChartType(Charts.ChartType.COLUMN)
        .addRange(range)
        .setNumHeaders(1) // 最初の1行を見出しとして設定
        .setPosition(5, 2, 0, 0)
        .setOption('title', '月別カテゴリ支出')
        .setOption('isStacked', 'absolute')
        .setOption('hAxis', {title: '月'})
        .setOption('vAxis', {title: '金額'})
        .setOption('legend', {position: 'top', maxLines: 3})
        .setOption('annotations', {
            alwaysOutside: true,
            textStyle: {fontSize: 12, color: '#000', auraColor: 'none'}
        })
        .build();
    sheet.insertChart(chart);

    //グラフを画像として保存
    //var charts = sheet.getCharts();
    var chartBlob = chart.getBlob(); // グラフを画像に変換
    var today = Utilities.formatDate(new Date(), 'Asia/Tokyo', 'YYYY-MM-dd');
    var fileId;

    // Googleドライブに画像を保存
    var folder = DriveApp.getFolderById(FOLDER_ID);
    var file = folder.createFile(chartBlob).setName(today + '.png');
    fileId = file.getId(); // 保存したファイルのIDを取得

    // 保存した画像をラインに送信
    replyWithImage(reply_token,fileId)    

}

/**
 * replyWithImage
 * グーグルドライブの画像データをラインに送信
**/
function replyWithImage(reply_token, fileId) {
    var fileUrl = 'https://drive.google.com/uc?export=view&id=' + fileId;

    /* 画像メッセージを送る */
    var response = UrlFetchApp.fetch(URL, {
        'headers': {
            'Content-Type': 'application/json',
            'Authorization': 'Bearer ' + ACCESS_TOKEN,
        },
        'method': 'POST',
        'payload': JSON.stringify({
            'replyToken': reply_token,
            "messages": [{
                "type": "image",
                "originalContentUrl": fileUrl,
                "previewImageUrl": fileUrl
            }],
            "notificationDisabled": false
        }),
    });

    // レスポンスを確認して成功した場合にファイルを削除
    if (response.getResponseCode() === 200) {
        var file = DriveApp.getFileById(fileId);
        file.setTrashed(true);  // ファイルをゴミ箱に移動
    } else {
        Logger.log('Failed to send image: ' + response.getContentText());
    }
}

コードデプロイ

　コードの実装が終わったら、画面右上のデプロイ->新しいデプロイを押します。APIからアクセスできるようにアクセスできるユーザーを全員に設定してデプロイボタンを押します。すると、デプロイされたGAPのウェブアプリURLが表示されるので、このURLをLINEのMessaging APIのWebhook URLに入力し、Verify を押します。Successと表示されたら実装されています、BOTにメッセージを送って動作を確認しましょう。

おわりに

　この記事で紹介したアプリは一週間ほど使っていますが、非常に便利で、妻にも満足してもらっています。生成AIのおかげでこうしたプログラムはアイデアさえあればすぐに実装できるようになりました。正直javaもweb app開発も完全に自分の専門外なのですが、さしあたって動くソフトを作るのは難しくない時代になったものだなと嬉しく思っています。
　私は現在生成AIサービスとしてperplexity proを使っています。生成AIはChatGPTを始め様々なものが開発されていますが、perplexityは主要な生成AI(ChatGPT-4 Turbo, Claude 3 Opus, Stable Diffusion XL, DALL-E 3, etc.)が使用でき、かつ一日のプロンプト上限が400と多いのに$20とほかの生成AIのサービスを直接契約するよりもかなりお得です。下のリンクから契約すると$10のディスカウントを受けられるので、興味がある人は是非使ってみてください。

https://perplexity.ai/pro?referral_code=REQI8FPG


いいなと思ったら応援しよう！