I need to add to an existing script... It needs to add the created event, including all the event details, to the guests calendar without sending an email or invite.
Here is my sheet, look at the 'Working' sheet to find the guest email, it's in column B.
My script:
//this creates a calendar event for each row where onCalendar is empty.
function createCalendarEvent() {
//Get the data from the 'Working' sheet
let tripData = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Working').getDataRange().getValues();
let busDriverCalendar = CalendarApp.getCalendarById('vlkexampletest@gmail.com');
let coachCalendar = CalendarApp.getCalendarById('2c95526055802636cb6c0a10534c9b4117e506ebda17c031d44ffafbabfba455@group.calendar.google.com');
let blueCalendar = CalendarApp.getCalendarById('c_b48cc9afd2e6d4ca24f29199dd1a0a9d056725bc84db9b35542ef00b13ba2851@group.calendar.google.com');
//iterate over the trip data starting at index 1 to skip the header row.
for(let i=0;i<tripData.length;i++) {
//If there's something in the oncalendar row skip it
if(tripData[i][30]) {
continue;}
//create the event
// skip rows that do not have all the data needed to create the event
if(!(tripData[i][28] && tripData[i][34] && tripData[i][35])){
continue
}
if(tripData[i][15] == "I need a driver."){
let newEvent = busDriverCalendar.createEvent(tripData[i][28], tripData[i][34], tripData[i][35], { description: tripData[i][29], location: tripData[i][32]});
//Add the ID of the event to the 'oncalendar' row.
tripData[i][30] = newEvent.getId();
//Set the values in the spreadsheet.
//Get just the oncalendar data
const oncalendarColumnData = tripData.map(row => [row[30]])
//Only write data to oncalendar column (column 30)
SpreadsheetApp.getActiveSpreadsheet()
.getSheetByName('Working')
.getRange(1, 31, oncalendarColumnData.length, 1)
.setValues(oncalendarColumnData)
}
if(tripData[i][15] == "I have already arranged a coach to drive."){
let newEvent = coachCalendar.createEvent(tripData[i][28], tripData[i][34], tripData[i][35], { description: tripData[i][29], location: tripData[i][32]});
//Add the ID of the event to the 'oncalendar' row.
tripData[i][30] = newEvent.getId();
//Set the values in the spreadsheet.
//Get just the oncalendar data
const oncalendarColumnData = tripData.map(row => [row[30]])
//Only write data to oncalendar column (column 30)
SpreadsheetApp.getActiveSpreadsheet()
.getSheetByName('Working')
.getRange(1, 31, oncalendarColumnData.length, 1)
.setValues(oncalendarColumnData)
}
if(tripData[i][15] == "Blue bus (505)"){
let newEvent = blueCalendar.createEvent(tripData[i][28], tripData[i][34], tripData[i][35], { description: tripData[i][29], location: tripData[i][32]});
//Add the ID of the event to the 'oncalendar' row.
tripData[i][30] = newEvent.getId();
//Set the values in the spreadsheet.
//Get just the oncalendar data
const oncalendarColumnData = tripData.map(row => [row[30]])
//Only write data to oncalendar column (column 30)
SpreadsheetApp.getActiveSpreadsheet()
.getSheetByName('Working')
.getRange(1, 31, oncalendarColumnData.length, 1)
.setValues(oncalendarColumnData)
}
}
}
So I fiddled around with the code provided by @Leylou and I could not get it to work. I decided to go back to my original code and did a BUNCH of reading!
In my original script this line:
let newEvent = busDriverCalendar.createEvent(tripData[i][28], tripData[i][34], tripData[i][35], { description: tripData[i][29], location: tripData[i][32] });
needed to be changed like this:
let newEvent = busDriverCalendar.createEvent(tripData[i][28], tripData[i][34], tripData[i][35], {description: tripData[i][29], guests: tripData[i][1], location: tripData[i][32]});
If you read through my original script you will see that three different calendars are used. In the same line for each calendar I changed it to include guests: tripData[i][1],
It works perfectly, adding the person who submitted the form without sending notification or updates.
I want to thank @Leylou for the work you did on the answer you provided. Ultimately it was not useful to me but it might be useful to someone else. That answer did work in my test account, I just could not get it to work in the main account all this google app script work is for.