LoginSignup
5
1

More than 1 year has passed since last update.

@quwaji(加地 正典)in株式会社Aoba-BBT

LINE BotをGoogle Spreadsheetで実装 - 最新の集計をBotが応答

Posted at

前書き

この記事は連作で、ラン記録をシェアし、メンバーの走行記録を集計する活動を、LINE Botで自動化しようとしている取り組みです。

先々週の記事でのTODOと達成状況は以下のようになっていて、今回はそれらを実装した部分についてまとめたいと思います。

  • 返信と合わせてSpreadsheetに記録
  • 誰の記録かわかるようLINEのuserIDから名前を追加
  • 毎日定刻に、２４時間分の集計をLINEに送信
  • FBメッセンジャーから引っ越してもらう

記事のタイトルも重要ですね。前回の記事ではLINE Botとして動作させるための諸事項もスキップして書いたので、また別途まとめたいと思います。

返信と合わせてSpreadsheetに記録

前回、アップされた画像から「距離」「タイム」を検出できたら返信するところまでできていました。
image.png
これを記録しておき、「集計」って言われたら集計してサマリを返すのが今回のゴールです。
そんなわけで、解析した結果を記録する「Analaize Log」というシートに、追記していきます。

解析結果を記録する

今回の実装での最終的なWebhookは下記のようになりました。

コード.gs
function doPost(e) {

  Logger.log('webhook received: ' + e.postData.contents);
  recordRequest(e);

  var userId = JSON.parse(e.postData.contents).events[0].source.userId;
  var type = JSON.parse(e.postData.contents).events[0].source.type;
  var groupId = '';
  var replyTo = '';
  if(type == 'group') {
    groupId = JSON.parse(e.postData.contents).events[0].source.groupId;
    replyTo = groupId;
  }
  else {
    replyTo = userId;
  }
  var msgType = JSON.parse(e.postData.contents).events[0].message.type;
  var messageText = '';
  switch(msgType) {
    case 'text':
      messageText = JSON.parse(e.postData.contents).events[0].message.text;
      if(messageText == '集計') {
        sendLine(replyTo, getSummary());
      }
      break;
    case 'image':
      var messageId = JSON.parse(e.postData.contents).events[0].message.id;
      // コンテンツをGETして解析、ラン画像なら返信。
      var image = getContent(messageId);
      var obj = analyzeImage(image);
      var result = obj.responses[0].textAnnotations[0].description;
      var duration = detectTime(result);
      var distance = detectDistance(result);
      Logger.log('image analyzed: ' + result + String.fromCharCode(10) + 'distance: ' + distance + ', duration: ' + duration);
      recordResult(userID, groupId, result, JSON.stringify(obj), distance, duration);
      if(duration != null && distance != null) {
        messageText = 'ナイスラン！' + String.fromCharCode(10);
        messageText += '距離' + String.fromCharCode(9) + distance + String.fromCharCode(10);
        messageText += 'タイム' + String.fromCharCode(9) + duration;
        sendLine(replyTo, messageText);
      }
      break;
    default:
  }

  return JSON.stringify({});

}

前回からの追加としては、recordResult()を作成、これを呼び出して、解析結果のスプレッドシートに追加しています。

また「集計」と言ったら集計を返す動作を実装しましたので、下記の分岐が追加になっています。

    case 'text':
      messageText = JSON.parse(e.postData.contents).events[0].message.text;
      if(messageText == '集計') {
        sendLine(replyTo, getSummary());
      }
      break;

getSummary()は、後述します。

解析結果が記録された「Analyze Log」シートのイメージ
image.png
解析結果をスプレッドシートに追加するコードはこちら。

コード.gs

function recordResult(userId, groupId, analyzed, textAnnotations, distance, duration) {

  date = new Date();

  //  
  // 結果をスプレッドシートに追記
  //

  var ss = SpreadsheetApp.getActive()
  var sheet = ss.getSheetByName('Analyze Log');

  // ヘッダ行を取得
  var headers = sheet.getRange(1,1,1,sheet.getLastColumn()).getValues()[0];

  // ヘッダに対応するデータを取得
  var values = [];
  for (i in headers){
    var header = headers[i];
    var val = "";
    switch(header) {
      case "date":
        val = new Date();
        break;
      case "analyzed":
        val = analyzed;
        break;
      case "userId":
        val = userId;
        break;
      case "groupId":
        val = groupId;
        break;
      case "textAnnotations":
        val = textAnnotations;
        break;
      case "name":
        val = getListedUserName(userId);
        if(val == 'unknown') {
          // リストになければ追加しておく
          recordUser(userId);
        }
        break;
      case "distance":
        val = distance;
        break;
      case "duration":
        val = duration;
        break;
      default:
        break;
    }
    values.push(val);
  }

  // 行を追加
  sheet.appendRow(values);
}

analyzedは、Cloud Vision APIでテキスト抽出した際に返された、改行区切りのテキストで、上のイメージでは、以下のようなテキストです。

PELAR
サマリー
開始時間
O
△
9:14
O
00:55:29.3
8.05km
MH30
△

加えて、今後の取り組みのためにCloud Vision APIのレスポンスもまるっと textAnnotationsというカラムに記録しています。

名前は後述する処理で取得し、ない場合はUser Listに追加しています。

      case "name":
        val = getListedUserName(userId);
        if(val == 'unknown') {
          // リストになければ追加しておく
          recordUser(userId);
        }
        break;

誰の記録かわかるようLINEのuserIDから名前を追加

LINE Messenging APIのWebhookではuserIdが通知されます。グループトークの場合はgroupIdも通知されます。
しかし名前は含まれません。お友達登録されているuserIdであれば、LINEのAPIで取得できるのですが、グループに参加しただけでは友達の関係になく、取得できません。また、取れても集計に表示したい名前とは限りません。
そこで、Spreadsheetに「User List」シートを作成し、通知されるuserIdと集計の時に使用したい名前をリストにしておきます。
image.png
このシートを使ってuserIdで検索して名前を得るgetListedUserName()は以下のようにしました。

コード.gs
function getListedUserName(userId) {

  var ss = SpreadsheetApp.getActive()
  var sheet = ss.getSheetByName('User List');

  const lastRow = sheet.getLastRow();

  for (var i = 2; i <= lastRow; i++) {
    if (sheet.getRange(i, 1).getValue() == userId) {
      return sheet.getRange(i, 2).getValue();
    }
  }
  return 'unknown';

}

ベタに、ぐるぐる行を順次走査し一致するuserIdがあれば２列目の名前を返しています。

この呼び出し元で、unknownが返された場合にリストへuserIdを追加するrecordUser()が下記になります。

コード.gs
function recordUser(userId) {

  //  
  // userIdをスプレッドシートに追記
  //

  var ss = SpreadsheetApp.getActive()
  var sheet = ss.getSheetByName('User List');

  // ヘッダ行を取得
  var headers = sheet.getRange(1,1,1,sheet.getLastColumn()).getValues()[0];

  // ヘッダに対応するデータを取得
  var values = [];
  for (i in headers){
    var header = headers[i];
    var val = "";
    switch(header) {
      case "userId":
        val = userId;
        break;
      case "displayName":
        val = '未設定さん';
        break;
      case "addDate":
      case "updateDate":
        val = new Date();
        break;
      default:
        break;
    }
    values.push(val);
  }

  // 行を追加
  sheet.appendRow(values);
}

はじめて追加されたメンバーは、まだ手で名前を設定しています。行の追加は自動化できました。
LINEのWebhookで、グループにメンバーが参加したイベントも取得できるため、そのタイミングでUser Listへ追加をしたり、自動返信のようにWelcomeメッセージで集計に表示する名前を尋ねても良いかもしれません。

毎日定刻に、２４時間分の集計をLINEに送信

まず、手集計を行っていた部分を自動化しました。
Spreadsheetにはquery関数があり、あるシート範囲をテーブルに見立ててsqlのように検索集計し、結果をそのセルの位置に展開してくれます。
image.png
これを使って、「Analize Log」に記録されたラン記録のなかから、前日（8:30以降は当日）朝8:10以降の記録を集計します。条件が計算式の中には書けなかったため、スクリプトでセルの計算式を更新しています。
計算式を更新し、集計した情報を整形したテキストを返すgetSummary()はこんな感じ。

