Google

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.

 

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")

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

4 Comments

Recently

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

2 Comments

Google recently (1st October 2013) announced improved segmentation in Google Analytics using age, gender and interests

It been interesting to read some of Tony Hirst’s posts on the use of Google Analytics within education. The thread goes back to 2008 with Library Analytics but most recently Tony has writing about this in an open course context such as MOOC Platforms and the A/B Testing of Course Materials and MOOC Busting: Personal Googalytics… which looks at the  idea of collecting and feeding back performance data to users from across platforms.

When Daphne Koller was on the early Coursera push one of the aspects that caught my eye was using student generated data (aka their answers to questions) to in course design, in particular, to identify misconceptions or incorrectly set questions. To see what I mean here’s a cued clip from a presentation Koller gave at the Centre for Distance Education back in 2012.

YouTube: The Online Revolution: Education at Scale

Merging the two lines of thought I wondered if there was a way you could use Google Analytics to create a similar feedback mechanism. My starting point was Google Analytics Event tracking. From the Event Tracking documentation:

Event Tracking is a method available in the ga.js tracking code that you can use to record user interaction with website elements, such as a Flash-driven menu system. This is accomplished by attaching the method call to the particular UI element you want to track. When used this way, all user activity on such elements is calculated and displayed as Events in the Analytics reporting interface. Additionally, pageview calculations are unaffected by user activity tracked using the Event Tracking method. Finally, Event Tracking employs an object-oriented model that you can use to collect and classify different types of interaction with your web page objects.

Examples include:

  • Any Flash-driven element, like a Flash website, or a Flash Movie player
  • Embedded AJAX page elements
  • Page gadgets
  • File downloads
  • Load times for data

Essentially anything you can trigger with a bit of JavaScript is up for grabs. Looking at setting up event tracking each event can include:

  • category (required) - The name you supply for the group of objects you want to track.
  • action (required) - A string that is uniquely paired with each category, and commonly used to define the type of user interaction for the web object.
  • label (optional) - An optional string to provide additional dimensions to the event data.
  • value (optional) - An integer that you can use to provide numerical data about the user event.
  • non-interaction (optional) - A boolean that when set to true, indicates that the event hit will not be used in bounce-rate calculation.

So we can capture events and have some control over how they are described. What might we want to catch? Lets start by looking at was multiple choice questions (MCQs). Looking at the anatomy of an event this is one way we might want to encode it:

  • category: ‘MCQ’
  • action: ‘right’ or ‘wrong’
  • label: a question identifier. This needs to be unique and might be something like coursecode_module_section_question (having a consistently structured label will help filter the data later)
  • value: this is optional but as it needs to be an integer this restricts you a bit. You may want to use time taken to respond, confidence based mark etc.

I should say before you get carried away with tracking that:

The first 10 event hits sent to Google Analytics are tracked immediately, thereafter tracking is rate limited to one event hit per second.

To see how this works I’ve created this example page with a simple MCQ. This is a ‘live’ example with some crude code to push events to my Google Analytics account. You’ll notice on the page a response graph generated from the GA data. I’ll explain how that was made later.

GA Real-time eventsThe fist thing to note is that we can now see responses in real-time via the Google Analytics admin interface. The interface is not really geared for MCQs and there is a complication of who has access to the Analytics dashboard, but given that there is a Real Time Reporting API in beta a custom slice’n’dice should be possible in the future (I’ve got beta access so this might be one I revisit if/when Events get added to the API).

Similarly the Content Events report gives us access to historic data but again it has accessibility issues in terms of who has access to the Google Analytics account. On the plus side tweaking the display from the default ‘data’ view  to ‘performance’ gives a basic bar chart which is more intuitive for this type of data.

Default data view for Content > Events
Default data view for Content > Events
Performance data view for Content > Events
Performance data view for Content > Events

Segmentation and cohort analysis

There are some other built-in Google Analytics features that may also support analysis of the data including filtering:

GA Filtering

or switching from a ‘line chart’ to a ‘motion chart’ (there are limits on what can be used for x-y values so some experimentation is required) and adding event reports to custom dashboards which may pull in other GA data.

GA Motion Chart

This is where is potentially get even more interesting as the new Google Analytics Advanced Segmentation* allows you to do cohort analysis. The built-in segments are perhaps not relevant for this scenario but the custom options have lots of potential. Google provide 6 segment templates for ‘Demographics’, ‘Technology’, ‘Behavior’, ‘Data of First Visit’, ‘Traffic Sources’ and ‘E-commerce’ but it is easy for you to add custom conditions and sequences for segmentation.

GA custom conditions and sequences for segmentation

*I’m not sure if Google are still following this out but noticed the new UI and segmentation options were only available in my Google Apps GA account, my standard @gmail account not having this option.

Examples of conditions/sequences you might want to explore include combining Tony’s suggestion of using Analytics A/B testing with event tracking e.g. identifying any correlation with content to performance or if someone visiting page x did they perform better in the test. It is also worth noting that:

Previously, advanced segments were based on visits. With the new segment, a new option is provided to create user segment. In a user segment, all visits of the users who fit the segment criterias will be selected (such as specific demographics or behaviors). It will be a useful technique when you need to perform user level analysis.

This is particularly useful as “Google Analytics customers are prohibited from sending personal information to Google.” [ref]. So while named individual level analysis isn’t possible you can get down to a user level.

Distributing data

On a practical level whilst these options potentially open some interesting avenues for exploration Google Analytics account administration is still not easy. Whilst this area has been recently improved the granularity of permissions is very course, an all or nothing approach. There is a growing list of tools/add-ins that integrate with Google Analytics which let you create custom workflows for data distribution. This is an area Google appear to be working on recently announcing the Google Analytics superProxy which is a  web application that runs on Google App Engine to allow the distribution of GA data.  This uses the Google Analytics Reporting APIs to define data queries and generate data files. Along similar lines (and announced before Google) I’ve published a similar solution that works in Google Drive (Using Google Spreadsheets as a Google Analytics Data Bridge). Below is an example query I using the the MCQ example at the beginning of the post. It's currently using a very specific filter to exctract the data for all the event labels beginning EMD101_Mod1_1.1_Q1_, but if using a standardise labeling you could include results for the entire module or course. I'm also not using an segment filters. As well as using standard segments you can also use custom segments

Google Analytics Query ExporterAs I outlined in my original post there is a number of ways that these slices of Google Analytics data can be shared or consumed into other tools. In the example above the data is written (and refreshed every hour) to the sheet below, Google Sheets providing a convenient environment for sharing and querying data with the relative familiarity of a spreadsheet interface.

At this point I’d imagine some of you are wondering why go through all of this bother when your VLE is able to do similar, if not better, levels of reporting. My eye is primarily on the open education context where the institutional  VLE is usually not and option. It also potentially provides a more holistic data source where you can experiment with content and resources across your little oasis (like ocTEL).

So what do you think? Will you be event tracking your MCQs?