#面倒くさいはストレスそのもの
うちの会社では部内全員の勤務予定を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);
}
###コード全文は以下 ##3. Google Apps Scriptのトリガー機能で毎朝実行されるように設定する ##4.スプレッドシートの設定 ##5.結果 ##6.詰まったポイント #チームメンバーへ勝手にメールを送信した #総括 ###今後改善したいポイント
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);
}
}
①左の時計マークからトリガーを選択
②イベントのソースを選択を時間主導型
③時間ベースのトリガーのタイプを選択を日付ベースのタイマー
③時刻を選択を朝配信にするため、午前６時～７時
スプレッドシートのリンクを共有、アドレス表をユーザー側で編集可能にし、メンバー側で配信の有無を決定できるようにした。
予定の時刻にメールが正しく送信できた。
コードはおかしくないのにメールが送信できないことが多数あった。
調べてみるとGASでGmailの送信回数は上限があるとのことだった。
参考リンクを元に、メールの残り送信回数を確認したところ、見事に0だった。
一日の送信限界では無かったため、一時間程経ったらメール送信可能回数は100に戻っていた。
####メンバーからのフィードバック結果
概ねメンバーからの好意的なフィードバッグも得られたので、他のデータや他のチームに対しても類似の水平展開が可能になりました。Gmailの送信回数はデバッグ時の鬼門でした、この手のサービスでコーディングするときは、サービスを実行するコードはある程度書けてから実行するが望ましいですね。今回は、GoogleSpreadSheetを簡易的なDBとして利用する手段を覚えられたこと、似たようなパターンの業務で使えるテンプレートを作ることができたことが収穫です。