You want a week of calendar events on a sheet: one row per meeting, with the duration as a number you can sum. listCalendarEventRows(calendarId, start, end, opts) returns a 2D array with a header row (Date, Start, End, Hours, Title, Guests, Event Id), ready for setValues.
When to use
- Hours reports for clients or timesheets
- Auditing who you met with last week
- Feeding a pivot table of meeting time by title or guest
How to use this snippet
/**
* Calendar events between start and end as sheet rows (header included).
* Skips all-day events unless opts.skipAllDay === false.
*
* @param {string|null} calendarId null = default calendar
* @param {Date|string} start
* @param {Date|string} end
* @param {{timeZone?: string, skipAllDay?: boolean, titleFilter?: RegExp}} [opts]
* @return {Array<Array<*>>}
*/
function listCalendarEventRows(calendarId, start, end, opts) {
opts = opts || {};
var cal = calendarId ? CalendarApp.getCalendarById(calendarId) : CalendarApp.getDefaultCalendar();
if (!cal) throw new Error('Calendar not found: ' + calendarId);
var tz = opts.timeZone || SpreadsheetApp.getActive().getSpreadsheetTimeZone();
var events = cal.getEvents(new Date(start), new Date(end));
var rows = [['Date', 'Start', 'End', 'Hours', 'Title', 'Guests', 'Event Id']];
events.forEach(function (ev) {
if (opts.skipAllDay !== false && ev.isAllDayEvent()) return;
if (opts.titleFilter && !opts.titleFilter.test(ev.getTitle())) return;
var s = ev.getStartTime(), e = ev.getEndTime();
rows.push([
Utilities.formatDate(s, tz, 'yyyy-MM-dd'),
Utilities.formatDate(s, tz, 'HH:mm'),
Utilities.formatDate(e, tz, 'HH:mm'),
Math.round((e - s) / 36e5 * 100) / 100,
ev.getTitle(),
ev.getGuestList().map(function (g) { return g.getEmail(); }).join(', '),
ev.getId()
]);
});
return rows;
}
Example
function dumpLastWeek() {
var end = new Date();
var start = new Date(end.getTime() - 7 * 864e5);
var rows = listCalendarEventRows(null, start, end, { titleFilter: /client/i });
var sh = SpreadsheetApp.getActive().getSheetByName('Hours');
sh.clearContents();
sh.getRange(1, 1, rows.length, rows[0].length).setValues(rows);
}
Tips:
- Times are formatted in the spreadsheet's timezone by default. Pass
timeZoneto override it. Hoursis a plain number (1.5, not 1:30), soSUMand pivots work right away.Event Idlets a later run update rows instead of duplicating them.
Happy Coding!
