Google Sheets Sync – Sync Google Spreadsheet with Google Calendar

google sheetsgoogle-calendar

I create a GTD system based on excel, whose link I report here.

The advantage of having this gtd is that I can share it with my european colleagues without the need of having a dedicated software like Trello which is – yes! – great, but has to be understood by many people. And they are not going to spend more time on understanding it.

I know it is sad to say, but in the 3rd millennium there is still a lot of people who likes more working with excel rather than with dedicated software.

This said, I need to synchronize or quickly report the deadlines of the attached document (mostly the ones in the details of each task) in some calendar application (let's say iCal) so that I can have all the deadlines in one glance.

My question is (three questions actually):

  1. is there a way to put the deadlines in iCal (or gCal or whatever) different from copying/pasting? Something practical and quick.
  2. is there any other solution (included simple software) which still has the same simplicity and usability and that might be used by my colleagues (*)?
  3. another solution could be using google spreadsheet. In this case you thing there is a simple way to sync with gCal?

(*) note on my colleagues: they belong to the science field. The all use excel, and – I say it for experience – are not going to learn another difficult tool. They will only use something very very simple.

Best Answer

This should be possible using Google Script. There are some scripts in the Script Gallery that you might find interesting, like EventManagerV3 - Script - Import your events into Calendar in one click. This script relies on the Google Calendar APIs.

Although you will probably have to modify it to suit your needs, it could be a good starting point.

You need to define when data is to be pushed from your spreadsheet to your calendar. Will it be triggered on every change to the spreadsheet? Or is it enough to push data once a day? You will also have to deal with duplicates - you don't want to create a new calendar event every time a row in your spreadsheet is updated.

To install the script, go to your Google Spreadsheet and click Tools -> Script gallery.

Another possible way is to write your own script, and publish it as a service, in effect creating a web endpoint to your spreadsheet. If you can get it to serve iCal formatted data (or another format that the Calendar software can use), you might be able to set up a subscription from your Calendar system to the spreadsheet endpoint.

I created a small example spreadsheet, and created a script that I published as a web service:

function doGet() {
  var result = "";
  var ss = SpreadsheetApp.openById("0Avea1NXBTibYdGYzdlluUDV2R3FSTnBPU2JVNmFqeWc");
  SpreadsheetApp.setActiveSpreadsheet(ss);
  var values = ss.getSheetByName("Ark 1").getDataRange().getValues();
  for (var i = 1; i < values.length; i++) {
    result += values[i][0] + ": " + values[i][3] + "\n";
  }
  return ContentService.createTextOutput(result);
}

When invoking the web service, I get a list of events in plain text format. Now, obviously, no calendar software will be able to parse that format, but it seems possible to create a proper iCal format by modifying the script.

Related Topic