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

Question

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.

Did my answer show you the result what you want? Would you please tell me about it? That is also useful for me to study. If this works, other people who have the same issue with you can also base your question as a question which can be solved. If you have issues for my answer yet, I apologize. At that time, can I ask you about your current situation? I would like to study to solve your issues. — Tanaike
– Tanaike, Commented Apr 8, 2021 at 6:36

Tanaike · Accepted Answer · 2021-03-27 06:01:20Z

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

Collectives™ on Stack Overflow

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

1 Answer 1

Modification points:

Modified script:

References:

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Modification points:

Modified script:

References:

Comments

Your Answer

Sign up or log in

Post as a guest

Related