目的
勤怠連絡先が多種にわたるためGoogleフォームを起点として、連携するために作成しました。
メーリングリストを起点とした連携は出来ていました。
- メーリングリスト
- Slack
- Chatwork
連絡後にGoogleカレンダーへの記載を忘れたり、Googleカレンダーを記載しても連絡が漏れるケースがありました。
そのためGoogleフォームで必要事項を記入後、GASでGoogleカレンダーへの記載、ならびに連絡が出来るようにします。
またログインしたユーザIDと同じカレンダーIDに書き込みをするようにしております。
Google フォーム
-
下記の様に作りました。
-
Googleアカウントによりログインしたメールアドレスを取得するために設定をします。
Google App Script
スプレッドシートレイアウト
-
回答は下記レイアウトでスプレッドシートに記載されます。上記で設定したメールアドレスも記載されます。
タイムスタンプ 種別 日付 出社予定時刻(出社遅れのみ記入) 登録理由 メールアドレス -
その他に必要な情報、メール送信内容を記載するための項目を追加しました。
タイムスタンプ 種別 日付 出社予定時刻(出社遅れのみ記入) 登録理由 メールアドレス 報告済み カレンダーID 名前 内容
スプレッドシートからデータ取得
-
アクティブなシートを指定します。
var mySheet = SpreadsheetApp.getActiveSheet(); var dat = mySheet.getDataRange().getValues(); -
指定したシートから"報告済み"になにも記載がない回答を取得します。
// Googleフォームの回答結果が記載されているスプレッドシートから情報を取得する。 // dat[i][6]に"報告済み"が記載させていないデータが対象 for(var i=1;i<dat.length;i++){ if(dat[i][6] == ""){ // 回答を取得したあとの処理 }
### カレンダーへの書き込み処理
1. 種別によりカレンダーの開始・終了時間を指定します。
```JavaScript:
// 種別取得
var selectedType = dat[i][1];
if (selectedType == "終日休暇"){
var fromDate = new Date(dat[i][2]);
fromDate.setHours(9);
fromDate.setMinutes(30);
var toDate = new Date(dat[i][2]);
toDate.setHours(17);
toDate.setMinutes(30);
}else if(selectedType == "午前半休"){
var fromDate = new Date(dat[i][2]);
fromDate.setHours(9);
fromDate.setMinutes(30);
var toDate = new Date(dat[i][2]);
toDate.setHours(14);
toDate.setMinutes(30);
}else if(selectedType == "午後半休"){
var fromDate = new Date(dat[i][2]);
fromDate.setHours(14);
fromDate.setMinutes(30);
var toDate = new Date(dat[i][2]);
toDate.setHours(17);
toDate.setMinutes(30);
}else if(selectedType == "出社遅れ"){
var fromDate = new Date(dat[i][2]);
fromDate.setHours(9);
fromDate.setMinutes(30);
var toDate = new Date(dat[i][2]);
var arriveAtWorkTime = new Date(dat[i][3]);
toDate.setHours(arriveAtWorkTime.getHours());
toDate.setMinutes(arriveAtWorkTime.getMinutes());
}
`
-
カレンダーに書き込みます。カレンダーを共有していないときに発生するエラーを拾っています。
// Googl フォーム機能により取得した回答したユーザのメールアドレスをスプレッドシートから取得 var calendarID = dat[i][5]; // カレンダーオプションjson作成 var calDescription = dat[i][4]; var options = { description: calDescription } try{ //ログインユーザＩＤでカレンダー特定 var myCal = CalendarApp.getCalendarById(calendarID); // カレンダー記入 var myEvt = myCal.createEvent(selectedType,fromDate,toDate, options); var result = ""; }catch(e){ var result = "エラーの内容:" + e; }
メール送信処理
- メール本文作成functionを書きます。
// 本文生成
var mailBody = makeBody(selectedType,fromDate, toDate, calDescription,calendarID, result);
// メールを送信する
GmailApp.sendEmail(toMail, subject, mailBody)
- functionの中身となります
function makeBody(selectedType,fromDate, toDate, calDescription,calendarID, result){
// メール内容の分岐
if (selectedType == "出社遅れ"){
var workTime = Utilities.formatDate(toDate,"GMT+0900","HH:mm");
var message = "お疲れ様です。\n"+ calendarID + "です。\n\n" + calDescription + "のため" + selectedType + "とさせていただきます。\n"+ "出社は" + workTime + "を予定しています。\n\n以上、よろしくお願いいたします。"
}else{
var message = "お疲れ様です。\n"+ calendarID + "です。\n\n" + calDescription + "のため" + selectedType + "とさせていただきます。\n\n"+ "以上、よろしくお願いいたします。"
}
// エラーが有った場合の文言追加
if (result != ""){
message += "\n\n エラーのためカレンダーに書き込めませんでした。管理者にご連絡後、使用可能となります。" + result;
}
return message
}
結果をスプレッドシートに書き込むために配列に格納します。
// 完了結果を配列に格納
dat[i][6]="報告済み";
dat[i][7]=calendarID;
dat[i][8]=mailBody;
スプレッドシートに書き込みます。
//完了内容記入
mySheet.getRange(1,1,i,10).setValues(dat);
トリガー設定
Google App Scriptが動作するためのトリガーを設定します。
結果
スプレッドシートへの書き込みはこうなります。
ずれてました…。
メール内容はこんな感じです。
カレンダー内容はこんな感じです。
悩んだところ。
当初メールアドレス取得は下記の様にしていました。
// Googleにログインしているメールアドレスを取得
var emailaddress = Session.getActiveUser();
// メールアドレスからフルネームを取得
var contact = ContactsApp.getContact(emailaddress).getFullName();
しかしこの方法だとGoogleフォームにログインしているメールアドレスが取得できないため、フォームの回答から取得するように変更しました。もうすこし上手い方法がありそうですが…。