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

# 概要

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

# どんなシステム？

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/412322/97d14350-e1ec-cea5-572e-0215e926569c.png)

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

## ユーザー登録をする

![Screenshot_20201221-164507.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/412322/c03ad528-83bf-0bc2-9bb6-e5d900813b5b.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/412322/79e0402a-1db4-27b5-1cb9-6a0a97b2f38c.png)


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

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/412322/7e79ba72-fc7f-a5ed-b6db-f98b9c6d7e06.png)

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

![Screenshot_20201221-161355.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/412322/1ea57411-7ad9-682a-ddc6-ecf470445b1c.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/412322/836022d7-928f-4f00-f06e-e530670338af.png)


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



# 構成・設定

- 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)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/412322/7f976c22-bbd4-40f4-9fcf-68047705aae2.png)

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

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/412322/e4ba248a-3107-da45-fff4-fd4c50d52dbf.png)

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

### ユーザーのシート(UserList)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/412322/e4cb8385-3d1e-bcd6-701a-6f53a5600c6c.png)

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

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

## Google Apps Script

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

### Main.gs

```javascript
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

```javascript
// 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の画面](#messaging-api)から `Channel access token`のトークンを記載します。


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



### Fetch.gs

```javascript
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

```javascript
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

```javascript
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」を結合させて一意のデータとしてチェックするようにしています。

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/412322/3350e46b-3f2d-6fe6-d551-ac7bbc889720.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/412322/d0fed84f-b303-717e-b0c0-398396a30041.png)

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

## LINE developers

### Provider

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/412322/7f39c476-fd42-1610-c024-643413a1467b.png)

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

### Channnel

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/412322/6eae140b-6446-70fb-c00a-ff61893ae2cf.png)

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

#### Messaging API

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/412322/7f109e9d-e5fd-587b-4bdf-c244d7e3412d.png)

`Messaging API`の設定を行います。

#### Webhook settings

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/412322/2c320a74-a64e-d438-2850-8adb51b4b759.png)

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

#### Auto-reply messages

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/412322/cffb48ab-6d81-1d08-6449-ab680149aa73.png)

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

#### Channnel access token

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/412322/3e58820b-e461-bdf0-8d53-589c400e1ab9.png)


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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/412322/3cc75ac0-e2b1-1db2-710f-d3f47f39cb46.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/412322/a7a48277-21e5-6fbb-e0b5-09e8a50cfae0.png)


`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との連携などもできるようなので、またチャレンジしてみようと思います。

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