Skip to content

Instantly share code, notes, and snippets.

@swinton
Created October 1, 2015 21:48
Show Gist options
  • Save swinton/d04733d92dac1c66fcbc to your computer and use it in GitHub Desktop.
Save swinton/d04733d92dac1c66fcbc to your computer and use it in GitHub Desktop.
No big deal. Just some code for a Google Spreadsheet to list events from a Google Calendar, to help track + report time. If you like it, you can use it!
var settings = getSettings();
function updateTimesheet() {
/**
* Export Google Calendar Events to a Google Spreadsheet.
*
* Reference Websites:
* https://developers.google.com/apps-script/reference/calendar/calendar
* https://developers.google.com/apps-script/reference/calendar/calendar-event
*/
var cal = CalendarApp.getCalendarById(settings.CALENDAR);
// Optional variations on getEvents
// var events = cal.getEvents(new Date("January 3, 2014 00:00:00 CST"), new Date("January 14, 2014 23:59:59 CST"));
// var events = cal.getEvents(new Date("January 3, 2014 00:00:00 CST"), new Date("January 14, 2014 23:59:59 CST"), {search: 'word1'});
//
// Explanation of how the search section works (as it is NOT quite like most things Google) as part of the getEvents function:
// {search: 'word1'} Search for events with word1
// {search: '-word1'} Search for events without word1
// {search: 'word1 word2'} Search for events with word2 ONLY
// {search: 'word1-word2'} Search for events with ????
// {search: 'word1 -word2'} Search for events without word2
// {search: 'word1+word2'} Search for events with word1 AND word2
// {search: 'word1+-word2'} Search for events with word1 AND without word2
//
var events = cal.getEvents(settings.START_DATE, settings.END_DATE);
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(settings.DESTINATION_SHEET);
sheet.clearContents();
// Create a header record on the current spreadsheet in cells A1:N1 - Match the number of entries in the "header=" to the last parameter
// of the getRange entry below
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"
]
];
var range = sheet.getRange(1,1,1,14);
range.setValues(header);
// Loop through all calendar events found and write them out starting on calulated ROW 2 (i+2)
for (var i = 0; i < events.length; i++) {
var row = i+2;
var myformula_placeholder = '';
// Matching the "header=" entry above, this is the detailed row entry "details=", and must match the number of entries of the GetRange entry below
// NOTE: I've had problems with the getVisibility for some older events not having a value, so I've had do add in some NULL text to make sure it does not error
var details = [
[
settings.CALENDAR,
events[i].getTitle(),
events[i].getDescription(),
events[i].getLocation(),
events[i].getStartTime(),
events[i].getEndTime(),
myformula_placeholder,
('' + events[i].getVisibility()),
events[i].getDateCreated(),
events[i].getLastUpdated(),
events[i].getMyStatus(),
events[i].getCreators(),
events[i].isAllDayEvent(),
events[i].isRecurringEvent()
]
];
var range = sheet.getRange(row,1,1,14);
range.setValues(details);
// Writing formulas from scripts requires that you write the formulas separate from non-formulas
// Write the formula out for this specific row in column 7 to match the position of the field myformula_placeholder from above: foumula over columns F-E for time calc
var cell = sheet.getRange(row,7);
cell.setFormula(
'=(HOUR(F' +row+ ')+(MINUTE(F' +row+ ')/60))-(HOUR(E' +row+ ')+(MINUTE(E' +row+ ')/60))'
);
cell.setNumberFormat('.00');
}
}
function onOpen() {
// https://developers.google.com/apps-script/guides/menus?hl=en
var ui = SpreadsheetApp.getUi();
ui.createMenu('Timesheet')
.addItem('Update', 'updateTimesheet')
.addToUi();
}
function getSettings() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Settings");
var settings = sheet.getDataRange().getValues().reduce(function(previousValue, currentValue, index) {
var key = currentValue[0],
value = currentValue[1];
previousValue[key] = value;
return previousValue;
}, {});
return settings;
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment