Retrieve Guest List from Calendar issue, Google Ap

Question

edited from original I'm trying to figure out how to retrieve and report out details of calendar events repeated for each guest in a table format so that the information can be turned into a simple printed-out register of attendees for each event.

I'm getting an error about range height - my code does create a Log of each event with each guest (so I can see it compiling the information correctly) but it doesn't output that to individual rows. The error is "Incorrect range height, was 1 but should be 7".

My code is below. I commented it to be clear on what each bit should do:

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

With the help of a wonderful academic at work I have completed my code and it operates exactly as required. Final code posted below to help others.

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

}
}