I've the following columns in a spreadsheet:

The spreadsheet is linked with the following google apps script:

function createCalendarEvents() {
  var spreadsheetId = '1NQULPeaxoaAaNWU4ojapb4R50JEdl62Ip2e9yjUB_sw';
  var calendarId = "[email protected]";
  var sheetName = '2';

  var spreadsheet = SpreadsheetApp.openById(spreadsheetId);
  var sheet = spreadsheet.getSheetByName(sheetName);
  var calendar = CalendarApp.getCalendarById(calendarId);
  var data = sheet.getDataRange().getValues();
  var headers = data.shift();
  // console.log("spreadsheet: " spreadsheet);
  // console.log("sheet: " sheet);
  // console.log("calendar: " calendar);
  // console.log("headers: " headers);
  // console.log("data: " data);
  console.log("email: " email);
  for (var i = 0; i < data.length; i  ) {
    var row = data[i];
    var email = row[0];
    var firstName = row[1];
    var lastName = row[2];
    var position = row[3];
    var topic = row[4];
    console.log("email: " email);
    for (var j = 5; j < row.length; j  ) {
      if (row[j] === '1') {
        var date = new Date(headers[j]);
        var eventTitle = firstName   " "   lastName   " - "   position   " - "   topic;
        var event = calendar.createEvent(eventTitle, date, date);
        event.setLocation("Office");
        event.addEmailReminder(15);
      }
    }
  }
}

When a column contains a 1 in the date columns, the script should create an event filled with infos from the row in the calender [email protected]. But something it is not working, no calendar entry is created. I'm 100% sure I have the authorization for the calendar and the script is connected to the right sheet. Calendar and Spreadsheet API is activated. Maybe someone can give me a hint or tips. Thanks!

CodePudding user response：

From your reply and your script,

• I think that in your script, if (row[j] === '1') { should be if (row[j] === 1) { when row[j] is a number, or if (row[j] == '1') {.

  • I thought that the reason of your current issue of No calendar event is created by executing the script might be due to this.

• From your sample data, I'm not sure whether the date of 19.02.23, 22.02.23, and 28.02.23 in your header titles are the date object.

From the above situation, how about the following modification?

From:

if (row[j] === '1') {
  var date = new Date(headers[j]);
  var eventTitle = firstName   " "   lastName   " - "   position   " - "   topic;
  var event = calendar.createEvent(eventTitle, date, date);
  event.setLocation("Office");
  event.addEmailReminder(15);
}

To:

if (row[j] == "1") {
  var date = headers[j] instanceof Date ? headers[j] : Utilities.parseDate(headers[j], Session.getScriptTimeZone(), "dd.MM.yy");
  var eventTitle = firstName   " "   lastName   " - "   position   " - "   topic;
  var event = calendar.createEvent(eventTitle, date, date);
  event.setLocation("Office");
  event.addEmailReminder(15);
}

• If the date of 19.02.23, 22.02.23, and 28.02.23 in your header titles are the date object, var date = headers[j]; can be used instead of {var date = headers[j] instanceof Date ? headers[j] : Utilities.parseDate(headers[j], Session.getScriptTimeZone(), "dd.MM.yyyy");.

References:

• Strict equality (===)
• Equality (==)