はじめに

「アレまであと何日？」「アレから何日経った？」など、ちょくちょく気になるけど、毎回計算するのはすごく大変ってことありませんか？

自分の場合だと、
「スプラトゥーンのアカウントロックがかかってから何日経ったっけ？？？（禁断症状）」
「確定申告の締め切りまであと何日だっけ？？？」
などなど、日数が気になることがたくさんあり、わざわざそのためにスプレッドシートで計算するのも面倒なので、登録したらすぐに教えてくれる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がない状態で回答が始まり、データ不整合が発生する
などの問題があります。

「アレまであと何日？」「アレから何日経った？」をすぐ教えてくれるLINEBotを作ったら地味に便利だった話