GASとLINE Botによる会議出欠管理システムを作ってみた（コード記載あり）

概要

GAS(Google Apps Script)とGoogleSpreadSheetでLine Botをつくってみました。何か使えるモノを作った方がよいと思い、カンタンな会議出席管理システムをつくりました。GASのコードも併せて記載しますので、記載されているコードを流用してシートやスプレッドシートのIDなどを設定していただけばご利用いただけます。

どんなシステム？

まずはLine Botを友達招待する

Messaging API setingsのQR codeから友達としてLine Botを招待してもらうことでユーザーに使ってもらえます。LINE Developersの設定については以下のページで説明します。

ユーザー登録をする

友達から登録するとあいさつのメッセージが表示されます。メッセージに従ってユーザーを登録します。登録することでスプレッドシートのユーザー管理のシートにユーザーとして追加され会議出欠の通知を受け取れます。

会議の通知を受け取り出欠を連絡する

スプレッドシートから会議出欠に案内を連絡します。

登録されているユーザーに一斉に通知され、通知されたユーザーは出欠の参加について「出席・欠席」を連絡することができます。途中に出欠を変更した場合は出欠の変更（更新）も可能です。

構成・設定

• Google Spread Sheet

  • 出欠データのシート(AttendanceList)
  • 会議マスターのシート(MeetingList)
  • ユーザーのシート(UserList)

• Google Apps Script

  • Main.gs
  • Common.gs
  • Fetch.gs
  • UserFunc.gs
  • MeetFunc.gs

• LINE Developers

  • Provider
  • Channnel
  • Messaging API

Google Spread Sheet

シートは３つになります。シート名はGASのコード側に記載していますので、もしシート名を変更したい場合は以降のコードの記述する際に該当箇所を変更してください。

出欠データのシート(AttendanceList)

出欠データをインサートするシートです。ユーザーから出欠の連絡があったら、出欠の会議がない場合は新しい行を追加します。以前に出欠した行がある場合は更新します。

会議マスターのシート(MeetingList)

ユーザーに会議の連絡をしたり出欠の連絡があった際に会議IDを参照するシートです。TRUEしてある会議のみユーザーに通知されます。

ユーザーのシート(UserList)

ユーザー登録されたユーザーのIDや氏名を記載するシートです。IDはLineのIDなので取り扱い注意です。そのままLineのIDだとさすがにコワイのでbase64でエンコードしています。ここから逆にこのシートからbase64でデコードすることでLineのIDを取り出すことができます。

画像はモザイクかけた方がよさそうですが表示が途切れているので大丈夫だと思っています。

Google Apps Script

GASのファイルは機能ごとに分けています。コードの記述形式はJavaScriptに近いのですが、ファイルを分けても特段インポートなどは不要のようです。

Main.gs

function doPost(e) {
  const postJson = JSON.parse(e.postData.contents);
  const replyToken = postJson.events[0].replyToken;
  const sendMsg = postJson.events[0].message.text;
  const userId = postJson.events[0].source.userId;
  const postResult = ContentService.createTextOutput(JSON.stringify({'content': 'post ok'})).setMimeType(ContentService.MimeType.JSON);
  const events = postJson["events"];
  
  if (sendMsg.indexOf('登録') != -1){
    ReplyFetch(UpsertUser(userId, sendMsg), replyToken);
  }
  
  if (sendMsg.indexOf('出席') != -1 || sendMsg.indexOf('欠席') != -1){
    ReplyFetch(UpsertMeeting(userId, sendMsg), replyToken);
  }
  
  return postResult;
}

主にdoPostでイベントを受け取りだけを記載します。ユーザーからのメッセージのキーワードから処理を分岐させています。Main.gsには書こうと思えばいくらでも書けるので、機能や役割ごとにファイルに分けた方がわかりやすいと思っています。

Common.gs

// LINE developersのメッセージ送受信設定に記載のアクセストークン
const CHANNEL_ACCESS_TOKEN = '';
const API_URL = 'https://api.line.me/v2/bot/message';

// 書込先スプレッドシートのIDを入力
const SPREAD_ID = '';
const SHEET_ATTEND = 'AttendanceList';
const SHEET_MEET = 'MeetingList';
const SHEET_USER = 'UserList';

