スプレッドシートとGoogle Calendarを全部Slackにつなげるお話

はじめに

この記事は、SLP-KBIT アドベントカレンダー2020 10日目の記事です。
年一回の投稿の季節がやってきました。

今回は、ありふれた内容な感は否めませんが、GoogleスプレッドシートとGoogle Calendarを、Slackと連携するお話になります。
というのも、私が所属している組織では、タスクの管理をGoogleスプレッドシートを利用して行い、スケジュールの管理をGoogle Calendarを利用して行っています。この2つを毎回確認するのもめんどうなので、連絡ツールとして利用しているSlackに、タスクもスケジュールも通知させてやることにしました。

Google CalendarはSlackとは簡単に連携できるアプリがあるのですが、GoogleスプレッドシートもあるのでGoogleのことはGoogleにおまかせして、Google Apps Script(GAS)でプログラムを作っていきます。

事前準備

Slack編

Slackにメッセージを飛ばすためには、Webhookの設定をする必要があります。
こちらの公式ドキュメントを参考にして設定を行ってください。
設定が完了したら以下のような画面になっていると思います。
この中のWebHook URLを使ってSlackにメッセージを飛ばしていきます。

Google Calendar編

SlackとGoogle Calendarを連携するためには、どのカレンダーを連携するのか明確にする必要があります。
そのため、連携するカレンダーの名前、もしくはIDを確認しておきます。今回はIDを利用して実装を進めています。
確認の仕方を、日本の祝日のカレンダーを例にして説明します。
画像のところから、連携したいカレンダーのオーバーフローメニューを開き、対象のカレンダーの設定を確認します。

設定を開くと次のような画面が開かれます。赤線がカレンダーの名前で、黄線がカレンダーのIDです。

Googleスプレッドシート編

こちらも連携のためにIDを確認します。
連携したいスプレッドシートのURLを確認すると、次のようになっていることが確認できると思います。この xxxx の箇所が対象のスプレッドシートのIDになります。
https://docs.google.com/spreadsheets/d/xxxx/edit#grid=
スプレッドシートに関しては、はじめからスプレッドシートに紐付けられたスクリプトを作成することも可能です。参考

実装

Google Calendarとの連携

処理の流れとしては、メッセージを飛ばす日の日付を取得して、その日に予定されているスケジュールをカレンダーから取得する。そして、取得した予定を整形してSlackに送信するという形になります。

まず、Slackにメッセージを飛ばす部分を実装します。
引数として、送信するメッセージを受け取り、payloadの辺りで送信する形に情報をまとめます。
そして、UrlFetchAppの辺りで、対象のSlackのチャンネルに対して、JSON形式でメッセージをPOSTしています。

function SendMessage(message){
  // Webhook URL
  const WebhookUrl = "事前準備で発行したWebhook URL";
  const payload = {
      text: message
  };
  // Slackに送信
  UrlFetchApp.fetch(WebhookUrl, {
    method: "POST",
    contentType: "application/json",
    payload: JSON.stringify(payload)
  });
}

次に、Google Calendarと連携して、Slackに送信するメッセージを作成する部分を実装します。
大まかな流れを構成するのは3つです。
まず、以下のようにして予定を確認したいカレンダーを取得します。

// Googleカレンダーより予定を通知したいカレンダーを取得
const calendars = CalendarApp.getCalendarById("事前準備で確認したカレンダーのID");

次に、以下のコードで実行日の予定を配列で取得します。

var events = CalendarApp.getEventsForDay(date);

最後に、以下のコードで一つ一つの予定のタイトル、開始時間と終了時間を取得します。

for (var i in events) {
  var event = events[i];
  var startTime = event.getStartTime();
  var displayStartTime = Utilities.formatDate(startTime, 'Asia/Tokyo', 'HH:mm');
  var endTime = event.getEndTime();
  var displayEndTime = Utilities.formatDate(endTime, 'Asia/Tokyo', 'HH:mm');
  var title = event.getTitle().toString();
}

コードの全体像は以下の通りです。上記の流れの中で送信するメッセージを作成しています。

function ScheduleManager(){

  // 日付を取得
  const date = new Date();

  // 曜日を取得
  const day = date.getDay();

  // Googleカレンダーより予定を通知したいカレンダーを取得
  const calendars = CalendarApp.getCalendarById("事前準備で確認したカレンダーのID");
  // 月曜日から金曜日の場合の処理
  if ( 0 < day && day < 6 ) {
    // Slackに送信する内容
    var text = "";
    var scheduleList = "";
    var events = CalendarApp.getEventsForDay(date);
    for (var i in events) {
      var event = events[i];
      var startTime = event.getStartTime();
      var displayStartTime = Utilities.formatDate(startTime, 'Asia/Tokyo', 'HH:mm');
      var endTime = event.getEndTime();
      var displayEndTime = Utilities.formatDate(endTime, 'Asia/Tokyo', 'HH:mm');
      var title = event.getTitle().toString();
      scheduleList = `${scheduleList}・${displayStartTime}~${displayEndTime} ${title}` + "\n";
    }
    if (taskList == "") {
      text = "本日の予定はありません"
    } else {
      text = `本日の予定です \`\`\`${taskList}\`\`\``;
    }
    sendMessage(text)
  }
}

Google Calendarに対して、GASでできることはこちらを参考にしてください。

Googleスプレッドシートとの連携

