Creating calendar event in sheets using Google App Script and want to add attendees

Question

I'm trying to create calendar events in sheets using Google App Script (I'm very new to this). The sheet contains details of the event (date, time, event title, and guest list) as well as the calendar ID (this is a training calendar). I want to make it simple for the end-user to fill in the information on the sheet, click 'schedule now' and the script run and send the events out to all email addresses mentioned in the guest list.

Here is an example of the sheet:

Here is a copy of the code (I found this on the Google Developer website and tried to adapt it to add guests but haven't been able to get it working and really not sure where to go with this. Ideally, I'd like the guest list to come from the sheet and not be written into the code as an option.

function scheduleTraining() {

  var spreadsheet = SpreadsheetApp.getActiveSheet();
  var calendarId = spreadsheet.getRange("C3").getValue();
  var eventCal = CalendarApp.getCalendarById(calendarId);
  
  Logger.log(eventCal)
  
  var signups = spreadsheet.getRange("A5:C20").getValues();
 
  for (x=0; x<signups.length; x++) {
    
    var shift = signups[x];
    
    var startTime = shift[0]
    var endTime = shift[1]
    var title = shift[2]
    
    eventCal.createEvent(title, startTime, endTime, {
      location: 'remote',
      description: 'snacks provided',
    })
  }
  
}  

function onOpen(e) {
  SpreadsheetApp.getUi()
      .createMenu('Schedule Training')
      .addItem('Schedule Now', 'scheduleTraining')
      .addToUi();
}

If anyone can help with this or has any ideas on how to do this better, I'm all ears!

Thanks!

I can spot the first issue. The dates in your spreadsheet file are strings/texts. You can verify that if you do =isDate(A7). If the latter returns false then you don't have a valid date object. If you see the official documentation you need to pass date objects and not date texts. Another remark I have is that your range is "A5:C20" but you should start from A7. Row 5 and 6 don't contain the information required according to your screenshot. Hope that helped. — Marios
– Marios, Commented Oct 14, 2020 at 11:49
Hi Jose, Thanks. I've checked the dates and they're objects. I've modified the range and the script is working to create events using the date, time, and titles. Now I just need to figure out how I can include inviting the guests when it's run. — Laurene Hamilton
– Laurene Hamilton, Commented Oct 14, 2020 at 13:23
I posted my answer, but please edit your question to include your second follow-up question. — Marios
– Marios, Commented Oct 14, 2020 at 14:02

Marios · Accepted Answer · 2020-10-14 14:00:54Z

3

Issues:

The dates in your spreadsheet file are strings/texts. You can verify that if you do =isDate(A7). If the latter returns FALSE then you don't have a valid date object. If you see the official documentation you need to pass date objects and not date texts.
Another remark I have is that your range is "A5:C20" but you should start from A7. Row 5 and 6 don't contain the information required, according to your screenshot.

Question based on your comment:

how I can include inviting the guests when it's run

Again according to the documentation, you can add guests using the advanced parameters options and in particular guests:

  eventCal.createEvent(title, startTime, endTime, {
      location: 'remote',
      description: 'snacks provided',
      guests: '[email protected],[email protected]'
    })

guests is a type of string that contains a comma-separated list of email addresses that should be added as guests.

answered Oct 14, 2020 at 14:00

Marios

27.5k9 gold badges40 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Laurene Hamilton Over a year ago

Thanks Marios! Sorry, this is my first time on stack overflow. I'll add and see if it runs.

Andres Molina Perez-Tome Over a year ago

is it possible so i can use instead of string with all the emails, a column form my site with shift[3]?

Collectives™ on Stack Overflow

Creating calendar event in sheets using Google App Script and want to add attendees

1 Answer 1

Issues:

Question based on your comment:

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Issues:

Question based on your comment:

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related