google-apps-script - 如何使用 Google Calendar API 和 Apps 脚本找到与多个参与者开会的时间?
问题描述
我正在使用 Dialogflow API、Apps Script、Calendar API 开发一个聊天机器人项目。聊天机器人应该能够直接从聊天中组织两个或更多参与者之间的会议。例如,用户说“与 john.smith@mail.com 组织明天下午 5 点的会议”,聊天机器人应该去我和 John 的日历,检查这个时间范围的可用性并预订会议。到目前为止,一切都很好。我已经有了这个阶段的解决方案(检查下面的代码片段)。我的问题是如果用户在这个时间范围内很忙,我怎样才能得到所有参与者都空闲的建议时间。我正在使用空闲/忙碌呼叫,但它仅在用户忙碌时返回。
//the function search in spreadshet containing all timezones and selects the corresponding offset
function setCorrectTimeZone(rawTime,rawDate){
var spreadSheet = SpreadsheetApp.openById("10tJCi5bRHs3Cl8Gvw8NGMeHhfRBDnvPD338peH2MWyg"); //the timezones sheed ID stored in CEE Google Assistant shared drive
var sheet = spreadSheet.getSheetByName("timezones");
var timezoneRange = sheet.getRange(2, 1, 513, 2).getValues();
//getting the user's timezone
var userTimeZone = CalendarApp.getDefaultCalendar().getTimeZone();
Logger.log("User time zone: " + userTimeZone);
var userTimeZoneOffset = "";
var correctDateTimeAndZone = "";
//iterating over the timezones from the sheet and comparing with user's to find the correct offset
for(var i = 0; i<timezoneRange.length; i++){
if(timezoneRange[i][1] == userTimeZone){
userTimeZoneOffset = timezoneRange[i][0];
}
}
//taking the date only
var date = rawDate.split('T')[0];
//taking the time only
var timeNoTimeZone = rawTime.split('+')[0].split('T')[1];
//concatenating the date, time and the correct timezone together
correctDateTimeAndZone = date + 'T' + timeNoTimeZone + userTimeZoneOffset;
return correctDateTimeAndZone;
}
function organizeMeeting(dialogflowRawResponse, email) {
var guestEmail = dialogflowRawResponse.queryResult.parameters.email; //the list of all guests
var rawDate = dialogflowRawResponse.queryResult.parameters.date;
var rawTime = dialogflowRawResponse.queryResult.parameters.time;
var eventTitle = dialogflowRawResponse.queryResult.parameters.meetingName;
var hasAllParams = dialogflowRawResponse.queryResult.hasOwnProperty('allRequiredParamsPresent'); //checker for all parameters
var correctedTimezone = setCorrectTimeZone(rawTime,rawDate);
Logger.log("Has all required parameters? " + hasAllParams);
//check if all parameters are passed
while(hasAllParams == false){
Logger.log("Parameters are missing");
Logger.log(dialogflowRawResponse.queryResult.fulfillmentText);
return { text: dialogflowRawResponse.queryResult.fulfillmentText };
}
Logger.log("Guests email list detected: " + JSON.stringify(guestEmail) + "\nDate-time detected: " + rawTime + "\nCorrect date-time timezone: " + correctedTimezone +"\nTitle detected: " + eventTitle);
//setting the date-time for the start and the end of the event
var dateTimeStart = new Date(correctedTimezone);
var dateTimeEnd = new Date(correctedTimezone);
dateTimeEnd.setHours(dateTimeEnd.getHours() + 1);
dateTimeStart = dateTimeStart.toISOString();
dateTimeEnd = dateTimeEnd.toISOString();
Logger.log("ISO dateTimeStart: " + dateTimeStart);
Logger.log("ISO dateTimeEnd: " + dateTimeEnd);
var participants = [{"id": email}]; //array of objects. Each object is a particpant for the event
for(var i = 0; i < guestEmail.length; i++){
participants.push({"id": guestEmail[i]}); //filling the participant array
}
//preparing the body for the Calendar API free-busy request
var requestBody = {
"timeMin": dateTimeStart,
"timeMax": dateTimeEnd,
"items": participants
}
//Calendar freebusy request to check if the slot is available for all particiaptns
var response = Calendar.Freebusy.query(requestBody);
for(var i = 0; i < participants.length; i++){
var calendarId = participants[i].id;
if(response.calendars[calendarId]['busy'].length != 0){
Logger.log(calendarId + " is busy at this time");
return { text: calendarId + " is busy at this time" };
break;
}
}
//guest array of objects for each participant
var guestsArr = [{"email":email}];
for(var i = 0; i < guestEmail.length; i++){
guestsArr.push({"email": guestEmail[i]});
}
//preparing the event details for the Calendar API call
var event = {
"summary": eventTitle,
"end": {
"dateTime": dateTimeEnd
},
"start": {
"dateTime": dateTimeStart
},
"attendees": guestsArr
}
//preapring the event options for the Calendar API call
var eventOptions = {
"sendNotifications": true,
"sendUpdates": "all"
}
//Calendar API call
var calendarEventRequest = Calendar.Events.insert(event, "primary",eventOptions);
//logs the Calendar API response to the logs
Logger.log(JSON.stringify(calendarEventRequest));
return { text: "Done! Check you calendar." };
}
上面的代码从 Dialogflow API 获取参数 - 日期、时间、会议标题和参与者,并使用这些信息进行忙/闲呼叫,然后最终调用日历 API。它还使用电子表格数据库根据用户位置查找正确的用户时区。
如果有人已经完成了此类功能以获得可用的时间段,我们将不胜感激。
解决方案
如果每个人都有空,您可以检查每个一小时的时间段,如果他们都有空,然后在日历中发送邀请。
示例代码:
var dateTimeStart = new Date(correctedTimezone);
var dateTimeEnd = new Date(correctedTimezone);
do {
dateTimeEnd.setHours(dateTimeStart.getHours() + 1);
dateTimeStart = dateTimeStart.toISOString();
dateTimeEnd = dateTimeEnd.toISOString();
Logger.log("ISO dateTimeStart: " + dateTimeStart);
Logger.log("ISO dateTimeEnd: " + dateTimeEnd);
var participants = [{"id": email}]; //array of objects. Each object is a particpant for the event
for(var i = 0; i < guestEmail.length; i++){
participants.push({"id": guestEmail[i]}); //filling the participant array
}
//preparing the body for the Calendar API free-busy request
var requestBody = {
"timeMin": dateTimeStart,
"timeMax": dateTimeEnd,
"items": participants
}
//Calendar freebusy request to check if the slot is available for all particiaptns
var response = Calendar.Freebusy.query(requestBody);
for(var i = 0; i < participants.length; i++){
var calendarId = participants[i].id;
if(response.calendars[calendarId]['busy'].length != 0){
dateTimeStart.setHours(dateTimeStart.getHours() + 1);
Logger.log(calendarId + " is busy at this time");
//return { text: calendarId + " is busy at this time" };
break;
}
}
}
while (response.calendars[calendarId]['busy'].length != 0);
推荐阅读
- algorithm - 使用 EM 算法填充多个缺失数据
- python - django URL 翻倍
- c++ - 取消定义和重新定义 __cplusplus 宏
- apache-flink - Apache Flink:REST API 检索指标值
- python - Python 迁移:找不到记录器“root”的处理程序
- java - JSONObjectRequest 无法用数据填充对象
- javascript - 如何在表单完成之前禁用 Stripe 付款请求按钮?
- html - 如何在其父 div 中调整 SVG 文件的大小、居中对齐并使其响应?
- php - php如何获取xml标签
- mysql - 如何获取一天收入大于或等于前一天收入的人的详细信息