Not able to create event on Calendar with this script-CodePudding

I have the title for event, start date and end date in Google Sheet cell U69, U70, X69 respectively.

It says execution successful in script but events are not getting added.

function calendar() {
  var spreadsheet = SpreadsheetApp.getActiveSheet();
  var eventCal = CalendarApp.getCalendarById("[email protected]");
  var name = spreadsheet.getRange('U69').getValue();
  var start = new Date(spreadsheet.getRange('U70').getValue());
  var end =  new Date(spreadsheet.getRange('X69').getValue());
  eventCal.createEvent(name,start,end);

  Logger.log('Reminder is added to your calendar');
}

Edit 2:

PS - I am doing it on a sheet, of whom I am the owner. I am also creating the script. I am running it and I can't see entry on calendar.
5 people have access to that sheet. I have given access to all 5 - "Make Changes and manage sharing" from the calendar settings section.

Pls help. What am I doing wrong? Where is the syntax error? Am I putting the quotes for the variables correctly? or is it something else?

Edit 3: I added logger.log(calendar). It says execution successful (as per image attached) in script but the event is not getting added.

function calendar() {
  var spreadsheet = SpreadsheetApp.getActiveSheet();
  var calendarId = spreadsheet.getRange("AA69").getValue();
  var eventCal = CalendarApp.getCalendarById("[email protected]");

  var name = spreadsheet.getRange('U69').getValue();
  var start = new Date(spreadsheet.getRange('U70').getValue());
  var end =  new Date(spreadsheet.getRange('X69').getValue());
  eventCal.createEvent(name,start,end);
  Logger.log(calendarId);
}

[![enter image description here][1]][1]

Edit 4: I checked with ISDATE() formula. Both U70 and X69 are True. The code also shows execution done. Logger.Log also gives correct cell value.

What should I do with syntax nw? event is still not getting added.

  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Course');
  var eventCal = CalendarApp.getCalendarById('[email protected]');
  var name = spreadsheet.getRange('U69').getValue();  

  var start = spreadsheet.getRange('U70').getDisplayValue(); 
  var end = spreadsheet.getRange('X69').getDisplayValue();
  eventCal.createEvent(name,new Date(start),new Date(end));
  Logger.log(end);
}

Edit 5: Mike's answer has helped me. Thank you so much, Mike

CodePudding user response：

Here is a code that works fine in 3 ways getCalendarsByName, getCalendarById, getDefaultCalendar

function testCalendar() { 
  
  var sh = SpreadsheetApp.getActiveSheet()
  var start = new Date(sh.getRange('A1').getValue()).getTime()
  var end = new Date(sh.getRange('A2').getValue()).getTime()

  var calendName = CalendarApp.getCalendarsByName('alertes');
  var calendById = CalendarApp.getCalendarById('[email protected]')
  var calend = CalendarApp.getDefaultCalendar()

  calendName[0].createEvent('test script createEvent', new Date(start), new Date(end)).addSmsReminder(0); 
  calendById.createEvent('test script createEvent', new Date(start), new Date(end)).addSmsReminder(0);   
  calend.createEvent('test script createEvent', new Date(start), new Date(end)).addSmsReminder(0);   

}

so, I suggest you to try

function calendar() {
  var spreadsheet = SpreadsheetApp.getActiveSheet();
  var eventCal = CalendarApp.getCalendarById("[email protected]");
  var name = spreadsheet.getRange('U69').getValue();
  var start = new Date(spreadsheet.getRange('U70').getValue()).getTime();
  var end =  new Date(spreadsheet.getRange('X69').getValue()).getTime();
  eventCal.createEvent(name,new Date(start),new Date(end));
  Logger.log('Reminder is added to your calendar');
}

CodePudding user response：

The reason why you are having eventCal.createEvent...... TypeError: Cannot read property 'createEvent' of null is CalendarApp.getCalendarById(calendar id) returns null if the calendar does not exist, if the user cannot access it, or if the user is not subscribed to the calendar.

To solve the issue, make sure you have edit access to the calendar and rerun your code. If you have access to the calendar it should return a Calendar Class and inside that Calendar class is the createEvent() method.

Update #1:

Given that the OP is also the owner of the calendar, one possible reason is that the value of var calendarId is not a valid id for calendar. You can try to debug it by adding Logger.log(calendarId) below it to check if you were able to fetch the value of calendar ID in cell AA69.

Follow this link on how to get the calendar ID.

Update #2:

For date issue here are the options you can take:

Remove the new Date() in start and end variables.

use getDisplayValue() instead of getValue().

Update #1:

Update #2:

References: