Send Invites on Menu Trigger

Question

I want to be able to review Form submissions before sending out Calendar invites to add to students Google Calendar.

The sheet I am organizing the invites in (pulling in eventID and one row per session) is separate from the Google Form Submission tab.

ColA: Name ColB: Email ColC: Session Title ColD: eventID (populated through VLOOKUP function

Here's the code I have so far:

function addAttendeeToEvent() {
  const ss = SpreadsheetApp.getActive().getSheetByName('Invites');
  const [h, ...vs] = ss.getDataRange().getValues();//assume one header row  
  let cal = CalendarApp.getCalendarById('bd8tmudge9f6ti9vgtm7kkagss@group.calendar.google.com');
  if (cal) {
    vs.forEach((r, i) => {
      let ev = cal.getEventById(r[4]);
      if (ev) {
        ev.addGuest(r[1]);
      }
    })
  }
  let attendeeEmail = [2]
  let calendarId = '2980k01ekkpeeb4e1qsb40hhgg@group.calendar.google.com'
  let eventId = [4]
 
}

TIA!

Answer 1

From your question and your reply, I understood your current situation is as follows.

• Columns "B" and "D" are the email addresses you want to add to the event and the event ID you want to use.

When I saw your script, the value of vs is the data except for 1st row. In this case, when let ev = cal.getEventById(r[4]); is run, the value is retrieved from the column "E" instead of column "D". By this, if (ev) {} is false. So, ev.addGuest(r[1]) is not run. I thought that this might be the reason for your issue of When I ran the script, there were not errors, but it did not share the event..

If my understanding is correct, how about the following modification?

From:

let ev = cal.getEventById(r[4]);

To:

let ev = cal.getEventById(r[3]);

• By this modification, the event ID retrieved from column "D" is used.