この記事はRetty Advent Calendar 2022 Part2の12日目の記事です.

はじめに

2023年4月に入社予定の冨田です。現在は週2回ほどインターンに参加しています。最近は四川麻婆にハマっています。

今回は、Retty社内で日々発生するお店データの修正業務の効率化をSlack Workflow（以下WF）とGoogle Spreadsheet+Google Apps Script(以下GAS)で行なった事例についてお伝えしたいと思います。

Rettyにおけるその他のWFの活用事例については、アドカレ1日目のimaizumeさんの記事にまとめられています！ぜひ、ご覧いだだければと思います！ engineer.retty.me

背景（お店データの修正）

Rettyでは正確な情報をお届けするため、日々お店情報を見直し、更新をしています。そのオペレーションの一部にお店（レストラン）の識別子(ID)を特定テーブルにINSERTするというものがあるのですが、管理画面整備が追いついておらず、人手でSQLクエリを用意する手間が発生していました。

RettyではこれらをGitHub Issueとして管理しており、以下のような流れで処理されています。

依頼主(主に非エンジニア)がIssueを作成し、DBにINSERTするクエリを記載する。
Slack(クエリ実行依頼WF)で権限を持ったメンバーにクエリの実行依頼をする。
依頼されたメンバーは実行されるクエリを確認（&レビュー）し、本番DBに対してクエリを実行する。

今回は、WFとSpreadsheet(+GAS)を使って

データ修正するお店の登録を簡素化（クエリの自動生成）
クエリ実行依頼を日次バッチ化（1日1回にまとめる）

をすることにより、業務の負担軽減をすることとしました。

今回実装した処理の流れ

詳細な処理の流れについての図を以下に示します。

修正するお店の登録（主にお店データチームが実行）

お店登録WFで修正するお店の”ID”と"名前"を登録する。

↑依頼主がすることはこれだけになりました。

このWFで入力されたお店は、Spreadsheetに追記されていきます。

クエリの生成＆実行依頼（自動で実行）

クエリの生成と実行は、Google Apps Script(GAS)で行います。

Apps Script は、Google Workspace の統合、自動化、拡張のためのビジネスソリューションをすばやく簡単に構築するための唯一のローコードプラットフォームです。 Google Apps Script: Google Workspace を自動化、統合、拡張。です。

GASはSpreadsheetとも組み合わせることができ、Spreadsheet単体ではできないような処理を実現できます。また、特定の時間に動くようにトリガーを設定することができるので、日次で実行される処理が簡単にできます。

GASでは、以下の処理を行なっています。

Spreadsheetを読み込み、処理すべきデータがあるか確認する（データがあれば以下を実行する）。
GitHubにIssueを作成し、データ修正用のクエリを記載する。
Webhookからクエリ実行依頼WFを開始させて、Slackでクエリ実行依頼を行う。

各要素についての紹介

今回の自動化におけるポイントを7つ紹介します。

お店登録WF
クエリ実行依頼WF
SpreadsheetのQUERY関数
GASのトリガー設定
GASからSpreadsheetのデータを取得
GASからGitHub Issueの作成
GASからWFを開始

お店登録WF

お店登録WFは、修正したいお店の情報をSpreadsheetに追加していくだけのシンプルなWFです。

工夫点としては、作成日時をSpreadsheetに書き込む際に、WFで用意されている時刻関係の変数は使わなかったということです。代わりにSlackのワークフローで、送信日時をスプレッドに入力する - Qiitaを参考に、SpreadsheetのNOW関数から得た時刻を書き込んでいます。理由としては、WFで用意されている時刻がアメリカ表記になっていて、Spreadsheetで処理する際に扱いづらかったためです。

クエリ実行依頼WF

クエリ実行依頼WFは、GASからWebhookで呼ばれるWFです。 WF作成時に一番下にあるWebhookを選択すると作成できます。

実際に組んだWFは以下になります。（このWFの大枠は、imaizumeさん作です）

生成されたWebhookのURLにリクエストされると、指定のチャンネルにメッセージ（クエリ実行依頼）が自動的に送信されます。このメッセージ中の可変テキストの部分は、Webhookのリクエストボディにセットされた値を使うことができます。ただし、一番上のアプリまたはサービスがウェブリクエストを送信したら開始するステップの「編集」をクリックして開くモーダルで事前に設定する必要があります（WebhookのURLも確認できる）。これをサンプルのような形のJSONにして送ることにより、WF内で使える他の変数と同じように使えます。

SpreadsheetのQUERY関数

対応したいお店は、お店登録WFによってSpreadsheetに追加されていきます。このSpreadsheetから当日処理するお店を抽出する処理は、SpreadsheetのQUERY関数で行なっています。 GASで処理していない理由としては、当日処理されるデータがぱっと見てわかるようにしたかったためです。

具体的には、当日処理分シートのA1セルに以下のような数式を記載しています。

