CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheetsgoogle-calendar-api
Mehdi Abbas
Mehdi Abbas

Reputation: 105

Need help in Google App Script for Creating event in Google Calendar

I need help in Creating a google apps script to create events in Google Calendar, following the code I'm using but gives the error "Cannot read property 'setColor' of null" for color settings.

function myFunction() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Test jun")
  var index = 2;
  var lastRow = sheet.getLastRow();

  for (; index <= lastRow; index++) {
    var title = sheet.getRange(index, 1, 1, 1).getValue();
    var startTime = sheet.getRange(index, 2, 1, 1).getValue();
    var endTime = sheet.getRange(index, 3, 1, 1).getValue();
    var description = sheet.getRange(index, 4, 1, 1).getValue();
    var location = sheet.getRange(index, 5, 1, 1).getValue();
    var guests = sheet.getRange(index, 6, 1, 1).getValue();
    var eventColour = sheet.getRange(index, 7, 1, 1).getValue();
    var sendInvites = true;

    var calendar = CalendarApp.getCalendarById("[email protected]").createEvent(title, startTime, endTime,
      { description: description, location: location, guests: guests, sendInvites: sendInvites }).getId();


    if (eventColour === 'Condition1') CalendarApp.getEventById(calendar).setColor("10")
    if (eventColour === "Condition2") CalendarApp.getEventById(calendar).setColor("11");
    if (eventColour === "Condition3") CalendarApp.getEventById(calendar).setColor("12");
    if (eventColour === "Condition4") CalendarApp.getEventById(calendar).setColor("13");


  }// End of For Loop
}// End of Function

please help.

Upvotes: 1

Views: 423

Answers (1)

Tanaike
Tanaike

Reputation: 201378

Modification points:

  • When I saw your script of var calendar = CalendarApp.getCalendarById("[email protected]").createEvent(title, startTime, endTime, {description: description, location: location, guests: guests, sendInvites: sendInvites }).getId();, the event is created to the calendar except for the default calendar.

  • The official document of getEventById(iCalId) of Class Class CalendarApp says as follows.

    Gets the event with the given ID. If the series belongs to a calendar other than the default calendar, this method must be called from that CalendarApp. Calling getEventById(iCalId) only returns an event in the default calendar.

    • I think that this is the reason of your issue.

  • In this case, it is required to retrieve the event from the specific calendar.

    • In your script, I think that the event object of the created event can be directly used for setColor().

  • And also, in your script, the event color IDs of 10, 11, 12, 13 are used. But in the current stage, the event color is from 1 to 11. Please be careful this.

  • In your script, your each if statement is independence. So you can use else.

When above points are reflected to your script, it becomes as follows.

Modified script:

From: 
var calendar = CalendarApp.getCalendarById("[email protected]").createEvent(title, startTime, endTime,
{description: description, location: location, guests: guests, sendInvites: sendInvites }).getId();

  
if (eventColour === 'Condition1') CalendarApp.getEventById(calendar).setColor("10")
if (eventColour === "Condition2") CalendarApp.getEventById(calendar).setColor("11");
if (eventColour === "Condition3") CalendarApp.getEventById(calendar).setColor("12");
if (eventColour === "Condition4") CalendarApp.getEventById(calendar).setColor("13");
To: 
var event = CalendarApp.getCalendarById("[email protected]").createEvent(title, startTime, endTime, { description: description, location: location, guests: guests, sendInvites: sendInvites });
if (eventColour === 'Condition1') {
  event.setColor("1");
} else if (eventColour === "Condition2") {
  event.setColor("2");
} else if (eventColour === "Condition3") {
  event.setColor("3");
} else if (eventColour === "Condition4") {
  event.setColor("4");
}

Or, you can also modify your script using an object as follows.

var obj = {"Condition1": "1", "Condition2": "2", "Condition3": "3", "Condition4": "4"};
CalendarApp
  .getCalendarById("[email protected]")
  .createEvent(title, startTime, endTime, { description: description, location: location, guests: guests, sendInvites: sendInvites })
  .setColor(obj[eventColour] || "11"); // In this case, when "eventColour" is not included in "obj", "11" of event color is used.
  • If you want to use the event ID, you can use CalendarApp.getCalendarById("[email protected]").getEventById({eventId}).setColor("1");.
  • About the event color, you can also use it like CalendarApp.EventColor.BLUE. Ref

References:

Upvotes: 1

Related Questions