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

Question

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?

I think that in your script, the arguments `a,b,c` of `createEvent(a, b, c)` are string, date object, and date object. [Ref](https://developers.google.com/apps-script/reference/calendar/calendar-app#createEvent(String,Date,Date)) From your showing error, it seems that all values are string. I think that the reason for your issue is due to this. So, if you want to directly retrieve date object from Spreadsheet, can you confirm Spreadsheet, again. Or, if you want to convert date string on Spreadsheet to date object using script, I think that it is required to know your actual value. — Tanaike, Apr 30 '22 at 00:32

score 1 · Accepted Answer · answered Apr 30 '22 at 01:16

1

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);
  }
}

answered Apr 30 '22 at 01:16

Cooper

59,616
6
23
54

At now, i have an issue with implantation of the project... i will wait a time and try again. – NEEMIAS Apr 30 '22 at 16:29
I don't understand you last comment – Cooper Apr 30 '22 at 16:35
https://developers.google.com/apps-script/guides/services/quotas – NEEMIAS Apr 30 '22 at 16:42
I think i need use Utilities.sleep()... how many milliseconds should i use? And where??? – NEEMIAS Apr 30 '22 at 16:45
Try 60 milliseconds. Somewhere in the loop – Cooper Apr 30 '22 at 16:54
Exception: You have been creating or deleting too many calendars or calendar events in a short time. Please try again later. – NEEMIAS May 02 '22 at 18:19
I have the same error with Utilities.sleep(60) – NEEMIAS May 02 '22 at 18:20
Can i create only new events that i add in the spreadsheet? At now, if i run two times the script, a duplicate event is generated. I think i will need a conditional to select only new records in the spreadsheet. Please, help me! – NEEMIAS May 04 '22 at 00:26

score 0 · Answer 2 · answered Apr 30 '22 at 14:57

The method signature for calendar#createEvent is

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());

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

2 Answers2