Skip to content

Instantly share code, notes, and snippets.

@vanne02135
Created May 2, 2011 21:52
Show Gist options
  • Save vanne02135/952453 to your computer and use it in GitHub Desktop.
Save vanne02135/952453 to your computer and use it in GitHub Desktop.
Get calendar entries to spreadsheet on Google Apps (see Tools -> Script Editor on Google Spreadsheets)
function onOpen() {
// create menu entry for starting calFetch script
var ss = SpreadsheetApp.getActiveSpreadsheet();
var menuEntries = [{name: "Get calendar entries", functionName: "calFetch"}];
ss.addMenu("Calendar", menuEntries);
}
function calFetch() {
// get calendar entries and show them and total times
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];
// hard coded cells to get input data
var eventsFrom = sheet.getRange(1, 2).getValue();
var eventsTo = sheet.getRange(1, 4).getValue();
var calName = sheet.getRange(1, 6).getValue();
var cal = CalendarApp.getCalendarsByName(calName);
var events = cal[0].getEvents(eventsFrom, eventsTo);
sheet.getRange(2, 1).setValue("Start");
sheet.getRange(2, 2).setValue("Title");
sheet.getRange(2, 3).setValue("Description");
sheet.getRange(2, 4).setValue("Duration (h)");
sheet.getRange(2, 6).setValue("Title");
sheet.getRange(2, 7).setValue("Total duration (h)");
durations = {}
if (events[0]) {
// display each entry
for (var i = 0; i < events.length; i++) {
// Browser.msgBox(events[i].getDescription());
var startTime = events[i].getStartTime();
var endTime = events[i].getEndTime();
var duration = (endTime - startTime) / (60*60*1000); // scale ms -> hour
var title = events[i].getTitle();
sheet.getRange(i+3, 1).setValue(startTime);
sheet.getRange(i+3, 2).setValue(title);
sheet.getRange(i+3, 3).setValue(events[i].getDescription());
sheet.getRange(i+3, 4).setValue(duration);
if (durations[title]) {
durations[title] = durations[title] + duration;
} else {
durations[title] = duration;
}
}
}
if (durations) {
var i = 3;
for ( title in durations) {
// display durations for each entry
sheet.getRange(i, 6).setValue(title);
sheet.getRange(i, 7).setValue(durations[title]);
i += 1;
sheet.getRange(i+2, 3).setValue(events[i].getDescription());
}
}
}
@geb92011
Copy link

geb92011 commented Feb 6, 2020

Are you using any external libraries, because I literally copied and pasted this in and filled the dependent cells, but it still didn't even compile?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment