从GCal导入颜色到GSheets

Question

有没有办法将GCal中使用的颜色导出到Gsheets中？这是我目前使用的脚本，从谷歌日历获取数据，我想加入一个代码，以带来颜色以及，我只是不确定这是否可能。

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');
    }
}

Answer 1

虽然我不确定我是否能够正确理解I want to incorporate a code to bring colours over as well，但在您的情况下，下面的2种模式如何？

在这些模式中，检索到的事件颜色作为十六进制值放置到列"O“中。

为了检索颜色代码作为十六进制，使用Calendar API。所以，在使用这个脚本之前，请在高级Google服务中启用Calendar API。

模式1：

在此模式中，您的显示脚本只需修改即可。

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');
  }
}

模式2：

在您的脚本中，setValues和setFormula在循环中使用。在这种情况下，过程成本将变得很高。因此，在这种模式中，通过降低流程成本来修改显示脚本。

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);
}

• 在这个模式中，列"O“具有十六进制值和使用检索到的十六进制值的背景色。

参考资料：

• 颜色:得到