コード.gs
function getSummary() {

  var ss = SpreadsheetApp.getActive()
  var sheet = ss.getSheetByName('24h Report');
  var key = Utilities.formatDate(getCurrentPeriod(), "JST", "yyyy-MM-dd HH:mm:ss");
  var result = `${key} からの集計\n`;

  // queryの条件（抽出対象期間）を更新
  sheet.getRange(1, 1).setValue(`=QUERY('Analyze Log'!A:G,"SELECT E, F, G WHERE A > datetime '${key}'", -1)`);

  // queryで転写された24時間以内のラン記録を取得
  var records = sheet.getRange(1,1,sheet.getLastRow(),3).getDisplayValues();
  for(i = 1; i < sheet.getLastRow(); i++) {
    result += records[i][0] + '\t' + records[i][1] + '\t' + records[i][2] + '\n';
  }
  // 計算式で集計された合計距離・走行時間と残り距離
  var summary = sheet.getRange(1,5,3,3).getDisplayValues();
  for(i = 1; i < 3; i++) {
    result += '\n' + summary[i][0] + '\t' + summary[i][1] + '\t' + summary[i][2];
  }
  if(summary[1][1] == '0' && summary[1][2] == '0') {
    return '記録なし';
  }

  return result;
}

queryに与える基準日時を組み立てるgetCurrentPeriod()はこのようになっています。

コード.gs
function getCurrentPeriod() {
  // 現在時刻から集計対象の日時を返す。
  // 8:30 まで、前日の8:10を返す。
  var dt = new Date();
  if(dt.getHours() * 100 + dt.getMinutes() < 830) {
    dt.setDate(dt.getDate() -1);
  }
  dt.setHours(8);
  dt.setMinutes(10);
  dt.setSeconds(0);

  return dt;
}

image.png
「集計」というだけで、その時点での集計が返されるようになりました！

毎朝8時過ぎに、そこまでのアップ状況を見て集計していましたが、叩けば最新の集計が得られるので、まだ報告の自動化はしなくてもよさそうです。

FBメッセンジャーから引っ越してもらう

こちらは何らテクニカルなことではないのですが、記録の自動起こしがまぁまぁ安定してきたので、フィードバックももらえたらと、段階的にLINEグループに入ってもらい、下地を作っていきました。

まず現在のFBメッセンジャーグループを維持してくださっているメンバーの方を追加し、スクショや写真から認識されるところ、「集計」というと集計結果を返してくれるところを見てもらいました。
数日、その状態で朝の集計を送り、リアルに会う機会があった日に、他のメンバー二人もグループに参加してもらってスクショを自身でアップしてみてもらいました。
いけそうな感触を持ってもらえたので、いよいよFBグループの方にも案内し、LINEグループへの移行が始まりました！

これで朝、寝坊したり緊急事態になっても大丈夫！　にかなり近づいた。

query()関数でハマったこと

datediff

目からウロコなquery()関数だったのですが、datetimeの条件を指定するのにSQLのようには柔軟に指定できませんでした。

最初、A列にあるLog日時を使って

=QUERY('Analyze Log'!A:G,"SELECT E, F, G WHERE datediff(A, now()) >= 0")

のように書いてみていましたが、24時を跨いでdatediffの結果が変わるので、24時間以内といった抽出はできませんでした。

また条件中にcase when ... と書けるわけでなく、結局条件に与える値を編集し、都度セルのQUERY式を更新することになりました。

group by

同じ期間内に２回走って２つのラン記録をアップしてくれたメンバーがいました。group byでsumできるかと思いましたが経過時刻のような形式がなく集計できませんでした。

TODO

  • 集計はgroupIdまたはuserId単位に行う → 複数のグループや開発用のトークと混じらないように。
  • 記録が間違っていた時に編集削除できる → Botのナイスラン！発言に返信とか分かりやすそう。
  • 新しいユーザがJoinしたらUser Listに追加
    • Welcomeメッセージを発して名前を入れてもらうのもアリか。
  • 検出された候補（距離・タイム）が複数あった場合にLINEのクイックリプライとか使えるか検討
  • Cloud Vision APIの使用状況確認と課金への対応検討 → 月1000ユニットまで無料だが、利用が進むと収まらない恐れも。

記録の訂正削除、名前の設定ができればメンテフリーになりそう。もう一息だ！

参考記事・サイト

転写の仕掛けは以下の記事がわかりやすかった。

ユーザ名を検索するのも下記の記事を参考にした。結局User Listではquery()関数を使わなかったが。。

5
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
5
1