Combining a post-production work schedule with a location work schedule is difficult. My post work booking are often for big blocks of 6-12 weeks, with dates often changing wholesale. Many post houses and production companies run undecipherable booking diagrams which makes communicating dates and changes very clunky.
I use Google Calendar and one of my main clients uses software called farmerswife, which last time I checked won’t sync with Google Calendar. Luckily, they’ve had the foresight to start using Google Sheets and its sharing system to better communicate schedules.
The spreadsheets end up looking like this:
They have locked “floating” title rows and correctly defined date columns.
Google Sheets (and possibly some of the other Google apps) has a scripting language built-in called Apps Script which (as far as I know) is Javascript. I have some background in programming, so with a lot of Googling and borrowing scraps from here and there I came up with the code below. It can perform two different “one-way” (Sheets>Calendar) sync tasks.
It takes a little setting up – you have to assign the code to the sheet, and also configure a trigger for the “onedit” functionality to work. I’ve set it up so that a complete “erase and rewrite” sync can be initiated from the script editor only, and quicker partial syncs are performed only when cells are changed.
I’ve put some instructions in the code itself. Hopefully someone can find it useful, however – there may be huge holes in the code and potential security issues. This isn’t my area of expertise so please use at your own risk.
// Google Sheets to Google Calendar sync
//
// USE AT YOUR OWN RISK
//
// last edited 12/01/21
//
// The code below is made up of 3 functions….
//
// runOnEdit is a function that syncs only changed sheet data. It requires a trigger (onedit) to be bound to a sheet before it will run. You can do this in the sheet's settings on the project page.
//
// EraseAndRewriteCalendars is a function that erases and rewrites all data across the entire sheet date duration. This function needs to be launched manually from the code editor.
// Best practice is to launch this function at the beginning of sheet use, then rely on runOnEdit for subsequent syncs.
//
// guiItem is a function which (onced renamed) can be used to put a menu item in the sheet itself. Haven't maintained this area of the code, but left it in in case someone finds it useful.
//
// The Google calendar URLs can be found under settings and sharing in GCal.
// Calendars require authorisation to be written - when running a function from the script editor, google should prompt for authorisation. This is required only on first run on each sheet.
// "runOnEdit" function below requires a trigger (onedit) to be bound to a sheet before it will run. You can do this in the sheet's settings on the project page.
//
// Global variables need to be adjusted for use.
//
//
// Global variables below….
var top_left_data_cell = "ENTRY_HERE"; // Define the top-left data cell (below any title rows). Needs to be in the XY "cell format" - i.e. A4.
var last_readable_column = "ENTRY_HERE"; // Define the last column that you want to bother reading. Columns are usually defined by an alphabetical character - i.e. A, B or AA, AB etc.
// Here you give each column a variable name, i.e. day, date, picturelock etc. It is important to leave "NAME_COLUMN_HERE" for any columns to be left un-named or un-used.
var column1 = "NAME_COLUMN_HERE";
var column2 = "NAME_COLUMN_HERE";
var column3 = "NAME_COLUMN_HERE";
var column4 = "NAME_COLUMN_HERE";
var column5 = "NAME_COLUMN_HERE";
var column6 = "NAME_COLUMN_HERE";
var column7 = "NAME_COLUMN_HERE";
var column8 = "NAME_COLUMN_HERE";
var column9 = "NAME_COLUMN_HERE";
var column10 = "NAME_COLUMN_HERE";
var column11 = "NAME_COLUMN_HERE";
var column12 = "NAME_COLUMN_HERE";
var column13 = "NAME_COLUMN_HERE";
var column14 = "NAME_COLUMN_HERE";
var column15 = "NAME_COLUMN_HERE";
var column16 = "NAME_COLUMN_HERE";
var column17 = "NAME_COLUMN_HERE";
var column18 = "NAME_COLUMN_HERE";
var column19 = "NAME_COLUMN_HERE";
var column20 = "NAME_COLUMN_HERE";
var column21 = "NAME_COLUMN_HERE";
var column22 = "NAME_COLUMN_HERE";
var column23 = "NAME_COLUMN_HERE";
var column24 = "NAME_COLUMN_HERE";
var column25 = "NAME_COLUMN_HERE";
var column26 = "NAME_COLUMN_HERE";
var column27 = "NAME_COLUMN_HERE";
var column28 = "NAME_COLUMN_HERE";
var column29 = "NAME_COLUMN_HERE";
var column30 = "NAME_COLUMN_HERE";
// Each array entry below contains a column variable name (use the name defined above) and the appropriate Google Calendar URL to be written to.
// If more calendars are required, add an entry to the array below and link to a valid Google Calendar URL.
var MyCalendarArray = new Array();
MyCalendarArray[0] = new Array("NAMED_COLUMN_HERE", "GOOGLE_CALENDAR_URL");
MyCalendarArray[1] = new Array("NAMED_COLUMN_HERE", "GOOGLE_CALENDAR_URL");
MyCalendarArray[2] = new Array("NAMED_COLUMN_HERE", "GOOGLE_CALENDAR_URL");
// End of global variables.
function runOnEdit(e) {
// This function runs every time a cell is edited and only writes changed columns.
var spreadsheet = SpreadsheetApp.getActiveSheet();
var firstrow = e.range.getRow();
var lastrow = e.range.getLastRow();
var lastcolumn = e.range.getLastColumn();
var count = spreadsheet.getRange("A"+firstrow+":"+last_readable_column+lastrow+"").getValues();
for (x=0; x<count.length; x++) {
var shift = count[x];
eval ('var ' + (column1) + '= shift[0];');
eval ('var ' + (column2) + '= shift[1];');
eval ('var ' + (column3) + '= shift[2];');
eval ('var ' + (column4) + '= shift[3];');
eval ('var ' + (column5) + '= shift[4];');
eval ('var ' + (column6) + '= shift[5];');
eval ('var ' + (column7) + '= shift[6];');
eval ('var ' + (column8) + '= shift[7];');
eval ('var ' + (column9) + '= shift[8];');
eval ('var ' + (column10) + '= shift[9];');
eval ('var ' + (column11) + '= shift[10];');
eval ('var ' + (column12) + '= shift[11];');
eval ('var ' + (column13) + '= shift[12];');
eval ('var ' + (column14) + '= shift[13];');
eval ('var ' + (column15) + '= shift[14];');
eval ('var ' + (column16) + '= shift[15];');
eval ('var ' + (column17) + '= shift[16];');
eval ('var ' + (column18) + '= shift[17];');
eval ('var ' + (column19) + '= shift[18];');
eval ('var ' + (column20) + '= shift[19];');
eval ('var ' + (column21) + '= shift[20];');
eval ('var ' + (column22) + '= shift[21];');
eval ('var ' + (column23) + '= shift[22];');
eval ('var ' + (column24) + '= shift[23];');
eval ('var ' + (column25) + '= shift[24];');
eval ('var ' + (column26) + '= shift[25];');
eval ('var ' + (column27) + '= shift[26];');
eval ('var ' + (column28) + '= shift[27];');
eval ('var ' + (column29) + '= shift[28];');
eval ('var ' + (column30) + '= shift[29];');
for(var i=0; i<MyCalendarArray.length;i++){ var calendarId = MyCalendarArray[i][1]; var eventCal = CalendarApp.getCalendarById(calendarId); var fromDate = new Date(date); var toDateEight = new Date(date); var toDateEightInMS = toDateEight.getTime(); var add = 12 * 60 * 60 * 1000; var twelveHoursLater = toDateEightInMS + add; var toDate = new Date(twelveHoursLater); var events = eventCal.getEvents(fromDate, toDate); for(var j=0; j<events.length;j++){ var ev = events[j]; console.log(ev.getTitle()); // show event name in log ev.deleteEvent(); } if (eval(MyCalendarArray[i][0]) !=""){ eventCal.createAllDayEvent(eval(MyCalendarArray[i][0]), date); } }
}
}
function EraseAndRewriteCalendars() {
// This function will only run when called manually and will erase and rewrite all sheet contents to the calendars specified below.
var spreadsheet = SpreadsheetApp.getActiveSheet();
var lr = spreadsheet.getLastRow();
var count = spreadsheet.getRange(top_left_data_cell+":"+last_readable_column+lr+"").getValues();
for (x=0; x<count.length; x++) {
var shift = count[x];
eval ('var ' + (column1) + '= shift[0];');
eval ('var ' + (column2) + '= shift[1];');
eval ('var ' + (column3) + '= shift[2];');
eval ('var ' + (column4) + '= shift[3];');
eval ('var ' + (column5) + '= shift[4];');
eval ('var ' + (column6) + '= shift[5];');
eval ('var ' + (column7) + '= shift[6];');
eval ('var ' + (column8) + '= shift[7];');
eval ('var ' + (column9) + '= shift[8];');
eval ('var ' + (column10) + '= shift[9];');
eval ('var ' + (column11) + '= shift[10];');
eval ('var ' + (column12) + '= shift[11];');
eval ('var ' + (column13) + '= shift[12];');
eval ('var ' + (column14) + '= shift[13];');
eval ('var ' + (column15) + '= shift[14];');
eval ('var ' + (column16) + '= shift[15];');
eval ('var ' + (column17) + '= shift[16];');
eval ('var ' + (column18) + '= shift[17];');
eval ('var ' + (column19) + '= shift[18];');
eval ('var ' + (column20) + '= shift[19];');
eval ('var ' + (column21) + '= shift[20];');
eval ('var ' + (column22) + '= shift[21];');
eval ('var ' + (column23) + '= shift[22];');
eval ('var ' + (column24) + '= shift[23];');
eval ('var ' + (column25) + '= shift[24];');
eval ('var ' + (column26) + '= shift[25];');
eval ('var ' + (column27) + '= shift[26];');
eval ('var ' + (column28) + '= shift[27];');
eval ('var ' + (column29) + '= shift[28];');
eval ('var ' + (column30) + '= shift[29];');
for(var i=0; i<MyCalendarArray.length;i++){ var calendarId = MyCalendarArray[i][1]; var eventCal = CalendarApp.getCalendarById(calendarId); var fromDate = new Date(date); var toDate = new Date(date); toDate.setDate(fromDate.getDate()+1); var events = eventCal.getEvents(fromDate, toDate); for(var j=0; j<events.length;j++){ var ev = events[j]; Logger.log(ev.getTitle()); // show event name in log ev.deleteEvent();} if (eval(MyCalendarArray[i][0]) !=""){ eventCal.createAllDayEvent(eval(MyCalendarArray[i][0]), date); } }
}
}
function guiItem() { //change guiItem to onOpen to have it appear in the GUI
var ui = SpreadsheetApp.getUi();
ui.createMenu('Sync to Calendar')
.addItem('Sync to Google Calendar', 'EraseAndRewriteCalendars')
.addToUi();
}
Leave a Reply