Skip to content

Instantly share code, notes, and snippets.

@damncabbage
Last active Feb 25, 2019
Embed
What would you like to do?
Google Calendar Crappy Day Planner, Calendar Import Script
function importCalendar(){
// Settings, because who needs function arguments?
// --------------
var calendarName = 'my.name@companydomain.com';
var day = new Date();
var firstHour = 8;
var lastHour = 18;
var canonicalStartRow = 2;
var canonicalColumn = 2;
var templateSheetName = 'Template';
var rowsPerHour = 4;
// --------------
// Assumptions:
// - A source calendar, <calendarName>.
// - A spreadsheet that has:
// - A sheet that has a button that fires this script (eg. add an image, go 'Attach Script', fill in 'importCalendar').
// - A sheet called <templateSheetName> that has three columns:
// - A list of times, 08:00, 08:15, ..., until 17:00. 15-minute intervals (see rowsPerHour above).
// - A "canonical" column that has the calendar events dumped into it.
// - A "planner" column that will be user-editable, for day planner scribblings.
// - Conditional Formatting setting up for both of the latter columns, to have blocks
// automatically be highlighted if they have content in them. Not required, but good.
// The actual script:
// Misc date calculations needed for sheet names and calendar lookups.
var dateString = day.getFullYear() + '-' + padStart(day.getMonth() + 1, 2, "0") + '-' + padStart(day.getDate(), 2, "0");
var offset = padStart(day.getTimezoneOffset() / 60 * -1 * 100, 4, "0"); // Wrong, but good enough for AEST / AEDT (1000 / 1100).
var start = new Date(dateString + 'T' + padStart('' + firstHour, 2, '0') + ':00:00+' + offset);
var end = new Date(dateString + 'T' + padStart('' + (lastHour + 1), 2, '0') + ':00:00+' + offset);
// Get or create the day's sheet.
var doc = SpreadsheetApp.getActiveSpreadsheet();
var sheet = doc.getSheetByName(dateString);
if (!sheet) {
var template = doc.getSheetByName(templateSheetName);
if (!template) throw ('Could not find template: ' + templateSheetName);
doc.setActiveSheet(template);
doc.duplicateActiveSheet();
doc.renameActiveSheet(dateString);
sheet = doc.getActiveSheet();
}
var calendar = CalendarApp.getCalendarById(calendarName); // or: CalendarApp.getDefaultCalendar();
if (!calendar) calendar = CalendarApp.getCalendarsByName(calendarName)[0];
if (!calendar) throw ('Could not find calendar: ' + calendarName);
var events = calendar.getEvents(start, end);
var eventDetails = [];
for(var i = 0; i<events.length; i++){
var event = events[i];
if (event.isAllDayEvent()) continue;
if (event.getMyStatus() == CalendarApp.GuestStatus.NO) continue;
var startTime = event.getStartTime();
var startRow = canonicalStartRow + ((startTime.getHours() - firstHour) * rowsPerHour) + Math.floor(startTime.getMinutes() / (60 / rowsPerHour));
var endTime = event.getEndTime();
var endRow = canonicalStartRow + ((endTime.getHours() - firstHour) * rowsPerHour) + Math.ceil(endTime.getMinutes() / (60 / rowsPerHour));
if (startRow < canonicalStartRow) continue;
if (startRow > canonicalStartRow + (lastHour * rowsPerHour)) continue;
var contents = fill(new Array(endRow - startRow), "|");
contents[0] = event.getTitle();
if (contents.length > 1) {
contents[contents.length - 1] = "|_______________";
}
eventDetails.push({ startRow: startRow, endRow: endRow, contents: contents });
}
// Write calendar details to spreadsheet
for(var j = 0; j<eventDetails.length; j++){
var details = eventDetails[j];
var tempRange = sheet.getRange(details.startRow, canonicalColumn, details.contents.length);
tempRange.setValues(details.contents.map(function(x) { return [x] }));
}
return eventDetails; // Y'know, if you ever want them.
}
// Why yes these *are* some copy-pasted ponyfills.
function padStart(s, intMaxLength, filler) {
var str = s + '';
var stringLength = str.length;
if (intMaxLength <= stringLength) return str;
var fillLen = intMaxLength - stringLength;
while (filler.length < fillLen) {
var fLen = filler.length;
var remainingCodeUnits = fillLen - fLen;
filler += fLen > remainingCodeUnits ? filler.slice(0, remainingCodeUnits) : filler;
}
var truncatedStringFiller = filler.length > fillLen ? slice(filler, 0, fillLen) : filler;
return truncatedStringFiller + str;
}
function fill(array, value) {
var O = Object(array);
var len = O.length >>> 0;
var start = undefined;
var relativeStart = start >> 0;
var k = relativeStart < 0 ? Math.max(len + relativeStart, 0) : Math.min(relativeStart, len);
var relativeEnd = len;
var final = relativeEnd < 0 ? Math.max(len + relativeEnd, 0) : Math.min(relativeEnd, len);
while (k < final) {
O[k] = value;
k++;
}
return O;
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment