How to make google app script - calendar createEvent() to accept GSheet inputs from cells populated using array formula

Question

A few days ago I got help from stack overflow to modify my then app script code used to make calendar events from info on google sheet, so as to tick a checkbox whenever an entry from the corresponding row is made and subsequently make new events only when the checkbox is unticked.

function addEvent() {
  let webinarCalendar = CalendarApp.getCalendarById("[email protected]");
  let calendarSheet = SpreadsheetApp.getActiveSheet();
  let schedule = calendarSheet.getDataRange().getValues();
  schedule.splice(0, 1);

  const k = 16; // colIndex of checkbok col
  const created = schedule.map(e => [e[k]]);
  schedule.forEach(function(entry, i) {
    if (entry[k] == true) { return; }
    webinarCalendar.createEvent(entry[3], entry[14], entry[15], {description: entry[13]});
    created[i][0] = true;
  });
  calendarSheet.getRange(2, k + 1, created.length, 1).setValues(created);
}

This current code worked just fine until 2 days ago when I updated 3 of the 4 cells with the required inputs to work on an array formula so that they get populated automatically whenever a new row entry is made.

The error on the app script console says : Exception: The parameters (String,String,String,(class)) don't match the method signature for CalendarApp.Calendar.createEvent.

The parameters required for this createEvent() as per documentation are title(string), start time(string), finish time(string) and description(which is inside a javascript object I think and is also a string). To ensure that the datatype did not somehow get changed in the process of creating array formula, I cross checked the cells with an ISTEXT() and all of the inputs returned TRUE.

Second trial that I made was to change the splice() from (0,1) to (0,2) so that it ignores the first row which has the array formula written into the cells, which also did not fix the issue.

I would greatly appreciate if someone could show me what is causing this issue and help me fix it.

Answer 1

For people trying to run the scripts, one underlying cause might be the fact that you may be using US locale when the date have been formatted as UK. (e.g. Date that App Script is looking for is mm/dd/yyyy tt:tt:tt, but if you click in the formula cell it shows as dd/mm/yyyy tt:tt:tt)

What you would do is to go to Files > General > Locale > (Country of Choice) > Save settings.

You would then reload the page and try if the script is working now without that "Cannot find method createEvent(string,string,string)" error.

The line of code to use in your script would be:

SpreadsheetApp.getActive().setSpreadsheetLocale('en_UK');

You could include it in your onOpen trigger function.