javascript - Google Appscript - 导出来宾列表成员中有特定字符串的日历条目
问题描述
我是 GAS 和 Javascript 的新手。我正在使用下面的非常有用的代码,我在这里的另一篇文章中找到了将日历条目导出到工作表。
我正在尝试将其修改为仅导出条目,其中它的日历条目在来宾列表上带有地址/名称,其中包含字符串“Team”。
我尝试输入 if(guestList.indexOf("Team")>-1){... } 但它不起作用并返回 0 个条目。有人知道该怎么做吗?
function onOpen()
{
var sheet = SpreadsheetApp.getActiveSpreadsheet();
var entries = [{
name : "Get calendar info",
functionName : "getCal"
}];
sheet.addMenu("Calendar Actions", entries);
}
// Export Google Calendar Events to a Google Spreadsheet, one row for each guest
//
// This code retrieves events between 2 dates for the specified calendar including all guests included in the event.
// It logs the results in the current spreadsheet starting at cell A2 listing the events,
// dates/times, etc and also calculates event duration (via creating formulas in the spreadsheet) and formats the values.
//
// Reference Websites:
// https://developers.google.com/apps-script/reference/calendar/calendar
// https://developers.google.com/apps-script/reference/calendar/calendar-event
function getCal()
{
// Export Google Calendar Events to a Google Spreadsheet, one row for each guest
//
// This code retrieves events between 2 dates for the specified calendar.
// It logs the results in the current spreadsheet starting at cell A2 listing the events,
// dates/times, etc and even calculates event duration (via creating formulas in the spreadsheet) and formats the values.
//
// I do re-write the spreadsheet header in Row 1 with every run, as I found it faster to delete then entire sheet content,
// change my parameters, and re-run my exports versus trying to save the header row manually...so be sure if you change
// any code, you keep the header in agreement for readability!
//
// 1. Please modify the value for mycal to be YOUR calendar email address or one visible on your MY Calendars section of your Google Calendar
// 2. Please modify the values for events to be the date/time range you want and any search parameters to find or omit calendar entires
// Note: Events can be easily filtered out/deleted once exported from the calendar
//
// Reference Websites:
// https://developers.google.com/apps-script/reference/calendar/calendar
// https://developers.google.com/apps-script/reference/calendar/calendar-event
var mycal = "email@domain.com";
var cal = CalendarApp.getCalendarById(mycal);
//var startDate = Browser.inputBox("Start Date, in format MM / DD / YYYY");
//var endDate = Browser.inputBox("End Date, in format MM / DD / YYYY");
//var startDate = "September 25, 2015 00:00:00 CST";
//var endDate = "September 26, 2015 23:59:59 CST";
// Optional variations on getEvents
// var events = cal.getEvents(new Date("January 3, 2014 00:00:00 CST"), new Date("January 14, 2014 23:59:59 CST"));
// var events = cal.getEvents(new Date("January 3, 2014 00:00:00 CST"), new Date("January 14, 2014 23:59:59 CST"), {search: 'word1'});
//
// Explanation of how the search section works (as it is NOT quite like most things Google) as part of the getEvents function:
// {search: 'word1'} Search for events with word1
// {search: '-word1'} Search for events without word1
// {search: 'word1 word2'} Search for events with word2 ONLY
// {search: 'word1-word2'} Search for events with ????
// {search: 'word1 -word2'} Search for events without word2
// {search: 'word1+word2'} Search for events with word1 AND word2
// {search: 'word1+-word2'} Search for events with word1 AND without word2
//
//var events = cal.getEvents(new Date("September 25, 2015 00:00:00 CST"), new Date("October 01, 2015 23:59:59 CST"), {search: '-project123'});
//var events = cal.getEvents(new Date("September 25, 2015 00:00:00 CST"), new Date("October 02, 2015 23:59:59 CST"));
//var events = cal.getEvents(new Date(startDate), new Date(endDate));
var events = cal.getEvents(new Date("August 1, 2018 00:00:00 CST"), new Date("August 30, 2018 00:00:00 CS"));
var sheet = SpreadsheetApp.getActiveSheet();
// Clear the spreadsheet content before running
sheet.clearContents();
// Create a header record on the current spreadsheet in cells A1:N1 - Match the number of entries in the "header=" to the last parameter
// of the getRange entry below
var header = [["Calendar Address", "Event Title", "Event Description", "Event Location", "Event Start", "Event End", "Calculated Duration", "Visibility", "Date Created", "Last Updated", "MyStatus", "Created By", "All Day Event", "Recurring Event", "ID","Email","Status","Name"]]
var range = sheet.getRange(1,1,1,18);
range.setValues(header);
// Loop through all calendar events found and write them out starting on row 2 (row = 2) to allow for the header on row 1
var row = 2;
for (var i=0;i<events.length;i++)
{
var myformula_placeholder = '';
// Matching the "header=" entry above, this is the detailed row entry "details=", and must match the number of entries of the GetRange entry below
var guestList=events[i].getGuestList();
//GET THE EMAIL AND STATUS OF EACH GUEST FOR EACH EVENT
for(var d=0; guestList!=null && d<guestList.length; d++)
{
if (guestList.indexOf("Team")>-1) {
var details=[[mycal,events[i].getTitle(), events[i].getDescription(), events[i].getLocation(), events[i].getStartTime(), events[i].getEndTime(), myformula_placeholder, ('' + events[i].getVisibility()), events[i].getDateCreated(), events[i].getLastUpdated(), events[i].getMyStatus(), events[i].getCreators(), events[i].isAllDayEvent(), events[i].isRecurringEvent(), events[i].getId(), guestList[d].getEmail(), guestList[d].getGuestStatus(), guestList[d].getName()]];
Logger.log(details);
var range2 = sheet.getRange(row+d,1,1,18);
range2.setValues(details);
var cell=sheet.getRange(row+d,7); // go to column 7 (the placeholder) of the output data
cell.setFormula('=(HOUR(F' +row+ ')+(MINUTE(F' +row+ ')/60))-(HOUR(E' +row+ ')+(MINUTE(E' +row+ ')/60))'); // calculate the number of hours of the session
cell.setNumberFormat('.00');
}
}
row=row+d; // increment row to start the next output after the previous output
}
}
解决方案
推荐阅读
- powerbi - 如何在 Power BI 中插入日期
- r - 从 R 表中的 2 列创建一个新的百分比变化列
- node.js - 无服务器 lamdas 使用缓存的处理程序代码,但观察者检测到更改并重新编译。怎么修?
- javascript - 包含方法与严格相等
- visual-studio-2019 - Visual Studio 2019 跳过属性和运算符不起作用?
- python - 我导入的帮助文件是否也需要导入语句?
- java - 是否有任何替代 LocalBroadcastManager 的方法,用于从 Worker 触发 Activity 功能?
- react-native - react-navigation 中的自定义抽屉导航器
- azure - 新创建的 Azure 应用服务无法连接到现有的 Azure SQL 数据库
- javascript - 如何将变量键入为对象的任何潜在属性?