Edited at

Spreadsheetから自分や他の人のカレンダーにイベントを追加してSlack通知するGAS


TL;DR

Spreadsheetの表にイベント名や時間、担当者名を記入し、ボタンを押したらその担当者のGoogle Calendarにイベントが追加されるようなスクリプトを書いてほしいと頼まれたので書いたものです。

Google Calendarにイベントを追加するGASは、そのへんに沢山情報が転がっています。

この記事は、それらを少し拡張しただけに過ぎません。

コードは以下に示します。


作ったもの

Spreadsheetはこんな感じ。



  • 未登録 の行のイベントを、担当1から担当5のカレンダーに追加する。


    • 担当者は5名以上にならないという前提



  • 登録後、登録済みとセルを更新する。

  • 右上のボタンをクリックすると実行される。

  • 正常に終了したら、slackに通知する。

  • 簡単な必須チェックとかはする。

担当者のリストは、以下のように別シートで情報を持ち、そこから取得します。

担当者リストのシートには、メールアドレス(後述するカレンダーID)も併せて保持しています。

slack通知はこのような感じです。


実装

ボタンのクリックがトリガーになるGASを以下のように書きました。

社内で使うだけのものだし、あまりこれに時間をかけたくなかったので、すごく微妙なコードだと思います。

ほんの参考程度に考えてください。

これはボタンクリックで呼ばれるファンクションです。


addEventsToCalendar.gs

function addEventsToCalendar(){

var sht,
i,
eventDate,
eventName,
startTime,
endTime,
place,
chargeList,
assignedName1,
assignedName2,
assignedName3,
assignedName4,
assignedName5,
willAddFlag, // '未登録' or '登録済み'
isUpdate; // すべて登録済みの場合false

var result = {};
result.count = 0; // イベント追加件数
result.summary = []; // slack通知メッセージ用

sht = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("シート名");
isUpdate = false;

// 今回のSpreadsheetでは3行目からがイベントの情報
for(i = 3; i <= sht.getLastRow(); i++){
willAddFlag = sht.getRange(i,1).getValue();
if(willAddFlag === "未登録"){
isUpdate = true;

eventName = sht.getRange(i,2).getValue();
eventDate = sht.getRange(i,3).getValue();
startTime = sht.getRange(i,4).getValue();
endTime = sht.getRange(i,5).getValue();
place = sht.getRange(i,6).getValue();
assignedName1 = sht.getRange(i,7).getValue();
assignedName2 = sht.getRange(i,8).getValue();
assignedName3 = sht.getRange(i,9).getValue();
assignedName4 = sht.getRange(i,10).getValue();
assignedName5 = sht.getRange(i,11).getValue();

eventDate = Utilities.formatDate(eventDate,"JST","yyyy/MM/dd");

if(startTime === ""){
Browser.msgBox("開始時間時間を入力して下さい。");
return;
}else{
var start = buildFormattedTime(eventDate, startTime)
}

if(endTime === ""){
Browser.msgBox("終了時間を入力して下さい。");
return;
}else{
var end = buildFormattedTime(eventDate, endTime)
}

if(eventName === "") {
Browser.msgBox("イベント名を入力してください。");
return;
}
if(eventName === "") {
Browser.msgBox("イベント日を入力してください。");
return;
}

if(assignedName1) {
result.count = createCalenderEvent(assignedName1, eventName, start, end, place, result);
result.summary = buildNotificationBody(assignedName1, eventName, eventDate, startTime, endTime, place, result);
}else {
Browser.msgBox("少なくとも担当1は入力してください。");
return;
}

if(assignedName2) {
result.count = createCalenderEvent(assignedName2, eventName, start, end, place, result);
result.summary = buildNotificationBody(assignedName2, eventName, eventDate, startTime, endTime, place, result);
}
if(assignedName3) {
result.count = createCalenderEvent(assignedName3, eventName, start, end, place, result);
result.summary = buildNotificationBody(assignedName3, eventName, eventDate, startTime, endTime, place, result);
}
if(assignedName4) {
result.count = createCalenderEvent(assignedName4, eventName, start, end, place, result);
result.summary = buildNotificationBody(assignedName4, eventName, eventDate, startTime, endTime, place, result);
}
if(assignedName5) {
result.count = createCalenderEvent(assignedName5, eventName, start, end, place, result);
result.summary = buildNotificationBody(assignedName5, eventName, eventDate, startTime, endTime, place, result);
}
}

sht.getRange(i,1).setValue("登録済み");
}

if(!isUpdate) {
Browser.msgBox("登録対象のイベントが存在しません。");
return;
}

if(!result.count) {
Browser.msgBox("イベントを登録できませんでした。");
return;
}

resultNotification("*" + result.count + "件のイベントが登録されました。*\\n" + result.summary.join("\\n"));
Browser.msgBox(result.count + "件のイベントを登録しました。");
}


