0

We have a script that we connect to the Google Sheets responses table of a form that allows us to send an invitation to a calendar entry.

It seems to be really hit and miss, some people are able to get it working first time and I'm now trying to run it but keep getting the error message in the execution log:

TypeError: Cannot read property 'push' of undefined
     at addGuestAndSendEmail(Code:47:13)
     at addGuest(Code:39:3)

This is the code (with placeholders - which I've double and triple checked) and I've also made sure to turn on access and grant permission for the Calendar API service. Any help would be really appreciated - I've just hit a total block on where to go!

// Add a trigger - add guest on form submit
function Initialize() {

  var triggers = ScriptApp.getProjectTriggers();

  for(var i in triggers) {
    ScriptApp.deleteTrigger(triggers[i]);
  }

  ScriptApp.newTrigger("addGuest")
  .forSpreadsheet(SpreadsheetApp.getActiveSpreadsheet())
  .onFormSubmit()
  .create();

}


function addGuest(e) {
  // Find your calendar ID in "Settings and sharing" > "Integrate Calendar". Mine was my email address.
  var calendarId = "EMAIL ADDRESS GOES HERE";
  
  var email = e.namedValues['Email'].toString();
  
  // This will only work for standalone events, or the first in an event series (recurring event).
  // To find eventId: https://stackoverflow.com/a/46428456
  var eventId = "EID GOES HERE";

  addGuestAndSendEmail(calendarId, eventId, email);
}

// Note: requires advanced API https://stackoverflow.com/a/55509409
// If an Event Series, this will only work for the the first event
function addGuestAndSendEmail(calendarId, eventId, newGuest) {
  var event = Calendar.Events.get(calendarId, eventId);
  var attendees = event.attendees;
  attendees.push({email: newGuest});

  var resource = { attendees: attendees };
  var args = { sendUpdates: "all" };

  Calendar.Events.patch(resource, calendarId, eventId, args);
}
marcwalsh
  • 23
  • 4
  • This `Calendar.Events.get(calendarId, eventId);` returns JSON string. You would need to parse it before attendees behaves like an array. – Cooper May 18 '21 at 15:23
  • This is the JSON string that it returns https://developers.google.com/calendar/v3/reference/events#resource – Cooper May 18 '21 at 15:25
  • I tried to replicate the issue. Issue occurred when the current event has no attendees yet. event.attendees is null. Could you check if that is also the trigger of your issue? If yes then you might consider adding this `if(attendees){ attendees.push({email: newGuest}); }else{ attendees = [{email: newGuest}]; }` – Ron M May 18 '21 at 17:55
  • @RonM - that solved it - thank you so much! – marcwalsh May 20 '21 at 11:55

1 Answers1

0

Assuming that you have already enabled calendar API in GCP console and use project number from GCP in AppScript settings, here's the code of working function:

// If it's your primary google calendar, use your email as ID
const calendarId = 'john.smith@gmail.com'
// How to get event ID:
// 1. Open event editor in Calendar Web UI
// 2. Base64 decode part or URL after "...eventedit/"
// 3. Use first string from the output as the eventId below
const eventId = 'xxxxxxxxxxxxx'
// Email column number in the form responses Google Sheet, 0-based
const emailColumnId = 2

function addEventGuests() {
  var sheet = SpreadsheetApp.getActiveSheet()
  var email = sheet.getDataRange().getCell(sheet.getLastRow(), emailColumnId).getValue();

  var event = Calendar.Events.get(calendarId, eventId);
  if(event.attendees) {
    event.attendees.push({
      email: email
    });
  } else {
    event.attendees = new Array({email: email});
  }
  event = Calendar.Events.patch(event, calendarId, eventId, {
    sendNotifications: true
  });
}
Slava Medvediev
  • 1,431
  • 19
  • 35