Posted at

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


経緯


  • ぼくらの会社ではオフィス内の掃除当番をgoogleのspreadsheetで管理していますが、管理者がSlackで当月分の当番を共有したり自分の当番の日をspreadsheetを見て確認する必要があって毎回面倒だなーと思っていました。


  • SheetDBというサービスを利用することで、簡単にspreadsheetの内容をRESTful APIとして取得できることを知り、簡易的なslackのボットを作ってみたので、その内容になります。


やりたいこと


  • やりたいことはこんな感じです


    • 毎日1回、spreadsheetの内容からその日と次の日の掃除当番をslackに通知する

    • spreadsheetの運用自体に影響は与えないようにする



  • あとは、なるべく簡単に作るようにしました


構成


  • 簡易的に作りたいのでLambda上で定期実行するようにしました。

  • Lambda上のfunctionからSheetDBを使ってAPIでspreadsheetの内容を取得し、Slackへ通知します。

  • LambdaはCloudWatch Eventsを利用して定期実行させます。


SheetDB


  • SheetDB

  • 他にもおなじようなサービスがありましたが、以下の点でこちらのサービスを利用することにしました。


    • READ、CREATEなどAPI単位で細かくPermissionsを設定できる

    • APIにベーシック認証が設定できる


      • お掃除当番表はそこまで機密性は高くないですが、一般公開して良い情報でもないので。





  • ただしフリープランだと以下2つの制限があります。今回は要件的にこの内容でも問題はありませんでした。



  • 類似サービス




SheetDBの設定



  • サービスサイトからgoogleのアカウントでログインします


  • create new で新しくAPIを作ります

  • spreadsheetのURLを入力すると完了です
    image.png
    image.png


  • api settingsで細かい設定が可能です。今回はデータを読み込むだけなので、READ以外のチェックを外してベーシック認証をONにしておきます。ログインIDとパスワードが表示されるので、APIをコールする際に利用します。
    image.png

  • SheetDBの設定は完了です。簡単です。


APIの確認


  • 念の為APIをコールしてみます

  • 取得する元のspreadsheetはこんな感じで、日付ごとに担当者の名前が書かれています。

    image.png


  • 先程設定画面で表示されたベーシック認証の情報を用いてcurlコマンドでAPIをコールしてみます。


$ curl -u [ID]:[パスワード] https://sheetdb.io/api/v1/******


  • こんな感じでjsonで取得出来ます。カラム名がkeyになってレコード分配列になってデータを取得することが出来ます。いいですね。

[

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


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

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


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




Lambda


  • APIでSpreadsheetのデータを取得することまで出来たので、あとはこれを定期的に実行してSlackに通知するだけとなります。

  • Lambda はpythonでServerless Frameworkを使って作りました。



  • 以下サンプルコードになります

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

# 日付の表記は[1月1日]
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)を設定しています。

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


実行結果


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

image.png


まとめ


  • pythonからgoogleのspreadsheetのデータを直接取得することももちろん可能なのですが、少し実装が面倒なのでこれまでなかなかやる気になりませんでした。。:bow:

  • SheetDBを使うことで簡単にAPIとしてデータが取得出来るので、気持ち的にも実装コストもかなり下がりました。


    • もしspreadsheetを使って何かの当番を管理しているとかあれば、是非試して頂けると良いかと思います!