how to automatically add a schedule from Google Sheets into Calendar?

user_18995606 0
Viewed 116

I have tried to implement this code to integrate google calendar with my spreadsheet, but i am fail until now. Follow the code...

function scheduleShifts() {
var spreadsheet = SpreadsheetApp.getActiveSheet();
    var calendarId = spreadsheet.getRange("Dados!CJ3").getValue();
    var eventCal = CalendarApp.getCalendarById(calendarId);
var signups = spreadsheet.getRange("Dados!CI5:CK3500").getValues();
for (x=0; x<signups.length;x++)
{
    var shift = signups[x];
    var startTime = shift[0];
    var endTime = shift[1];
    var volunteer= shift[2];
    eventCal.createEvent(volunteer,startTime,endTime);
}
}

Error message:

21:19:29 Erro Exception: The parameters (String,String,String) don't match the method signature for CalendarApp.Calendar.createEvent. scheduleShifts @ Agenda.gs:12

Based article:

https://cloud.google.com/blog/products/g-suite/g-suite-pro-tip-how-to-automatically-add-a-schedule-from-google-sheets-into-calendar

Can you help me?

google-calendar-api google-apps-script google-sheets-api javascript
2 Answers

Try this:

function scheduleShifts() {
  var spreadsheet = SpreadsheetApp.getActiveSheet();
  var calendarId = spreadsheet.getRange("Dados!CJ3").getValue();
  var eventCal = CalendarApp.getCalendarById(calendarId);
  var signups = spreadsheet.getRange("Dados!CI5:CK3500").getValues();
  for (x=0; x<signups.length;x++) {
    var shift = signups[x];
    var startTime = new Date(shift[0]);
    var endTime = new Date(shift[1]);
    var volunteer= shift[2];
    eventCal.createEvent(volunteer,startTime,endTime);
  }
}
user_72150910

The method signature for calendar#createEvent is

enter image description here

You are currently sending string parms (String,String,String). As shown in the error message

Exception: The parameters (String,String,String) don't match the method signature for CalendarApp.Calendar.createEvent. scheduleShifts

While the method requires that both the start date and end date are actually dates not strings.

var event = CalendarApp.getDefaultCalendar().createEvent('Apollo 11 Landing',
    new Date('July 20, 1969 20:00:00 UTC'),
    new Date('July 21, 1969 21:00:00 UTC'));
Logger.log('Event ID: ' + event.getId());
user_18418390
Related