以下は、Google Calendarに登録できるようにDateオブジェクトを生成するファンクション。


buildFormattedTime.gs

function buildFormattedTime(date, time){

var hour, minute, second, formattedTime;

hour = time. getHours();
minute = time.getMinutes();
second = time.getSeconds();

formattedTime = new Date(date + " " + hour + ":" + minute + ":" + second); 

return formattedTime;
}


以下は、実際にカレンダーにイベントを追加するファンクション。


createCalenderEvent.gs

function createCalenderEvent(name, eventName, start, end, place, result){

var mapSht = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("担当者一覧");
var id = findAssignedId(mapSht, name, 1);
Cal = CalendarApp.getCalendarById(id);

try {
Cal.createEvent(eventName,start,end,{location:place});
} catch(e) {
Browser.msgBox("エラーが発生しました: " + e);
return;
}
result.count++;
return result.count;
}


以下は、担当者名と対応するカレンダーIDを取得するファンクション。

カレンダーIDとは、Google Calendar固有のIDで、以前は複雑な英数字だったようですが、今はメールアドレスと同じです。


findAssignedId.gs

function findAssignedId(sheet,val,col){

var data, id;

data = sheet.getDataRange().getValues();

// 今回、IDは担当者一覧のシートの2列名に記入しています
for(var i=1; i<data.length; i++){
if(data[i][col-1] === val){
id = sheet.getRange(i+1, 2).getValue();
return id;
}
}
Browser.msgBox("担当者一覧から正しくデータを取得できませんでした。");
return;
}


以下は、slack通知用のメッセージを生成するファンクションです。


buildNotificationBody.gs

function buildNotificationBody(name, eventName, date, startTime, endTime, place, result){

var startTimeMinutes = startTime.getMinutes();
var endTimeMinutes = endTime.getMinutes()

// 10:00とかだと .getMinutes() で "0"を取得して "10:0"というように表示されて見栄えが悪いので"00"にする。
if(startTimeMinutes === 0) {
startTimeMinutes = "00";
}

if(endTimeMinutes === 0) {
endTimeMinutes = "00";
}

var body = name + ": " + eventName + " " + place + " " + date + " " + startTime.getHours() + ":" + startTimeMinutes + " ~ " + endTime.getHours() + ":" + endTimeMinutes + "\\n";
result.summary.push(body);

return result.summary;
}


以下は、slackに通知を送るファンクションです。


resultNotification.gs

function resultNotification(body){

// Incoming WebhooksのURL
var key = "https://hooks.slack.com/services/xxxxxxxxxxx/xxxxxxxxxx/xxxxxxxxxxxxxxxxxxxx";
var options = {
"method" : "POST",
"headers" : {"Content-type":"application/json"},
"payload" : '{"text":"' + body + '"}'
}
UrlFetchApp.fetch(key, options);
}



注意

同じGoogleのグループに所属しているユーザーなら、他のユーザーからでもイベント追加できるだろうと勝手に思っていましたが、

ボタンをクリックしたユーザーが、イベントを追加したいユーザーのカレンダーの編集権限を持っていないとちゃんと動作しないです。

(CalendarAppオブジェクトがnullになってcreateEventできない)

普通に考えれば当たり前のことですね。。。


おわり

以上のような感じで実装しました。

すごく読みづらいコードで申し訳ないですが、同じようなことをしようとしている方の参考になれば嬉しいです。