0

This is my introduction to Google's script editor, I have very little knowledge on the matter. Anyway, I am trying to run a script to create events on Google Calendar with data from Google Sheets. I ended up with the code below.

function createCalendarEvent() {
  var sheet = SpreadsheetApp.getActiveSheet();
    var calendar = CalendarApp.getCalendarById('ID');

  var startRow = 2;  
  var numRows = sheet.getLastRow();   
  var numColumns = sheet.getLastColumn();

  var dataRange = sheet.getRange(startRow, 1, numRows-1, numColumns);
  var data = dataRange.getValues();

  var complete = "Done";

  for (var i = 0; i < data.length; ++i) {
    var row = data[i];
    var client = row[0]; //Client
    var task = row[1];  //Task
    var employee = row[2] //Employee
    var date = new Date(row[3]);  //Start Date
    var rDate = new Date(row[4]); //End Date
    var addguest = row[8]; //Employee Email
    var employeecolor = row[9]; //Employee Calendar Color
    var eventID = row[10]; //Scheduled?

    Logger.log(row)

    if (eventID != complete) {
      var currentCell = sheet.getRange(startRow + i, numColumns);
      calendar.createAllDayEvent(employee+ " - " + client, rDate, 
      {description: task, colorId: employeecolor, guests: addguest, sendInvites: true

      });

      currentCell.setValue(complete);

    }
  }
}

The script creates an event and sends guests email invites but the only thing I can't get to work is the ColorID. Right now, the colorID cell in Sheets generates a number between 1-11. I've tried hex color codes and Enum EventColor properties from Google's reference but nothing seems to change the color from the default calendar color. Help. Not sure what's wrong and I am sorry if this has been answered already. Thanks for reading.

avillv
  • 1
  • 1

1 Answers1

0

Without using Advanced Services, you cannot set the event color. You can see this on the description of the setColor method on the documentation. To change the color of an event, insert it into the calendar with the correct color. The object you are passing as options does not have those properties, check the documentation for available properties.

However, if you are using Advanced Services you can set the colors as explained on this answer.

ZektorH
  • 2,680
  • 1
  • 7
  • 20
  • calendar advanced service allows to set color of a single event, see doc here : https://developers.google.com/calendar/v3/reference/events , full answer here: https://stackoverflow.com/questions/31548227/google-apps-script-setting-color-of-an-event-using-calendar-api/35492002#35492002 – Serge insas Nov 05 '19 at 22:20
  • Thank you @Sergeinsas for the information! Great Answer! – ZektorH Nov 06 '19 at 07:47