素人が翌日の勤務(ｼﾌﾄ)を告げるLINEbotを作った話【14】

2021年1月12日 13:31

2週間ほどサボっている間に年があけていました。今年もよろしくおねがいします。

noteを始めて3ヶ月ちょっと経ちますが、ここまで書いてきたシフト確認ツールの記事もいよいよ今回で終了です。今回からはGoogleカレンダーとの連携について書いていきます。基本的に必要な機能は前回までに揃ってますので、ここからは番外編ということになると思います。

予定をLINEで通知したい

筆者の所属する会社では、Googleカレンダーで予定の管理をしています。せっかく毎日シフトを通知しているのだから、翌日の予定も一緒に通知できたら便利です。

大まかな流れとしては、カレンダーから特定の日の予定を取得→テキスト化→LINEメッセージ、という感じで、そんな複雑じゃありません。

予定を取得→テキスト化

function getScheduleText(targetDate) {
 const week = ['日','月','火','水','木','金','土'];
 const cal = CalendarApp.getCalendarById('*******@gmail.com');
 var date = new Date(targetDate.getFullYear(),targetDate.getMonth(),targetDate.getDate());
 var strHeader = '【予定】\n ';  
 var strBody = getEvents(cal,date);

 if ( _isNull(strBody) ) strBody = '予定はありません。';
 return strHeader + strBody;
}

function getEvents(prmCal,prmDate) {
 var strEvents = '';
 var strStart = '';
 var strEnd = '';
 var strTime = '';
 var strLocation = '';
 var strDescription = '';
 if ( !_isNull(prmCal) ) {
    var arrEvents = prmCal.getEventsForDay(new Date(prmDate));
    for (var i=0; i<arrEvents.length; i++) {
      if ( !_isNull(strEvents) ) strEvents += '\n';
      strStart = _HHmm(arrEvents[i].getStartTime());
      strEnd = _HHmm(arrEvents[i].getEndTime());
      if ( strStart===strEnd ) {
        strTime = '終日';
      } else {
        strTime = strStart + '～' + strEnd;
      }
      strEvents += '･' + strTime + ' ' + arrEvents[i].getTitle();
      strLocation = arrEvents[i].getLocation();
      strDescription = arrEvents[i].getDescription();
      if ( !_isNull(strLocation) ) strEvents += '\n　場所：' + strLocation;
      if ( !_isNull(strDescription) ) strEvents += '\n　説明：' + strDescription;
    }
 }
 return strEvents;
}

function _HHmm(str){
 return Utilities.formatDate(str,'JST','HH:mm');
}

function _isNull(prm) {
 if ( prm=='' || prm===null || prm===undefined ) {
   return true;
 } else {
   return false;
 }

"getEvents"でGoogleカレンダーから予定を取得し、"getScheduleText"でテキストとして成形しています。

これを”getWorkDataFromSheet”に貼り付ければ出来上がりです！具体的には、最終行（return {text: text, workers: workers};）の直前にtext = text + getScheduleText(targetDate)の1行を追加するだけです。

 //省略
 text = text + getScheduleText(targetDate)
 return  {text: text, workers: workers}; 
}

これでOK

テストしてみます

Googleカレンダーに登録されている予定が記載されました。

予定がない日は、「予定はありません」と表示されます

これまでの全コードまとめ

スプレッドシート側

const channel_access_token = "トークン************************";
const headers = {
  "Content-Type": "application/json; charset=UTF-8",
  "Authorization": "Bearer " + channel_access_token
};
const spreadsheet = SpreadsheetApp.openById("ID**************");
const sheet = spreadsheet.getSheetByName("勤務表");
const userSheet = spreadsheet.getSheetByName("ユーザー");
const logSheet = spreadsheet.getSheetByName("ログ");


function doPost(e) {
 const webhookData = JSON.parse(e.postData.contents).events[0];
 const eventType = webhookData.type;
 const replyToken = webhookData.replyToken;

 switch(eventType){
   case "follow":  //友だち追加
     follow(webhookData);
     break;
   case "message":  //メッセージ受信
     messageEvent(webhookData);
     break;
   case "unfollow":  //ブロック
     unfollow(webhookData)
     break;
   case "postback":  //ポストバックイベント
     var postbackData = webhookData.postback.params.date
     var targetDate = new Date(postbackData);
     var obj = getWorkDataFromSheet(targetDate);
     var text = obj.text
     sendLineMessageFromReplyToken(replyToken, text)
     break;
 }
}

