今日他の人どんな勤務予定だっけ？表を見に行くのが面倒くさいのでGASで毎朝自動メール配信する

面倒くさいはストレスそのもの

うちの会社では部内全員の勤務予定をMicrosoft SharePointに掲載してます。
皆さんTeams経由で勤務予定を開くことが多いんですが、職場での声に耳を傾けていると以下のような声が聴こえてきました。

• いちいち表を見に行くのが面倒くさい
• Teamsを立ち上げるとPCが重くなるから開きたくない
• 一ヶ月分の日程×人数分の中から探すのが億劫

あ～・・・と思わず同調してしまいました。

今回の課題と方向性

そう、能動的に情報を得に行くって面倒くさいんです。それが大したことじゃないと尚更。
ならどうすれば楽になるのかなぁ・・・と考えた時、朝のルーティンワークにノンストレスで紛れ込めば楽なのでは？と考えました。
うちの部署では(どこでも当たり前でしょうが)、どの担当者も朝のメールチェックは実施します。
つまり毎朝勤務予定をメール配信しておけば、面倒くさい作業をすることなく勤務予定が把握できるわけです！

今回やりたいこと

毎朝、今日の勤務予定表をメンバーに自動でメール配信したい。

何を使ってどうする

Google Apps Scriptを使ってGoogle Spread Sheetの勤務予定表を対象のメンバーに毎朝メール配信する。

制作過程

1.勤務予定表から担当者の名前と今日の勤務予定を変数へ代入する

①１行目の日付を参照し、今日の日付の列番号を変数iに代入する。

const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('yotei');//シートyotei
var i = 2;//下記のwhileで使用 2列目から勤務予定が表示
var chkDay = Utilities.formatDate(sheet.getRange(1, i).getValue(), 'Asia/Tokyo', 'yyyy/MM/dd');//シートの1行目の日付部分を参照 yyyy/mm/dd形式
var toDay = Utilities.formatDate(new Date, 'Asia/Tokyo', 'yyyy/MM/dd');//今日の日付を参照 yyyy/mm/dd形式

//ループ処理 iが今日の日付と同じ列の値になるまで加算
while (toDay > chkDay){;
  i++;
  chkDay = Utilities.formatDate(sheet.getRange(1, i).getValue(), 'Asia/Tokyo', 'yyyy/MM/dd')
}

②1列目の担当者名とi列目の勤務予定を、配列に追加する。
担当者の人数分これを繰り返し、代入された値をjoinメソッドで改行コードを挟み結合する。

//担当者の名前1列目+i列の勤務予定をn人分を配列に追加
var staffs = [];
var lastRow = sheet.getLastRow();
for (let n = 2;n<=lastRow; n++) {
  var staffName = sheet.getRange(n,1).getValue();//1列目の担当者名
  var staffPlan = sheet.getRange(n,i).getValue();//i列目の勤務予定
  staffs.push(staffName + ':' +  staffPlan)
}
let allStaffs = staffs.join('\n'); //一次配列を担当者単位で改行

③変数allStaffs出力結果：改行された値が代入された。

2.アドレス表に記載された全アドレスにメールを送信する

①全てのメールアドレスを配列に個別で代入する。

//シートaddressのメールアドレスを一次配列へ代入する
const sheet2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('address');
var recipient = []
var lastRow2 = sheet2.getLastRow();
for (let z = 1;z<=lastRow2; z++){
  recipient.push(sheet2.getRange(z,1).getValue())
}

②メールの題名、本文を設定、宛先は配列に代入されたメールアドレスとし、メール送信をループさせる。

//メール文章を設定
const subject = toDay + 'チームの出勤状況';//件名
const body = toDay + 'チーム勤務状況は下記になります\n' + allStaffs;//本文

//メール送信を配列分ループする
for (var x in recipient){
  GmailApp.sendEmail(String(recipient[x]), subject, body, options);
}

• 参考
  • 初心者でも簡単！Google Apps ScriptでGmailを操作してメールを送る方法

