Google

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

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

For a while I’ve been interested in the intersection between Google Analytics (and Google’s other analytic reporting APIs like YouTube) applied to the field of Learning Analytics. There are a number of features of Google Analytics such as segmentation, a-b testing, event tracking which I believe could potentially give useful insight to teaching a learning, for example, a look last year at tracking and validating MCQs with GA.

One of the reasons for my interest in this area is the ubiquity of Google Analytics, the majority of institutions already using Google Analytics for their main institutional websites. It should not be forgotten that with power comes responsibility. Whilst Google Analytics usage policies prevent you using it to track personally identifiable information you are still tracking people which should never be forgotten.

The Google Apps for Education European User Group Meeting (GEUG14) at the University of York is another opportunity to roadtest some of these ideas. The process for preparing for an event often not only sees me revisiting prior knowledge but is often turned into an opportunity to create something new. This can be a product, like the Google Analytics data bridge made for IWMW13, or new knowledge.

This time personal exploration has taken me into the land of the Google Tag Manager. Those familiar with the mechanics of Google Analytics tracking will know that this usually requires adding code to every page you want to track. Often this can be achieved modifying page templates. But what if these templates are hard/costly to edit or you want to make changes to what is tracked. This is where Google Tag Manager comes in. Like Google Analytics you need to install some code in your pages. After that what and how you track things becomes completely cloud based. Through Google Tag Manager to can add additional code/markup to your pages even setting up rules to decide when these are used. Whilst Tag Manager is build around Google products like Analytics and Ads you can use it for other purposes. This video gives you an overview of Google Tag Manager.

Below are the slides from my session which will hopefully be streamed via Google Hangout 23rd June at 11:30am BST (see programme).

Share this post on:
| | |
Posted in Analytics, GDE, Google and tagged on by .

As part of some work I'm doing with the Open University around the OER Research Hub project I developed this high fidelity prototype which let users explore survey responses collected by the project (the short video below highlights the main features):

In the guest post I wrote on the 'OER Survey Exploratoratorium' I outlined the problem:

When presented with over 4,000 survey responses the challenge was how to let people explore the data set. When presented with this challenge the first thought invariably is what is the shape of the data. In this case the survey responses were collected in Survey Monkey. After considering options like consuming the data into the OER Impact Map via the Survey Monkey API, the overhead in terms of developing user interfaces and squeezing into a WordPress data structure resulted in the exploration of other options. The approach that looked like it would squeeze the most functionality out of little development time was to use Google Fusion Tables to host the data and put a visualisation layer over the top. The reason for choosing Fusion Tables is it allows a Guardian Datastore model of letting the user easily access and reuse the source data either in Fusion Tables itself or exporting into another tool. If you would like to peek at the data behind this there are two tables: one with the survey questions and another with the survey data.

