発端はこれです。

構成

構成要素としては以下のA~Eの5つになります。
A,Bはチケット管理システムとしてMUSTで必要なものです。
C,D（通知機能）, E（可視化機能）はやりたかったらこういうのもできるよ、というオプション要素なので解説はサラッと。

A. 依頼するフォーム
- リンク起動
- Slackワークフローのフォームでありスプシに書き込むのもこいつ
B. チケット管理スプレッドシート
- Aにより、 依頼一覧 チケット一覧 の2シートにデータが書き込まれる
- 作業が完了したら チケット一覧 のステータスを手動更新する必要あり
  - これは現時点で（2023年8月時点）スプレッドシートの更新がないため
C. メッセージ通知用のGAS
- Bを読み込んでチケット一覧を取得し、Dを起動してメッセージを送信するGAS
- 時間トリガー起動
D. メッセージ通知用のSlackワークフロー
- Webhook起動
E. チケット可視化用のLooker Studio
- Bのスプレッドシートを読み込んで可視化する

画面	スクショ
Slack WFのフォーム申請画面
Slackへの申請通知画面
チケット管理スプレッドシート画面
チケット状況をメッセージ通知する画面
チケット可視化用のLooker Stuido画面

必須部分の実装

B.チケット管理スプレッドシートの実装

以下のファイルでリンク共有しているのでそれをコピーしてベースにするのがはやいです。

質問項目に合わせて列を追加する必要があるのですが、その際のポイントは 質問項目は必ず列の右側に追加する です。
途中に列を追加すると後述のSlackワークフローからスプシに書きこむ値を設定ときに列がズレていってしまうのでなおすのが面倒になるためです。

A.依頼するフォームの実装

Slackワークフローの作成画面に新しいWFが出てるのでそちらを使います。

こんな感じでSlack WFを組む。

1つ目のスプレッドシート記入の中身

依頼一覧シートに記入する処理が書いてあります。

細かい設定がある部分は別でスクショを貼っときます。

項目	スクショ
依頼日時_raw
依頼者(メールアドレス)

ポイントは 依頼日時_raw ですね。これはUnixタイムスタンプを指定しています。
そのままだとスプシで扱いづらいので、スプシ側でUnixタイムスタンプを日時データに変換する関数を入れた列を作り、日時として扱えるようにしています。

2つ目のスプレッドシート記入の中身

チケット一覧シートに記入する処理が書いてあります。

細かい設定がある部分は別でスクショを貼っときます。

項目	スクショ
対応者(メールアドレス)
依頼日時_raw

ポイントは 依頼日時_raw ですね。先程と同じようにUnixタイムスタンプを指定しているのですが、これは依頼一覧シートと紐付けるためのキー情報として使っています。

（Optional）通知機能の実装

GASとSlackワークフローのWebhookを使ったSlack通知を組み合わせるやり方は、下記の記事を参照ください。

上記の記事はGoogleフォームの入力内容を通知しているのですが、スプレッドシートを参照するように変えたぐらいでだいたい一緒です。

D. メッセージ通知用のSlackワークフローの実装

WebhookでSlackWFを定義してあげてメッセージ送信処理を入れています。

Webhook部分は noStartedTicketsMessage , inprogressTicketsMessage の2つを設定してください。

C. メッセージ通知用のGASの実装

スプシのコンテナバインドで実装しているので、スプシをコピーしてればGASのコードはそのまま利用できます。
Dで作ったWebhookのURLを WEBHOOK_URL のスクリプトプロパティに設定すればOKです。

項目	スクショ
D. メッセージ通知用のSlackワークフローの実装で作ったWebhookのURL

GASのコードも記載しておきます。

GAS

const WEBHOOK_URL = PropertiesService.getScriptProperties().getProperty('WEBHOOK_URL');

function noticeTicketsStatus() {
  let inprogressTicketsMessage = '';
  SheetsService.getTickets() 
    .filter(t=> t.対応ステータス === '着手中')
    .forEach(t => {
      inprogressTicketsMessage += `* ${t.依頼概要}` + '\n'
  }
  );

  let noStartedTicketsMessage = '';
  SheetsService.getRequests() 
    .filter(t=> t.未着手かどうか === '未着手')
    .forEach(t => {
      noStartedTicketsMessage += `* ${t.依頼概要}` + '\n'
  }
  );

SlackService.notifySlack({inprogressTicketsMessage, noStartedTicketsMessage});
}

class SlackService {
  static notifySlack({inprogressTicketsMessage, noStartedTicketsMessage}) {
    const options = {
      method: 'POST',
      headers: {'Content-type': 'application/json' },
      payload: JSON.stringify({inprogressTicketsMessage, noStartedTicketsMessage}),
    };
    UrlFetchApp.fetch(WEBHOOK_URL, options);
  }
}

class SheetsService {
  static getSheet() {
    return SpreadsheetApp.getActiveSpreadsheet();
  }
  static getTickets() {
    const ticketsSheet = this.getSheet().getSheetByName('チケット一覧');
    return this.convertSpreadSheetTable_(ticketsSheet.getDataRange().getValues());
  }
  static getRequests() {
    const requestsSheet = this.getSheet().getSheetByName('依頼一覧');
    return this.convertSpreadSheetTable_(requestsSheet.getDataRange().getValues());
  }

  static convertSpreadSheetTable_(ssValues) {
    const [headers, ...rows] = ssValues;
    
    const results = [];
    rows.forEach((row) => {
      const obj = {};
      headers.forEach((header, i) => {
        obj [header] = row[i];
      });
      results.push(obj);
    });

    return results;
  }
}

時間トリガーの設定

こんな感じで作れば毎朝10時〜11時に起動します。

（Optional）可視化機能の実装

ここまでやる人はあまり居ない気がするので解説は割愛するのですが参考までにリンク共有しておきます。

その他注意点

Slackワークフローの仕様について

Slackワークフローで動く内容は動かした時点のものを引き継いています。
そのためあとからSlackワークフローに項目を増やした際に「対応する」ボタンを押したときの動きは、過去に起動したSlackワークフローの設定内容で動く ことに注意しましょう。

「対応する」をちゃんと日常的に押していて古い依頼を溜め込まなければ問題になることはないです。

今回作ったSlackワークフローはプレミアムワークフローですので課金対象です

1つのSlackワークフロー内でスプレッドシートの書き込みが2つはいっているので、プレミアムワークフローになります。
プレミアムワークフローは 6円/1実行 になるようなのでその点をご注意ください。

Discussion