処理の大まかな流れは、Google Calendarと同様です。
スプレッドシートからタスク情報を取得して、整形した後にSlackに送信します。

スプレッドシートからタスク情報を取得して整形する部分を実装します。
まず、今回想定しているスプレッドシートは以下のようなものになります。
完了したタスクはステータスをDONEにして、未了のものはTBCとしています。
また、実際には時間まである場合もありますが、ここでは締切は月日のみの表示を想定することとします。

ここからタスク情報を取得する準備となるコードは、以下のようになります。
冒頭の2行で、連携するスプレッドシートとその中のシートを指定しています。
次に、何行目及び、何列目から何列目までの情報が欲しいのかを確認して記録します。

function ToDoManager() {

  // スプレッドシートよりToDoのシートを取得
  const spreadsheet = SpreadsheetApp.openById("事前準備で確認したスプレッドシートのID");
  const sheet = spreadsheet.getSheetByName("タスクを管理しているシートの名前");

  // 内容のカラム
  const detailColumn = 1;
  // 締切のカラム
  const deadlineColumn = 2;
  // 担当者のカラム
  const asigneeColumn = 3;
  // ステータスのカラム
  const commentColumn = 4;
  // タスクが記載されている先頭の列
  const startRow = 1;

  const columnFVals = sheet.getRange('A:A').getValues(); // タスクのカラムを配列で取得
  const lastRow = columnFVals.filter(String).length; // 空白を除いて配列の長さを取得
}

取得する対象が決まったので、次に実際に取得して整形をする部分を実装します。
おおよそはコード内のコメントの通りです。全体は、上記のコードの最後に以下を追記すれば完了です。
この部分で、先程準備した欲しいカラムの情報をそれぞれ列単位で取得しています。
sheet.getRange(row, xxx).getValue();

  // 実行日の日付を取得
  const date = new Date();
  const today = Utilities.formatDate(date, 'Asia/Tokyo', 'MM/dd');

  // 曜日を取得
  const day = date.getDay();

  // 月曜日から金曜日の場合の処理
  if ( 0 < day && day < 6 ) {
    var taskList = "";
    var text = "";
    // Slackに送信するメッセージの作成
    for (row = startRow; row <= lastRow; row++) {
      // タスクの内容を取得
      var detail = sheet.getRange(row, detailColumn).getValue();
      // 締切を取得
      var deadlineDate = sheet.getRange(row, deadlineColumn).getValue();
      // 担当者を取得
      var asignee = sheet.getRange(row, asigneeColumn).getValue();
      // ステータスを取得
      var comment = sheet.getRange(row, commentColumn).getValue();

      // 日付の整形
      deadlineDate = Utilities.formatDate(deadlineDate, "Asia/Tokyo", "MM/dd");

      // 今日の日付と作業予定日を比較、taskListの生成
      if (comment !== "DONE" && today == deadlineDate) {
        taskList = `${taskList}・${detail}` + "\n" + ` - 締切: ${deadlineDate} 担当者: ${asignee}` + "\n";
      }
    }

    if (taskList == "") {
      text = "本日のタスクはありません"
    } else {
      text = `本日のタスクです \`\`\`${taskList}\`\`\``;
    }
    sendMessage(text);
  }

ここまでで連携させるプログラムは完成です。
ただ、これらを定期的に実行しなければ、予定を確認しに行く手間を省くことはできないので、最後に定期実行のためのコードを作成します。

定期実行編

定期実行をするためのコードは次のとおりです。
まず、実行させたいタイミングをhour、minutesにわけて設定します。
そして、設定した時間に発火させるようにトリガーを設定するといった形です。
なぜこのようなことをしなければならないかというと、GASの機能として設定できるトリガーは1時間の幅の中で、どこかのタイミングで実行させるものだからです。
要するには、細かい実行時刻を設定できないからです。

function setTrigger() {

  const time = new Date();
  // 実行時間の設定
  time.setHours(18);
  time.setMinutes(23);
  // 実行トリガーの設定
  ScriptApp.newTrigger('ScheduleManager').timeBased().at(time).create();
  ScriptApp.newTrigger('ToDoManager').timeBased().at(time).create();

}

実行

実装が完了したら、最後は実行です。
Google Calendar、Googleスプレッドシートそれぞれと連携したコードを発火させるものは準備したため、発火させる物自体を動かす設定をしていきます。

この処理は、GASに実行タイマーを設定することで実現できます。
まず、以下の画像の時計のようなアイコンをクリックします

クリックすると以下のようなページに遷移します。

ページの右下の トリガーを追加 をクリックした後、以下のように設定すれば完了です。
このとき選択する時刻は、先程設定した時間よりも早い時間にしなければ、期待したとおりの動作にならないため、その点だけ気をつけてください。
認証周りでエラーが発生するかもしれませんが、その場合はこちらのページを参照してください。

例がよくないですが、実行されると次のようなメッセージが届きます。
こういったメッセージが確認できたら完成です。

まとめ

いろいろと時間がなかったので、かなりやっつけな内容になってしまいました。
大まかな流れが伝わっていればなと思います…暇があったら修正します。
今回はすでに登録されている内容を通知するためだけのものでしたが、気が向いたら予定の変更とかできるように作れたらいいかなと思っています。
個人的な話ですが、完了したタスクのステータスをそのままにしてしまうこととかもありますので…