I’ve extracted the main part of the code into this gist so you can get a sense of what’s going on. If this is something you are interested in doing yourself there is some documentation on the Google Visualisation API  for getting Google Fusion Tables. This page is has one example of how you can fetch data from Fusion Tables. It’s however worth noting that as Google Fusion Tables implements  the Chart Tools Datasource Protocol you can query the data as a datasource. This allows you to use the Google Visualization API Query Language with  SQL like syntax. The gist below is a reworking of this query example in the Google Code Playground which you can use to see the differences. The main one is how the query is set by specifying which table the data is from in the query. A couple of notes I have on using Google Fusion Tables as a datasource in this way are:

  • data returned limited to 500 rows. If you want more you can turn to the full Google Fusion Tables API  which has a separate SQL like query language. Using this API is rate limited and requires OAuth and/or API key. I got more than 500 by using LIMIT and OFFSET in my queries. (the full Google Fusion Table API is worth bookmarking for cross-referencing).
  • using back-quotes ` specified in the Visualisation API to wrap column names with spaces doesn’t appear to work. You do specify columns by their name rather that A, B C etc as used in Google Sheets. (The Google Fusion Tables API specifies single quotes which I don't think work in this scenario - this is an example of where cross-referencing helps)
  • Google Fusion Tables doesn’t implement the OR operator (related issue ticket marked Won't Fix. When I mentioned to Tony Hirst (@psychemedia) he suggested De Morgan's laws which would be an alternative)

Hope you enjoy and look forward to seeing you Google Fusion/Visualization mashups ;)

Share this post on:
| | |
Posted in GDE, Google, Visualisation on by .

7 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.

 

repeating seriesSometimes it’s useful to generate a column of data based on a series repeating x number of times e.g. a series 1,2,3 repeated 3 times would give 1, 1, 1, 2, 2, 2, 3, 3, 3 (see column A in here for example). In my particular scenario I want to repeat week numbers for a series from 0-6. There are a number of ways you can do this like indexing row numbers but here’s a little formula I quickly threw together for Google Sheets:

=TRANSPOSE(SPLIT(JOIN(",", ARRAYFORMULA(REPT(SPLIT(D2,",")&","D3))), ","))

where

  • D2 is a comma separated series e.g. Week 0,Week 1, Week …
  • D3 is the number of times to repeat

How does it work?

Like a lot of spreadsheet formula is starts in the middle with SPLIT(D2,",") which turns our series of values into an array. If you use this in a single cell in a Google Sheet the values Week 0, Week 1 will be split out across the columns.

Next we want to repeat Week 0 and so on x number of times. This is done with the a combination of REPT, which repeats a given string x times. If we use this by itself it will only apply to the first column of data from the SPLIT so we wrap it in an ARRAYFORMULA like so ARRAYFORMULA(REPT(SPLIT(D2,",")&",",D3))),",")). This repeats the series value the number of times specified in D3. Something to note is the &"," in the REPT. This adds a comma at the end of each repeated value.

ARRAYFORMULA Enables the display of values returned from an array formula into multiple rows and/or columns and the use of non-array functions with arrays.

This now gives us our columns with the repeating text but across several columns e.g. “Week 0,Week 0,Week 0”,  “Week 1,Week 1,We..” etc To get a single value in each column we use a trick of using a JOIN to turn our array of columns into a single cell value separated with a comma. We then use the SPLIT formula again to turn this single cell into multiple cells.

The final part is to use TRANSPOSE to convert our columns of data into rows. Here is the finished version of the Google Sheet with the stages broken down.

Can you think of a better way to do this?

Not sure if this is a documented feature but you can search within Google+ Communities by adding s/yoursearchterm to the community home address (operators like AND/OR also appear to work).

Emma Duke-Williams commented:

I have some students using a private community for group work, and they have to provide a list of posts for assessment.

The only way we'd found to search just that community was to scroll down the page to the start of the community -then use the browser's search feature, but that only found the posts they'd started, not the discussions. I have just tested this with my most prolific student & seem to have found both his posts & his comments.

I tried the ‘s’ switch in the url after seeing a Googler share this link which lists all the ‘Apps Script’ communities  https://plus.google.com/u/0/s/”Apps Script”/communities so it looks like it can be used elsewhere in the url.

I notice however that using this didn’t always return the expect result, which is worth bearing in mind.

Share this post on:
| | |
Posted in Google, GooglePlus, How-to on by .

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"). Also remember new Sheets has heavy formula caching so if using this technique for tracking templates you should add a reference to a cell you know will be edited to get the actual spreadsheet ID e.g. =image(getGABeacon("UA-48225260-1",A1))

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!

7 Comments

Mobile - TTRSS themeAfter my last post on ‘Thieving Feedly’, which got a lovely follow up on The Digital River, I mentioned that I’d uninstalled the Feedly Android app. That left a hole in my feed consumption ways which I think I’ve now filled. Below is a screenshot of how I’m now consuming my feeds. Those from the Google Reader good old days might recognise it. Yep if you can’t replace Google Reader then the best solution for me is to recreate it and this post outlines how I did it.

...continue reading