はじめに
「アレまであと何日？」「アレから何日経った？」など、ちょくちょく気になるけど、毎回計算するのはすごく大変ってことありませんか？
自分の場合だと、
「スプラトゥーンのアカウントロックがかかってから何日経ったっけ？？？（禁断症状）」
「確定申告の締め切りまであと何日だっけ？？？」
などなど、日数が気になることがたくさんあり、わざわざそのためにスプレッドシートで計算するのも面倒なので、登録したらすぐに教えてくれるLINEBotを作ってみました。
作ったもの
「登録」から送られてくるURLから、計算したいアイテムを登録すると、
「計算」と押すと、日数がリプライされ、
「完了」を押すと、クイックリプライで選択肢が表示され、アイテムを完了します。
こちらのQRコードから友達追加してみてください！
＊ ちなみにアイコンやリッチメニューはCanvaで5分くらいで作りました。
システム構成
リッチメニュー
以下のような構成で、「登録」「計算」「完了」という文字に対応して返答します。
「登録」のとき
「計算」のとき
「完了」のとき
クイックリプライで、完了にしたいアイテムを指定させます
実装
前提
GASでLINEBotを作るための準備はこちらを参考にしてください。
5分でつくるLINEBot（改良版）
作成手順
① 5分でつくるLINEBot（改良版）をもとに、オウム返しのLINEBotを作る
② LINE Bot でGoogleフォームを使って簡易的なユーザー登録をさせ、プッシュメッセージを送るを参考に、登録用のグーグルフォームを準備し、回答を①のスプレッドシートに書き込む設定にする（この記事の後半にポイント解説あり）
フォームは以下の様に、「タイトル」「日数の計算形式」「日付」「user_id」で構成されています
③ スプレッドシートにシート「calculate」を追加する
シート「calculate」は以下の様に「A列：タイトル」「B列：日数の計算形式」「C列：日付」「D列：user_id」「E列：経過日数」「F列：あと何日」「G列：完了」という構成にしています。
④ Google App Scriptを以下の「全体のコード」で全部上書きし、自分の「CHANNEL_ACCESS_TOKEN」「formUrl」を設定する
⑤ GoogleFormで回答してもらった情報をSpreadSheetの関数で加工するを参考に、Googleフォームに回答があるたびに内容をコピーする関数を実行する
⑥ LINE Botでリッチメニューを表示する等を参考に、リッチメニューを設定
で完成！（のハズ）
全体のコード
var CHANNEL_ACCESS_TOKEN = 'あなたのLINEBotのチャンネルアクセストークンを貼り付けてください';
const logSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('log');
var replyToken, json
const formUrl = "あなたのGoogleFormのURLを貼り付けてください 例=> https://docs.google.com/forms/d/e/xxxxxx/viewform?usp=pp_url&entry.20xxxxxxx="
//ポストで送られてくるので、ポストデータ取得
function doPost(e) {
// 動作確認用のログ出力
// log_to_sheet("A", "doPost")
// LINEBotから送られてきたデータを、プログラムで利用しやすいようにJSON形式に変換する
json = JSON.parse(e.postData.contents);
//返信するためのトークン取得
replyToken= json.events[0].replyToken;
if (typeof replyToken === 'undefined') {
return;
}
let user_message = json.events[0].message.text;
// ユーザーID
let userId = json.events[0].source.userId
let messages
if(user_message == '登録') {
messages = register_message(userId)
} else if (user_message == '計算'){
messages = calculate_message(userId)
} else if (user_message == '完了'){
messages = complete_message(userId)
} else if (user_message.match(/^complete:\d+/) ) { // メッセージが"complete:" + 数字1文字以上 の時は、該当の行に「完了」と入力
messages = fillOutComplete_message(userId, user_message.split(':')[1]) // splitで「:」で区切って配列にし、2番目（行番号）を渡す
} else {
// log_to_sheet("B", json.events[0])
}
// 返信するメッセージを作成
// messages = test_message()
// メッセージの中身を確認したい時には以下のコメントアウトを外して、sheet「log」に書き込まれる内容を確認しましょう
// log_to_sheet("A", messages)
// line-bot-sdk-gas のライブラリを利用しています ( https://github.com/kobanyan/line-bot-sdk-gas )
const linebotClient = new LineBotSDK.Client({ channelAccessToken: CHANNEL_ACCESS_TOKEN });
// メッセージを返信
linebotClient.replyMessage(replyToken, messages);
return ContentService.createTextOutput(JSON.stringify({'content': 'post ok'})).setMimeType(ContentService.MimeType.JSON);
}
// 動作確認用のオウム返しのメッセージを作成する関数
// function test_message() {
// //送られたLINEメッセージを取得
// var user_message = json.events[0].message.text;
// //送られたメッセージをそのままオウム返し
// var reply_messages = [user_message,user_message,user_message];
// // メッセージを返信
// var messages = reply_messages.map(function (v) {
// return {'type': 'text', 'text': v};
// });
// return messages
// }
// 登録フォームのURLを返却
function register_message(userId) {
if(getTargetRows(userId).length <= 10){
return [{'type':'text', 'text': 'アイテムの登録はこちらから\n' + formUrl + userId}]
} else {
// 「完了」の時に使う、クイックリプライの最大が13個なので登録を10個までに制限しておく
return [{'type':'text', 'text': 'アイテムの登録は10個までです。\n新たに登録したい場合は、完了してください。'}]
}
}
// スプレッドシートで計算してある日数を文字列にまとめて返却
function calculate_message(userId) {
let messages = []
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('calculate');
const target_rows = getTargetRows(userId)
if(target_rows.length > 0){
let daysMessage = []
for(let i = 0; i < target_rows.length; i++){
let title = sheet.getRange(`A${target_rows[i]}`).getValue()
let calculate_type = sheet.getRange(`B${target_rows[i]}`).getValue()
let days
if(calculate_type == '何日経ったか'){
days = sheet.getRange(`E${target_rows[i]}`).getValue()
daysMessage.push(`「${title}」から、${days}日経ちました`)
} else if (calculate_type == 'あと何日か'){
days = sheet.getRange(`F${target_rows[i]}`).getValue()
daysMessage.push(`「${title}」まで、あと${days}日です`)
}
messages = [{'type':'text', 'text': daysMessage.join('\n')}]
}
}else{
messages = [{'type':'text', 'text': '登録されているアイテムはありません'}]
}
return messages
}
// 「完了」にしたいものをクイックリプライ形式で返却
function complete_message(userId) {
let messages = []
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('calculate');
const target_rows = getTargetRows(userId)
let items = []
if(target_rows.length > 0){
for(let i = 0; i < target_rows.length; i++){
let title = sheet.getRange(`A${target_rows[i]}`).getValue()
items.push({
"type": "action",
"action": {
"type": "message",
"label": title,
"text": `complete:${target_rows[i]}`
}
})
if(items.length > 10){
break;
}
}
return [{
"type": "text",
"text": "完了にしたいものを選択してください",
"quickReply": {
"items": items
}
}]
} else {
return [{'type':'text', 'text': '登録されているアイテムはありません'}]
}
}
// クイックリプライで返却された完了にしたいアイテムに「完了」と入力する
function fillOutComplete_message(userId, row){
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('calculate');
if(sheet.getRange(`D${row}`).getValue() == userId){
let title = sheet.getRange(`A${row}`).getValue()
sheet.getRange(`G${row}`).setValue('完了')
return [{'type':'text', 'text': `「${title}」を完了にしました`}]
}else{
return [{'type':'text', 'text': '完了にできませんでした'}]
}
}
// 指定されたuserIdで検索を行い、「完了」なっていないアイテムの行番号を配列にして返却
function getTargetRows(userId) {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('calculate');
var allData = sheet.getDataRange().getValues(); // データを全件取得
var target_rows = [];
// ヘッダーを含める場合はi=0で初期値を与える
for(var i = 1; i < allData.length; i++) { // データを順に調べていく
// userIDは4列目なので、配列は0からはじまるので4-1で3が指定される
if(allData[i][4-1].indexOf(userId) != -1 && allData[i][7-1] != "完了"){
target_rows.push(i+1); // 検索に引っかかる箇所の行番号を配列に集める
}
}
return target_rows
}
// フォームに回答があるたびに、「フォームの回答 1」シートから計算用のシートに値と関数をコピーする
function copyFormToCalculate() {
const formSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('フォームの回答 1');
const calculateSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('calculate');
const formData = formSheet.getDataRange().getValues();
const calculateData = calculateSheet.getDataRange().getValues();
if(formData.length == calculateData.length){
console.log('no copy')
} else {
console.log('do copy')
let num = calculateData.length
while(num < formData.length){
calculateSheet.getRange(`A${num+1}`).setValue( formSheet.getRange(`B${num+1}`).getValue() )
calculateSheet.getRange(`B${num+1}`).setValue( formSheet.getRange(`C${num+1}`).getValue() )
calculateSheet.getRange(`C${num+1}`).setValue( formSheet.getRange(`D${num+1}`).getValue() )
calculateSheet.getRange(`D${num+1}`).setValue( formSheet.getRange(`E${num+1}`).getValue() )
calculateSheet.getRange(`E${num+1}`).setValue( `=datedif(C${num+1},today(),"D")` )
calculateSheet.getRange(`F${num+1}`).setValue( `=datedif(today(),C${num+1},"D")` )
num++
}
}
}
// 処理の確認用にログを出力する関数
function log_to_sheet(column, text) {
if(logSheet.getRange(column + "1").getValue() == ""){
lastRow = 0
} else if(logSheet.getRange(column + "2").getValue() == ""){
lastRow = 1
} else {
var lastRow = logSheet.getRange(column + "1").getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow();
// 無限に増えるので1000以上書き込んだらリセット
console.log("lastRow", lastRow)
if(lastRow >= 1000){
logSheet.getRange(column + "1:" + column + "10").clearContent()
lastRow = 0
}
}
var putRange = column + String(lastRow + 1)
logSheet.getRange(putRange).setValue(text);
}
ポイント① LINEのuserIDをGoogleフォームに渡す
LINE Bot でGoogleフォームを使って簡易的なユーザー登録をさせ、プッシュメッセージを送る
こういった記事を参考に、グーグルフォームを開いたときに、既に値が入っている状態で開けるURLを取得します。
取得したURLをコードの以下の箇所に貼り付けを行います。
const formUrl = "あなたのGoogleFormのURLを貼り付けてください 例=> https://docs.google.com/forms/d/e/xxxxxx/viewform?usp=pp_url&entry.20xxxxxxx="
LINEアカウントに「登録」という文字が送られてきた場合に呼び出されるregister_message関数です。
// 登録フォームのURLを返却
function register_message(userId) {
if(getTargetRows(userId).length <= 10){
return [{'type':'text', 'text': 'アイテムの登録はこちらから\n' + formUrl + userId}]
} else {
// 「完了」の時に使う、クイックリプライの最大が13個なので登録を10個までに制限しておく
return [{'type':'text', 'text': 'アイテムの登録は10個までです。\n新たに登録したい場合は、完了してください。'}]
}
}
'アイテムの登録はこちらから\n' + formUrl + userId
\n は改行です。
さきほど設定したformUrl に ユーザーのIDをくっつけて返却しています。
entry.20xxxxxxx=
というふうに 「=」 で終わるのがURLのポイントです。
ポイント② Googleフォームの回答を関数で集計できるように、回答のたびに別のシートにコピーする
こちらの記事に詳細をまとめました
GoogleFormで回答してもらった情報をSpreadSheetの関数で加工する
上記記事の「解決法① GASを使ってフォームに回答があるたびに別シートにコピーする」のやり方で、フォームに回答があるたびに以下のcopyFormToCalculate関数を実行して別シートに情報をコピー、関数を埋め込んで日数を計算しています。
// フォームに回答があるたびに、「フォームの回答 1」シートから計算用のシートに値と関数をコピーする
function copyFormToCalculate() {
const formSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('フォームの回答 1');
const calculateSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('calculate');
const formData = formSheet.getDataRange().getValues();
const calculateData = calculateSheet.getDataRange().getValues();
if(formData.length == calculateData.length){
console.log('no copy')
} else {
console.log('do copy')
let num = calculateData.length
while(num < formData.length){
calculateSheet.getRange(`A${num+1}`).setValue( formSheet.getRange(`B${num+1}`).getValue() )
calculateSheet.getRange(`B${num+1}`).setValue( formSheet.getRange(`C${num+1}`).getValue() )
calculateSheet.getRange(`C${num+1}`).setValue( formSheet.getRange(`D${num+1}`).getValue() )
calculateSheet.getRange(`D${num+1}`).setValue( formSheet.getRange(`E${num+1}`).getValue() )
calculateSheet.getRange(`E${num+1}`).setValue( `=datedif(C${num+1},today(),"D")` )
calculateSheet.getRange(`F${num+1}`).setValue( `=datedif(today(),C${num+1},"D")` )
num++
}
}
}
ここではシート「フォームの回答 1」からシート「calculate」に値をコピーしています
シート「calculate」は以下の様な構成にしています。
日数を計算するために、スプレッドシートに以下の関数を設定します
2つの日付から期間を計算するDATEDIF関数
=datedif(開始日,終了日,"D") // 基本式
=datedif(C${num+1},today(),"D") // 何日経過したか
// または
=datedif(today(),C${num+1},"D") // あと何日か
ポイント③ 完了したいアイテムをクイックリプライで選択させる
「完了」とう文言に反応して、以下のようにクイックリプライを飛ばします。
それぞれがボタンのようになっていて、押すと設定した値をテキストとして送信します。
// 「完了」にしたいものをクイックリプライ形式で返却
function complete_message(userId) {
let messages = []
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('calculate');
const target_rows = getTargetRows(userId)
let items = []
if(target_rows.length > 0){
for(let i = 0; i < target_rows.length; i++){
let title = sheet.getRange(`A${target_rows[i]}`).getValue() // 登録されているアイテムの名前
items.push({
"type": "action",
"action": {
"type": "message",
"label": title,
"text": `complete:${target_rows[i]}` // クイックリプライを押すと送信される値
}
})
// MessagingAPIの仕様で、クイックリプライは13件までしか送れないので、10個までしか送れない様に制限をかけています
if(items.length > 10){
break;
}
}
return [{
"type": "text",
"text": "完了にしたいものを選択してください",
"quickReply": {
"items": items
}
}]
} else {
return [{'type':'text', 'text': '登録されているアイテムはありません'}]
}
}
// complete:何か数字 という形式の文字に反応してアイテムを完了にする関数
// クイックリプライで返却された完了にしたいアイテムに「完了」と入力する
function fillOutComplete_message(userId, row){
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('calculate');
if(sheet.getRange(`D${row}`).getValue() == userId){
let title = sheet.getRange(`A${row}`).getValue()
sheet.getRange(`G${row}`).setValue('完了')
return [{'type':'text', 'text': `「${title}」を完了にしました`}]
}else{
return [{'type':'text', 'text': '完了にできませんでした'}]
}
}
おわりに
以上、yubioriの作り方でした！
「データを登録して、処理を加え、用が済んだら完了にする」といった基本的なデータの流れをLINEBotで実現できるので、他にもいろいろ応用が効くんじゃないかと思います！
ぜひぜひこれをベースにいろいろ作ってみてください！
プロトタイプとしては使えますが、いろいろセキュリティには問題があるので、個人情報などの重要な情報を扱うときには、エンジニアさんに相談するなどきちんとしたものを作りましょう。
たとえば、
・スプレッドシートのURLが漏洩したら、内容を直で書き換えられる恐れがある
・ユーザーログインなどがないので、GASへのURLアクセスを解析すれば改ざんが容易
・Googleフォームで「続けて回答」を選択すると、userIDがない状態で回答が始まり、データ不整合が発生する
などの問題があります。