javascript - 从日历问题中检索 guest 列表，Google Apps 脚本 (GAS)

Question

从原始内容编辑我正在尝试找出如何以表格格式检索和报告为每位客人重复的日历事件的详细信息，以便可以将信息转换为简单的打印-每次事件的参加者登记册。

我收到有关范围高度的错误 - 我的代码确实为每个 guest 创建了每个事件的日志(因此我可以看到它正确编译信息)，但它不会将其输出到单独的行。错误是“范围高度不正确，为 1，但应为 7”。

我的代码如下。我评论它是为了清楚地说明每一部分应该做什么:

function onOpen() 
{
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [{
    name : "Get Calendar Info",
    functionName : "getCal" 

  }];
  sheet.addMenu("Calendar Actions", entries);

}


function getCal()
{

// Export Google Calendar Events to a Google Spreadsheet, one row for     each guest
//
// This code retrieves events and guests 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 also calculates event duration (via creating formulas in the spreadsheet).

// Reference Websites:
// https://developers.google.com/apps-script/reference/calendar/calendar
// https://developers.google.com/apps-script/reference/calendar/calendar-event

var mycal = "myemailaddress";  //this is the email address of whichever Google account is to use this
var cal = CalendarApp.getCalendarById(mycal);
var guestEmail = "";
var guestStatus = "";
var guestName = "";

//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";

//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 22, 2015 23:59:59 CST"));
//var events = cal.getEvents(new Date(startDate), new Date(endDate));
var sheet = SpreadsheetApp.getActiveSheet();

// Uncomment this next line if you want to always clear the spreadsheet content before running - Note people could have added extra columns on the data though that would be lost
sheet.clearContents();  

// Create a header record on the current spreadsheet in cells A1 onwards - 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 calulated ROW 2 (i+2)

for (var i=0;i<events.length;i++) 
{
var row=i+2;
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

    Logger.log("Event "+i+": "+events[i].getId());

    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++)
    {
      guestEmail = guestList[d].getEmail();
      guestStatus = guestList[d].getGuestStatus();
      guestName = guestList[d].getName();
      Logger.log("Guest "+d+": "+guestList[d].getEmail()+", Status: "+guestList[d].getGuestStatus());

      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 range = sheet.getRange(row,1,guestList.length,18);
    range.setValues(details);

// Writes the formula out to calculate number of hours, for the specific row, in column 7 to match the position of the field myformula_placeholder from above.
var cell = sheet.getRange(row,7);
cell.setFormula('=(HOUR(F' +row+ ')+(MINUTE(F' +row+ ')/60))-(HOUR(E' +row+ ')+(MINUTE(E' +row+ ')/60))');
cell.setNumberFormat('.00');

}
}
}

Answer 1

在一位出色的学者的帮助下，我完成了我的代码，并且它完全按照要求运行。下面发布了最终代码以帮助其他人。

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 = "YOUREMAILORCALENDARADDRESS";
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("September 29, 2015 00:00:00 CST"), new Date("September 29, 2015 23:59:59 CST"));

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++)
    {
        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

}
}