CODEWITHSUNDEEP
google-apps-scriptcalendargoogle-calendar-api
Laurene Hamilton
Laurene Hamilton

Reputation: 13

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

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!

Upvotes: 1

Views: 10455

Answers (1)

Marios
Marios

Reputation: 27410

Issues:

  1. 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.

  2. 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.

Upvotes: 3

Related Questions