【Googleスプレッドシート×Slack】関数だけでコストの使いすぎアラートをできる限り自動化してみた

#Google Spreadsheet

Harada

2024.08.17

■最初に

社内メンバーが利用しているサービスの月額利用費の締め処理をしています。
締め処理に加え、先月から、以下の取り組みを実施しています。

・月額5000円以上の利用料を出したユーザーと管理職にSlackで通知

利用ID毎の利用費はBigQueryでGoogleスプレッドシートに抽出されます。

通常であれば、ZapierやIFTTTでSlackを連携して通知するのがスマートなのですが
「誰でもメンテナンスが可能」
「でも人力箇所はなるべく少な目に」
をベースに非エンジニアでもメンテナンス可能な方法を考えました。

■準備

利用費・利用者を抽出するための準備です。

①BigQueryの抽出データとは別シートに、ID毎の利用費をピボットテーブルで抽出します
-A列：ID
-B列：利用費
・シート名「ピボットテーブル 2」
<イメージ>
ccc

②ID情報のシートを準備します。ID、部門、利用者のデータが必要になります
-A列：利用者名
-B列：ID
-C列：部門名
・シート名「ID一覧」
<イメージ>
bbb

③部門シートを準備します。部門、部門管理者のデータが記載されています
-A列：部門名
-B列：部門管理者
・シート名「部門」
<イメージ>
ddd

④Slackで通知する文言を考えます。
＜例＞

@利用者 @部門管理者
前月の利用料金が 5000円を超えましたので、お知らせいたします:moneybag:

詳細:
部門名:XXXX
プロジェクト名:利用者
利用料金: XXXX円

お手数ですが、管理者におきましては、利用状況をご確認の上、本スレッドに問題ない旨のご返信をお願いいたします:pray:
ご不明な点がございましたら、お気軽にご連絡ください:speech_balloon:

以上で準備は整いました。

Slack報告用文言の作成

①更にGoogleスプレッドシートで新シートを用意し、A～Fに以下のようなタイトルを入力します。
-A列：ID
-B列：月額利用費
-C列：部門
-D列：部門管理者
-E列：利用者
-F列：Slack文言
（※Haradaは68行目に上記データを入力しています）

②A列：IDの下に以下関数を入力します。
FILTER関数は、Googleスプレッドシートで指定した条件に合致するデータのみを抽出する関数でDBから特定の条件でデータを絞り込むイメージで使用することができます

=FILTER('ピボット テーブル 2'!A:B, 'ピボット テーブル 2'!B:B >= 5000)

※他のブックから抽出する場合はIMPORTRANGE関数を組み込むことで実現可能です。

A列：IDの直下に5000円以上の利用費を使ったID、
B列：月額利用費の直下に5000円以上の利用費金額が表示されました。

③C列：部門の下に以下関数を入力します。
簡単なVLOOKUP関数を利用します。

=VLOOKUP(A69,'ID一覧'!$B$2:$C$16,2,FALSE)

対象IDが利用されている部門が抽出されます。

④D列：部門管理者の下に以下関数を入力します
③で抽出した部門をキーにVLOOKUP関数で部門管理者を抽出します。

=VLOOKUP(C69,'部門'!$A$2:$B$5,2,FALSE)

⑤E列：利用者の下に以下関数を入力します

=INDEX('ID一覧'!A:A,MATCH(A69,'ID一覧'!B:B,0))

「ID一覧」シートで、利用者はIDの左側の列にあるためVLOOKUPが使えませんでした。
代わりに、INDEX関数を使います。
INDEX関数は、行番号と列番号を指定して、値を抽出する関数です。

⑥F列：Slack文言の下に以下関数を入力します
カナリ強引です…。
・CHAR(10) を使いスプレッドシートの関数内で改行させます。
・利用しているサービス利用費が、時折小数点も出してくるためINT・ROUNDUP関数を利用し少数第一位の値を四捨五入し、整数に変換してます
・既に手順②で5000円以上のIDを抽出していますが、念のため「>5000」で再確認しています。

=IF(B69>5000,CONCATENATE("@",E69," @",D69, CHAR(10),"前月の利用料金が 5000円 を超えましたので、お知らせいたします:お金の袋:",CHAR(10),CHAR(10),"詳細:",CHAR(10),"部門名: ",69,CHAR(10),"プロジェクト名: ",A69,CHAR(10),"利用料金: ",INT(ROUNDUP(B69,0))," 円",CHAR(10),CHAR(10),"お手数ですが、管理者におきましては、利用状況をご確認の上、本スレッドに問題ない旨のご返信をお願いいたします:祈る:",CHAR(10),"ご不明な点がございましたら、お気軽にご連絡ください:入力中アイコン:"),"")

できあがったのがこちら

kkk