Cannot find method createEvent(string,string,string)

Question

I have a spreadsheet with +250 lines of prospects. For each prospect I have a row in wish I inform the date we are supposed to meet.

I have tried to create a script so that the dates I input on the cell are automatically transformed into Google Calendar Events.

Still, each time I run the script I get the "Cannot find method (string, string, string)" error message.

I don't know if the fact that I have several blank cells is the problem.

There goes the code I tried to run:

function scheduleshifts() {
var Spreadsheet = SpreadsheetApp.getActiveSheet();
var CalendarId = Spreadsheet.getRange("Y2").getValue();
var eventcal = CalendarApp.getCalendarById(CalendarId);

var signups = Spreadsheet.getRange("C3:V250").getValues();

for (x=0; x<signups.length; x++){
var shift = signups [x];
var startTime = shift[18];
var endTime = shift[19];
var title = shift[0];
eventcal.createEvent(title, new Date(startTime), new Date(endTime));
}
}

Rubén · Answer 1 · 2019-08-20T22:38:29.227

From the OP's question:

I don't know if the fact that I have several blank cells is the problem.

Yes that is the problem because getValues returns an empty string as the value of a blank cell.

The solution is to include a condition to use a default date or to skip the row with empty cells for the starTime and endTime variables.

How about this alternative? Lets assume that the source data for startTime and endTime are Google Sheets date values or blanks (empty cells). If startTime or endTime are valid dates, then the calendar event is created, otherwise isn't.

function scheduleshifts() {
  var Spreadsheet = SpreadsheetApp.getActiveSheet();
  var CalendarId = Spreadsheet.getRange("Y2").getValue();
  var eventcal = CalendarApp.getCalendarById(CalendarId);

  var signups = Spreadsheet.getRange("C3:V250").getValues();

  for (x=0; x<signups.length; x++){
    var shift = signups [x];
    var startTime = shift[18];
    var endTime = shift[19];
    var title = shift[0];
    if(isValidDate(startTime) && isValidDate(endTime)){
      eventcal.createEvent(title, startTime, endTime);
      // Added the following line to prevent to exceed the limit of events created 
      // "in a short time"
      Utilities.sleep(1000); 
    }
  }
}
/* 
 * From the answer https://stackoverflow.com/a/1353711/1595451 to 
 * "Detecting an “invalid date” Date instance in JavaScript"
 */
function isValidDate(d) {
  return d instanceof Date && !isNaN(d);
}

@Rubén Could you please modify from `isValiadDate(startTime)` to `isValidDate(startTime)`? It's a typo. — Tanaike, Aug 20 '19 at 06:09

Cooper · Answer 2 · 2019-08-20T18:34:13.583

0

Try this:

function scheduleshifts() {
  var Spreadsheet = SpreadsheetApp.getActive();
  var CalendarId = Spreadsheet.getRange("Y2").getValue();
  var eventcal = CalendarApp.getCalendarById(CalendarId);

  var signups = Spreadsheet.getRange("C3:V250").getValues();

  for (x=0;x<signups.length;x++){
    var shift=signups[x];
    var startTime=shift[18];
    var endTime=shift[19];
    var title=shift[0];
    eventcal.createEvent(title, new Date(startTime), new Date(endTime));
    Utilities.sleep(18000);//9000 if G-Suite
  }
}

You just need to make sure that startTime and endTime strings are in a form that Date() constructor will accept. Reference

edited Aug 20 '19 at 18:34

answered Aug 20 '19 at 02:12

Cooper

59,616
6
23
54

Thanks everybody. Unfortunately now I get a message saying I tried to create too many calendar events in too short amount of time. Please Try Again Later :/ There's also a suggestion to use "Utilities.sleep(1000)". Is that actually relevant? How can it help? – Marco Aug 20 '19 at 12:32
function scheduleshifts() { var Spreadsheet = SpreadsheetApp.getActiveSheet(); var CalendarId = Spreadsheet.getRange("Y2").getValue(); var eventcal = CalendarApp.getCalendarById(CalendarId); var signups = Spreadsheet.getRange("C3:V250").getValues(); for (x=0; x – Marco Aug 20 '19 at 12:53
Please post it in your question – Cooper Aug 20 '19 at 13:47
If you have a regular Gmail account you can create an event every 18 seconds. 86400/5000. Which means that a script will timeout after around 16 events. My understanding suggests that Google calculates a frequency limit in determining quotas rather that counting for an entire day. This isn't something I know for sure. I just use this approach in avoiding quota limits in my own code and thus far it has served me well. – Cooper Aug 20 '19 at 18:29

Cannot find method createEvent(string,string,string)

2 Answers2