function FindRow(sheet,col,val){
  const dat = sheet.getDataRange().getValues(); //受け取ったシートのデータを二次元配列に取得
  let row = 0
  for(let i=1; i<dat.length; i++){
    if(dat[i][col-1] == val){
      // 一番下まで検索
      row = i + 1;
      //// 上から見つかったら処理中断したい場合は以下を使用
      //      return i+1;
    }
  }
  return row;
}

function GetColumnList(sheet, col, row){
  const colObjList = sheet.getRange(row, col, sheet.getLastRow() - 1).getValues();
  let resultList = [];
  colObjList.forEach(value => resultList.push(value[0]));

  return resultList;
}

// エンコード
function EncodeBase64Text(text) {
  return Utilities.base64Encode(text, Utilities.Charset.UTF_8)
}

// デコード
function DecodeBase64Text(text) {
  const dec = Utilities.base64DecodeWebSafe(text);
  return Utilities.newBlob(dec).getDataAsString();
}

共用で使う定数や処理はCommon.gsに記載しています。
CHANNEL_ACCESS_TOKENはLine DevelopersのMessaging APIの画面から Channel access tokenのトークンを記載します。

SPREAD_IDはスプレッドシートを開いているときのURLの一部分から取得します。以下の内容を参考にしてください。
https://docs.google.com/spreadsheets/d/[ここがSPREAD_IDの該当箇所です]/edit
シート名を変更したい場合は合わせて定数部分も変更が必要になります。

Fetch.gs

function ReplyFetch(sendMsg, replyToken) {
  const replyUrl = API_URL + '/reply';
  UrlFetchApp.fetch(replyUrl, {
    'headers': {
      'Content-Type': 'application/json; charset=UTF-8',
      'Authorization': 'Bearer ' + CHANNEL_ACCESS_TOKEN,
    },
    'method': 'post',
    'payload': JSON.stringify({
      'replyToken': replyToken,
      'messages': [{
        'type': 'text',
        'text': sendMsg,
      }],
    }),
  });
}

function PushFetch(sendMsg, userId) {
  const pushUrl = API_URL + '/push';
  UrlFetchApp.fetch(pushUrl, {
    'headers': {
      'Content-Type': 'application/json; charset=UTF-8',
      'Authorization': 'Bearer ' + CHANNEL_ACCESS_TOKEN,
    },
    'method': 'post',
    'payload': JSON.stringify({
      "to" : userId,
      'messages': [{
        'type': 'text',
        'text': sendMsg,
      }],
    }),
  });
}

function ConfirmFetch(sendMsg, userId, meetId, meetName) {
  const pushUrl = API_URL + '/push';
  UrlFetchApp.fetch(pushUrl, {
    'headers': {
      'Content-Type': 'application/json; charset=UTF-8',
      'Authorization': 'Bearer ' + CHANNEL_ACCESS_TOKEN,
    },
    'method': 'post',
    'payload': JSON.stringify({
      "to" : userId,
      'messages': [{
        "type": "template",
        "altText": "会議案内",
        "template": {
          "type": "confirm",
          "text": sendMsg,
          "actions": [
            {
              "type":"message",
              "label":"出席",
              "text": meetId + '. ' +  meetName + ' に出席'
            },
            {
              "type":"message",
              "label":"欠席",
              "text": meetId + '. ' +  meetName + ' に欠席'
            }
          ]
        }
      }],
    }),
  });
}

ユーザーに対してメッセージを返信・送付する処理が記載されています。
ReplyFetchはユーザーに返信する形でreplyTokenを用いて送付します。
PushFetchはLINEのIDでメッセージを送付します。
ConfirmFetchはYES/NO形式の会議出席用のテンプレートで送付します。

UserFunc.gs

function UpsertUser(userId, sendMsg){
  const userlistSh = SpreadsheetApp.openById(SPREAD_ID).getSheetByName(SHEET_USER);
  const userName = NameSplit(sendMsg);
  const userId_b64 = EncodeBase64Text(userId); 
  
  if(userName != ''){
    const userCheckVal = FindRow(userlistSh,1,userId_b64);
    let replyMsg = '';
    console.log(userCheckVal);
    if (userCheckVal　> 0){
      userlistSh.getRange(userCheckVal, 2).setValue(userName);
      replyMsg = 'ユーザー名を更新しました。';
    } else {
      userlistSh.appendRow([userId_b64, userName]);
      replyMsg = 'ユーザー名を新規登録しました。'
    }
        
    return replyMsg ? replyMsg : 'ユーザー登録に失敗しました。';
  }
}

