1 Comment

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.

 

Back in the good old days when I was a member of the Glasgow based supergroup with my then colleagues Lorna Campbell and Sheila MacNeill we were approached to write a chapter for the soon to be published ‘Reusing Open Resources’.  We were tasked with writing something on ‘Analytics for Education’. Prior to print our chapter along with four others have been published in the Journal of Interactive Media in Education (JiME) under a CC-BY license. You can read the full Analytics in Education chapter here and copied below is the section I had most input on was ‘future developments’.

Given ‘prediction is very hard, especially about the future’ its interesting to look back at what we wrote in the summer 2013. Something we should have perhaps expanded upon was data privacy concerns particularly in light of the news that news that  non-profit inBloom is shutting down. I often find myself with conflicted interests between data collection as part of my personal quantified self and data collection for quantifying others. TAGS is a prime example of where I initially wanted to collect data to understand the shape of the communities I was in, but now is used by myself and others to extract data from communities we have no investment in.

And right now I'm developing the next iteration of ocTEL which thanks to funding  from the MOOC Research Initiative has helped find areas where we can improve data collection, in particular, resolving identities across networks. Achieving this personally feels like progress but I’m sure many others will disagree.

Are we bound by a data dogma? ...continue reading

Share this post on:
| | |
Posted in Analytics, Half baked on by .

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

This post originally appeared in the ALT Online Newsletter on 10th March 2014. You subscribe ALT Online Newsletter RSS feed or subscribe to the ALT Online Newsletter via email

Martin Hawksey -ALTI’ve been an ALT Individual Member since 2010 but having started my career in learning technology at Glasgow Caledonian University in 2004 and sharing my
workplace with the likes of Linda Creanor and Terry Mayes the Association has been known to me for a lot longer. I joined ALT when I was working in the Jisc RSC Scotland North & East and in my day-to-day work, supporting colleges and universities in the region, I started to realise the importance of communities and connections. ALT was the obvious place for me to start making new connections with both people and ideas, learning from my peers and sharing that new knowledge with my own networks.

Last week I had the pleasure of becoming an ALT employee. The role I’ve taken on is Chief Innovation, Community and Technology Officer. I find this title a bit of a tongue twister (and if honest slightly daunting) but I do like how it captures different aspects of the role and mirrors the very reason I became an ALT Member, to be part of a community which not only has an interest in technology but also routinely demonstrates a desire to improve, develop new ideas and provide new and better experiences for learners … to innovate.

I’ve been fortunate over the years to have worked closely with ALT staff and members on a number of projects. From mashing up the ALT-C 2010 keynotes with a Twitter subtitle track to more recently developing ALT’s Open Course in Technology Enhanced Learning (ocTEL) platform. One of the main things I take from these projects, particularly my later work, is the dedication of members who willingly donate time and expertise in both the running of ALT and special projects like ocTEL (if you’d like to help this year fill in this form, or if you’d like to keep informed register here). I would encourage members to seriously consider putting forward their names for the governance roles within ALT.

But what will I actually do at ALT? Well given ‘Technology’ features in the Association’s title (I’ve been in meetings where this itself creates debate) it seems fitting to explore how ‘technology’ can enhance membership benefits. This includes member services, online events, communications and internal systems. Given the wide scope I’ll be working closely with the ALT Staff Team and committees working together to enhance what we do. As part of this we’ll be focusing on community development, providing and enhancing the ways in which members connect. The world has changed in the 20 years since ALT was founded and there are new opportunities to help ALT Members make those important connections.

Aligned to ALT’s values where possible we’ll be doing this openly and transparently. As part of this I’ll be doing a monthly update here so hopefully you can play along too.

Share this post on:
| | |
Posted in ALT on by .

6 Comments

backchannelOne of the uses of Twitter that caught the edtech community’s imagination early was the backchannel used for audience conversation, broadcast / amplification or just tweckling. As a presenter there are various ways you can use the backchannel such as directing discussion and highlighting example responses or monitoring on a second screen. When I’ve presented in the past I’ve struggled to check the backchannel whilst speaking but will try to weave video clips into my presentation as an opportunity to take stock.

An idea I'm keen to try for my next presentation is listening to the backchannel. Similar to the idea of TV presenters receiving directions from the production crew this might be a way to shape what you are saying based on what is being said at or about you. There are a couple of ways you could do this but the method I've found easiest to do is using Twitter SMS alerts and a ‘read aloud’ SMS notifier on my phone.

Click for your Twitter Device SettingsTwitter SMS messaging was something I wrote about in 2008 and essentially it’s the same concept, using mobile SMS notification. There are some limitations such as the Twitter supported mobile carriers and what you can be notified about. Since SMS notifications are not possible from search results you would have to reply on either tweets that mention you and/or people you've selectively enabled mobile notifications from. You could set this up in a number of ways. For example, you might ask a colleague in the audience and/or remote to give you feedback as you present.

Talking SMS settingsThe last step is to hear the message. I played around with a couple of Android Apps to do this and the best one I found was Talking SMS and Caller ID by Bit Studio Ltd (£1.50). This has a very small file size and unlike other apps which needed the phone to be awake to work Talking SMS happily runs in the background while your phone is in standby.  I should say I tried using Android’s TalkBack accessibility settings but couldn't find a way for it to automatically read the entire message.

So the next time you see me present and I've got a headphone in one ear and a confused face you'll know why. So do you think it'll work? Will you be trying this recipe yourself? s this easier to setup on iPhone?

Share this post on:
| | |
Posted in Idea, Twitter on by .

6 Comments

I’ve mentioned the appear.in service a couple of times. This allows you to convene small meetings (up to 8 people) with video, voice and chat without the need for logins or additional browser plugins on both desktop and mobile (my quick video demo here). Today I got an email from appear.in saying

Get notified when someone enters your room!

We have now made it even easier to start a video conversation. When someone enters your appear.in room, you will receive a desktop notification that you can click to enter the room.

How can you use notifications?

  • Get notified when someone shows up for a meeting
  • People who want to talk to you can just go into your room
  • Make sure everyone on your team is alerted when your team meetings start

Read more on our blog.

Rooms you followNotifications work using a Chrome extension, but once you have this installed to can monitor multiple rooms.

So if you were wanting to run remote tutor support hours you could claim an appear.in room and enable notifications. Once you advertise your office ours you can monitor the room, get on with other work and wait for notification.

Because appear.in allows you to ‘lock rooms’ if you are providing one to one support you can prevent someone else ‘walking in’.

The awkward bit is handling the locked room. There is no queuing service and anyone visiting a locked room will be presented with the message below. Unfortunately if someone visits a locked room, sees the locked message when the message doesn’t go away when the room is unlocked.

Locked room

A way around this might be to have two rooms – corridor and office. The corridor room would always be open. As people arrive in the corridor room you could greet them and invite them to your ‘office’ and lock the office during consultation. Once done you could go back to the ‘corridor’ room if anyone else is waiting. If the ‘corridor’ gets busy (more than 7) you’ll have to sit in it yourself or lose the ability to enter (unless as an owner you get priority).

[Writing this it’s all sounding very faffy. I’d imagine you could do something similar with Google Hangouts but I love the fact appear.in requires no login. What do you think?]

Share this post on:
| | |
Posted in Feedback, Half baked, Mashup on by .

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.

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!