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('[email protected]');
  let coachCalendar = CalendarApp.getCalendarById('2c95526055802636cb6c0a10534c9b4117e506ebda17c031d44ffafbabfba455@group.calendar.google');
  let blueCalendar = CalendarApp.getCalendarById('c_b48cc9afd2e6d4ca24f29199dd1a0a9d056725bc84db9b35542ef00b13ba2851@group.calendar.google');

//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)
    }
  }
  }

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('[email protected]');
  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)
    }
  }
  }

• google-apps-script
• google-calendar-api

• Here is my sheet, look at the 'Working' sheet to find the guest email, it's in column B. --- However, you didn't provide the sheet in your question. Please provide it as a plain text table in the question. Click here to create a table easily, which is easy to copy/paste. – leylou Commented Feb 5 at 19:04
• When you say add guest to event, do you mean adding a guest to an existing calendar event? – leylou Commented Feb 5 at 20:44
• I really want this to be all one step. When I see the form has been submitted, I run the script to create the event and it also adds the person who submitted to the event. So it shows up on their calendar. – vlkirkpatrick Commented Feb 6 at 17:18
• I believe that would be considered a new question, as your primary concern is How to add guests to the calendar event without sending an email or invite. I recommend posting it as a new question for your additional inquiry. Please only ask one question per post. Any followup questions must be posted as new questions instead. – leylou Commented Feb 6 at 18:19
• I have posted an answer to your original question. If my response has resolved your issue, please click the accept button on the left (check icon) . By doing so, other people in the community, who may have the same concern as you, will know that theirs can be resolved. see how to accept the answer. – leylou Commented Feb 6 at 18:19

1 Answer 1

Use Events: patch

You may try this script I added to your code that should work with what you'd like to do:

  const calendarMap = {
    "I need a driver.": busDriverCalendar,
    "I have already arranged a coach to drive.": coachCalendar,
    "Blue bus (505)": blueCalendar
  };

  for (let i = 1; i < tripData.length; i++) {
    const eventId = tripData[i][30];
    const condition = tripData[i][15];
    if (eventId && calendarMap[condition]) {
      const calendar = calendarMap[condition]
      Calendar.Events.patch({
        "attendees": [
          {
            "email": tripData[i][1]
          }
        ]
      }, calendar.getId(), [eventId].map(x => x.replace("@google.com", "")), { "sendNotification": "false", "sendUpdates": "none" });
      continue;
    }

I changed for (let i = 0; i < tripData.length; i++) to for (let i = 1; i < tripData.length; i++), replacing 0 with 1 to exclude the header in the "Working" sheet because I am getting an error since the header is not included in the patch. The Calendar.Events.patch will get the guest's email from column B in the "Working" sheet tab of the spreadsheet. It will add the guest to the calendar event without sending an email notification or invitation.

This script checks whether [i][30] (the Calendar Event ID found in column AE) contains a value. If it does, that means an event exists in the calendar. Next, it verifies whether column P contains the text I need a driver. If this condition is met, the script updates (patches) the busDriverCalendar by adding a guest. The guest's email is taken from column B. This update is made without sending an email notification to the added guest. This same logic applies to I have already arranged for a coach to drive and Blue bus (505), which will patch which calendar event and add the guest (taken from column B) to either the coachCalendar or blueCalendar event.

Note: If the Calendar event ID found in column AE is blank or empty, run the script to add the calendar event ID in column AE. After running the script, the calendar event will be created for the dates in columns E and F. Run the script again to add the guest from column B to the created calendar event, depending on Column P (driverStatus). This follows the same logic explained above.

Here is the full code:

function createCalendarEvent() {
  let tripData = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Working').getDataRange().getValues();
  let busDriverCalendar = CalendarApp.getCalendarById('[email protected]');
  let coachCalendar = CalendarApp.getCalendarById('2c95526055802636cb6c0a10534c9b4117e506ebda17c031d44ffafbabfba455@group.calendar.google.com');
  let blueCalendar = CalendarApp.getCalendarById('c_b48cc9afd2e6d4ca24f29199dd1a0a9d056725bc84db9b35542ef00b13ba2851@group.calendar.google.com');

  const calendarMap = {
    "I need a driver.": busDriverCalendar,
    "I have already arranged a coach to drive.": coachCalendar,
    "Blue bus (505)": blueCalendar
  };

  for (let i = 1; i < tripData.length; i++) {
    const eventId = tripData[i][30];
    const condition = tripData[i][15];
    if (eventId && calendarMap[condition]) {
      const calendar = calendarMap[condition]
      Calendar.Events.patch({
        "attendees": [
          {
            "email": tripData[i][1]
          }
        ]
      }, calendar.getId(), [eventId].map(x => x.replace("@google.com", "")), { "sendNotification": "false", "sendUpdates": "none" });
      continue;
    }

    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] });
      tripData[i][30] = newEvent.getId();

      const oncalendarColumnData = tripData.map(row => [row[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] });

      tripData[i][30] = newEvent.getId();

      const oncalendarColumnData = tripData.map(row => [row[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] });

      tripData[i][30] = newEvent.getId();

      const oncalendarColumnData = tripData.map(row => [row[30]])
      SpreadsheetApp.getActiveSpreadsheet()
        .getSheetByName('Working')
        .getRange(1, 31, oncalendarColumnData.length, 1)
        .setValues(oncalendarColumnData)
    }
  }
}

Reference:

• Events: patch