Google Apps Script

2 Comments

Get TAGS

Amazing to think TAGS has been going for over 4 years now. Version 6 is a major code rewrite and the most important new feature is a better setup processes. No more digging into the script editor to run functions, no more entering your Twitter API key and secret each time you create a new archive. Both these things are history thanks to some base code released by Arun Nagarajan at Google. Now you enter/register for a Twitter API key and secret once and each copy of TAGS you make will remember these. This is made possible by incorporating this functionality as a custom Apps Script library I’ve called TwtrService. TwtrService makes it easy to make calls to all of Twitter’s API and I’ll be explaining how it works in another post.

Version 6 comes with some other new features. The one that was most requested was archiving favourited tweets. There was a quick hack version of TAGS that did this but was limited to the last 200. Now when you setup a favourite archive you can get up to the last 3,000 favourited tweets. Another option with TAGS v6.0 is to use Google’s new version of Sheets. This gives TAGS more capacity and performance. One issue however with new Sheets is it isn’t very stable with the Google Visualisation API which is used in TAGSExplorer.

With TAGS v6.0 I’ve also created a dedicated support site. So if you have any questions or need help head over to http://tags.hawksey.info where you can also get the latest version of TAGS.

1 Comment

Thanks to a recommendation by Josef Šlerka (@josefslerka) I got a chance to speak at WebExpo 14 (#webexpo) about some of my work using Google Sheets for Twitter data mining and analysis. I’m always get uneasy at speaking at events for web professionals/developers about spreadsheets as … it is spreadsheets.

Dangers aside, particularly ‘spreadsheet addiction’, hopefully I was able to covey  the possibilities of using Google Apps Script turbocharged Google Sheets. To do this I focused on some of the work I’ve been doing interacting with the Twitter search API. I’m sure many people are familiar with the Twitter Archiving Google Sheet (TAGS) but perhaps not aware of some of the experiments I’ve used it for. Some of these include:

(I was surprised myself to discover the whole project started in 2010)

After my talk it was great that some fellow educators came to speak to me interested in using Google Apps Script for social data mining as part of their curriculum. Slides for my talk and abstract are at the end of this post and the link bundle is here and the #webexpo TAGS archive is here.

Thanks also to my fellow GDE Ivan Kutil for capturing a picture of me dwarfed by one of my own sheets.

IMG_20140913_161030

.

 

There is growing interest in the use of data to provide actionable insight. This interest goes beyond the professional analysts and just as fields such as mathematics and astronomy have benefited from the enthusiastic amateur so does data science. Social networks are a rich playground of data and whilst many provide access to their data via APIs but access via this route can be daunting. You can of course turn to 'analytics as a service' sites which will take your credentials and provide you with some answers, but often this can be what they want to tell you and not what you want to hear. A solution is the spreadsheet. Spreadsheets provide an interface for data exploration for those with basic skills. With Google Sheets the opportunities increase exponentially, not just in terms of collaboration, but also with the power of Google Apps Script. Apps Script provides easy integration into other Google products and services, such as Google Analytics, as well as third party APIs like Twitter. In this presentation we show how Google Sheets can become a rich playground where data from different services can be collected and analysed.

1 Comment

I was building an analytics dashboard today that collected data from various services including Google Anaytics and YouTube. Apps Script makes this very easy as highlighted in my previous post. An issue I encountered when I tried to access our YouTube channel reports is even though my account is attached to as a manager I was getting a ‘Forbidden’ error.  Turning to the Channel Reports documentation I discovered:

channel==CHANNEL_ID – Set CHANNEL_ID to the unique channel ID of the channel for which you are retrieving data. The user authorizing the request must be the owner of the channel.

As our YouTube channel is associated with our Google+ page you can’t log in to Google Drive with that account. I did notice however that when I added YouTube Analytics as an Advanced Apps Script service the authentication prompt gave an option of authenticating using our Google+ page.

auth window 

The issue then is if you authenticate against the Google+ page you can’t get access to other services like Google Analytics. I thought of a couple of ways I might tackle this such as writing a separate Apps Script project that just got the YouTube Analytics data and wrote it to the spreadsheet I was working on. I’m not entirely sure how the permissions would work out on that. Instead my solution was to expose the YouTubeAnalytics.Reports.query  in a separate Apps Script published as a web app. Setting this to run ‘anyone, even anonymously’ I could then use UrlFetchApp to get the data in my main script.

Here’s how I set it up. Below (or in this gist) the 'main' script is handling all the data read/write to sheet and a separate 'proxy' Apps Script project running the YouTube Analytics data collection.

Note: This technique exposes our YouTube Channel report to the world (barring security by obscurity). The method we are exposing is read only so we don’t have to worry about an injection. 

Feels a bit hacky but can you see a better way of doing this?

Update 22/07/2014: Matias Molinas had the great suggestion of writing the results to a sheet which would avoid exposing your data. Jarom McDonald has also suggested using Google App Engine would give security and scalability à la superProxy

4 Comments

There are regular reporting features built in to Google Analytics but what if you want to do customised reporting or other regular data collection and integration techniques? One answer is Google Apps Script. Apps Script is a Google Drive feature that makes custom automation so much easier. The big advantage of Apps Script is you are already in a Google authenticated environment. So while anyone can use the existing Google Analytics APIs using Apps Script you don’t need to worry about the authentication handshake to get your data. This means that you can access Google Analytics data in one line of code. There is a couple of tickboxes to get to that one line of code but in this tutorial I’ll walk you through the process.

Note: You can also get Google Analytics (and other analytics data) in a Google Sheet with zero lines of code using add-ons. The current limitation of these is they cannot currently be automatically run on a scheduled basis.

Setting up our project

Google Apps Script lives in a couple of places but for this example we are going to use a Google Sheet so step one is:

  1. From your Google Drive account create a new Spreadsheet
  2. In the new spreadsheet open Tools > Script editor…
  3. If this is your first script you might see a window popup with options to ‘Create projects for’, if so click ‘Close’
    Close Apps Script project window
  4. You should now see the Script editor
    Script editor
  5. The connection to Google Analytics is a Google Apps Script ‘Advanced Service’ and to enable we need to turn it on. To do this in the Script Editor select Resources > Advanced Google services 
    image
  6. At this point you will be prompted to create a project name. Enter a name for your project and click ‘OK’.
  7. You should now see a list of available advanced services. For this project we want to just turn on Google Analytics API by clicking the on/off toggle. So far we have enabled Analytics in our project but we also need to click on the Google Developers Console link highlighted to enable this at the console end.
    Advanced services control
  8. Similarly to the ‘Advanced Google Services’ we need to enable the Analytics API by toggling the on/off (at this point you may be prompted to review and accept terms of service)
    image
  9. Once enabled in the Developer Console you can close this tab/window and click OK in the ‘Advanced Google Services’ box in your Script Editor.

To recap our project is now configured to use the Google Analytics API. You can use multiple advanced services in the same project as needed. Remember ‘Advanced Services’ need to be enabled for each project you create but this only needs to be done once for each project.

Getting Google Analytics data (and some basic Script Editor tips)

For those familiar with coding environments the Script Editor comes with a code autocomplete. This is useful when starting rather than remembering a long list of classes and methods. To use this:

In the Script Editor click on line 2 between the moustaches (‘{‘ and ‘}’) and for PC press CTRL + SPACE or Mac ⌘ + SPACE, from this you can select Analytics from the list. To continue the autocomplete follow it with a dot ‘.’ which brings the associated options. The line we want to get is Analytics.Data.Ga.get(ids, start-date, end-date, metrics, optionalArgs); 

autocomplete

So you’ll see there are a couple of parameters we need to provide defined in the GA Core Reporting Query Parameters Summary:

  • ids - Unique table ID for retrieving Analytics data. Table ID is of the form ga:XXXX, where XXXX is the Analytics view (profile) ID.
  • startDate - Start date for fetching Analytics data. Requests can specify a start date formatted as YYYY-MM-DD, or as a relative date (e.g., today, yesterday, or 7daysAgo). The default value is 7daysAgo.
  • endDate - End date for fetching Analytics data. Request can should specify an end date formatted as YYYY-MM- DD, or as a relative date (e.g., today, yesterday, or 7daysAgo). The default value is yesterday.
  • metrics - A comma-separated list of Analytics metrics. E.g., 'ga:sessions,ga:pageviews'. At least one metric must be specified.
  • optionalArgs – is an object array of the optional query parameters like dimensions, segment, filters, sort etc. An example from this Google Analytics Apps Script tutorial is:
var optionalArgs = {
 'dimensions': 'ga:keyword',              // Comma separated list of dimensions.
 'sort': '-ga:sessions,ga:keyword',       // Sort by sessions descending, then keyword.
 'segment': 'dynamic::ga:isMobile==Yes',  // Process only mobile traffic.
 'filters': 'ga:source==google',          // Display only google traffic.
 'start-index': '1',
 'max-results': '250'                     // Display the first 250 results.
};

Building a Google Analytics Query with Query Explorer

If you are unfamiliar with the Google Analytics Core Reporting API building a query can be quite daunting. Fortunately the Google Analytics team have made the Google Analytics Query Explorer which gives you an interactive interface to build and test queries. Here’s an example to query your top referring sites, which should give you a page like this:

Google Analytics Query Explorer 2

If you haven’t used the Query Explorer before you’ll need to click on the ‘Authorize Access’ button, which will enable a ‘Get Data’ button. You can test and tweak your query as much as you like using the ‘Get Data’ button to see what is returned from your Google Analytics accounts. The Query Explorer is a great starting point but remember it only contains a few of the optional arguments.

If you are using the Query Explorer then below is a helper script for converting your Query URI into an Apps Script snippet. To use this build your query in the Query Explorer and then press the Query URI button image to get a URI to paste in the textfield and then click ‘Submit’:

The little helper script gives us some extra code to store/pass the parameters we need. If you are scheduling this script to run on a regular basis you’ll need to modify the start/end date in the query. The are two main ways you can do this: either building the date by manipulating a Date instance formatted as YYYY-MM-DD; or as a relative date (e.g., today, yesterday, or NdaysAgo where N is a positive integer). Below is an example of a modified query made with the Query Explorer and exported to generate the script we need which gets data from the last 7 days. In this example I’ve wrapped it in a new function name fetchMyQuery. Note: In your example you need your own ids value to return results.

function fetchMyQuery() {
  var query = {
    "optionalArgs": {
      "dimensions": "ga:source",
      "filters": "ga:medium==referral",
      "sort": "-ga:pageviews",
      "max-results": "50"
    },
    "ids": "ga:82426939",
    "metrics": "ga:pageviews,ga:sessionDuration,ga:exits",
    "start-date": "7daysAgo",
    "end-date": "yesterday"
  };
  var results = Analytics.Data.Ga.get(query.ids, query['start-date'], query['end-date'], query.metrics, query.optionalArgs);
  Logger.log(results);  
}

Testing/Debugging Apps Script

With your script saved we can test the code. When developing scripts there is a debug feature demonstrated in the video below (the first time you run a new script you need to authorise it. This only needs to be done the first time the script runs or when new permissions are required.

Insert Data Into A Spreadsheet

The final step is to output the results from our query into Google Sheets. For this example let reuse a modified version of the outputToSpreadsheet method in the Automated Access to Google Analytics Data in Google Sheets tutorial:

function outputToSpreadsheet(results, sheet) {
  // Print the headers.
  var headerNames = [];
  for (var i = 0, header; header = results.getColumnHeaders()[i]; ++i) {
    headerNames.push(header.getName());
  }
  sheet.getRange(1, 1, 1, headerNames.length)
      .setValues([headerNames]);

  // Print the rows of data.
  sheet.getRange(2, 1, results.getRows().length, headerNames.length)
      .setValues(results.getRows());
}

This function inserts all the header and reporting data to the sheet. For more information on how to insert data into Google Sheets with Apps Script there is a  Storing Data in Spreadsheets tutorial.

The outputToSpreadsheet method in our example is expecting two objects to be passed in. To do this your fetchMyQuery method needs to include getting Sheet object and passing it to will need to outputToSpreadsheet. The entire project should look like this:

When you now Run > fetchMyQuery you should see the data written in the sheet you specified.

Automate the Script

This project has been about automated data collection so let look at how we set this up. Google Apps Script makes automation very easy using the triggers feature. To set this up

  1. In the Script Editor click Resources > Current project’s triggers
  2. Click ‘No triggers set up. Click here to add one now’
  3. Lets configure the fetchMyQuery to run once a week by setting:
    • The Run dropdown to: fetchMyQuery
    • The Events dropdown to: Time-driven, and selecting Week timer to run Every Monday between 7:00 a.m. to 8:00 a.m.

Triggers

Once saved this script will run as scheduled with no need for your to have the sheet open. If you would like to be told if the script fails whilst unattended click the notifications link which opens a new dialogue box to allow you to configure to which email you want errors to be sent and when.

Summary

This post has introduced you to using Google Apps Script to automatically collect and write Google Analytics data to a Google Sheet. Once you get started with Apps Script you’ll start discovering many more opportunities to automate tasks. For example, as part of our triggered event we could notify people of an update using the MailApp service or write an entire document using Document Service. For some ideas you might want to read about Analytics reporting with Google Apps Script at the UK Cabinet Office.  If you are just starting to use Google Apps Script the Google Developers has extensive documentation and tutorials and if you get stuck there is a dedicated tag in Stackoverflow and an active community on Google+.

1 Comment

Back in 2011 I showed how you can use Google Apps Script to write POST/GET data to a Google Sheet. Over the years a couple of things have changed in Apps Script so I thought it was worth a revisit.  The main changes are:

The core concept behind the script is the same. You have a Google Sheet with a set of header column names that matches the names of the data you are passing through. For example if I had a form with:

<input name="bar" type="text" value="" />

I'd need a sheet with the column name 'bar'. For this post I’m going to assume we use a container bound Apps Script in a Google Sheet, but you could easily modify this for a standalone script. So to start you can either create or open an existing Sheet and click Tools > Script editor and enter the code below or copy this template.

Usage

There are a couple of ways you can use this script to collect data. You could use a very traditional HTML form using the web app url as the action parameter. This would send users to a very unattractive JSON response which you could alternatively beautify using the HTMLService. A nicer solution is to use AJAX to submit the data without refreshing or moving page. Below is a simple form based on this Stackoverflow jQuery Ajax POST example which sends responses to this Google Sheet (if you are reading this via RSS/Email you need to visit this post):

The only real change to the stackoverflow example is to specify the destination web app url:

// fire off the request to /form.php
		request = $.ajax({
			url: "https://script.google.com/macros/s/AKfycbzV--xTooSkBLufMs4AnrCTdwZxVNtycTE4JNtaCze2UijXAg8/exec",
			type: "post",
			data: serializedData
		});

The example is using POST but you can also use GET. There is more you can do when handling the data at the Apps Script end other than writing to a Google Sheet. For example, if you wanted to send an email on each submission you could use the MailApp service and add something like:

MailApp.sendEmail("youremailaddress", "a subject", JSON.stringify(e.parameters));

in the try statement. If you do this there are a couple of things to remember. First Apps Script web apps using versioning. This means changes to your script are not 'live' until you push a new version. To do this you need to save your new script and then from the Script Editor select File > Manage versions... and 'Save New Version' before going into Publish > Deploy as web app and updating Project Version. Also when you add new services to your script the authentication scope changes and you need to approve additional services. For example, if you add the MailApp service to your code you need to give permission to send email. The easiest way to trigger this in this example is in the Script Editor Run > setup. I'm sure there are other trip ups but hopefully this gets you most of the way

Google Sheet/Apps Script Code

//  1. Enter sheet name where data is to be written below
        var SHEET_NAME = "Sheet1";
        
//  2. Run > setup
//
//  3. Publish > Deploy as web app 
//    - enter Project Version name and click 'Save New Version' 
//    - set security level and enable service (most likely execute as 'me' and access 'anyone, even anonymously) 
//
//  4. Copy the 'Current web app URL' and post this in your form/script action 
//
//  5. Insert column names on your destination sheet matching the parameter names of the data you are passing in (exactly matching case)

var SCRIPT_PROP = PropertiesService.getScriptProperties(); // new property service

// If you don't want to expose either GET or POST methods you can comment out the appropriate function
function doGet(e){
  return handleResponse(e);
}

function doPost(e){
  return handleResponse(e);
}

function handleResponse(e) {
  // shortly after my original solution Google announced the LockService[1]
  // this prevents concurrent access overwritting data
  // [1] http://googleappsdeveloper.blogspot.co.uk/2011/10/concurrency-and-google-apps-script.html
  // we want a public lock, one that locks for all invocations
  var lock = LockService.getPublicLock();
  lock.waitLock(30000);  // wait 30 seconds before conceding defeat.
  
  try {
    // next set where we write the data - you could write to multiple/alternate destinations
    var doc = SpreadsheetApp.openById(SCRIPT_PROP.getProperty("key"));
    var sheet = doc.getSheetByName(SHEET_NAME);
    
    // we'll assume header is in row 1 but you can override with header_row in GET/POST data
    var headRow = e.parameter.header_row || 1;
    var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
    var nextRow = sheet.getLastRow()+1; // get next row
    var row = []; 
    // loop through the header columns
    for (i in headers){
      if (headers[i] == "Timestamp"){ // special case if you include a 'Timestamp' column
        row.push(new Date());
      } else { // else use header name to get data
        row.push(e.parameter[headers[i]]);
      }
    }
    // more efficient to set values as [][] array than individually
    sheet.getRange(nextRow, 1, 1, row.length).setValues([row]);
    // return json success results
    return ContentService
          .createTextOutput(JSON.stringify({"result":"success", "row": nextRow}))
          .setMimeType(ContentService.MimeType.JSON);
  } catch(e){
    // if error return this
    return ContentService
          .createTextOutput(JSON.stringify({"result":"error", "error": e}))
          .setMimeType(ContentService.MimeType.JSON);
  } finally { //release lock
    lock.releaseLock();
  }
}

function setup() {
    var doc = SpreadsheetApp.getActiveSpreadsheet();
    SCRIPT_PROP.setProperty("key", doc.getId());
}

Update 24/06/2014: Recording of the session currently here

As well as talking about Google {Learning} Analytics at the Google Apps for Education European User Group Meeting (GEUG14) at the University of York I’m also doing a session on Google Apps Script. The abstract I submitted at the time for the session was:

Recently Google announced Add-ons which allow anyone to enhance Google Documents and Sheets with customised features. Already there are a number of add-ons to support teaching and learning such as bibliography and track changes tools. Add-ons are developed in Google Apps Script. Apps Script is free for anyone with a Google account and not only can let you author your own add-ons but also automate your workflows within Google Apps, integrate with external APIs, and more. This talk will introduce users to add-ons exploring some educational scenarios. As part of this we will discuss some threats and opportunities. We will then touch upon how add-ons are authored using Apps Script and highlight opportunities for personalised automation of workflows.

Having been to two other GEUG events I know the event attracts a diverse audience from educators to administrators to developers. This is one of the events strengths but for me makes presenting something on Google Apps Script difficult particularly thinking back to my experience starting with very little code experience but still able to make stuff.

A factor I have to also consider is since submitting the session proposal Google announced Classroom. Classroom is still in development but looks like a layer to Google Drive to administer assignments. This type of functionality has been achievable with Apps Script for a long time and I will be definitely highlighting the awesome work Andrew Stillman & Co. have been doing with scripts like Doctopus.

Another factor in my mind is from my circles the feedback I’m getting is add-ons are causing Google Apps admins headaches in that there are currently no built in fine grained controls to restrict certain add-ons (3rd party solutions are available). I don’t know how many Admins to choosing to switch off add-ons on their Google Apps domain and it’s one of the questions I’m looking for feedback on at the event.

All this has shaped the focus of my talk but hopefully there is enough there for everyone. You can tune in to the streamed Google Hangout of the session 23rd June at 3pmBST (see programme). The current slides for my talk are below:

4 Comments

import.io is a nice service I’ve been dipping into for a while. It’s one of a number of services that provides structured web data scraping. One of the nice features of import.io is it:

transform any website into a table of data or an API in minutes without even writing any code

You load a webpage with their web browser app and start highlighting the parts of the page you’d like to extract. Int3rhacktives has a nice How to scrape data without coding? A step by step tutorial on import.io if you want to find out more.

Once you have the data you want extracted import.io continue to try and keep the bar low allowing easy data download in various formats including .csv. and if you want to use live data there are example itegrations for Excel, Google Sheet and other programming languages.

Looking more closely at the Google Sheet integration import.io document a method that uses their REST API’s HTML table output which is then wrapped in a Google Sheet importHTML formula e.g.

=ImportHtml("https://query.import.io/store/connector/48fd118b-7572-44a6-816c-8f02d088fb6a/_query?_user=5895d593-9461-4b8b-8452-95bb82458bd2&_apikey=YOUR_API_KEY&format=HTML&input/webpage/url=http%3A%2F%2Fwww.scoop.it%2Ft%2Fgas", "table", 1)

import.io easy as 1, 2

I’m a big fan of Google Sheet ‘import’ and have some tutorials on these. The ‘import’ formula are useful for quick results but not appropriate if you need to do additional manipulation or integration into other automated workflows. import.io do have a number of client libraries and code examples you can look at to address this but the one I thought was missing was one for Google Apps Script. One of the great strengths of Apps Script is it’s easy to create time-based routines to pull and push data around as and when needed. So based on import.io’s php example here’s what it would look like in Google Apps Script.

You can read the Google Apps Script Documentation to find out more about what you can do with the result.  Something the guys at import.io might want to think about is creating a Google Apps Script Library. Similar to their other client libraries it will again lower the bar for developers. As a starter I’ve implemented the query method here which means anyone creating a Apps Script project and including a library using the Project Key: M2ZyMvVZdgKdy3SaLP8gq3X797_hv7HHb could just use:

function getImportioExample(){
  // Query for tile Integrate Page Example
  var result = importio.query("caff10dc-3bf8-402e-b1b8-c799a77c3e8c", {"searchterm": "avengers 2",}, userGuid, apiKey, false);
  Logger.log(result);
}

with the benefit of also getting a code autocomplete:

autocomplete

If you've already got Google Apps Script/import.io integrations I'd love to hear about them. Hopefully I'll follow-up this post with an example automation to illustrate what is possible.

 

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.

5 Comments

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… ;)

4 Comments

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

https://ga-beacon.appspot.com/UA-XXXXX-X/sheets/UNIQUE_ID

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 'https://ga-beacon.appspot.com/'+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.

Note: GA Beacon recently defaulted to using a SVG image which is not compatible with the old version of Google Sheets. The tracking still appears to work but a #N/A error is returned. To get around this I requested an option to add ?gif to the beacon url is added. To use this you can add +'?gif' to the script or use the formula =image(getGABeacon("UA-48225260-1")&"?gif")

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!