function follow(webhookData){
 const userId = webhookData.source.userId;
 const nickname = getUserProfile(userId);
 const last_row = userSheet.getLastRow();

 userSheet.getRange(last_row+1,2).setValue(nickname);
 userSheet.getRange(last_row+1,3).setValue(userId);
 userSheet.getDataRange().removeDuplicates([3])  
}


function messageEvent(webhookData){
 const userId = webhookData.source.userId;
 const nickname = getUserProfile(userId);
 const timestamp = new Date(webhookData.timestamp)
 const message = webhookData.message.text.split("\n");
 const replyToken = webhookData.replyToken;
 const whatToDo = message[0];
 const name = message[1];
 const date = message[2];
 const newshift = message[3];
 const last_row = userSheet.getLastRow();
 const lastRow = logSheet.getLastRow();

 logSheet.getRange(lastRow+1,1).setValue(timestamp);
 logSheet.getRange(lastRow+1,2).setValue(nickname);
 logSheet.getRange(lastRow+1,3).setValue(userId);
 logSheet.getRange(lastRow+1,4).setValue(message[0]);
 logSheet.getRange(lastRow+1,5).setValue(message[1]);
 logSheet.getRange(lastRow+1,6).setValue(message[2]);
 logSheet.getRange(lastRow+1,7).setValue(message[3]);

 switch (whatToDo) {
    case 'こんにちは':
      sendLineMessageFromReplyToken(replyToken, "さようなら");
      break;
    case '勤務確認':
      datetimePickerAction(replyToken);
      break;  
    default:
      return;
      break;  
 }
}

function unfollow(webhookData){
 const data = userSheet.getDataRange()
 const userId = webhookData.source.userId;
 const userFinder = data.createTextFinder(userId).findAll();
 for ( var i = 0; i < userFinder.length; i++ ) {
       var userRow = userFinder[i].getRow();
       var user = userFinder[i].offset(0,1).getValue()
   userSheet.deleteRows(userRow);
 }
}

function getUserProfile(userId){ 
 const Url = 'https://api.line.me/v2/bot/profile/' + userId;
 const userProfile = UrlFetchApp.fetch(Url,{
   'headers': {
     'Authorization' :  'Bearer ' + channel_access_token,
   },
 })
 return JSON.parse(userProfile).displayName;
}

function sendLineMessageFromReplyToken(token, replyText) {
 var url = "https://api.line.me/v2/bot/message/reply";
 var headers = {
   "Content-Type": "application/json; charset=UTF-8",
   "Authorization": "Bearer " + channel_access_token
 };
 var postData = {
   "replyToken": token,
   "messages": [{
   "type": "text",
   "text": replyText
   }]
 };
 var options = {
   "method": "POST",
   "headers": headers,
   "payload": JSON.stringify(postData)
 };
 return UrlFetchApp.fetch(url, options);
}

function sendLineMessageUsingBroadcast(text) {
 var Url = "https://api.line.me/v2/bot/message/broadcast";
 var postData = {
   "messages": [{
   "type": "text",
   "text":  text,
   }]
 };
 var options = {
   "method": "POST",
   "headers": headers,
   "payload": JSON.stringify(postData)
 };
 return UrlFetchApp.fetch(Url, options);
}

function sendLineMessageFromUserId(text,userId) {
 var url = "https://api.line.me/v2/bot/message/push";
 var postData = {
   "to": userId,
   "messages": [{
   "type": "text",
   "text":  text,
   }]
 };
 var options = {
 "method": "POST",
 "headers": headers,
 "payload": JSON.stringify(postData)
 };
 return UrlFetchApp.fetch(url, options);
}

