1. はじめに
あるサークルで、「あんた暇でしょ」と言われ、毎回の練習日に参加者の集計をしなければならなくなりました。練習日ごとの参加者の合計と、各人のある一定期間の練習参加日数を把握したいと言われ、前者はともかく、後者は投票の記録から一人ずつカウントしていかなくてはならず、手作業でやるなら信じられないほど大変です。
手作業だとかなり大変になるので、Lineチャットボットを利用して自動集計機能をつくろうと考え、この度実装しました。
学んだことも多かったので、忘備録も兼ねて共有しようと思います。
目次
2. Liffとは
Liffとは、Line front-end frameworkのことであり、Lineのアカウントを持つ人であれば比較的、だれでも簡単にチャットボットなどをつくることができます。これと似た機能として、決済機能などとも連携できるLineミニアプリがありますが、これはLineの審査を通す必要があるそうなので、ハードルは高めです。
簡単にbotをつくれるらしいということで、今回はLiffのほうを使わせていただきました。
本当のところは、折角Next.jsが使えるのでフロントとサーバーをNext.js、データベースをPostgreSQLを使って、ちょっとリッチな参加集計・記録アプリを作りたかったのですが、いかんせんLiffとNext.jsとの連携方法を書いた記事が少なく、エラーを乗り越えることができませんでした（自分の理解力がなかっただけとも言います）。そのため、今回は静的ページをフロントとして、GASをデプロイしてサーバーとする比較的シンプルな方法をとりました。
とはいえ、効率的に動くものはできたので、個人的には満足です。
Liffの具体的な実装方法については、以下の二つの記事を大いに参考にさせてもらいました。
自分のプロダクトのうち、Liffに関連するコードは多くが流用しているものなので、ここで具体的なコードを示すよりは、元記事に飛んでもらった方がいいかもしれません。
そうはいっても何も解説しないままだと、GASコードにおけるLiffとの連携箇所がわかりにくくなると思うので、とりあえず流れだけでもさらっと紹介しようと思います。
【目標】
Line chat botに対してフォームで値を入力して送信してもらい、その値に基づいて自動的にGoogle SpreadSheetに記録を保存する。
【手順】
Ⅰ：Line Botをつくり、Google SpreadSheetと連携させる。
①Line Chat Botをつくる。
Lineの開発アカウントを作り、新規チャンネルをMessageAPIでつくる。そのうえで、応答の設定を定める。
②MessageAPIのチャンネルからアクセストークンを発行し、控えておく。
③Google Spread Sheetを開き、App Scriptを立ち上げ、Lineと連携してSpreadSheetを動かすコードを書きこむ。ここで、控えておいたアクセストークンとLineのMessageAPIアプリのURLをスクリプトに書き込んでおく。
④GASのスクリプトをWebアプリケーションとしてデプロイし、そのURLをLineのWebhook URLにコピーする。
→Line botとSpreadsheetとの連携が完成！
Ⅱ：フォームに入力した値をLine Botに送信してもらう。(LiffとMessageAPIの連携)
①Ⅰと同プロバイダーにおいてLineログインをしてLiffアプリを追加する。
②フロントのフォーム画面を担当する（静的）ファイルを作成し、インターネット上に公開する。
※私の場合は、index.jsを作ってvercelにデプロイし、公開しました。
③LiffのエンドポイントURLに、フォームページのWeb urlを設定する。
④Line Botのページから、リッチメニューの設定においてLiffフォームを呼び出す。
→外部ページのフォームとLine chatbotとの連携の完成！
私の場合、フォームは以下の画像のようになりました。
細かいところはかなり省いているので、もし気になるようでしたら元記事を辿ることをお勧めします。
3. GASの基本
GASとは何かと言われたら、端的に言えばVBAのようなものと言えます。
VBAは何かといわれれば、GASのようなものと答えておけば構いません。
（無限ループ！）
冗談はともかく、簡単に言えば、JavaScriptライクなコードによってスプレッドシートを制御するのがGASだと考えてもらって差し支えないと思います。ほとんどがJavaScriptの構文であり、Utilitiesのように、たまにGASだけが提供する組み込みオブジェクト/apiが存在するする程度だと思います。
基本の操作は以下の三つに集約されると思います。
①getRange：範囲を指定する
②getValue(s)：指定した範囲の値を取得する。
③setValue(s)：指定した範囲に値を設定する。
これに付け加えて、行・列数を確認するfindLast...メソッドや行ごと追加・削除する、append/deleteRowメソッドなど、上の三つの操作を補助する各メソッドが存在するようなイメージだと考えます。
具体的なコードの紹介に移ります。
例えば、Lineから三行のメッセージが送られてきたとして、それをSpreadsheetについかするコードは以下のようになります。
// 事前にLineからメッセージを値として取得し、改行で文字列を切り分け、三つの要素を含む配列を変数dataに代入しているとします。
const name = data[0];
const date = data[1];
const attendance = data[2];
const sheet = SpreadsheetApp.getActiveSpreadsheet();
Sheet.appendRow([name,date,attendance]);
実のところ、spreadsheetの行末に値を入力するだけなら、Sheet.apppendRow(<リストインスタンス>)だけでよいのです。
GASメソッドの簡便さがありがたいですね。
4. 条件に従ってセルを更新する方法
三節で基本は押さえました。
しかし、このままでは値のバリデーションも、spreadsheetに既に値があるかどうかも、ある基準に従ってsortすることもできません。
今回の開発に際して、ここの部分が何気に一番の難関でした。
端折ってきたGASのコードもより詳しく掲載して、コードとロジックを解説していこうと思います。
まず、今回GASで実現したいことを確認します。
【目標】
Line MessageAPIから受け取った出席連絡をSpreadsheetに登録する。
【手順】
①Lineから送られてきた文字列を改行ごとに区切って、name,date,attendanceの三つの変数に代入する。
②行を日付、列を名前として、行に取得した日付がない場合に行末に日付を登録し、同様に列に取得した名前がない場合に列末に名前を登録し、出席値を登録する。
③日付をsortする。
以下、詳しいコードを紹介します。
function doPost(e) {
// 送信するスティッカーの種類やその値は別ファイルに関数の返り値として定義しました。
// falseがエラーの場合、trueが成功した場合に送信するスティッカーです。
const [failPackageId,failStickerId] = getStickers(false);
const error_messages = ( msg = "入力情報が正しくありません。") => [
// エラーハンドリング
{
"type":"sticker",
"packageId":failPackageId,
"stickerId":failStickerId,
},
{
type:"text",
text:`${msg}`
}
];
// 応答用Tokenを取得
const replyToken = JSON.parse(e.postData.contents).events[0].replyToken;
// メッセージを取得
const userMessage = JSON.parse(e.postData.contents).events[0].message.text;
// メッセージを改行ごとに分割
const all_msg = userMessage.split("\n");
if(all_msg.length !== 3) {
sendMessage(replyToken,error_messages());
return false;
}
const name = all_msg[0];
const date = all_msg[1];
const attendance = !!Number(all_msg[2]);
// 日付の形式とマッチするか、出席の値が0 1(falsy truthyな値)であるかどうかを判定しています。
const flag = date.match(/[0-9]{4}-[0-9]{2}-[0-9]{2}/) && all_msg[2] === "0" || all_msg[2] === "1";
if(!flag ) {
sendMessage(replyToken,error_messages());
return false;
}
const today = Utilities.formatDate(new Date(), "JST", "yyyy-MM-dd");
if(today > date) {
sendMessage(replyToken,error_messages("過去の記録を変更することはできません。"));
return false;
}
// 返答用メッセージを作成
const [successPackageId,successStickerId] = getStickers(true);
const messages = [
{
"type": "sticker",
"packageId":successPackageId,
"stickerId":successStickerId
}
];
// spreadsheetにデータを登録する準備。
const sheet = SpreadsheetApp.getActiveSpreadsheet();
const listSheet = sheet.getSheetByName("シート1");
// 行と列のインデックスを取得
try{
let dateIndex = findRowIndex(listSheet, date);
let nameIndex = findColumnIndex(listSheet, name);
// 行が存在しない場合は追加
if (dateIndex === 0) {
listSheet.appendRow([date]);
dateIndex = listSheet.getLastRow();
}
// 列が存在しない場合は追加
if (nameIndex === 0) {
const lastColumn = listSheet.getLastColumn();
listSheet.insertColumnAfter(lastColumn);
listSheet.getRange(1, lastColumn + 1).setValue(name);
nameIndex = lastColumn + 1;
}
// 出席状況を書き込み
listSheet.getRange(dateIndex, nameIndex).setValue(attendance);
// setValueは新規登録と上書きを兼ねるので、同じ日であれば参加連絡の更新を行えます。
}catch(error){
const msg = `原因不明のエラーが発生しました。管理者に連絡してください。\nエラー詳細:${error.message}`;
sendMessage(replyToken,error_messages(msg));
return false;
}
// 返答用メッセージを追加
const after_msg = {
'type': 'text',
'text': "データを登録しました。",
}
messages.push(after_msg);
// lineで返答する
sendMessage(replyToken,messages);
sortByDate(listSheet);
ContentService.createTextOutput(JSON.stringify({'content': 'post ok'})).setMimeType(ContentService.MimeType.JSON);
}
// 日付から行のインデックスを取得
function findRowIndex(sheet, date) {
const values = sheet.getRange("A:A").getDisplayValues();
const dates = values.map(row => row[0]);
return dates.indexOf(date) + 1;
}
// 名前から列のインデックスを取得
function findColumnIndex(sheet, name) {
const values = sheet.getRange("1:1").getValues();
const names = values[0];
return names.indexOf(name) + 1;
}
// 日付順でソートする。
function sortByDate(sheet){
const range = sheet.getRange(2,1,sheet.getLastRow() -1,sheet.getLastColumn());
range.sort(1);
}
// lineの返答メッセージを関数化。
function sendMessage(replyToken,messages) {
return UrlFetchApp.fetch(LINE_URL, {
'headers': {
'Content-Type': 'application/json; charset=UTF-8',
'Authorization': `Bearer ${LINE_TOKEN}`,
},
'method': 'post',
'payload': JSON.stringify({
'replyToken': replyToken,
'messages': messages,
}),
});
}
先述したように、行が日付で、列が名前です。
spreadsheetを検索して、第一列に既に取得した日付があるかどうかを判定するのがfindRowIndex関数、第一行の中で既に取得した名前があるのかどうかを判定するのがfindColumnIndex関数です。特に注意したいのは、findRowIndex関数でしょうか。
そのロジックを具体的に紹介してみようと思います。
function findRowIndex(sheet, date) {
// 第一列から値を取得。
const values = sheet.getRange("A:A").getDisplayValues(); // 重要！
// getValues()だと、セルの中身にまで潜って、そのデータ型ごと値を取得してきてしてしまいます。
// 即ち、日付型で値を取得してきてしまうため、Lineから得た文字列データ型と比較することが面倒なのです。
// このとき、セルの表面をなぞるように、表示されている値をそのまま文字列として取得するgetDisplayValue(s)メソッドが便利です。
const dates = values.map(row => row[0]);
// テーブルを取得する時、二次元配列（行列）の形になっています。
// 列だけを取得するメソッドは無いよう（見つけられませんでした）なので、mapで一個目の要素を返却して、新たな列の配列としています。
return dates.indexOf(date) + 1;
}
値が見つからなかった場合は、-1になりますので、+1したときはゼロです。
関数の返り値が0だったら日付を新しく登録、そうでなければ、その行番号を出席値を登録する行番号とする条件分岐処理を行っています。
それが、以下のコードです。
if (dateIndex === 0) {
listSheet.appendRow([date]);
dateIndex = listSheet.getLastRow();
}
最後に、日付のソートをしたいと思います。
過去の記録は変えられないように制御していますが、もしかしたら誤って一週間後の日付で参加登録をして、慌ててあさっての出席連絡をするかもしれません。
このままでは、一週間後の日付があるセルの下に、三日後の日付が来てしまい、期間ごとにおける出席日数確認がままならなくなってしまいます。
そこでやるのが日付ソートというわけです。
function sortByDate(sheet){
const range = sheet.getRange(2,1,sheet.getLastRow() -1,sheet.getLastColumn());
range.sort(1);
}
第一行第一列目(a11)のセルは空にしているので、第二列目から、日付がセルに登録されています。
getRange(2,1,sheet.getLastRow() -1,sheet.getLastColumn())とは、第二行第一列目を起点にして、行は最後の行数-1行分、列は名前が登録されてあるデータ全体を範囲として指定するということです。
これをsortすれば、無事日付の並びをきれいにそろえてデータを管理することができます。
5. おわりに
いかがだったでしょうか。
ユーザーに対してどのような入力を許すかという検証や、エラーの場合にメッセージを送ることなど、参照した記事の補足的・発展的な内容にすることができたと思います。運用していても特に問題なく自動化できているため、個人的にはいい仕上がりになったと思いますし、わたしはまんぞくです。
6. 参考
【GAS】スプレッドシートの入力日付をそのまま文字列で取得するgetDisplayValueメソッド
再掲
非エンジニアでもLINE Bot開発できる！ SpreadsheetをデータベースにGASで動かす
10分でLINE BotにLIFFフォームを追加してスプレッドシートにデータを保存