CODEWITHSUNDEEP
google-apps-scriptgoogle-api
Drew McAllister
Drew McAllister

Reputation: 3

Google Scripts - Add multiple guests to a calendar event from a single cell in a google sheet

I have a Google spreadsheet that we're using to deliver single calendar events to multiple accounts. Code below. I am getting an "Invalid argument" error for .addguest whenever there is more than one email address in the cell I'm trying to pull from (separated by commas).

The code below will pull correctly if only one account is in the cell, but not if multiple accounts are listed with commas in that same cell.

All ideas appreciated.

Thank you!

--Drew

 function onOpen() {
 var s = SpreadsheetApp.getActiveSpreadsheet();
  var menuEntries = [ {name: "Add Events to Google Calendar", functionName: "scheduleClass"}];
   s.addMenu("Google Calendar Functions", menuEntries);
 }

function scheduleClass(){
  var sheet = SpreadsheetApp.getActiveSheet();
  var calendar = CalendarApp.getCalendarById("[email protected]");

  var title = sheet.getRange('A2:A').getValues();
  var startTime = sheet.getRange('E2:E').getValues();
  var endTime = sheet.getRange('F2:F').getValues();
  var theDescription = sheet.getRange('G2:G').getValues();
  var theLocation = sheet.getRange('H2:H').getValues();
  var theGuests = sheet.getRange('K2:K').getValues();

  for(var i = 0; i < title.length+1; i++){
    if(title[i][0] != ""){
      calendar.createEvent(title[i][0], startTime[i][0], endTime[i][0]).addGuest(theGuests[i][0]).setLocation(theLocation[i][0]).setDescription(theDescription[i][0]);

    }
  }
}

function toString(value){
  var newString = "" + value;
  return newString;
}

Upvotes: 0

Views: 1657

Answers (1)

Tanaike
Tanaike

Reputation: 201358

  • You want to create the calendar events with the multiple guests.
    • In your situation, the value of column "K" is like [email protected],[email protected] which is separated by ,.
    • The values of columns "A", "E", "F", "G", "H" and "K" in the active sheet are title, start time, end time, description, location and guests, respectively.

If my understanding is correct, how about this answer? Please think of this as just one of several possible answers.

Modification points:

  • When the guests are separated by ,, you can retrieve each email using split(). And they can be added with addGuest(email).
  • In your script, an error occurs at the loop by title.length+1 of for(var i = 0; i < title.length+1; i++){.
  • When you retrieve the values from the data range of "A2:K#", the process cost can be reduced. This was mentioned by Cooper's comment and discussions.

When these points are reflected to your script, it becomes as follows.

Pattern 1:

In this pattern, the calendar service of CalendarApp is used. In this case, scheduleClass() was modified.

Modified script:

function scheduleClass(){
  var sheet = SpreadsheetApp.getActiveSheet();
  var calendar = CalendarApp.getCalendarById("[email protected]");
  var values = sheet.getRange(2, 1, sheet.getLastRow() - 1, 11).getValues();
  for(var i = 0; i < values.length; i++){
    var [title,,,,startTime,endTime,theDescription,theLocation,,,theGuests] = values[i];
    if(title != "") {
      var event = calendar.createEvent(title, startTime, endTime)
        .setLocation(theLocation)
        .setDescription(theDescription);
      theGuests.split(",").forEach(function(e) {event.addGuest(e.trim())});
    }
  }
}

Pattern 2:

In this pattern, Calendar API is used.

Modified script:

When you use this script, please enable Calendar API at Advanced Google services.

function scheduleClass(){
  var sheet = SpreadsheetApp.getActiveSheet();
  var calendarId = "[email protected]";
  var values = sheet.getRange(2, 1, sheet.getLastRow() - 1, 11).getValues();
  for(var i = 0; i < values.length; i++){
    var [title,,,,startTime,endTime,theDescription,theLocation,,,theGuests] = values[i];
    if(title != "") {
      var resource = {
        start: {dateTime: startTime.toISOString()},
        end: {dateTime: endTime.toISOString()},
        summary: title,
        description: theDescription,
        location: theLocation,
        attendees: theGuests.split(",").map(function(e) {return {email: e.trim()}})
      };
      Calendar.Events.insert(resource, calendarId);
    }
  }
}

Note:

  • Above both modified scripts are the same results.

References:

If I misunderstood your question and this was not the direction you want, I apologize.

Upvotes: 3

Related Questions