コード全文は以下

Click

function sendMail() {

const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('yotei');//シートyotei
var i = 2;//下記のwhileで使用 2列目から勤務予定が表示
var chkDay = Utilities.formatDate(sheet.getRange(1, i).getValue(), 'Asia/Tokyo', 'yyyy/MM/dd');//シートの1行目の日付部分を参照 yyyy/mm/dd形式
var toDay = Utilities.formatDate(new Date, 'Asia/Tokyo', 'yyyy/MM/dd');//今日の日付を参照 yyyy/mm/dd形式

//ループ処理 iが今日の日付と同じ列の値になるまで加算
while (toDay > chkDay){;
  i++;
  chkDay = Utilities.formatDate(sheet.getRange(1, i).getValue(), 'Asia/Tokyo', 'yyyy/MM/dd')
}

//担当者の名前1列目+i列の勤務予定をn人分を配列に追加
var staffs = [];
var lastRow = sheet.getLastRow();
for (let n = 2;n<=lastRow; n++) {
  var staffName = sheet.getRange(n,1).getValue();//1列目の担当者名
  var staffPlan = sheet.getRange(n,i).getValue();//i列目の勤務予定
  staffs.push(staffName + ':' +  staffPlan)
}
let allStaffs = staffs.join('\n'); //一次配列を担当者単位で改行
console.log(allStaffs)

//シートaddressのメールアドレスを一次配列へ代入する
const sheet2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('address');
var recipient = []
var lastRow2 = sheet2.getLastRow();
for (let z = 1;z<=lastRow2; z++){
  recipient.push(sheet2.getRange(z,1).getValue())
}

//メール文章を設定
const subject = toDay + 'チームの出勤状況';//件名
const body = toDay + 'チーム勤務状況は下記になります\n' + allStaffs;//本文
const options = {name: 'メール配信実験中です' };//送信者名

//メール送信を配列分ループする
for (var x in recipient){
  GmailApp.sendEmail(String(recipient[x]), subject, body, options);
}
}

3. Google Apps Scriptのトリガー機能で毎朝実行されるように設定する

①左の時計マークからトリガーを選択
②イベントのソースを選択を時間主導型
③時間ベースのトリガーのタイプを選択を日付ベースのタイマー
③時刻を選択を朝配信にするため、午前６時～７時

4.スプレッドシートの設定

スプレッドシートのリンクを共有、アドレス表をユーザー側で編集可能にし、メンバー側で配信の有無を決定できるようにした。

5.結果

予定の時刻にメールが正しく送信できた。

6.詰まったポイント

コードはおかしくないのにメールが送信できないことが多数あった。
調べてみるとGASでGmailの送信回数は上限があるとのことだった。
参考リンクを元に、メールの残り送信回数を確認したところ、見事に0だった。
一日の送信限界では無かったため、一時間程経ったらメール送信可能回数は100に戻っていた。

- 参考
- 【日本語リファレンス】GASの日次割当と日次制限について
- GASのメール送信は実行上限に注意！残り回数確認と上限以上のメールを送る方法

チームメンバーへ勝手にメールを送信した

メンバーからのフィードバック結果

• Aさん：朝Teamsを開く必要がなくなって良かった。
• Bさん：このデータ以外にも似たようなことができるならやって欲しい。
• Cさん：私はいらないので削除しときます。
• Dさん：他のデータもお願い。

総括

概ねメンバーからの好意的なフィードバッグも得られたので、他のデータや他のチームに対しても類似の水平展開が可能になりました。Gmailの送信回数はデバッグ時の鬼門でした、この手のサービスでコーディングするときは、サービスを実行するコードはある程度書けてから実行するが望ましいですね。今回は、GoogleSpreadSheetを簡易的なDBとして利用する手段を覚えられたこと、似たようなパターンの業務で使えるテンプレートを作ることができたことが収穫です。

今後改善したいポイント

• コードにコメントを適切に残したい
• コード自体をキレイに書きたい