Importing Colours from GCal into GSheets-CodePudding

Is there a way to export the colours used in GCal into Gsheets? This is the script I use at the moment to take data from Google Calendar and I want to incorporate a code to bring colours over as well, I'm just not sure if it's possible.

function export_gcal_to_gsheet() {
    var mycal = "Email";
    var cal = CalendarApp.getCalendarById(mycal);
    var events = cal.getEvents(new Date("July 01, 2022 00:00:00 UTC"), new Date ());
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Calendar2023");
    sheet.clearContents();  
    var header = [["Calendar Address", "Event Title", "Event Description", "Event Location", "Event Start", "Event End", "Calculated Duration", "Visibility", "Date Created", "Last Updated", "MyStatus", "Created By", "All Day Event", "Recurring Event"]]
    var range = sheet.getRange(6, 1, 1, 14);
    range.setValues(header);
    for (var i = 0; i < events.length; i  ) {
        var row = events.length   6 - i;
        var myformula_placeholder = '';
        var details=[[mycal,events[i].getTitle(), events[i].getDescription(), events[i].getLocation(), events[i].getStartTime(), events[i].getEndTime(), myformula_placeholder, (''   events[i].getVisibility()), events[i].getDateCreated(), events[i].getLastUpdated(), events[i].getMyStatus(), events[i].getCreators(), events[i].isAllDayEvent(), events[i].isRecurringEvent()]];
        var range=sheet.getRange(row,1,1,14);
        range.setValues(details);
        var cell=sheet.getRange(row,7);
        cell.setFormula('=(HOUR(F'  row  ') (MINUTE(F'  row  ')/60))-(HOUR(E'  row  ') (MINUTE(E'  row  ')/60))');
        cell.setNumberFormat('.00');
    }
}

CodePudding user response：

Although I'm not sure whether I could correctly understand I want to incorporate a code to bring colours over as well, in your situation how about the following 2 patterns?

In these patterns, the retrieved event color is put to the column "O" as the hex value.

In order to retrieve the color code as the hex, Calendar API is used. So, before you use this script, please enable Calendar API at Advanced Google services.

Pattern 1:

In this pattern, your showing script is simply modified.

function export_gcal_to_gsheet() {
  var mycal = "Email";
  var cal = CalendarApp.getCalendarById(mycal);
  var events = cal.getEvents(new Date("July 01, 2022 00:00:00 UTC"), new Date());
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Calendar2023");
  sheet.clearContents();
  var calColor = cal.getColor();
  var colors = Calendar.Colors.get().calendar;
  var header = [["Calendar Address", "Event Title", "Event Description", "Event Location", "Event Start", "Event End", "Calculated Duration", "Visibility", "Date Created", "Last Updated", "MyStatus", "Created By", "All Day Event", "Recurring Event", "Color"]]
  var range = sheet.getRange(6, 1, 1, 15);
  range.setValues(header);
  for (var i = 0; i < events.length; i  ) {
    var color = events[i].getColor();
    var row = events.length   6 - i;
    var myformula_placeholder = '';
    var details = [[mycal, events[i].getTitle(), events[i].getDescription(), events[i].getLocation(), events[i].getStartTime(), events[i].getEndTime(), myformula_placeholder, (''   events[i].getVisibility()), events[i].getDateCreated(), events[i].getLastUpdated(), events[i].getMyStatus(), events[i].getCreators(), events[i].isAllDayEvent(), events[i].isRecurringEvent(), colors[color] ? colors[color].background : calColor]];
    var range = sheet.getRange(row, 1, 1, 15);
    range.setValues(details);
    var cell = sheet.getRange(row, 7);
    cell.setFormula('=(HOUR(F'   row   ') (MINUTE(F'   row   ')/60))-(HOUR(E'   row   ') (MINUTE(E'   row   ')/60))');
    cell.setNumberFormat('.00');
  }
}

Pattern 2:

In your script, setValues and setFormula are used in a loop. In this case, the process cost will become high. Ref So, in this pattern, your showing script is modified by reducing the process cost.

function export_gcal_to_gsheet2() {
  var mycal = "Email";
  var cal = CalendarApp.getCalendarById(mycal);
  var events = cal.getEvents(new Date("July 01, 2022 00:00:00 UTC"), new Date());
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Calendar2023");
  sheet.clearContents();
  var calColor = cal.getColor();
  var colors = Calendar.Colors.get().calendar;
  var header = ["Calendar Address", "Event Title", "Event Description", "Event Location", "Event Start", "Event End", "Calculated Duration", "Visibility", "Date Created", "Last Updated", "MyStatus", "Created By", "All Day Event", "Recurring Event", "Color"];
  var offset = 6;
  var { v, c } = events.reverse().reduce((o, e, i) => {
    var color = e.getColor();
    var row = offset   i   1;
    var c = colors[color] ? colors[color].background : calColor;
    var f = `=(HOUR(F${row}) (MINUTE(F${row})/60))-(HOUR(E${row}) (MINUTE(E${row})/60))`;
    o.v.push([mycal, events[i].getTitle(), events[i].getDescription(), events[i].getLocation(), events[i].getStartTime(), events[i].getEndTime(), f, (''   events[i].getVisibility()), events[i].getDateCreated(), events[i].getLastUpdated(), events[i].getMyStatus(), events[i].getCreators(), events[i].isAllDayEvent(), events[i].isRecurringEvent(), c]);
    o.c.push([c]);
    return o;
  }, { v: [], c: [] });
  var values = [header, ...v];
  sheet.getRange(6, 1, values.length, values[0].length).setValues(values);
  sheet.getRange(7, 7, v.length - 1).setNumberFormat('.00');
  sheet.getRange(7, 15, c.length).setBackgrounds(c);
}

In this patten, the column "O" has the hex value and the background colors using the retrieved hex values.

Reference:

Colors: get