function NameSplit(sendMsg){
  if (sendMsg.indexOf(`(`) != -1 && sendMsg.indexOf(`)`) != -1 ) {
    return sendMsg.match(/\((.*.)\)/,'g')[1];
  } else if (sendMsg.indexOf(`[`) != -1 && sendMsg.indexOf(`]`) != -1 ) {
    return sendMsg.match(/\[(.*.)\]/,'g')[1];
  } else if (sendMsg.indexOf(`「`) != -1 && sendMsg.indexOf(`」`) != -1 ) {
    return sendMsg.match(/「(.*.)」/,'g')[1];
  } else if (sendMsg.indexOf(`［`) != -1 && sendMsg.indexOf(`］`) != -1 ) {
    return sendMsg.match(/［(.*.)］/,'g')[1];
  } else if (sendMsg.indexOf(`【`) != -1 && sendMsg.indexOf(`】`) != -1 ) {
    return sendMsg.match(/【(.*.)】/,'g')[1];
  }
  else {
    return ''
  }
}

ユーザーの登録が行われた場合に、LINEのIDとユーザーネーム（氏名）を追加します。すでに登録されているユーザーについては更新します。

すでにある場合は更新する仕組みであるため、関数名もUpsertとしています。これはMeetFunc.gsも同様の仕組みにしてあります。ただ会議の出席については別にInsertでもよかったのかなと思ったりします。

LINEのIDがわかるとメッセージをPushで通知できるようになるので取り扱いには注意が必要です。今回は若干マシになる程度ですがbase64でエンコードしています。GASには他の暗号化処理もありますが不可逆だったりします。そのためPush送信時にIDを使用時において元のIDを使いたいため、base64でエンコード・デコードできるようにしています。

MeetingFunc.gs

function SendMeeting() {
  const userSh = SpreadsheetApp.openById(SPREAD_ID).getSheetByName(SHEET_USER);
  const meetSh = SpreadsheetApp.openById(SPREAD_ID).getSheetByName(SHEET_MEET);
  const userList = GetColumnList(userSh,1,2);
  
  meetMultiList = meetSh.getDataRange().getValues();
  for(let i=0; i<meetMultiList.length; i++){
    const meetId = meetMultiList[i][0];
    const meetName = meetMultiList[i][1];
    const meetSummary = meetMultiList[i][2];
    const meetDate = meetMultiList[i][3]
    const isMeetSend = meetMultiList[i][4];
    console.log(isMeetSend)
    
    if(isMeetSend == true){      
      let sendMsg = '【会議名】\r';
      sendMsg += meetId + '.' + meetName + '\r\r';
      sendMsg += '【会議の概要】\r';
      sendMsg += meetSummary + '\r\r';
      sendMsg += '【日程】\r';
      sendMsg += Utilities.formatDate(meetDate, "JST", "yyyy/MM/dd (E) HH:mm");
      userList.forEach(userId => ConfirmFetch(sendMsg, DecodeBase64Text(userId), meetId, meetName));
    }
  }
}

