Created
May 2, 2011 21:52
-
-
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)
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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()); | |
} | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
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?