Google Apps Script

1 Comment

Do more with DocsGoogle recently announced (11th March 2014) the release of add-ons for Google Docs and new Sheets. This opens the opportunity for third-party developers, big and small, to create custom functionality within these products. This isn’t entirely new as for many years developers have been able to use Google Apps Script, which is also at the heart of add-ons, to create and distribute custom Google Apps enhancements. What add-ons do create is an a streamlined user experience fronted with a Chrome Web Store like interface. This post provides some notes for Google Apps admins about Google Docs add-ons.  You can see more of this in action in this video.

Add-ons store

Default on

The announcement from Google marked the immediate availability of add-ons for Google users as well as Google Apps accounts on rapid release (more on Google Apps release process). For Google Apps accounts on ‘scheduled release’ add-ons will appear “on Tuesdays only, at least one week after the feature was released to the Rapid Release track” giving time for admins to make local arrangements such as training and support. When Google activated add-ons the default was to have them enabled.

To disable Google Docs add-ons:

  1. Sign in to the Google Admin console.
  2. Click Google Apps > Drive > General.
  3. Deselect the Allow users to install Google Docs add-ons check box.
  4. Click Save changes.

The support for add-ons in Google Docs notes:

After you enable or disable Google Docs add-ons, it may take up to an hour before the change takes effect in previously opened documents, and the change won't be seen until the documents are refreshed or reopened

The support also notes:

The Admin console setting for add-ons controls both Docs and the new version of Sheets; there are not separate settings for each document type. (Add-ons are not available for other document types.)

Tip: If you are having trouble with an add-on in your document, you can force it to load without any add-ons by adding ?addon_free_mode=true

Google new Sheets are rolling out as the default

It’s worth noting that add-ons are only available for new Sheets. To support add-ons on the 21st March 2014 Google announced new Sheets are now the default, stating:

Over the next couple of weeks, rapid release domain users will automatically be upgraded to the new Sheets. Spreadsheets created after the upgrade will use the new version. Users may opt-out of this experience through the settings menu in Sheets.

The new Sheets will be available to Schedule release domains in the next four to six weeks.

Some Google Apps users have been already using new Sheets in their domain by creating a new Sheet with their Gmail account and sharing it with their Google Apps account, then using this as a template for all their spreadsheets. Users on domains on scheduled release will not see the add-ons menu even with a new Sheet. This will change as add-ons are rolled out and admins don’t enable add-ons to:

If you disable Docs add-ons in your organization, users will still see the add-ons menu in their documents and can browse the store, but they can't install any add-ons from the store.

Add-ons whitelist and authorization

A concern I’ve seen raised is the control domain admins have in approving add-ons for use. Currently the switch provided by Google is all on or all off. The current safeguard against malicious add-ons is that these need approval from Google before publication. Already some of Google’s partners have released services for providing per add-on control and it’ll be interesting to see if Google bake this feature in.

Something else for Google Apps admins consider is support informing users about add-on authorization. One of the features of add-ons is the source code is not visible to the end-user. Where as on Google Apps Scripts installed from the Script Gallery or copied elsewhere the code could be viewed before run. Admittedly the majority of script end users wouldn’t be able to understand the code but there is a degree of transparency. This shifts focus on the authorization process which warns users about what the script can do. Historically given the range of service interaction available in Apps Script these messages have been very broad. For example running the following code in the script editor which only gets a document on your Google Drive:

function myFunction() {
  var test = DriveApp.getFileById("FILE_ID");

results in:

Add-on authorisation

Click on the more information button ‘i’ reveals:

More information

Upload, download, update and delete files in your Google Drive Create, access, update and delete native Google documents in your Google Drive Manage files and documents in your Google Drive (e.g. search, organise and modify permissions and other metadata, such as title)

This problem isn’t unique and anyone installing apps on your phone will be presented with similar daunting messages. For me this partly comes down to digital literacy, educating users about how identify malicious programs by, such as, checking ratings/reviews and exploring the publisher websites, which are a requirement for add-on publication. For example, the Remove Blank Rows add-on support site highlights the authorisation requests used. Ultimately though better permissions scoping in add-ons would be useful.

If you are interested in developing your own add-ons Google have this introductory post with more information. You can also get support from Stackoverflow  or this dedicated ‘developing add-ons’ Google+ Community.


GDE Apps ScriptSo as well becoming ALT’s Chief Innovation, Technology and Community Officer I've recently been recognised as a Google Developers Experts (GDE).

Google Developers Experts (GDEs) are experts in one or more Google developer technologies. The GDE program recognizes the exemplary work done by these rock stars for the Google Developers worldwide by inviting them to be part of the growing GDE community. GDEs are gurus, mentors and friends; they are developers just like you. Visit the member directory to find an expert in the products you care about. Google Developers Experts speak in local and global events, have a strong online presence and an excellent technical background in their field. These independent developers bring their real-world experience and knowledge working with Google technologies to developer communities worldwide.

It was a pleasant surprise to be approached by Google to become a GDE and throughout the interview process I highlighted that I didn't consider myself to be a ‘developer’. If I was to give myself a label it would be ‘hacker’ … in the non-pejorative sense:

A hacker is someone who loves to program or who enjoys playful cleverness, or a combination of the two.[3] The act of engaging in activities (such as programming or other media[4]) in a spirit of playfulness and exploration is termed hacking. However the defining characteristic of a hacker is not the activities performed themselves (e.g. programming), but the manner in which it is done: Hacking entails some form of excellence, for example exploring the limits of what is possible,[5] thereby doing something exciting and meaningful.[4] Activities of playful cleverness can be said to have "hack value" and are termed hacks[5] – from Wikipedia

The product I've mostly be ‘hacking’ with is Google Apps Script and having picked up this early after launch in 2010 it's been useful to develop my own skills as the product develops as well. Its interesting times for Apps Script and it's noticeable in my circles that latest developments like add-ons has ignited the interest in using this tool to personalise the way teachers educate. Even more inspiring is it's not just the teachers as Scripts are for Kids, too!

So there you go those 100 posts, over 450 scoops, numerous presentations and other community activity have finally paid off. And guess what I'm really looking forward to the next 100 hacks, scoops and presentations...

Finally, remember I'm available for a speaking engagement near you… ;)


Google Docs Analytics Tracking - CC-BY-NC Tony Ruscoe
Google Docs Analytics Tracking - CC-BY-NC Tony Ruscoe

There was a time when you could enable Google Analytics tracking in what was Google Docs and is now Google Drive. Sadly the feature was removed and Google now recommend “embed them in your web pages, and then use Analytics to track the pages in which they're embedded”. For someone who has a number of Google Sheet templates this isn’t entirely convenient and workable. I can embed a link to a template in a site and attach an event to track the number of times clicked, but given these templates can easily be copied and recopied there’s no way to monitor use.

Part of the problem is that the Google Analytics predominantly relies on some embedded JavaScript to communicate when a page has been viewed.  Given the increasing range of interactions Google Analytics also provides a Measurement Protocol for developers to send tracking data in other ways using a HTTP POST or GET request. Without going too deep into the technical side this actual opens a way for including tracking in Google Documents, Sheets and Forms by using Google Apps Script.

Apps Script includes both triggers like onOpen and a URL Fetch service which would allow you to send data to the Measurement Protocol (and this little gist gives you the code to do it). Before you go implementing this in all your projects there are two issues to be aware of:

  1. URL Fetch calls are quotaed by fetches per day and overall runtime (current Google Apps Script Quotas).
  2. URLFetch Service requires authorisation before it can run. This means it cannot send data unless the user has given permission. So if you are viewing a Sheet template Google Analytics will only be pinged after you’ve File > Make a copy and authorised it.

Authorization for Google Services

Throwing up a beacon instead

All is not lost. Recently I came across the Google Analytics Beacon:

Sometimes it is impossible to embed the JavaScript tracking code provided by Google Analytics: the host page does not allow arbitrary JavaScript, and there is no Google Analytics integration. However, not all is lost! If you can embed a simple image (pixel tracker), then you can beacon data to Google Analytics.

This project by Googler Ilya Grigorik means if you can embed an image a Google App Engine service has been configured to make a hit against the Measurement Protocol for you. For this to work when you view the page the image needs to be served from the App Engine service. There are also limitations to this approach in that visitor and referral data is lost.

In Google Drive it’s easy for us to Insert > Image in various applications including Documents and Presentations and even specify these as ‘by URL’. Unfortunately these applications also create copies of the inserted image rather than using the image specified by URL. An anomaly to this is Google Sheets. Sheets permits Insert > Image and a cell function IMAGE. In both these cases the image is served from the URL you specify meaning we can track Google Sheets*.

*New Sheets appears to serve Insert > Image in the same way as Documents and Presentations but the IMAGE formula method outlined below still works.

Using Insert > Image

Using the GA Beacon Setup Instructions will walk you through creating a Google Analytics account and making an image URL like

Remember to use your own tracking ID. The ‘sheets’ and ‘UNIQUE_ID’ can also be whatever you like.

Using Insert > Image and selecting ‘By URL’ you can add a GA Beacon to a sheet (you can check you are collecting data by logging into Google Analytics and looking at Real-Time reporting). Now every time the Sheet is opened and the image is viewable the visit will be counted in Google Analytics. The url for the image is fixed so even if a copy is made of the spreadsheet as long as the image isn’t deleted you will get tracking data. Remember this way won’t work for New Sheets but the next method does and in my opinion is better.

Using IMAGE formula better tracking information

Using the IMAGE formula would work in exactly the same way using the same image url as above. There is something else we can do. Because it’s a formula the image url could have an identifier that is in some way calculated. As Google Apps Script permits creating your own custom formula there is even scope to use this as part of the calculation.  For example, using little Apps Script will include the sheet key and locale in the image url (to include this you your own project open you Sheet and then Tools > Script editor and paste the code in):

function getGABeacon(tid){
  var id = SpreadsheetApp.getActiveSpreadsheet().getId();
  var locale = SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetLocale();
  return ''+tid+'/sheets/'+id+'/'+locale;

In the Sheet we can then use the cell formula like =image(getGABeacon("UA-48225260-1"))

Image with GA Beacon

You can see this better in-situ in this Google Sheet which you are free to File > Make a copy to see how it works. As an added bonus the Apps Script methods used in this example don’t require authorisation so tracking data (limited to view count) is recorded for anyone opening the Sheet.

This is what the result looks like in Google Analytics Real-Time.

Google Analytics Real-Time

So there you go I can now track views of my Google Sheets by including an image in a cell!



Happy New Year to you too Simon! Having worked with Twitter and Google Maps API I was aware that their terms are becoming increasingly restrictive making the environment for 3rd party services for doing this increasingly difficult. There is a solution for doing using a modification of my Twitter Archiving Google Spreadsheet (TAGS) project (the guerrilla approach so to speak). The result for #rhizo14 is here (only viewable in non-mobile app versions of Google Maps and not the current preview version) and this post outlines how it was done. ...continue reading

1 Comment

It’s been a while since I’ve done a review post but as this year has been a blur if for no one else I wanted to look at my posts from 2013. Rather than a look at everything I wanted to extract some themes and for this first review I wanted to look back at some of the work I’ve done around Google Apps Script and Google Spreadsheets/Sheets. ...continue reading

1 Comment

From the postbag Marjolein Hoekstra (CleverClogs) writes:

Short description
Can you make a Google Script for me that compares two strings character by character? If differences are found, the script should point these out. If no differences are found at all, the script should put out the text "[ id. ]" .

Detailed description
I have two columns containing lists of horizontally identical, but sometimes almost identical text strings. This is on purpose. Each row has another couple of words that need to be compared.

I'd like to compare them on a character by character basis, and then point out in the second column at which positions it differs from the first, for example like this:



If you compare these two, you'll see that cell B2[3] has 'K' where A2[3] reads 'C'.

My envisioned formula would then populate cell C2 with: "[ – – K – – ]"

As far as I can tell, I'd need a Google Script that parses both strings character by character and output "–" when they are identical, and output the value of the character string from B2. It should be relative simple, with a FOR loop. Thing is, I've never written a Google Script, and it's a bit daunting for me to start on my own.

Note that LEN (A) is always identical to LEN (B)

Background info
In case you're interested in the actual use case: I want to use this formula to compare strings of Chinese characters, where the first column contains the traditional writing of these characters (typically requiring more strokes) and the second column containing the simplified writing of those same characters. Sometimes the characters are different, sometimes they are not. You can see this clearly in the screenshot below.

The Google Spreadsheet is used as input for a flashcard deck I'm building, using the iPhone app Flashcard Deluxe (top-notch system, highly flexible) [also available for Android].


Google Spreadsheet Example Flashcard Deluxe

There's no need to use Chinese characters to test the formula, I'm just providing this so that you know in what context the formula will be used.

The Solution

My initial thought was to use existing formula to SPLIT the cell text into individual character values and then do a comparison but unfortunately the SPLIT formula requires a character to split on. So instead I turned to Google Apps Script and wrote the following custom formula:

function stringComparison(s1, s2) {
  // lets test both variables are the same object type if not throw an error
  if ( !=={
    throw("Both values need to be an array of cells or individual cells")
  // if we are looking at two arrays of cells make sure the sizes match and only one column wide
  if( === '[object Array]' ) {
    if (s1.length != s2.length || s1[0].length > 1 || s2[0].length > 1){
      throw("Arrays of cells need to be same size and 1 column wide");
    // since we are working with an array intialise the return
    var out = [];
    for (r in s1){ // loop over the rows and find differences using diff sub function
      out.push([diff(s1[r][0], s2[r][0])]);
    return out; // return response
  } else { // we are working with two cells so return diff
    return diff(s1, s2)

function diff (s1, s2){
  var out = "[ ";
  var notid = false;
  // loop to match each character
  for (var n = 0; n < s1.length; n++){
    if (s1.charAt(n) == s2.charAt(n)){
      out += "–";
    } else {
      out += s2.charAt(n);
      notid = true;
out += " ";
  out += " ]"
  return (notid) ? out :  "[ id. ]"; // if notid(entical) return output or [id.]

One of the things to be aware of is Google Apps Script formulas are associated with a spreadsheet. You can't globally use a custom formula unless the script is attached. Fortunately when copying a spreadsheet you also get a copy of the script, so providing templates is a way around this.

With this limitation in mind I thought I’d have another go  at cracking this with built-in formula … and guess what it is possible. The key to unlocking this was when playing with the REGEXREPLACE formula I accidentally turned ‘ABCDE’ into ‘,A,B,C,D,E,’ by using =REGEXREPLACE(A20,"(.*?)",","). My RegEx is terrible so I’ll let someone else explain how this works in the comments, but getting to this point meant I could use a combination of SPLIT and REGEXREPLACE to do a character by character comparison on two cells of text. The final version of the formula goes (comparing cell A14 to B14):

=IF(EXACT(A14,B14),"[ id. ]","[ "&JOIN(" ",ARRAYFORMULA(REGEXREPLACE(SPLIT(REGEXREPLACE(B14,"(.*?)",","),","),SPLIT(REGEXREPLACE(A14,"(.*?)",","),","),"–")))&" ]")

My rough workings are embeded below. You can also make a copy of the entire project including the Apps Script solution here.

Update: Bruce Mcpherson has posted an alternative formula to do this which goes like:

"[ " & CONCATenate(ARRAYFORMULA(if(mid(A31, row(indirect("x1:x"&len(A31))) ,1)=mid(B31,row(indirect("x1:x"&len(A31))),1)," – "," "&mid(B31,row(indirect("x1:x"&len(A31)))&" ",1) ))) &" ]"

As you will see from the comments thread on that post Marjolein was having problems using my version with a Chinese characterset. Adding this to the example spreadsheet I'm unable to replicate the error but have encountered the problem here. If anyone can spot the difference I'd welcome your thoughts?

Update 2: Bruce pointed out that "the likely issue is that the columns with the problem are times - the characters mean AM. The same thing would probably happen with numbers. Have you tried wrapping the cell references in concatenate() to convert to a string?"

I said: ah I see what you mean 时 is being interpreted as 上午12:00:00. Not sure how I'd wrap the concatenate with my regexreplace. Your solution looks better all round so rather than loosing sleep I'd go with that


The concepts used in this post are very similar to the Google Analytics superProxy (announced after my solution ;) the main difference is this working in Google Drive meaning data access authentication is built in.

Access control to Google Analytics data is very coarse. Via the web interface you can have full access or none at all. Given the growing interest in data driven decision making at all levels of the institution I thought it would be beneficial to demonstrate how Google Spreadsheets and Google Apps Script could be used to selectively distribute Google Analytics data. This solution was developed for my session at the Institutional Web Managers Workshop 2013 (IWMW13) which I talk about in more detail at the end of this post.

Google Analytics Query ExporterSo here is the general flow. Google Apps Script, which is part of Google Drive, is used by Google Analytics admins to create custom queries which pull back slices of data. The admins can then control how these slices are distributed either within the security of Google Drive or published to the web. Also because Google App Script feature ‘script triggers’, which can be time based, admins can preconfigure mini scripts to decide when and how the data is distributed. Examples include:

  • Add data slice to a Google Spreadsheet (Spreadsheets feature options for publishing to the web)
  • Turn data slice into a .csv file and attached to an email for the recipient
  • Turn data slice into a .csv file and copy/share via Google Drive
  • Turn data slice into a .csv file and publish on Google Drive
  • All the above with different file formats including json

Here is the template I’ve developed which you can copy and use followed by some setup instructions (also there’s a setup video):

*** Google Spreadsheet GA Query Exporter ***

  1. File > Make a copy of this spreadsheet (need to be logged
  2. Open Tools > Script editor and then:
    File > Upgrade authorisation experience;
    Once it's upgraded still in the Script editor click Resources > Use Google's APIs; and
    Click the 'Google API Console' link at the bottom of the dialog window;
  3. In the Google Apis Console switch the Analytics API 'on' and read/accept the terms (you may have to accept two seperate terms windows)
  4. Close the Google Apis Console window and OK the Google API Services window in the Script editor
  5. Before closing the Script editor Run > authoriseGA to authenticate script

Once authenticated there are a number of ways to run the script. You can enter a query and click 'Get data' for one off fetches or open Tools > Script editor and modify the example cron jobs to automate collection and distribution of data (the cron jobs allow functionality that includes emailing/sharing links to csv files)

To create multiple queries in the same spreadsheet duplicate this sheet and modify the query

Tip: You can use formula to build the start and end dates e.g. yesterday is =(TODAY())-1

To help admins construct these data slices the tool is designed to work in collaboration with the official Google Analytics Query Explorer 2. With this users can experiment with queries like this one that returns the top blog posts based on visits. Within the Query Explorer I can export the Query URI and import to a sheet in the template. The general workflow is demonstrated in the video below which shows how to setup an example query/export:

Using the example query from above I’ve created this template sheet. Similar to the example in the video in cells E9 and E10 I use a formula to dynamically create a date range. Assuming I want to run the query on the 1st of each month for last months stats we start with the end-date generated using =TODAY()-1. To get the start-date we use the value of this field to get the 1st of last month using =DATE(YEAR(E10),MONTH(E10),1)

I could open the spreadsheet every month and manually click the ‘Get data’ button but instead I can setup a script trigger to run as a Month timer of the 1st between 1am and 2am.

Setup script trigger/cron job

The code I run is a little scriptlet like:

function exampleCronJobWriteCSVtoParticularFolder(){
  // copy new data as csv and then email as attachment (copy also kept in Google Drive)
  var sheetO = getData("input"); // name of sheet with query to execute e.g. 'input'
  var csv = saveAsCSV(sheetO.getName(), sheetO.getName()+" "+Utilities.formatDate(new Date(), "GMT", "yyyyMMdd"));
  var folder = DriveApp.getFolderById("0B6GkLMU9sHmLbThITlNvb2dzREE"); // folder id from url (navigate to folder and extract for your own folders)
  MailApp.sendEmail([email protected], "New data", "A new csv for '"+sheetO.getName()+"' has been created and is in the folder "+folder.getUrl() );
  // MailApp.sendEmail(recipient, subject, body, options) - More info

… which gets the data and then publishes it as a csv to this folder emailing a link to listed recipients. As the folder has been shared we can get access via Google Drive Host with the link

public folder

The template includes several example scriplet examples for you to play with and modify.  For those who are interested the slides for my IWMW13 session are on slideshare. The session was designed to introduce Google Apps Script followed by the query exporter template. I only made this yesterday so it might be a bit alpha. Feel free to leave issue and suggestions in the comments. I’m sure there are a number of clarifications required.


In this post I want to cover three things. First I want to introduce a little app I’ve developed which allows you to create a RSS feed for any of your Gmail labels (with the option to remove certain links – useful if you don’t want others unsubscribing you from mailing lists). Secondly I explain how it was made and how you can use it yourself. Finally I want to discuss how this could be used in an open course environment, utilising the vast processing power from services like Twitter and reusing there target marketing emails to your benefit with a bit of ‘dark social judo’.

What is Gmail Label Feeder?

It’s a little Google Apps Script app that you can setup to select one of your Gmail labels, preview content (with the option to remove all the links you’d like not to publish, like unsubscription links) and publish a public feed of the result. This video (embedded below) gives an overview of the problem and how the ‘Gmail Label Feeder’ app works:

How can I setup Gmail Label Feeder and how does it work?

Whilst logged in to your Google account open this script and then follow the instructions below:

  1. In the Script Editor File > Make a copy (this makes your own personal copy of the script which lives in Google Drive. With this copy you can control permissions etc and means I except no liability if it goes wrong, breaks, doesn’t work)
  2. From the Script Editor Run > setup
    IMPORTANT: when authenticating use the Gmail account you want to create feeds for
  3. Still in the Script Editor File > Manage versions... and create an initial name and 'Save New Version'
  4. Then, Publish > Deploy as web app... and select 'Execute the app as: me' and allow anyone access even anonymously
  5. Finally open (and bookmark) the 'current web app url' to create (atom) feeds for your gmail labels

The process covering the creation of individual feeds is covered in the video above. The video below shows the five steps for the initial setup:

How it works

Having already used Eric Koleda’s Feed+ script when creating a similar app to turn searches into an RSS feed it wasn’t too much effort to change the source data to a Gmail account. It’s not the first time I’ve hacked the functionality of Gmail having already used my inbox to re-enable Google Reader social share features, so I already knew/had code to using the ‘label:’ search operator. One thing I struggled with was removing selected links from emails. The UI side was straight forward thanks to the fantastic framework already developed by Eric. Initially I tried Removing html tags and content where tag content matches an array of values using Xml.parse() but as you see from the answer and comments from Jonathan Broughton and Bruce Mcpherson (thanks guys!) I used regex instead (only after getting caught out by line breaks and tabs).

Dark social judo: Pulling the email push

I was first made aware of ‘dark social’ via Alan Cann (who is ironically quoted in the THE today for his work around social media) and in particular his move to email as the main communication tool for students.

Alexis Madrigal at The Atlantic — who writes about the influence of what he calls “dark social” on engagement and traffic patterns. While everyone is busy watching Twitter and Facebook because they are easy to track, Madrigal argues that most social traffic still comes from old-fashioned or difficult-to-track sources like email and chat messages – (From Dark social: Why measuring user engagement is even harder than you think)

The use of email is something we’ve paid particular attention to in the open online course ocTEL, developing a daily newsletter to push a automated summary of course activity (talking about automated summaries read Tony Hirst’s Notes on Narrative Science and Automated Insights). There’s nothing particularly new in this but one whole I wanted to plug was getting an archive of these back into the ocTEL Course Reader (an RSS aggregation of all available course activity), hence the Gmail Label Feeder. Whilst doing this and picking over my own inbox for example emails I started to think about the ‘push’ I got from social sites like Twitter, Google and LinkedIn keeping me aware of activity and making suggestions for people and content I might like. Whilst some of these are very basic action reporting others require a degree of processing to generate.

Social push

The particular scenario I had in mind was if for example you were running a course Twitter account, when you get the inevitable ‘suggestions similar to’ or ‘do you know’ would there be some value judo style ’pull when your opponent pushes’  and publishing this content so it’s available to all your students. Even if this is of little individual value to the student wouldn't aggregating this data in a machine readable way be useful down the line. Have a look at the menu of push options, wouldn't at least some of these be useful to your students. Should we be doing a bit more dark social judo?

Twitter dark social menu


The Google Apps Script team have recently announced a host of new features. The three that caught my eye were:

  • Script editor added to Google Docs and Forms
  • Addition of the Forms Service which lets you programmatically manipulate forms
  • Extending Google Docs functionality using Custom menus and user interfaces including creating custom sidebars

The last one in particular looked interesting. Having a scriptable area to supplement the main control area immediately made me think about resurrecting tools like the citation robot ‘Igor’ or supplement Google Spreadsheets on the fly graphs or extra info from 3rd party sites.

As Tom Smith (University of York) has discovered sidebar integration in Google Spreadsheets isn't available yet, but the word from Google I/O session announcing this feature (video not available yet) is it’ll be here in a couple of weeks (see comments thread here).

Word Navigation PaneSo, like Tom, to kick the tyres on the Google Docs sidebar functionality I set myself a small project. One of the features of MS Word I like is the ‘Navigation Pane’, in particular for jumping around a document using section headings. Given this operates from a sidebar it seems an ideal candidate to try and replicate.

Looking at the Google Apps Script documentation we can see that we can getLinkUrl() from a TableOfContents within a Google Doc. Using an example from stackoverflow it’s easy to extract the link urls using:

 var tocDat = {};
  var doc = DocumentApp.getActiveDocument(); //get active document
  for (var i = 0; i < doc.getNumChildren(); i++) { // loop all the document elements
    var p = doc.getChild(i);
    if (p.getType() == DocumentApp.ElementType.TABLE_OF_CONTENTS) { // if the element type is a TABLE_OF_CONTENTS extract item links
      var toc = p.asTableOfContents();
      for (var ti = 0; ti < toc.getNumChildren(); ti++) { // looping over each ToC item
        var itemToc = toc.getChild(ti).asParagraph().getChild(0).asText();
        var itemText = itemToc.getText();
        var itemUrl = itemToc.getLinkUrl();
        tocDat[itemText] = itemUrl; // create object array

It’s worth noting that to get this requires the user to have already inserted a table of contents into the document. There is an open issue ticket to do this using script. Something else I was unable to do was return what level the heading link was for (e.g. Heading 1, Heading 2 etc). To do this I had to loop arose the entire document, which you can see in the final project code.

Here is a copy of the example document with the code included. Because no need edit rights to run custom menus you’ll need to File > Make a copy to get the ‘Custom’ dropdown menu option.

custom menu

The first time you select Custom > Show Document Map you get a big scary authentication window (another one of the new features announced was a pilot of a new authentication flow). Once you've ‘Ok’ you can run Custom > Show Document Map which launches the sidebar:

doc map

At this point you are probably asking where are the links in the document map. For some reason the caja sanitisation is stripping the anchor link. Regardless of this, if you dig around the page source you’ll see as part of the sanitisation links target _blank which will open a new browser tab.

href target blank

In the sidebar documentation it says that communication with other Apps Script services is possible, which might be a way to hook the navigation functionality in, but as I can’t find any methods to change document position it looks like for now it’s a lost cause.

So while I've hit a dead-end having the sidebar, particularly when it reaches Google Sheets, is a big bonus but as always it’s important to be aware of the limitations. I'm looking forward to what others come up with.

PS Must try the programmable forms next (it might be an opportunity to update EventManager v3)

Share this post on:
| | |
Posted in Google Apps Script on by .


This was a useful post to write and reflect on some ideas. If you are here because you want to make RSS feeds for posts searches here's the Feed+ Machine Chrome Web App and the standalone Feed+ Machine App

Recently when reflecting on my career path two key moments came to mind. First around 1999 having graduated as a structural engineer I got a job converting course content marked up in Word documents into HTML. Why this was important was I was forced to understand the raw building blocks of the web, I was in a foreign world and I needed to learn the language and fast.

Luke, view the source

A decade later and my interests shifted, less about static content and more dynamic data. Tapping into the work, primarily of Tony Hirst, my eyes were opened to the opportunity of remixing the web. Taking existing data, a dash of code/3rd party services and creating new things and ideas. In this world data feeds are the new building blocks to play with. In particular RSS and Atom feeds are the prize. Usually openly available and easy to manipulate/render.

Luke, find the feed

Unfortunately this ease of access appears to be putting off the big 3rd party services. With the latest Twitter API atom isn’t an option, with Google+ RSS feeds should be forgot about. This is a great shame a number of open online courses recognise the value of learners finding their own space, feeding their activity into tools which can aggregate, potentially sense make, and feed the rhizomes of the networked learner.

Like other open courses this is the challenge we face in ocTEL, pulling distributed activity into the machine. For some services RSS is still championed.  Diigo where the RSS badge with pride..

RSS from Diigo

JISCMail go even further also flying the auto-discovery flag (yet another front being eroded) …

RSS from JISCMail

What about Delicious …

Where's the RSS from Delicious

Mendeley … exists but you have to go digging

Mendeley have some, but not telling you

Google+ … this is where it gets interesting

[trumpets] Feed+ by Eric Koleda

Eric Koleda has created a Chrome Web App (Feed+) that lets you create RSS(Atom) feeds for Google+ searches. Using Google Apps Script Eric has created a user interface and it does all the negotiation with the Google+ API converting the data into RSS. Given the availability of a Scoopit API and using Eric’s base code I’ve forked the project to create the Feed+ Machine Chrome Web App and the standalone Feed+ Machine App (Google login is required to manage your feeds). To begin with it only supports creating feeds from! post searches, but given Eric has done such a great job on the design it’ll be easy to extend. I’ll spare you the details of the code but the source files are here if you want to unpick what’s going on and ask questions.

Here’s an example of a feed for Scoop.its referencing ocTEL, which we are already digesting in the ocTEL Course Reader. But why do this? My interest is primarily in capturing as much of the digital landscape, each artefact collected adding more detail to the map. Whilst material pulled from is potentially yet another churn of existing content, who is sharing and any insight they add provides additional context and may be another way in which participants can identify and migrate to new clusters of activity.

There is a downside to this to this approach. Having a search feed from is useful but having wrestled with the ScoopIt API there is much more data available not being captured such as metadata around posts and topics. So perhaps like others I should abandon the feed and favour JSON and start filling the Tin Can

PS NEW!!! CETIS briefing on Activity Data and Paradata for more developments in this area.