5 steps to auto-transfer your Calendar entries into a spreadsheet | Rainnic in the Clouds

5 steps to auto-transfer your Calendar entries into a spreadsheet

Submitted by Nicola Rainiero on 2018-06-03 (last updated on 2018-06-10)

In the article and more accurately described in the video, I explain you how to export your calendar events into a spreadsheet using the Google Apps Script. Besides I show you the use of triggers to automate the update of the sheet when you change, add or remove an event.

Previously I have introduced the topic in the article An example on transfer entries from Calendar to Sheet through Google Apps Script, in which I have highlighted the powerful features of the script without falling in the details.

This time I will focus my attention on the configuration, execution and automation of the entire process starting from the original script (with some updates) written by Justin Gale and found here: Export Google Calendar Entries to a Google Spreadsheet.

Once followed my guide you will be able to create a Google Sheet that imports the entries of your Calendar in an assigned period of time, formatted in this manner:

The final result

Requirements

  1. a Google account;
  2. an internet connection;
  3. a personal computer (only for the installation as some features are blocked in the mobile devices);
  4. a browser with the Google App (in this case: Calendar, Drive and Sheet).
Instructions to generate a Google sheet from your Calendar entries

Tutorial

The process can be summarised in 5 steps, in the video more below I will explain in details every point:

  1. in Google Calendar add a new calendar, copy the Calendar ID and type some events;
  2. in Google Drive create a new Blank Spreadsheet, open it and from the menu in the upper go to the Script editor;
  3. from my Github Account copy all the text of the file called Code_en.gs (English Version) or Code_it.gs (Italian Version) inside the Script editor;
  4. go in the Settings section and change the variables with your data, check the time zone offset (in my case Italy has UTC +2). The most important are:
    • calendarID, with the calendar ID just created;
    • sheetTitle, with the title of your spreadsheet;
    • startingDate, the initial date in the period of interest;
    • endDate, the end date in the period of interest.
  5. Give a name to your script in the upper left empty space, run it twice (Run -> Run function -> export_gcal_to_gsheet), the first one is necessary only to give the permission to the script; finally add a trigger (Edit -> Current project's triggers -> From calendar and put the Calendar ID instead of your email).

Video

If you want to see the entire process step by step, look at here:

P.S. This is the first video with my voice, so I want to apologize for my bad grammar, pronunciation and some embarrassment.

Download

You can find and test my Script on GitHub at the following link:

Coming soon

In the next weeks I am going to improve the script by adding new features, in particular the title of the articles may be:

  • how to alternate background row colours when day changes;
  • how to subdivide and count the hours in nocturnes (from 22 to 6 o'clock) and holidays;
  • how to consider different type of shift depending on the label put in event title and sum it;
  • how to make a sum considering all the introduced variables above;
  • how to add a pie chart that summarise all the data.


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.