MENU

GASとスプレッドシートでその日の担当者をアナウンスするリマインダーを作ってみた。

プログラミング JavaScript

作ったもの

スプレッドシートの当番表を元に、Google Chatの自動リマインダーをGASで作ってみました。

私の職場で

  • 部署内でやらなきゃいけない細々した雑務を当番表を作って回している。
  • Google Chatでその日の当番担当者をリマインドしたかったが、人力でやるのは面倒くさすぎる。

こんなことがあり、そういえば、Google App Scriptて使ったことないな...

参照元がスプレッドシートだし、GASで自動リマインダー作れるかな？と思い試してみました。

実現したいもの

やりたいこととしてはこんな感じでした。

  • スプレッドシートの当番表を元に、Google Chatのルームでリマインドしたい。
  • その日の当番が誰なのか、リマインダーでアナウンスしたい。
  • 平日の朝10時頃にリマインドを自動で投稿してほしい。

当番表のスプレッドシートはこんな感じです。

実際に作ってみる

  • GASってどんな言語なんだろう？と思ったが、調べてみたところJSベースのGoogleオリジナル言語らしい。JSの感覚で書いていけば大丈夫そう
  • スプレッドシートにApp Scriptを組み込むには、 スプレッドシート > [拡張機能] > [Apps Script] へ遷移し、実行したい関数を定義していく(これすら知らかなった)

処理の流れ

今回やりたいことを実現するためにこんな感じの処理の流れを考えました。

  1. 今日の日付を取得する。

  2. 当番表の日付から、今日の日付と一致する行を探す。

  3. 2で特定した行の担当者欄に書かれた人の名前を読み取って、出力する。

※　担当者が交代していれば、新しく担当になった人の名前を読み取る。

コード全文

function sendMessageFromGAS() {

  // 今日の日付を取得
  let today = new Date();

  // 今日が土日の場合、処理を終了
  if (today.getDay() == 0 || today.getDay() == 6) {
    console.log("今日は土日なのでリマインドしません。処理を終了します。")
    return;
  }
  let formatted_today = Utilities.formatDate(today, "JST","yyyy/M/d");

  // スプレッドシート＆シートオブジェクトを取得
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('当番表');

  // 日付リストをシートから取得
  let dateRange = sheet.getRange(25, 3, sheet.getLastRow() -1 );
  let dateList = dateRange.getValues();
  let recordNum = 0;

  // 今日の日付と一致する行を探す
  for(let i = 0; i < dateList.length; i++) {
    const target_date = new Date(dateList[i]);
    let formatted_target_date = Utilities.formatDate(target_date, "JST","yyyy/M/d");
    if(formatted_target_date == formatted_today){
      recordNum = i + 25;
      break;
    }
  }

  if(recordNum == 0) {
    console.log("該当の日付がシートに無いので、処理を終了します。");
    return;
  }

  // 今日の日付と一致する行に書かれた名前を読み取る
  let name_in_charge = sheet.getRange("E" + recordNum).getValue();
  let changed_name = sheet.getRange("F" + recordNum).getValue();
  if(changed_name !== "") {
    name_in_charge = changed_name;
  }

  // WebhookURL
  var url = "https://chat.googleapis.com/v1/spaces/xxx/messages?key=xxx";
  
  var message = {'text': "今日の担当は *" + name_in_charge + "* さんです！よろしくお願いします。"}
  var options = {
    'method': 'POST',
    'headers': {
      'Content-Type': 'application/json; charset=UTF-8'
    },
    'payload':JSON.stringify(message)
  };
  
  UrlFetchApp.fetch(url, options);

}

超シンプルで特に難しいことは何もやってないんですが、初見じゃわからなくて結構調べたところがいくつかあるのでメモしときます。

シートの情報を変数に代入する

そもそもどうやってシートの情報をコードで参照するんだろうと思ったんですが、GASでは専用クラスがしっかり定義されていました。

  • スプレッドシートを SpreadsheetApp#getActiveSpreadsheet で取得
  • 取得したスプレッドシート#getSheetByName で参照したいシートを取得できる

参考：Class SpreadsheetApp  |  Apps Script  |  Google Developers

// スプレッドシート＆シートオブジェクトを取得
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('当番表');

シートから特定の列の情報を取得する

こちらも専用の処理がありました。

Sheetクラスの 'getRange'メソッドで取れます。

参考：Class Sheet  |  Apps Script  |  Google Developers

  // 日付リストをシートから取得
  let dateRange = sheet.getRange(25, 3, sheet.getLastRow() -1 );
  let dateList = dateRange.getValues();
  let recordNum = 0;

シートから取得した日付をDate型で扱う

上記の処理で日付一覧はとってこれたんですが、どうやら文字列として扱われてしまってるようでした。

日付一覧と今日の日付を比較したいので、Date型になってもらわないと困る...

今回は文字列として取ってきた日付リスト dateList をDate型のコンストラクタにぶち込んでインスタンス生成するという荒業でなんとかなりました。

 const target_date = new Date(dateList[i]);

※　後日、この解決方法は違うだろうと思って調べてみたところ案の定正攻法ではないことがわかりました。

実行タイミングを設定する

処理は完成したので、定期実行の設定を行います。

これ作るまで全然知らなかったんですが、GASでは一般的なルールの定期実行であれば画面で設定できます。

かなり色んなパターンに対応してるのでここでは紹介しません。

今回は、「その日の10時くらいにリマインドしてほしい」というのがやりたいことなので、

  • 一日一回

  • 午前10時台に関数が実行されるよう設定します。

画面ベースだと、下記の様な設定の仕方になります。

上記の設定だと、その日の10:00~11:00(日本標準時)のいずれかに関数が実行される ことになります。

決して10時ちょうどに実行されることはないというのが注意点。

最初それを理解していなかったので、「10時になったのに何でリマインドされない...？」と混乱してしまいました。

さいごに

なにはともあれ、これでやりたいことが実現できました。

GAS、自分には縁がないと思ってましたが触ってみたらかなりお手軽で楽しかったです。

自分が思いついてないだけで、もっと色々できることあるんだろうなあ。

ランキング参加中
プログラミング