function sendLineMessageToAdmin(text) {
 var url = "https://api.line.me/v2/bot/message/push";
 var postData = {
   "to": "ユーザーID",
   "messages": [{
   "type": "text",
   "text":  text,
   }]
 };
 var options = {
   "method": "POST",
   "headers": headers,
   "payload": JSON.stringify(postData)
 };
 return UrlFetchApp.fetch(url, options);
}

function sendTomorrowsShiftEverynight(){
 var today = new Date();
 today.setDate(today.getDate()+1)
 const obj = getWorkDataFromSheet(today)
 const text = obj.text
 if(obj.workers == "0" ){
   return;  
 }
 sendLineMessageUsingBroadcast(text);
}

function getWorkDataFromSheet(targetDate){ 
 const week = ["日","月","火","水","木","金","土"];
 const lastRow = sheet.getLastRow();
 const month = Utilities.formatDate(targetDate, 'JST', 'MM');
 const day = Utilities.formatDate(targetDate, 'JST', 'dd');
 const date = month + "/" + day 
 const dateFinder = sheet.createTextFinder(date).findAll();
 
   for ( var i = 0; i < dateFinder.length; i++ ) {
       var dateColumn = dateFinder[i].getColumn();
   }
 
 const data = sheet.getRange(1,dateColumn,lastRow);
 let earlyTurnList = [];
 let dayShiftList = [];
 let lateTurnList =[];
 let nightShiftList = [];
 let afterNightShiftList = [];
 let holidayList = [];
 let paidVacationList = [];
 
 
 let earlyTurnFinder = data.createTextFinder('早').findAll();
 for ( let i = 0; i < earlyTurnFinder.length; i++ ) {
   let earlyTurn = earlyTurnFinder[i].offset( 0 , - dateColumn + 1).getValue();
   earlyTurnList.push(earlyTurn)
  }
 
 let dayShiftFinder = data.createTextFinder('日').findAll();
 for ( let i = 0; i < dayShiftFinder.length; i++ ) {
   let dayShift = dayShiftFinder[i].offset( 0 , - dateColumn + 1).getValue();
   dayShiftList.push(dayShift)
  }  
 
 let lateTurnFinder = data.createTextFinder('遅').findAll();
 for ( let i = 0; i < lateTurnFinder.length; i++ ) {
   let lateTurn = lateTurnFinder[i].offset( 0 , - dateColumn + 1).getValue();
   lateTurnList.push(lateTurn)
  } 
 
 let nightShiftFinder = data.createTextFinder('夜').findAll();
 for ( let i = 0; i < nightShiftFinder.length; i++ ) {
   let nightShift = nightShiftFinder[i].offset( 0 , - dateColumn + 1).getValue();
   nightShiftList.push(nightShift)
  }
 
 let afterNightShiftFinder = data.createTextFinder('明').findAll();
 for ( let i = 0; i < afterNightShiftFinder.length; i++ ) {
   let afterNightShift = afterNightShiftFinder[i].offset( 0 , - dateColumn + 1).getValue();
   afterNightShiftList.push(afterNightShift)
  }
 
 let holidayFinder = data.createTextFinder('公').findAll();
 for ( let i = 0; i < holidayFinder.length; i++ ) {
   let holiday = holidayFinder[i].offset( 0 , - dateColumn + 1).getValue();
   holidayList.push(holiday)
  }
 
 let paidVacationFinder = data.createTextFinder('有').findAll();
 for ( let i = 0; i < paidVacationFinder.length; i++ ) {
   let paidVacation = paidVacationFinder[i].offset( 0 , - dateColumn + 1).getValue();
   paidVacationList.push(paidVacation)
  }

 var text = date +"("+week[targetDate.getDay()]+") 勤務者一覧"+"\n\n" 
 if(earlyTurnList.length > 0){
   text = text +  "早番 ： " + earlyTurnList +"\n\n"
 }  
 if(dayShiftList.length > 0){
   text = text +  "日勤 ： " + dayShiftList +"\n\n"
 }  
 if(lateTurnList.length > 0){
   text = text +  "遅番 ： " + lateTurnList +"\n\n"
 }
 if(nightShiftList.length > 0){
   text = text +  "夜勤 ： " + nightShiftList +"\n\n"
 }
 if(afterNightShiftList.length > 0){
   text = text +  "明け ： " + afterNightShiftList +"\n\n"
 }  
 if(holidayList.length + paidVacationList.length > 0){
  text = text +  "公・休・有 ： \n" + holidayList+ "," + paidVacationList + "\n\n" ;
  }
 
 text = text + getScheduleText(targetDate)

 if(dayShiftList.length + earlyTurnList.length　+　lateTurnList.length + nightShiftList.length + afterNightShiftList.length < 1 ){
   var workers = 0; 
 }

 return  {text: text, workers: workers}; 
}

