CODEWITHSUNDEEP
google-apps-scriptgoogle-calendar-api
Marco
Marco

Reputation: 1

Cannot find method createEvent(string,string,string)

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

Upvotes: 0

Views: 958

Answers (2)

Wicket
Wicket

Reputation: 38140

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

Upvotes: 3

Cooper
Cooper

Reputation: 64042

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

Upvotes: 0

Related Questions