I have the following script that I'm using to send data from google sheets to google calendar so that it creates events in google calendar, but I now need to colour code based on a user selection.

In the worksheet (screen shot below) column "e" will have the colour (or colour code) selected. Is there a way to add to my current script so that colour is added to each event based on the selection in column 'e'?

function create_Events() {
  var ss = SpreadsheetApp.getActive();
  var sh = ss.getSheetByName("Timesheet");
  var vs = sh.getRange("A1:E"   sh.getLastRow()).getValues();
  var cal = CalendarApp.getCalendarById("John@email");
  vs.forEach(([t, s, e, d]) => {
    cal.createEvent(t, new Date(s), new Date(e), { description: d});
  });
}

CodePudding user response：

Modification points:

• From your showing sample Spreadsheet, unfortunately, I think that the hex color cannot be directly used for setting the event color. In this case, please use Enum EventColor.
• In order to retrieve the values from the column "E", please modify vs.forEach(([t, s, e, d]) => { to vs.forEach(([t, s, e, d, color]) => {. By this, color can be used as the value from column "E".

When these points are reflected in your script, how about the following modification?

Modified script:

Before you use this script, please modify the values of column "E" from the hex values to Enum EventColor. It's like PALE_BLUE, PALE_GREEN, MAUVE, and so on. By this, using the retrieved value and setColor(CalendarApp.EventColor[color.toUpperCase()]), the event color is set to the created event.

function create_Events() {
  var ss = SpreadsheetApp.getActive();
  var sh = ss.getSheetByName("Timesheet");
  var vs = sh.getRange("A1:E"   sh.getLastRow()).getValues();
  var cal = CalendarApp.getCalendarById("[email protected]");
  vs.forEach(([t, s, e, d, color]) => {
    cal.createEvent(t, new Date(s), new Date(e), { description: d }).setColor(CalendarApp.EventColor[color.toUpperCase()]);
  });
}

Note:

• When an error like Exception: Invalid argument: color occurs, please check the values of the column "E" again.

References:

• setColor(color)
• Enum EventColor