An example on transfer entries from Calendar to Sheet through Google Apps Script

Submitted by Nicola Rainiero on 2018-04-22 (last updated on 2018-05-29)

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.

Google Apps Script is a JavaScript cloud scripting language that provides easy ways to automate tasks across Google products and third party services, which is possible even to build web apps and automate tasks.1

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.

An example on transfer entries from Calendar to Sheet through Google Apps Script

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.

The transition from manual to automatic

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).

The video

In the article I show you how a Javascript code connected to a Google sheet can automate the transfer from a Calendar if it is instructed correctly. In particular, my modified version can:

  • 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.



Related Content:

Nicola Rainiero

A civil geotechnical engineer with the ambition to facilitate own work with free software for a knowledge and collective sharing. Also, I deal with green energy and in particular shallow geothermal energy. I have always been involved in web design and 3D modelling.

Add new comment

The content of this field is kept private and will not be shown publicly.

Plain text

  • No HTML tags allowed.
  • Web page addresses and email addresses turn into links automatically.
  • Lines and paragraphs break automatically.
CAPTCHA
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.