function datetimePickerAction(token) {
 var startDate = sheet.getRange(1,2).getValue()
 startDate = Utilities.formatDate(startDate, 'JST', 'yyyy-MM-dd');
 var endDate =sheet.getRange(1,sheet.getLastColumn()).getValue();
 endDate = Utilities.formatDate(endDate, 'JST' , 'yyyy-MM-dd');
   

 UrlFetchApp.fetch('https://api.line.me/v2/bot/message/reply', {
   'headers': {
	'Content-Type': 'application/json',
	'Authorization': 'Bearer ' + channel_access_token, 
	            },
	'method': 'POST',
	'payload': JSON.stringify({
	"replyToken": token, 
	"messages": [
				{
			    "type": "template",
				"altText": "勤務確認",
				"template": {
						    "type": "buttons",
						    "title": "勤務確認",
						    "text": "指定日の勤務者を確認できます",
						    "actions": [
							           {
								       "type": "datetimepicker",
								       "label": "日付を選択してください。",
								       "data": "action=settime",
								       "mode": "date",
                                      "min": startDate,
                                      "max": endDate
							           }
						               ]
					        }
				}
			    ],			
		}),
	});
}

function updateForm() {  
 const form = FormApp.openById("ID******************");
 const items = form.getItems();
 const firstQuestion = items[0];
 const secondQuestion = items[1];
 const fourthQuestion = items[3];
 const userList = userSheet.getRange(2,4,userSheet.getLastRow()).getValues().flat();
 var popIt = userList.pop();
 const staffList = sheet.getRange(2,1,sheet.getLastRow()).getValues().flat();
 popIt = staffList.pop();
 const workList = ["早","日","遅","夜","明","公","有"]
 Logger.log(userList)
 Logger.log(staffList)
 
 firstQuestion.asListItem().setChoiceValues(userList).setRequired(true);
 secondQuestion.asListItem().setChoiceValues(staffList).setRequired(false);
 fourthQuestion.asListItem().setChoiceValues(workList).setRequired(true);
};

function getScheduleText(targetDate) {
 const week = ['日','月','火','水','木','金','土'];
 const cal = CalendarApp.getCalendarById('*******************@gmail.com');
 var date = new Date(targetDate.getFullYear(),targetDate.getMonth(),targetDate.getDate());
 var strHeader = '【予定】\n ';  
 var strBody = getEvents(cal,date);

 if ( _isNull(strBody) ) strBody = '予定はありません。';
 return strHeader + strBody;
}

function getEvents(prmCal,prmDate) {
 var strEvents = '';
 var strStart = '';
 var strEnd = '';
 var strTime = '';
 var strLocation = '';
 var strDescription = '';
 if ( !_isNull(prmCal) ) {
    var arrEvents = prmCal.getEventsForDay(new Date(prmDate));
    for (var i=0; i<arrEvents.length; i++) {
      if ( !_isNull(strEvents) ) strEvents += '\n';
      strStart = _HHmm(arrEvents[i].getStartTime());
      strEnd = _HHmm(arrEvents[i].getEndTime());
      if ( strStart===strEnd ) {
        strTime = '終日';
      } else {
        strTime = strStart + '～' + strEnd;
      }
      strEvents += '･' + strTime + ' ' + arrEvents[i].getTitle();
      strLocation = arrEvents[i].getLocation();
      strDescription = arrEvents[i].getDescription();
      if ( !_isNull(strLocation) ) strEvents += '\n　場所：' + strLocation;
      if ( !_isNull(strDescription) ) strEvents += '\n　説明：' + strDescription;
    }
 }
 return strEvents;
}

