An example on transfer entries from Calendar to Sheet through Google Apps Script
I want to introduce you with a video, that show how fast and efficient is the use of Google Apps Script to automate the export of Google Calendar data into Google Sheet. Besides it is possible to instruct the code to make some calculation, change the style and do everything you want.
I came to this solution only last year, because I was fed up to manually copy my work shifts into a sheet and make sum and others calculation. Furthermore my work in Padua changes every day, not only the timetable but also the type of the shift, the location and hourly page. So I had to take note of the type and duration and then report in a sheet to make a comparison with my monthly paycheck.
The transition from manual to automatic
In 2006 I started with a classical paper organiser and I usually wrote only the emplacements with the starting and finishing time for one or more days. Then I inserted them in a spreadsheet and I made a report every month. But sometimes the hours and the location changed so I had to reload the sheet and change it every time it happened, first in the paper and later in the software.
In 2009 I bought my first smartphone: a Nokia N78 and with its organiser I removed the scribbles and the corrections in the paper sheets of agenda. Plus I had been able to export them in my PC, even Linux was compatible until 2015 (when the free service was stopped). But the insertion on a spreadsheet remained the same!
In 2016 I changed the phone with a modern one: an android powered cellular, but at the beginning the method was the same a double work! First editing in the phone calendar and second retyping in a spreadsheet.
In 2017 the turning point: I found an article called Export Google Calendar Entries to a Google Spreadsheet (the code was written by Justin Gale) and fiat lux! I understood that thanks to Google Apps Script an interaction with Google Calendar and Google Sheet could be done.
I have adapted the code reported here to my desiderata and now when I change or add an entry in my work Calendar, the linked sheet update itself and immediately. The only requirements, for some they will be probably unacceptables, are:
- a Google account;
- a calendar software connected to Google Calendar;
- an internet connection;
- the synchronisation of the phone enabled.
In addition I use another version to check the annual report of my credits (since as Italian engineer, I have to do 30 credits every year).
- apply alternate colour to rows when the day changes;
- subdivide and count the hours in nocturnes (from 22 to 6 o'clock) and holidays;
- consider different type of shift depending on the label put in event name and sum it;
- make a sum considering all those assigned variables.
In the next weeks I am going to publish my code (but as usual I have first to clean, fix and add useful comments to it) introducing the steps to install, set-up and the features above.