I have multiple staff who input their day into a goggle calendar and I take the data and run stats. At the moment I can only take data from one calendar at a time. How can I take this script and make it work on several calendars?
Note that each calendar has its own target sheet. I've tried duplicating the script but only one version seems to work at a time.
function export_gcal_to_gsheet() {
var mycal = "person 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("PersonCalendar");
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 your script, only one Calendar ID is used. In order to use multiple Calendar IDs, when your showing script is modified for using multiple Calendar IDs, how about the following modification?
In this modification, all values are put to "PersonCalendar" sheet.
function export_gcal_to_gsheet() {
var calendarIds = ["person email1", "person email2",,,]; // Please set your Calendar IDs.
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("PersonCalendar");
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", "Color"];
var colors = Calendar.Colors.get().calendar;
var { vv, cc } = calendarIds.reduce((oo, mycal) => {
var cal = CalendarApp.getCalendarById(mycal);
var events = cal.getEvents(new Date("July 01, 2022 00:00:00 UTC"), new Date());
var calColor = cal.getColor();
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: [] });
oo.vv = [...oo.vv, ...v];
oo.cc = [...oo.cc, ...c];
return oo;
}, { vv: [], cc: [] });
var values = [header, ...vv];
sheet.getRange(6, 1, values.length, values[0].length).setValues(values);
sheet.getRange(7, 7, vv.length - 1).setNumberFormat('.00');
sheet.getRange(7, 15, cc.length).setBackgrounds(cc);
}
In this modification, the values of each Calendar are put to each sheet.
function export_gcal_to_gsheet() {
var obj = [{ mycal: "person email1", sheetName: "PersonCalendar" }];
var colors = Calendar.Colors.get().calendar;
var ss = SpreadsheetApp.getActiveSpreadsheet();
obj.forEach(({ mycal, sheetName }) => {
var cal = CalendarApp.getCalendarById(mycal);
var events = cal.getEvents(new Date("July 01, 2022 00:00:00 UTC"), new Date());
var sheet = ss.getSheetByName(sheetName);
sheet.clear(); // or sheet.clearContents();
var calColor = cal.getColor();
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 modification, the values of each Calendar are put on each sheet. And, by using Sheets API, the process cost is reduced a little from that of "Modified script 2". So, please enable Sheets API at Advanced Google services.
function export_gcal_to_gsheet() {
var obj = [{ mycal: "person email1", sheetName: "PersonCalendar" },,,]; // Please set calendar IDs and sheet names.
var colors = Calendar.Colors.get().calendar;
var ss = SpreadsheetApp.getActiveSpreadsheet();
var { valuesBatchUpdate, batchUpdate } = obj.reduce((oo, { mycal, sheetName }) => {
var cal = CalendarApp.getCalendarById(mycal);
var calColor = cal.getColor();
var events = cal.getEvents(new Date("July 01, 2022 00:00:00 UTC"), new Date());
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().getTime() / 1000 / 86400) + 25569, (events[i].getEndTime().getTime() / 1000 / 86400) + 25569, f, ('' + events[i].getVisibility()), (events[i].getDateCreated().getTime() / 1000 / 86400) + 25569, (events[i].getLastUpdated().getTime() / 1000 / 86400) + 25569, events[i].getMyStatus().toString(), events[i].getCreators().join(","), events[i].isAllDayEvent(), events[i].isRecurringEvent(), c]);
o.c.push([c]);
return o;
}, { v: [], c: [] });
var sheetId = ss.getSheetByName(sheetName).getSheetId();
var hexToRgb = hex => { // Ref: https://stackoverflow.com/a/11508164
var bigint = parseInt(hex, 16);
var red = ((bigint >> 16) & 255) / 255;
var green = ((bigint >> 8) & 255) / 255;
var blue = (bigint & 255) / 255;
return { red, green, blue }
};
oo.valuesBatchUpdate.push({ range: `'${sheetName}'!A6`, values: [header, ...v] });
oo.batchUpdate.push(
{ updateCells: { fields: "*", range: { sheetId } } },
{ repeatCell: { range: { sheetId, startRowIndex: 6, endRowIndex: 6 + v.length, startColumnIndex: 6, endColumnIndex: 7 }, cell: { userEnteredFormat: { numberFormat: { pattern: '.00', type: "NUMBER" } } }, fields: "userEnteredFormat.numberFormat" } },
{ repeatCell: { range: { sheetId, startRowIndex: 6, endRowIndex: 6 + v.length, startColumnIndex: 4, endColumnIndex: 6 }, cell: { userEnteredFormat: { numberFormat: { pattern: 'yyyy/MM/dd', type: "DATE" } } }, fields: "userEnteredFormat.numberFormat" } },
{ repeatCell: { range: { sheetId, startRowIndex: 6, endRowIndex: 6 + v.length, startColumnIndex: 8, endColumnIndex: 10 }, cell: { userEnteredFormat: { numberFormat: { pattern: 'yyyy/MM/dd', type: "DATE" } } }, fields: "userEnteredFormat.numberFormat" } },
{ updateCells: { rows: c.map(([hex]) => ({ values: [{ userEnteredFormat: { backgroundColor: hexToRgb(hex.replace("#", "")) } }] })), range: { sheetId, startRowIndex: 6, endRowIndex: 6 + c.length, startColumnIndex: 14, endColumnIndex: 15 }, fields: "userEnteredFormat.backgroundColor" } }
);
return oo;
}, { valuesBatchUpdate: [], batchUpdate: [] });
var ssId = ss.getId();
Sheets.Spreadsheets.batchUpdate({ requests: batchUpdate }, ssId);
Sheets.Spreadsheets.Values.batchUpdate({ data: valuesBatchUpdate, valueInputOption: "USER_ENTERED" }, ssId);
}