I've created a google form, which goes to a google sheet from which calendar entries are automatically created. However, I'm having trouble figuring out how to invite a guest from my sheet. I've thoroughly googled it and found some similar solutions here and here, but their code is quite different from mine so I can't figure out how apply it to my code.
This is my latest attempt:
//insert your google calendar ID
var calendarId = "CALENDARID";
//index (starting from 1) of each column in the sheet
var titleIndex = 4;
var descriptionIndex = 7;
var startDateIndex = 5;
var endDateIndex = 6;
var googleCalendarIndex = 10;
var locationIndex = 8;
var mainGuestIndex = 2;
/*
find the row where the Google Calendar Event ID is blank or null
The data of this row will be used to create a new calendar event
*/
function findRow( sheet )
{
var sheet = SpreadsheetApp.getActiveSheet();
var dataRange = sheet.getDataRange();
var values = dataRange.getValues();
for ( var loopCounter = 0;
loopCounter < values.length;
loopCounter++ )
{
if ( values[ loopCounter ][ googleCalendarIndex - 1 ] == "" ||
values[ loopCounter ][ googleCalendarIndex - 1 ] == null )
newEvent( i + 1 );
}
};
/*
get the data of the new row by calling getSheetData() and
create a new Calendar event by calling submitToGoogleCalendar()
*/
function newEvent( row )
{
var sheet = SpreadsheetApp.getActiveSheet();
var eventId = submitToGoogleCalendar(getSheetData(sheet,row),null)
if ( eventId != null )
sheet.getRange( row,
googleCalendarIndex,
1,
1 ).setValue( eventId );
};
/*
Store the data of a row in an Array
*/
function getSheetData(sheet,row)
{
var data = new Array();
data.title = sheet.getRange( row,
titleIndex,
1,
1 ).getValue();
data.description = sheet.getRange( row,
descriptionIndex,
1,
1 ).getValue();
data.startDate = sheet.getRange( row,
startDateIndex,
1,
1 ).getValue();
data.endDate = sheet.getRange( row,
endDateIndex,
1,
1 ).getValue();
data.location = sheet.getRange( row,
locationIndex,
1,
1 ).getValue();
data.mainGuest = sheet.getRange( row,
mainGuestIndex,
1,
1 ).getValue();
return data;
};
/*
if a cell is edited in the sheet, get all the data of the corresponding row and
create a new calendar event (after deleting the old event) by calling submitToGoogleCalendar()
*/
function dataChanged( event )
{
var sheet = SpreadsheetApp.getActiveSheet();
var row = event.range.getRow();
var eventId = sheet.getRange( row,
googleCalendarIndex,
1,
1 ).getValue();
var eventId = submitToGoogleCalendar( getSheetData( sheet,
row),
eventId );
if ( eventId != null )
sheet.getRange( row,
googleCalendarIndex,
1,
1 ).setValue( eventId );
};
/*
This function creates an event in the Google Calendar and returns the calendar event ID
which is stored in the last column of the sheet
*/
function submitToGoogleCalendar( sheetData,
eventId )
{
// some simple validations ;-)
if ( sheetData.title == "" ||
sheetData.startDate == "" ||
sheetData.startDate == null )
return null;
var cal = CalendarApp.getCalendarById( calendarId );
var start = new Date( sheetData.startDate );
var end = new Date( sheetData.endDate );
// some simple date validations
if ( start > end )
return null;
var event = null;
//if eventId is null (when called by newEvent()) create a new calendar event
if ( eventId == null )
{
event = cal.createEvent( sheetData.title,
start,
end,
{
description : sheetData.description,
location : sheetData.location,
} ).addGuest( sheetData.mainGuest );
return event.getId();
}
/*
else if the eventid is not null (when called by dataChanged()), delete the calendar event
and create a new event with the modified data by calling this function again
*/
else
{
event = cal.getEventSeriesById( eventId );
event.deleteEventSeries();
return submitToGoogleCalendar( sheetData,
null );
}
return event.getId();
};
However, when I run this it makes an event, but still no guest shows up in the calendar event. From here I know I need to use .addGuest(email), and it seems like it should go in my submitToGoogleCalendar function as that's where all the other info in the event is, but no luck so far. I've also tried adding event.addGuest(sheetData.mainGuest); instead of tacking it on to the event of the event variable, but that didn't work either.
How can I add this without totally rewriting my script? Am I on the right track here?
