2

I would like to send email invites for calendar events when someone selects the checkbox for the relevant event in a Google form. The form has multiple options for events at one time, and I'd like to be able to send every event invite from the same form submission. The selections are stored in the same cell and separated by ", " which is why I'm using the toString and Split functions.

Here is what I have so far, apologies for inefficiency as I am quite new to this:

function onFormSubmit(e) {
var user = {name: e.namedValues['Name'][0], email: e.namedValues['Email Address'][0]};
var response = [];
var responseValues = SpreadsheetApp.openById('sheet_Id').getSheetByName('Form Responses').getDataRange().getValues();
var responseArray = []; // array for holding spreadsheet converted to String
for (var k = 0; k < responseValues.length; k++){
    responseArray.push(toString(responseValues[k]).split(", "));
  }
var eventValues = SpreadsheetApp.openById('sheet_Id').getSheetByName('Event Times').getDataRange().getValues();
// Search for event names within "Event Times" sheet. If the response includes an event name from "Event Times" sheet, log that event ID from the sheet
for(var i = 0; i < responseArray.length; i++){
    for(var j = 0; j < eventValues.length; j++){
        if(responseArray[i] == eventValues[j]) {
           response.push(eventValues[j+5]); //the event ID is 5 cells to the right of the event name in the "Event Times" sheet
        }
     }
  }
sendInvites_(user, response);
}


function sendInvites_(user, response) {
  var cal = CalendarApp.getCalendarById('calendar_Id');
  for (var i = 0; i < response.length; i++) {
      cal.getEventById(response[i]).addGuest(user.email);
  }
}

Thanks for the help.

Kyle Forgeron
  • 21
  • 1
  • Have you checked the quickstart documentation about [Managing Responses for Google Forms](https://developers.google.com/apps-script/quickstart/forms)? – Jessica Rodriguez Nov 29 '18 at 11:35
  • Yes, I used that code as a starting point, but it unnecessarily creates new calendar events when the events I am managing must be manually entered to include descriptions and hyperlinks. That's why I am only trying to search within my spreadsheet for the event name, return the event ID accordingly, and add the form user's email as a guest to that event. – Kyle Forgeron Nov 29 '18 at 14:13

0 Answers0