function _HHmm(str){
 return Utilities.formatDate(str,'JST','HH:mm');
}

function _isNull(prm) {
 if ( prm=='' || prm===null || prm===undefined ) {
   return true;
 } else {
   return false;
 }
}

フォーム側

var channel_access_token = "トークン*************************";
var headers = {
  "Content-Type": "application/json; charset=UTF-8",
  "Authorization": "Bearer " + channel_access_token
};
var spreadsheet = SpreadsheetApp.openById("ID*************");
var sheet = spreadsheet.getSheetByName("勤務表");
var userSheet = spreadsheet.getSheetByName("ユーザー");
var formSheet = spreadsheet.getSheetByName("勤務変更");

//勤務変更
function changeWorkinglistByForm(){
 
 const dateRow = formSheet.getRange(2,4,formSheet.getLastRow());
 dateRow.setNumberFormat('MM/dd');
 const userData = formSheet.getRange(formSheet.getLastRow(),2).getValue();
 const nameCell = formSheet.getRange(formSheet.getLastRow(),3)
 if(nameCell.isBlank()){
   var nameData = userData
   }else{
     var nameData = nameCell.getValue();
   };
 
 const dateData = formSheet.getRange(formSheet.getLastRow(),4).getValue();
 const newshift = formSheet.getRange(formSheet.getLastRow(),5).getValue();
 const month = ("00" + (dateData.getMonth()+1)).slice(-2);
 const day = ("00" + (dateData.getDate())).slice(-2);
 const date = month + "/" + day
 
 const nameFinder = sheet.createTextFinder(nameData).findAll();
 const dateFinder = sheet.createTextFinder(date).findAll();
 
 for ( var i = 0; i < nameFinder.length; i++ ) {
       var nameRow = nameFinder[i].getRow();
   };
 for ( var i = 0; i < dateFinder.length; i++ ) {
       var dateColumn = dateFinder[i].getColumn();
   };
 
 const oldShift = sheet.getRange(nameRow,dateColumn).getValue();
 sheet.getRange(nameRow,dateColumn).setValue(newshift);
 
 const text = "【勤務変更】\n日付：" + date + "\n職員：" + nameData + "\n変更内容：" + oldShift + "→"+ newshift +"\nby：" + userData ;
 
 sendLineMessageUsingBroadcast(text);
 
 const user =userSheet.getRange(2,4,userSheet.getLastRow());
 const userNumber = user.getValues().length-1;
 const userFinder = user.createTextFinder(userData).findAll();
 for ( var i = 0; i < userFinder.length; i++ ) {
       var userId = userFinder[i].offset( 0 , -1).getValue();
  }
 
 const textToUser = "変更内容が登録者全員に通知されました。\n登録者は現在"+ userNumber + "人です。従業員全員に伝わっているわけではありませんのでご注意ください"
 sendLineMessageFromUserId(textToUser,userId);
};

//管理者にメッセージを送る
function sendLineMessageFromUserId(text,userId) {
var url = "https://api.line.me/v2/bot/message/push";
var postData = {
  "to": userId,
  "messages": [{
    "type": "text",
    "text":  text,
  }]
};
var options = {
  "method": "POST",
  "headers": headers,
  "payload": JSON.stringify(postData)
};
return UrlFetchApp.fetch(url, options);
}

//登録者全員にメッセージを送る
function sendLineMessageUsingBroadcast(text) {
var Url = "https://api.line.me/v2/bot/message/broadcast";
var postData = {
    "messages": [{
    "type": "text",
    "text":  text,
  }
              ]};
var options = {
  "method": "POST",
  "headers": headers,
  "payload": JSON.stringify(postData)
};
return UrlFetchApp.fetch(Url, options);
}

14回に渡って書いてきた「素人が翌日の勤務(ｼﾌﾄ)を告げるLINEbotを作った話」シリーズ、なんとか書ききりました。ちょっと達成感！

今後も、私と同じようなプログラミング初心者の方に少しでも役に立てる記事が書けたらいいな！

この記事が気に入ったらサポートをしてみませんか？