Party time

Eat. Sleep. Rave. Repeat.

Welcome viewers to another exciting instalment of….!

This post is a bit late, but that’s because we’ve been jam packed busy with finishing up some projects and have needed to focus on them. We didn’t forget about you though, oh no! We’ve been working hard to bring our latest inspiring blog post, well maybe anyway…

This post came rather out of the blue, and for once actually solves a bit of a need for us. Here are 23Squared we have a pretty busy calendar chock full of events (mostly parties obviously, hence the title of this blog!). We also love a bit of spreadsheet planning. We like to use Google docs for this, mainly because it has such tight integration and is accessible everywhere. We’re not advocating that everyone uses them; they just work for us!

One of the problems we’ve found is that there’s limited native integration between apps, in our case Google Sheets and Google Calendar. We wanted the ability to be able to import our event guests into a spreadsheet so that we could manage our guest list and do fun things with it. There are some add-ons for Google sheets out there but where’s the fun in that? We decided to write our own add-on that would allow us to select a calendar invite and import them into an existing spreadsheet.

The Structure of an Add-on

Google add-ons are written in a language called ‘Google App Script’ which, for all intents and purposes is javascript. The structure of our add-on (and will be quite typical of many add-ons) is –

  • A Code file
  • A javascript file but with HTML extension
  • An HTML template (possibly with embedded javascript)
  • A Stylesheet

The code file is where the main bits of code will be located, the code in this file will be executed on the server.

The javascript file with HTML extension will be executed on the client side and contain functions related to rendering the HTML template.

The HTML file with embedded javascript will be executed on the client but will mostly be used to define the layout of any dialogs or sidebars (pop-up elements).

The stylesheet is plain old CSS although curiously with a .html extension

Our Add-on

Our add-on will consist of a pop-up dialog that allows a user to select a calendar event to import. The pop-up dialog structure will be define by dialog.html which looks like this –

<!-- Use a templated HTML printing scriptlet to import common stylesheet. -->
<?!= HtmlService.createHtmlOutputFromFile('Stylesheet').getContent(); ?>

<div>
  <div class="block">
    <p>Find all events in the next:</p>
    <select id="timescale-selection-list">
      <option value=86400000>Day</option>
      <option value=172800000>Two Days</option>
      <option value=604800000>Week</option>
      <option value=2629746000>Month</option>
      <option value=31556952000>Year</option>
    </select>
  </div>
  <div class="block" id="dialog-element">
    <select id="calendar-event-list" size="7" class="width-100">
     <? var events = getEventTitlesForTimescale(86400000); ?> 
         <? for (var i = 0; i < events.length-1; i++) { ?>
          <option class="left list-item"><?= events[i] ?></option>
      <? } ?>
    </select>  
  </div>
  <div class="block" id="dialog-button-bar">
      <button class="action" id="dialog-execute-button" disabled>Select</button>
      <button id="dialog-cancel-button" onclick="google.script.host.close()">Cancel</button>
  </div>
</div>

<!-- Use a templated HTML printing scriptlet to import JavaScript. -->
<?!= HtmlService.createHtmlOutputFromFile('DialogJavaScript').getContent(); ?>

The nice thing is that it’s possible to add snippets of code directly into the templates, which can make things a lot easier. As you can see, the above template populates the calendar events when it’s rendered. If you were wondering what the naughty little magic numbers were in there, they’re the time in milliseconds for each timescale (86400000 is a day in milliseconds!).

The other interesting line is –

<button id="dialog-cancel-button" onclick="google.script.host.close()">Cancel</button>

But don’t worry about this, we’ll come back to it, we promise…

Most of the meat and grunt work that our add-on does is in the magic file with javascript and an HTML extension. We can’t really figure out what it’s actually called so we propose that it should be called,’The magic HTML file with javascript’. Catchy. Anyway, moving on from bad nomenclature to what this file actually does. This file has access to the DOM and jquery (without explicitly importing it), so is the place to do any manipulation of the DOM and heavy lifting away from the template file.

We will refrain from posting the contents of this file, because well, it’s actually quite dull. It’s essentially just DOM manipulation and calling scripts on the server side. One of, if not the only interesting thing about this file is this bit of code –

google.script.run
  .withSuccessHandler(function(param) {
    populateEventsList(param);
  })
  .getEventTitlesForTimescale(timescale);

Remember the bit of code that we said we’d discuss later? We’ll does this ring any bells? The ‘google.script’ call is how the client side code gets access to server side code. If you’re calling code in your main code file on the server the call is asynchronous and therefore you must remember to specify a success handler if you’re returning data (if you’re just calling a function to trigger some action then there’s no need). It’s also possible to specify failure handlers but hey, our code never fails, right? See https://developers.google.com/apps-script/guides/html/reference/run if you write code that fails…

Note also that it’s possible to pass parameters to the server script and return parameters too. We did experience some difficulty when returning parameters from our server side script when they were Google app script objects. We found that converting them into arrays and returning those worked fine though, your mileage may vary if you wanted to build objects and return those.

The server side script then needs to one simple call to get the data from Google calendar –

CalendarApp.getDefaultCalendar().getEvents(now, endTime);

This call is a blocking, synchronous call so there’s no need to worry about success handlers. Yay! Once we have the calendar event the user wants to import data for we then need to get the guests attached to the event and put them in the spreadsheet. We need to get access to the spreadsheet that the add-on is currently running in –

var currentSheet = SpreadsheetApp.getActiveSpreadsheet();

and then create a new worksheet named, ‘Guests’ –

var guestsSheet = currentSheet.insertSheet("Guests", 0);

This creates a new sheet called, ‘Guests’ at position zero in the worksheet tabs.

The final step is populating the worksheet with the guest data. We found this a bit tricky due to the naming of the classes – it’s not abundantly clear at first that it’s possible to update data in a range without needing a cell object. The code below iterates through guests and adds in their email, name and attendance. Note that if you don’t have a name attached to their email in your contacts or you’ve added the email by pasting into the event, the name field will be blank –

// Add the guest names to the worksheet
if(guests) {
  for(var i=0; i<=guests.length-1;i++) {
   
    // Email - col 1
    var emailCell = guestsSheet.getRange(i+1, 1);
    emailCell.clear();
    emailCell.setValue(guests[i].getEmail());

    // Name - col 2
    var nameCell = guestsSheet.getRange(i+1, 2);
    nameCell.clear();
    nameCell.setValue(guests[i].getName());
      
    // Attendance - col 3
    var attendanceCell = guestsSheet.getRange(i+1, 3);
    attendanceCell.clear();
    attendanceCell.setValue(guests[i].getGuestStatus());
  }
    
  guestsSheet.autoResizeColumn(1);
  guestsSheet.autoResizeColumn(2);
  guestsSheet.autoResizeColumn(3);
}

Tada! That’s it folks, nice huh?

One of the things that we also noticed whilst doing this was debugging – there’s kind of a two headed approach. For all client side code, console.log can be used. This approach doesn’t work for server side scripts for obvious reasons. The best way to debug the server side code is to use Logger.log() and the debug functionality provided by the Google app script editor or ‘Execution transcript’ which can be useful for tracking down unexpected exceptions.

So, get scripting and build yourself some good ol’ fashioned add-ons!

~23Squared