Using Google apps to create a smarter public calendar

We’ve been building out our integration with Google apps – and reveling in the many amazing things that you can build with a bit of code and a little string. In the following articles, we’ll take you through some of the things that we’ve been creating for communities that we are working with.

First up – how to create a public calendar with a google spreadsheet and then embed events in your Geddup messages.

Creating your Google calendar

For this you will need a Google account – and no surprise – a Google calendar. We’ll leave it to Google to take you through the steps to do this, you can create an account here and instructions for creating a google calendar are here. As you’d expect, the process is pretty smooth. You will need to remember two things as you do this:

  1. Make your calendar public (further instructions here if you need them)
  2. You will need your Calendar ID – ( and here are some directions to find this)

(Note that If you want to speed up the next steps, feel free to use the example spreadsheet here. It will open in ‘View only’ mode but you can copy it with all the code intact by clicking File – Make a copy and rename the new copy…then just follow the case study and ignore the bits that you now won’t need.)

Assuming you were creating your spreadsheet from scratch, you would then head over to Google apps to create a new Google spreadsheet (File – New – Spreadsheet) and give it a name.

Create spreadsheet


Then add some dummy events that will be added to our calendar. The following should do the trick – you may want to change the dates to something current as you copy and paste into your sheet:

Class Name StartDateTime EndDateTime Description
3A – Garden Week 1 18/11/2015 13:00:00 18/11/2015 14:00:00 Planting tomatoes
3B – Garden Week 1 19/11/2015 13:00:00 19/11/2015 14:00:00 Planting tomatoes
3C – Garden Week 1 20/11/2015 13:00:00 20/11/2015 14:00:00 Planting tomatoes
4A – Kitchen Week 1 21/11/2015 13:00:00 21/11/2015 14:00:00 Cooking Quiche
4B – Kitchen Week 2 22/11/2015 13:00:00 22/11/2015 14:00:00 Cooking Quiche
4C – Kitchen Week 2 23/11/2015 13:00:00 23/11/2015 14:00:00 Cooking Quiche

Then in the sheet menu, click on Tools and choose Script editor. This will open a window for managing Google apps script. You’re going to be using a little javascript code to do some pretty nifty things. Don’t worry if javascript is a foreign language to you, this really is a cut-and-paste job.

Script editor

Click on Untitled to change the document name to what makes sense to you. Then delete the default text in the window and copy-and-paste the following:

//Script to add events to your public Google calendar from a spreadsheet

//FUNCTION to add a custom menu when the spreadsheet is opened
function onOpen() {
 var sheet = SpreadsheetApp.getActiveSpreadsheet();
 var menuEntries = []; 
 menuEntries.push({name: "Add Events", functionName: "addEvents"});
 sheet.addMenu("Events", menuEntries); 

//FUNCTION to push new events to calendar
function addEvents() {
 //spreadsheet variables
 var sheet = SpreadsheetApp.getActiveSheet();
 var range = sheet.getDataRange();
 var values = range.getValues(); 
 //calendar variables
 var calendar = CalendarApp.getCalendarById('copy and paste your calendar ID between these quotation marks') //input your calendar ID 

 //add events to calendar
 for (var i = 1; i < values.length; i++) { 
 if (values[4] != 'Added') { 
 var eventTitle = 'GA Class: ' + values[i][0];
 var eventDescription = {description: values[i][3]};
 var start = values[i][1];
 var end = values[i][2];
 var event = calendar.createEvent(eventTitle, start, end, eventDescription);
 //get each event ID
 var eventId = event.getId();
 //mark as entered, and enter ID

Now you will have to get your fingers a little dirty for the next bit. In the code you will see some lines where there are “//” followed by descriptive text (anything after a “//” will not be read as part of the script, so they can be used for descriptive comments). Find the comment that says “input your calendar ID” – it should be around line 20. Now copy and paste your Google Calendar ID between the single quotation marks. It should something like this…

var calendar = CalendarApp.getCalendarById('')

If you now File – Save your code, you are almost ready to go. As a custom menu is created when you open the spreadsheet, you will have to File – Open your spreadsheet again. Once you do so, you will see a new menu item called ‘Events‘. Click this and, taking a deep breath, then click Add Events. You will be asked to authorise a connection between your calendar and your sheet – click Sure – and presto….your events in the spreadsheet will be pushed to your calendar!

Geddup integration

If you add your google public calendar URL to Geddup these calendar events will now flow straight through to your organisation. You can then see a list of upcoming events from the calendar button on your home page – it’ll look something like this:

calendar screen


And if you are writing a message as an admin, you will now see Select an Event in your message screen which will enable you to auto-populate your messages with the event data pulled directly from your calendar. Enter the data once – and you can share it with your entire community in many ways. Sweet!

event screen



We’ve kept this example very simple. In coming weeks, we’ll show you how to integrate a form to collect registrations for events and some more cool things. But if you want to explore the world of Google apps script further, I’d suggest heading to their developer pages (entry to the cave here). There’s a whole new world waiting there…


Leave a Reply

Your email address will not be published. Required fields are marked *