=QUERY('ログ'!A1:E, "select A,B,C,D,E where A >= datetime '"& TEXT('環境変数'!B2-1, "yyyy-mm-dd")&" "&TEXT('環境変数'!B1, "HH:MM:SS")&"' and A <= datetime '"& TEXT('環境変数'!B2, "yyyy-mm-dd")&" "&TEXT('環境変数'!B1, "HH:MM:SS")&"'", True)

ここで、ログシートには登録された全データがあり、A列には作成日時が記入されています。また、環境変数シートのB1セルに締め時刻(17:00)、環境変数シートのB2セルに処理基準日(=TODAY())が入っています。なので、この数式が実行されると前日の17:00から当日の17:00までに追加されたお店が抽出できます。これを、当日処理分として扱います。

また、このSpreadsheetの環境変数シートには、リポジトリの情報やIssueのテンプレートが書かれています。これにより、簡易な変更であればGASのエディタを立ち上げることがなく変更でき、メンテナンス性が向上します。

GASのトリガー設定

まず、GASへのアクセスは、Spreadsheetの「拡張機能」→「Apps Script」を選択すると簡単にできます。

GASではトリガーを設定することで、何かのイベントをきっかけに関数を実行することができます。今回は、その日の17時までに追加されたお店を当日受付分として、17時から18時の間で処理されるようにしたいので、つぎのような設定としています。

イベントのソースを選択：時間主導型
時間ベースのトリガーのタイプを選択：日付ベースのタイマー
時刻を選択：午後5時〜6時

実際の処理は、大体17時半ぐらいに走っているようです。今回の用途だと特に問題ないのですが、あまり細かい時間は設定できないようです。

GASからSpreadsheetのデータを取得

GASからSpreadsheetの当日処理分シートのお店を取得するコードの例です。今回は取得したデータを、1件ずつカラム名をkeyとするkey-valueに変換してから配列にまとめるようにしています。

// Sheetを返す
function openSheet(sheetName) {
  const spreadsheetID = "<SpreadsheetのID>";
  const spreadsheet = SpreadsheetApp.openById(spreadsheetID)
  return spreadsheet.getSheetByName(sheetName)
}

// 当日処理分のお店を取得する
function getTargetRestaurants(){
  const sheetName = "当日処理分"
  sheet = openSheet(sheetName)
  
  const recodesLen = sheet.getLastRow() - 1
  
  Logger.log("レコードが"+recodesLen+"件見つかりました")
  if (recodesLen == 0) {
    return []
  }
  
  const columns = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues().flat()
  const columnsLen = columns.length

  const recodes = sheet.getRange(2, 1, recodesLen, columnsLen).getValues()
  
  // key-valueの配列として取得する
  let data = []
  for (let i = 0; i <recodes.length; i++) {
    _dict = {}
    for (let j = 0; j < columnsLen; j++){
      _dict[columns[j]] = recodes[i][j]
    }
    data.push(_dict)
  }

  return data
}

このようにして取得したデータを元にGitHub Issueを生成します。

GASからGitHub Issueの作成

今回はFine-grained personal access tokensを使っています。対象のリポジトリやリポジトリの権限が細かく設定できるようになっているので、万が一のときも被害を最小限に抑えることができます。

ここでは、Issueが作りたいのでIssueのRead and writeを選択しています（Metadataの権限も勝手に付加されます）。

GitHub のREST APIのドキュメント https://docs.github.com/ja/rest/issues/issues?apiVersion=2022-11-28#create-an-issue を参考に、GASからGitHub Issueを作成するAPIを叩きます。

Issueの作成コードの例

function simpleCreateIssue(){

  const TOKEN = "<YOUR-TOKEN>"
  const OWNER = "<OWNER>"
  const REPOSITORY = "<REPOSITORY>"

  const payload = JSON.stringify({title: "Issueのタイトル", body: "Issueの内容"})
  const options = 
  {
    method: "post",
    headers: {
      Accept: "application/vnd.github+json",
      Authorization: `Bearer ${TOKEN}`
      },
    payload: payload,
    muteHttpExceptions: true
  }

  const response = UrlFetchApp.fetch(`https://api.github.com/repos/${OWNER}/${REPOSITORY}/issues`, options)

  const contentText = response.getContentText()
  const json = JSON.parse(contentText)
  Logger.log(json)
}

実行するとこのような感じで、GitHubにIssueが作られます。

実際には、Spreadsheetに用意したテンプレートに変数を埋め込むようにして、Issueの内容を生成しています。

GASからWFを開始

WFをWebhookで動かすためには、事前にWFで設定したWebhookのURLにPOSTします。このときBodyには、WF側で設定したプロパティをJSONでセットしておきます。リクエストの仕方は、GitHub Issueの時とほぼ同じです(Content-Typeはapplication/jsonにする必要があります)。