SheetDBとLambdaで作るお掃除当番通知Slackボット

# 経緯

- ぼくらの会社ではオフィス内の掃除当番をgoogleのspreadsheetで管理していますが、**管理者がSlackで当月分の当番を共有したり**、**自分の当番の日をspreadsheetを見て確認する**必要があって毎回面倒だなーと思っていました。
- [SheetDB](https://sheetdb.io/)というサービスを利用することで、簡単に**spreadsheetの内容をRESTful APIとして取得**できることを知り、簡易的なslackのボットを作ってみたので、その内容になります。

# やりたいこと

- やりたいことはこんな感じです
    - 毎日１回、spreadsheetの内容から**その日と次の日の掃除当番をslackに通知する**
    - spreadsheetの運用自体に影響は与えないようにする
- あとは、なるべく簡単に作るようにしました

# 構成

- 簡易的に作りたいのでLambda上で定期実行するようにしました。
- Lambda上のfunctionからSheetDBを使ってAPIでspreadsheetの内容を取得し、Slackへ通知します。
- LambdaはCloudWatch Eventsを利用して定期実行させます。

# SheetDB

- [SheetDB](https://sheetdb.io/)
- 他にもおなじようなサービスがありましたが、以下の点でこちらのサービスを利用することにしました。
    - READ、CREATEなどAPI単位で**細かくPermissionsを設定できる**
    - APIに**ベーシック認証**が設定できる
        - お掃除当番表はそこまで機密性は高くないですが、一般公開して良い情報でもないので。
- ただし**フリープラン**だと以下２つの制限があります。今回は要件的にこの内容でも問題はありませんでした。
    - **２API(spreadsheet)まで**設定可能
    - **月に５００リクエスト**まで
    - https://sheetdb.io/pricing
- 類似サービス
    - [sheetson](https://sheetson.com)
        - 利用できるspreadsheetに制限はなさそう
        - 指定されたemailをアクセス許可する必要があるみたい
            - https://docs.sheetson.com/#getting-started
    - [Sheet.Best](https://sheet.best)
        - こちらも利用できるspreadsheetに制限はなさそう
        - spreadsheetがパブリックにアクセスできる必要があるみたい
            - https://sheet.best/#/how-to

## SheetDBの設定

- [サービスサイト](https://sheetdb.io/)からgoogleのアカウントでログインします
- **create new** で新しくAPIを作ります
- spreadsheetのURLを入力すると完了です
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/51778/4db98452-2ed7-89b8-54cb-90b1115f4802.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/51778/929b2072-d947-66df-3446-3a661ad23ec2.png)
- **api settings**で細かい設定が可能です。今回はデータを読み込むだけなので、**READ以外のチェックを外して**、**ベーシック認証をON**にしておきます。ログインIDとパスワードが表示されるので、APIをコールする際に利用します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/51778/0e4a027e-2b22-b200-cbad-a251dbbcb3d7.png)
- SheetDBの設定は完了です。簡単です。

## APIの確認

- 念の為APIをコールしてみます
- 取得する元のspreadsheetはこんな感じで、日付ごとに担当者の名前が書かれています。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/51778/03c0a882-055c-66cc-1961-2257f127d35a.png)
 
- 先程設定画面で表示されたベーシック認証の情報を用いてcurlコマンドでAPIをコールしてみます。

```bash
$ curl -u [ID]:[パスワード] https://sheetdb.io/api/v1/******
```
- こんな感じでjsonで取得出来ます。カラム名がkeyになってレコード分配列になってデータを取得することが出来ます。いいですね。

```json
[
  {
    "日付": "8月1日",
    "曜日": "木",
    "担当者": "AAA"
  },
  {
    "日付": "8月2日",
    "曜日": "金",
    "担当者": "BBB"
  },
  {
    "日付": "8月3日",
    "曜日": "土",
    "担当者": ""
  },
  {
    "日付": "8月4日",
    "曜日": "日",
    "担当者": ""
  }
]
```

- シートが複数ある場合、何も指定しないと一番最初のシートのデータが取得されるようです。シートを指定する場合は以下のようにパラメータを追加します。

```bash
$ curl -u [ID]:[パスワード] https://sheetdb.io/api/v1/******?sheet=Sheet2
```

- この他にもキー名(カラム名)だけ取得したり、ドキュメント名を取得したり色々とできるようなので、ドキュメントを参考にしてみて下さい。
    - https://sheetdb.io/documentation

# Lambda

- APIでSpreadsheetのデータを取得することまで出来たので、あとはこれを定期的に実行してSlackに通知するだけとなります。
- Lambda はpythonでServerless Frameworkを使って作りました。
    - プラグインとして[serverless-python-requirement](https://github.com/UnitedIncome/serverless-python-requirements)と[serverless-local-schedule](https://github.com/UnitedIncome/serverless-local-schedule)を利用しています。
- 以下サンプルコードになります

```python
import os
import datetime
import json
import requests
import jaconv

SPREADSHEET_API_BASE_URL = os.getenv('SPREADSHEET_API_BASE_URL')
SPREADSHEET_API_KEY = os.getenv('SPREADSHEET_API_KEY')
AUTH_ID = os.getenv('AUTH_ID')
AUTH_PASS = os.getenv('AUTH_PASS')
WEB_HOOK_URL = os.getenv('WEB_HOOK_URL')
G_SPREADSHEET = os.getenv('G_SPREADSHEET')


def notification(event, context):
    """
    eventはテスト用
      {
        "today": "20190101"
      }
    """
    if (event is not None) and ('today' in event):
        today_str = event['today']  # 20190101
        today = datetime.datetime.strptime(today_str, '%Y%m%d').date()
    else:
        today = datetime.date.today()

    today_year = today.year
    today_month = today.month
    today_weekday = today.weekday()

    next_date = None
    next_str = None

    # 金曜日の場合、次は月曜日
    if today_weekday == 4:
        next_date = today + datetime.timedelta(days=3)
        next_str = '来週月曜日'
    else:
        next_date = today + datetime.timedelta(days=1)
        next_str = '明日'

    # シート名は月の数字の全角
    zen_today_month = jaconv.h2z(str(today_month), digit=True)
    url = SPREADSHEET_API_BASE_URL + SPREADSHEET_API_KEY + '?sheet=' + zen_today_month
    # API call
    response = requests.get(url=url, auth=(AUTH_ID, AUTH_PASS))
    cleaning_duty_list = response.json()
    if 'error' in cleaning_duty_list:
        return False

    today_target_person = None
    next_target_person = None
    for cleaning_duty in cleaning_duty_list:

        if not '日付' in cleaning_duty and not '担当者' in cleaning_duty:
            print('Key not found error')
            continue

        # 日付の表記は[１月１日]
        ja_date = jaconv.z2h(cleaning_duty['日付'], digit=True)
        ja_date = str(today_year) + '年' + ja_date
        try:
            cleaning_date = datetime.datetime.strptime(
                ja_date, '%Y年%m月%d日').date()
        except ValueError:
            continue

        if cleaning_date == today:
            today_target_person = cleaning_duty['担当者']

        if cleaning_date == next_date:
            next_target_person = cleaning_duty['担当者']

    today_message = '*今日の掃除当番* \n {today_target_person}さんです'.format(
        today_target_person=today_target_person)

    next_message = '*{next_str}の掃除当番* \n {next_target_person}さんです'.format(
        next_str=next_str,
        next_target_person=next_target_person)

    if not today_target_person:
        today_message = '今日の掃除はお休みです'

    if not next_target_person:
        next_message = '{next_str}の掃除はお休みです'.format(next_str=next_str)

    # slack通知
    title_section = {
        "type": "section",
        "text": {
            "type": "mrkdwn",
            "text": ":sparkles: *<{link_url}|お掃除当番のお知らせです>*".format(link_url=G_SPREADSHEET)
        }
    }

    today_target_section = {
        "type": "section",
        "text": {
            "type": "mrkdwn",
            "text": today_message
        }
    }

    next_target_section = {
        "type": "section",
        "text": {
            "type": "mrkdwn",
            "text": next_message
        }
    }
    divider = {
        "type": "divider"
    }

    rtn = requests.post(WEB_HOOK_URL, headers={'Content-Type': 'application/json'},
                        data=json.dumps(
                            {
                                "blocks": [
                                    title_section,
                                    divider,
                                    today_target_section,
                                    divider,
                                    next_target_section
                                ]
                            }))
```

- 処理としては単純ですが、以下のような仕様に対応しているので、処理が少し長くなってしまっています。。
    - Spreadsheetのシート名が月の全角数字になっている
    - 日付のフォーマットは「＊月＊日」で全角で取得される
    - その日と次の日の担当者を通知する
        - 金曜日の次の日の担当者は「来週月曜日」という表現をする
- serverless.ymlはこんな感じです。cronで平日(月〜金)起動するように設定をしているのと、タイムゾーンの環境変数(TZ)を設定しています。

```yaml
service: cleaning-duty
provider:
  name: aws
  runtime: python3.7
  stage: ${opt:stage, self:custom.defaultStage}
  environment:
    SPREADSHEET_API_BASE_URL: ${env:SPREADSHEET_API_BASE_URL}
    SPREADSHEET_API_KEY: ${env:SPREADSHEET_API_KEY}
    AUTH_ID: ${env:AUTH_ID}
    AUTH_PASS: ${env:AUTH_PASS}
    WEB_HOOK_URL: ${env:WEB_HOOK_URL}
    G_SPREADSHEET: ${env:G_SPREADSHEET}
    TZ: Asia/Tokyo
  region: ap-northeast-1
  memorySize: 512
  timeout: 120
functions:
  notification:
    handler: handler.notification
    events:
      - schedule:
          rate: cron(0 9 ? * 2-6 *)
          timezone: Asia/Tokyo
plugins:
  - serverless-python-requirements
  - serverless-local-schedule
custom:
  defaultStage: dev
  package:
    exclude:
      - .git/**
      - node_modules/**
      - __pycache__/**
      - .env.local
  pythonRequirements:
    dockerizePip: false    
```

- コードはgithubにあげておきましたので、もしよければ参考にして下さい。
    - https://github.com/myoshioka/cleaning-notification-bot

# 実行結果

- こんな感じで毎日お掃除当番を通知してくれています:grin:

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/51778/002be714-4472-ae78-d6e0-7cc7806f77b9.png)

# まとめ

- pythonからgoogleのspreadsheetのデータを直接取得することももちろん可能なのですが、少し実装が面倒なのでこれまでなかなかやる気になりませんでした。。:bow:
- SheetDBを使うことで簡単にAPIとしてデータが取得出来るので、気持ち的にも実装コストもかなり下がりました。
    - もしspreadsheetを使って何かの当番を管理しているとかあれば、是非試して頂けると良いかと思います！