function UpsertMeeting(userId, sendMsg){
  const attendSh = SpreadsheetApp.openById(SPREAD_ID).getSheetByName(SHEET_ATTEND);
  const userSh = SpreadsheetApp.openById(SPREAD_ID).getSheetByName(SHEET_USER);
  const meetSh = SpreadsheetApp.openById(SPREAD_ID).getSheetByName(SHEET_MEET);
  
  const sendUserId_b64 = EncodeBase64Text(userId); 
  const sendMeetId = parseInt(sendMsg.split('.')[0]);
  
  let replyMsg = '';
  
  const userCheckVal = FindRow(userSh,1,sendUserId_b64) - 1;
  if (userCheckVal　<= 0){
    replyMsg = '名前が登録されていないので出欠を登録できませんでした。';
  }
  
  const meetIndex = FindRow(meetSh,1,sendMeetId) - 1;
  if (meetIndex <= 0 && replyMsg == ''){
    replyMsg = '会議が登録されていないので出欠を登録できませんでした。';
  }
  
  const meetName = meetSh.getDataRange().getValues()[meetIndex][1];
  const meetDate = meetSh.getDataRange().getValues()[meetIndex][3];
  const sendUserName = userSh.getDataRange().getValues()[userCheckVal][1];
  
  const attendMultiList = attendSh.getDataRange().getValues();
  for(let i=0; i<attendMultiList.length; i++){
    
    const shMeetUserId = attendMultiList[i][0] + attendMultiList[i][1];
    const sendMeetUserId = sendMeetId + sendUserId_b64;
    
    if (shMeetUserId == sendMeetUserId && replyMsg == ''){
      if (sendMsg.indexOf('出席') != -1){
        attendSh.getRange(i+1, 3).setValue(meetName);
        attendSh.getRange(i+1, 4).setValue(meetDate);
        attendSh.getRange(i+1, 5).setValue(sendUserName);
        attendSh.getRange(i+1, 6).setValue('出席');
        replyMsg = `「${meetName} 」の出欠を【出席】に変更しました。`;
      }
      if (sendMsg.indexOf('欠席') != -1){
        attendSh.getRange(i+1, 3).setValue(meetName);
        attendSh.getRange(i+1, 4).setValue(meetDate);
        attendSh.getRange(i+1, 5).setValue(sendUserName);
        attendSh.getRange(i+1, 6).setValue('欠席');
        replyMsg = `「${meetName} 」の出欠を【欠席】に変更しました。`;
      }
    }
  }
  
  if(replyMsg.indexOf('出欠') == -1){
    if (sendMsg.indexOf('出席') != -1){
      attendSh.appendRow([sendMeetId, sendUserId_b64, meetName, meetDate, sendUserName,'出席']);
      replyMsg =  `「${meetName} 」の出欠を【出席】で登録しました。`;
    }
    if (sendMsg.indexOf('欠席') != -1){
      attendSh.appendRow([sendMeetId, sendUserId_b64, meetName, meetDate, sendUserName,'欠席']);
      replyMsg =  `「${meetName} 」の出欠を【欠席】で登録しました。`;
    }
  }
  
  return replyMsg ? replyMsg : '会議の出席登録に失敗しました。';
}

function SendMeeting()

「会議マスターのシート(MeetingList)」に登録されている会議でTRUEになっている会議をユーザーにPushで通知します。
送信対象となるユーザーは「ユーザーのシート(UserList)」に記載されているユーザー全員に対して送付します。

function UpsertMeeting(userId, sendMsg)

ユーザーから会議出席の連絡があった場合にデータを追加・更新します。ここでは「LINEのbase64でエンコードしたID」と「会議のID」を結合させて一意のデータとしてチェックするようにしています。

ウェブアプリケーションとして公開

Current web app URLをLINE developersで設定する必要があるので控えておいてください。設定は画像のとおりにすることでLINEのBotから利用できるようになります。

LINE developers

Provider

まずはプロバイダーを作成します。プロバイダーは、LINEプラットフォームを通じてサービスを提供する個人、企業、またはそのほかの組織を意味する情報です。プロバイダー名には、自分の示す名前や企業名を入力してください。

Channnel

チャネルの作成します。チャネルはLINEのユーザーの表示されるので名称はちゃんとしておいた方がよさそうです。LINEプラットフォームが提供する機能を、プロバイダーが開発するサービスで利用するための通信路で、チャネルを作成するには、名前、説明文、およびアイコン画像が必要です。

Messaging API

Messaging APIの設定を行います。

Webhook settings

Webhook URLはウェブアプリケーションとして公開で設定したURLを設定します。あと、Use webhookもオンにしておきます。

Auto-reply messages

自動で返答されると2重にレスポンスを返すことになるので無効にします。

Channnel access token

あいさつメッセージの設定

Provider > Channnel > Auto-reply messages > Edit をクリックすることでLINE Official Account Managerのページに移動できます。このページからウェブ画面上でLine Botの設定が可能です。ここではBotを友達登録した際に表示される「あいさつメッセージ」を設定します。

作ってみての感想

GAS(Google Apps Script)とGoogleSpreadSheetでLine Botを作るのは意外とサクっとできました。Line Botの設定もLINE Official Account Managerだけでも何か作れそうな予感がします。

GAS(Google Apps Script)については手軽に作れるところはよいですがコード管理とかできないところはツライですね。GitLab.CIとの連携などもできるようなので、またチャレンジしてみようと思います。

コードや設定方法などは全て記載したつもりなので、ぜひ皆さんも作成してみてはいかがでしょうか。