Skip to content

Instantly share code, notes, and snippets.

@faboo03
Created October 17, 2020 13:21
Show Gist options
  • Save faboo03/bb3a88c496dd68f0ddae295549055579 to your computer and use it in GitHub Desktop.
Save faboo03/bb3a88c496dd68f0ddae295549055579 to your computer and use it in GitHub Desktop.
Google App Script - Extract event from calendar to Google Spreadsheet automatically
var calendarId = 'xxxx@group.calendar.google.com';
var startDate = "2020-01-01";
var stopDate = "2020-12-31";
// Add element to menu to force the trigger
function addMenu() {
var ui = SpreadsheetApp.getUi()
var menu = ui.createMenu("Menu AddOn").addItem("Extract Calendar", "extractFromCalendar").addToUi()
}
// extract data from the calendar
function extractFromCalendar() {
var activeSheet = SpreadsheetApp.getActiveSheet();
var start = new Date(startDate);
var stop = new Date(stopDate);
var calendar = CalendarApp.getCalendarById(calendarId);
// clean range
activeSheet.getRange("A:D").setValue("");
// populate with the extracted data
var row = 2;
calendar.getEvents(start, stop).forEach(event => {
activeSheet.getRange(row, 1).setValue(event.getCreators().pop());
activeSheet.getRange(row, 2).setValue(event.getStartTime());
activeSheet.getRange(row, 3).setValue(event.getEndTime());
activeSheet.getRange(row, 4).setValue(event.getTitle());
row++;
});
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment