Archive for the 'Google Apps Script' Category

Visual Analytics: Comparison of @SCOREProject and @UKOER (and template for making your own)

Published on May 10, 2012 at 9:32 am in Analytics, Google Apps, Google Apps Script, R and Visualisation. 4 Comments By Martin Hawksey Tags: .
Share this post on: Google+ | Facebook | Twitter | LinkedIn

Lou McGill from the JISC/HEA OER Programme Synthesis and Evaluation team recently contacted me as part of the OER Review asking if there was a way to analyse and visualise the Twitter followers of @SCOREProject and @ukoer. Having recently extracted data for the @jisccetis network of accounts I knew it was easy to get the information but make meaningful was another question.

There are a growing number of sites like twiangulate.com and visual.ly that make it easy to generate numbers and graphics. One of the limitations I find with these tools is they produce flat images and all opportunities for ‘visual analytics’ is lost.

Click to see twiangulate comparison of SCOREProject and UKOER
Twiangulate data		Click to see visual.ly comparison of SCOREProject and UKOER
create infographics with visual.ly

So here’s my take on the problem. A template constructed with free and open source tools that lets you visually explorer the @SCOREProject and @ukoer Twitter following.

Comparison of @SCOREProject and @ukoerIn this post I’ll give my narrative on the SCOREProject/UKOER Twitter followership and give you the basic recipe for creating your own comparisons (I should say that the solution isn’t production quality, but I need to move onto other things so someone else can tidy up).

Let start with the output. Here’s a page comparing the Twitter Following of SCOREProject and UKOER. At the top each bubble represents someone who follows SCOREProject or UKOER (hovering over a bubble we can see who they are and clicking filters the summary table at the bottom).

Bubble size matters

There are three options to change how the bubbles are sized:

  • Betweenness Centrality (a measure of the community bridging capacity); (see Sheila’s post on this)
  • In-Degree (how many other people who follower SCOREProject or ukoer also follow the person represented by the bubble); and
  • Followers count (how many people follower the person represented by the node

Clicking on ‘Grouped’ button lets you see how bubble/people follow either the SCOREProject, UKOER or both. By switching between betweeness, degree and followers we can visually spot a couple of things:

  • Betweenness Centrality: SCOREProject has 3 well connected intercommunity bubbles @GdnHigherEd, @gconole and  @A_L_T. UKOER has the SCOREProject following them which unsurprisingly makes them a great bridge to the SCOREProject community (if you are wondering where UKOER is as they don’t follow SCOREProject they don’t appear.
  • In-Degree: Switching to In-Degree we can visually see that the overall volume of the UKOER group grows more despite the SCOREProject bubble in this group decreasing substantially. This suggests to me that the UKOER following is more interconnected
  • Followers count: Here we see SCOREProject is the biggest winner thanks to being followed by @douglasi who has over 300,000 followers. So whilst SCOREProject is followed by less people than UKOER it has a potential greater reach if @douglasi ever retweeted a message.

Colourful combination

Sticking with the grouped bubble view we can see different colour grouping within the clusters for SCOREProject, UKOER and both. The most noticeable being light green used to identify Group 4 which has 115 people people following SCOREProject compared to 59 following UKOER. The groupings are created using community structure detection algorithm proposed Joerg Reichardt and Stefan Bornholdt. To give a sense of who these sub-groups might represent individual wordclouds have been generated based on the individual Twitter profile descriptions. Clicking on a word within these clouds filters the table. So for example you can explore who has used the term manager in their twitter profile (I have to say the update isn’t instant but it’ll get there. 

wordclouds

Behind the scenes

The bubble chart is coded in d3.js and based on Animated Bubble Chart by Jim Vallandingham. The modifications I made were to allow bubble resizing (lines 37-44). This also required handling the bubble charge slightly differently (line 118). I got the idea of using the bubble chart for comparison from a Twitter Abused post Rape Culture and Twitter Abuse. It also made sense to reuse Jim’s template which uses the Twitter Bootstrap. The wordclouds are also rendered using d3.js by using the d3.wordcloud extension by Jason Davies. Finally the table at the bottom is rendered using the Google Visualisation API/Google Chart Tools.

All the components play nicely together although the performance isn’t great. If I have more time I might play with the load sequencing, but it could be I’m just asking too much of things like the Google Table chart rendering 600 rows. 

How to make your own

I should say that this recipe probably won’t work for accounts with over 5,000 followers. It also involves using R (in my case RStudio). R is used to do the network analysis/community detection side. You can download a copy of the script here. There’s probably an easier recipe that skips this part worth revisiting.

  1. We start with taking a copy of Export Twitter Friends and Followers v2.1.2 [Network Mod] (as featured in Notes on extracting the JISC CETIS twitter follower network).
  2. Authenticate the spreadsheet with Twitter (instructions in the spreadsheet) and then get the followers if the accounts you are interested in using the Twitter > Get followers menu option 
  3. Once you’ve got the followers run Twitter > Combine follower sheets Method II
  4. Move to the Vertices sheet and sort the data on the friends_count column
  5. In batches of around 250 rows select values from the id_str column and run TAGS Advanced > Get friend IDs – this will start populating the friends_ids column with data. For users with over 5,000 friends reselect their id_str and rerun the menu option until the ‘next_cursor’ equals 0 
    next cursor position
  6. Next open the Script editor and open the TAGS4 file and then Run > setup.
  7. Next select Publish > Publish as a service… and allow anyone to invoke the service anonymously. Copy the service URL and paste it into the R script downloaded earlier (also add the spreadhsheet key to the R script and within your spreadsheet File > Publish to the web 
    publish as service window
  8. Run the R script! …  and fingers crossed everything works.

The files used in the SCOREProject/UKOER can be downloaded from here. Changes you’ll need to make are adding the output csv files to the data folder, changing references in js/gtable.js and js/wordcloud.js and the labels used in coffee/coffee.vis

So there you go. I’ve spent way too much of my own time on this and haven’t really explained what is going on. Hopefully the various commenting in the source code removes some of the magic (I might revisit the R code as in some ways I think it deserves a post on its own. If you have any questions or feedback leave them in the comments ;)

Automatic translation of TAGS Twitter archives using Google Apps Script ‘Language’ services

Published on March 24, 2012 at 10:22 pm in Google Apps Script and Twitter. 4 Comments By Martin Hawksey Tags: .
Share this post on: Google+ | Facebook | Twitter | LinkedIn

A flare went up from Hamburg this morning from Tony Hirst who is at the Daten, Recherchen, Geschichten (2012) conference (#drg12) in Hamburg:

Translating tweets is something I’ve dabbled with before as part of experiments with my Twitter Subtitling tool iTitle (really must revisit this). Notable examples were the Google I/O 2010 Android Keynote and presentations from #UIMPUni20 talked about in Kirsty Pitkin’s Lost In Translation blog post (because of domain shuffles most of the links are broken but here is Professor Alejandro Piscitelli talk at #UIMPUni20 with tweets normailsed to English).

Both these examples used the Google Translate API to convert tweets from one language to another. Back then the API was free for anyone to use but in August 2011 Google switched it to a paid for service … doh. All is not lost though as Google Apps’ programming environment Google Apps Script still has a ‘Language Service’ <cough>Google Translate</cough>. The disadvantage you have with this service is unlike Translate it doesn’t have an option to autodetect the source language. [Update: Re-read documentation and clearly states it can auto detect. Probably still not a bad idea to use iso codes] This is not a problem when dealing with Twitter as the metadata includes ‘iso_language_code’.

So I have a solution to archive tweets in a Google Spreadsheet using Google Apps Script (must write more about v4) … ponder, ponder, 5 minutes later by adding:

objects[i]["text_en"] = LanguageApp.translate(objects[i]["text"], objects[i]["iso_language_code"], "en");

I’ve got a spreadsheet archiving #drg12 tweets and translating the text into English. Impressed much?

image

Fast-tracking feedback: Google Apps Script user interface training material [Release 2]

Published on March 18, 2012 at 10:57 pm in Assessment, Feedback and Google Apps Script. 2 Comments By Martin Hawksey Tags: .
Share this post on: Google+ | Facebook | Twitter | LinkedIn
Note: This is a personal post made outwith my current employment at JISC CETIS

Back in May 2011 Tony Hirst looked at the Visual UI Editor For Google Apps Script and commented that he thought before long I would have posted something about it. Well almost a year later here’s what I’ve got for you.  As part of the Fast-tracking feedback project (funded by the LSIS Leadership in Technology grant) with Loughborough College I ran a training session at the beginning of the month to help staff learn about user interface construction in Google Apps Script. The session follows on one of my earlier blog posts releasing some code to batch fill in Google Docs from a spreadsheet of feedback comments. As part of the session I produced a step-by-step guide for creating a Google Sites based form/gadget that could read and write data to a spreadsheet. As part of the project this guide is available for re-use using the link below. Before you download/use a couple of things worth bearing in mind:

  • the guide has been tweaked slightly for publication and as a consequence I may have inadvertently broken it. If you find something is wrong leave a comment in the document
  • in the guide an image is used to help you layout a form and is not intended to be part of the final navigation

Introduction to Google Apps Script: Custom Interfaces Guide

Using Google Spreadsheets to combine Twitter and Google Analytics data to find your top content distributors

Published on March 6, 2012 at 5:37 pm in Analytics, Featured, Google, Google Apps Script and Twitter. 13 Comments By Martin Hawksey
Share this post on: Google+ | Facebook | Twitter | LinkedIn

When you share a link on Twitter there are a number of services, like bit.ly, which allow you to track the impact of the url in terms of the number of clicks it attracts from other users. At the same time there are a number of ways to monitor people sharing links to your site, the most basic being using a Twitter search like this one for hawksey.info. Using these search results you could start extracting the follower information from the person tweeting, workout potential reach and so on, but wouldn’t you like to know, as with your own bit.ly account, how many visits someone else’s tweet generated to your site? Fortunately there is a way to do this and in this post I give you two tools to help you do it and look at why this information might be useful, but lets first look at how it is possible.

Referral Traffic

In August 2011 Twitter started automatically wrapping links over 19 characters in it’s own shortening service t.co, later in October started wrapping all links in t.co. When you start navigating around the web at each page you visit the server generally knows which page you came from (exceptions include direct traffic and when coming from https://). When you click on a t.co link the site can track where you came from, known as referral traffic (interestingly it looks like Twitter also bypass your url shortener of choice by following the redirects until it reaches a final destination and use that link in the t.co redirect). So when you click on a t.co link posted on Twitter I can detect that, that’s where you came from. Furthermore, each time a person tweets a link it gets wrapped in a unique t.co url even if that url has been shortened before with the exception being new style retweets. This means that when someone clicks on a link tweeted by someone I can trace it back to a single person.

Lets see how this works in practice. When I fire up my Google Analytics account and look at referral traffic I can see it’s dominated by t.co sources. Drilling down into the t.co data I can actually see how many visits each t.co link generated.

Referral source Referral path

Searching Twitter for say http://t.co/wEbXrPah allows me to trace it back to this tweet:

So we can say this tweet and it subsequent 5 retweets generated 42 visits to my blog. At this point you might be saying but this tweet above has a bit.ly link, there’s no reference to t.co. It may say bit.ly but underneath the hyperlink is t.co:

Tweet html source

For some this isn’t news. In fact, Tom Critchlow was writing about how Twitter’s t.co link shortening service is game changing – here’s why way back in August 2011. His post probably has a better explanation of what is happening and also includes a bookmarklet (appears broken after recent Analytics overhaul) which takes the t.co referral path from your Google Analytics report and searches it on Twitter to find out which persons tweet is sending you the traffic.

I thought this was a neat idea but wanted to get the full impact of seeing the visit count associated with a named person. I tried a couple of ways to inject the data using a bookmarklet with no joy so turned to Google Spreadsheets (and in particular Google Apps Script) to marry data from Twitter with Google Analytics. So I give you:

Method 1: Quick 7 day search

*** Twitter/Google Analytics Top Distributor Sheet v1.0 ***
[File > Make a copy for you own version]

With this Google Spreadsheet I can authenticate with my Google Analytics account which then allows me to extract t.co data using the Google Analytics Core Reporting API. I then pass each t.co link to the Twitter Search API to find out who tweeted it first. This is all wrapped in a custom formula getGATwitterRef(startDate, endDate, optional numberOfResults) which generates a table of results like this:

Twitter/Google Analytics Top Distributor Sheet v1.0

So big thanks go to Brian E. Bennett (@bennettscience) for generating 16 visits, Alberto Cottica (@alberto_cottica), @futuresoup and others for also generating traffic. But who generated the 14 visits? The reason this row is blank is while the link is still generating traffic to my site the link was first tweeted over 7 days ago meaning it is outwith the capabilities of the Twitter Search API.

What a terrible shame never mind 9 out of 10 isn’t bad … ah but hold on I’ve got a Google Spreadsheet template that can archive Twitter searches (TAGS). So I give you:

Method 2: TAGS v4.0 with Google Analytics integration

*** TAGS v4.0 with Google Analytics integration ***
[File > Make a copy for you own version]

So by using a search term for your domain you can start collecting ten of thousands of tweets over months and years, which you then query against your Google Analytics data.

‘What the flip’ you might be asking. Here’s the explanation. By using the same search query at the beginning of this post for all tweets containing ‘hawksey.info’ (and because Twitter is wrapping everything in t.co it knows these even if they began life as a bit.ly or goo.gl) I can build up a corpus of tweets containing links to my site. If you also look at this archive I’m building you’ll see in the column labelled ‘text’ there is not a bit.ly or goo.gl in sight, all the links are t.co.

So all I need to do is extract a t.co referral path I’m interested in from my Google Analytics data and find who first tweeted it in my archive giving me the number of visits that link generated.   

So now I can say thank you @BillMew for the mystery 14 visits, thank you @TweetSmarter for the 38 visits generated from your tweet last month (you’ll see some #N/A I think because my collection went offline for a bit, working sweet now)

TAGS v4.0 with Google Analytics integration

Why

Whilst there could be a seriously creepy side to this (lets not forget people like Google have made serious bucks knowing where you go and what you share), there are a couple of reasons why I was interested in following up this concept. One was in relation to the Learning Registry/JLeRN experiment (background info here) which is trying to create a framework beyond metadata to include activity/paradata around resources. The idea is this data can be used to provide feedback and improve discoverability of resources. So potentially you’ve got some rich data to push into a node .. errm I think, maybe #haventplayedwiththespecyet

Another thought was during the OER Visualisation project I discovered that social sharing of resources appears, insert caveats, to be rare. If you could recognising and reward the people pushing content it might encourage them to distribute more (and as I highlighted in How do I ‘like’ your course? The value of Facebook recommendation there is real and measureable value in people distributing information through their networks) – the flip side to this being as soon as you start measuring something someone else will start gaming the system.

There’s also a degree of profiling you could do. If someone ends up at your resource having clicked on a linked shared by A that person may have something in common with them so you could target additional resources to them based on what A might like.

I’m sure there are others. As I’ve shared the tools to do this it’s only fair that you share your ideas in the comments.

But there is potentially more …

Data Feed Query ExplorerI will leave you with one last thought. I haven’t mentioned much about the code, which is available and open source (my bits anyway) via the Script Editor. To help construct the Google Analytics query I used the Data Feed Query Explorer. Here’s a permalink to the main query structure I used. If you open the link and hit the ‘Authenticate with Google Analytics’ button choosing one of your own analytics ids you can see what data comes back. I’ve been conservative only pulling what I need but if you click on the ‘dimensions’ box you can see I could also be pulling where the visits were coming from, time of day, and more. All potentially valuable intelligence to give you a picture of how a resource is being shared if you can unlock it of course.   

Introducing a RSS social engagement tracker in Google Apps Script #dev8d

Published on February 15, 2012 at 5:36 pm in Analytics, Google Apps Script and Social Networking. 4 Comments By Martin Hawksey Tags: .
Share this post on: Google+ | Facebook | Twitter | LinkedIn

For my session at Dev8D I got delegates building a RSS social engagement tracker similar to PostRank (Slides here) [Note to self: Too much coding for the room. Doh!]. Initially I was going to use my Fast-tracking feedback example for this session but forever wanting to make my life difficult decided late on to come up with something entirely new. Part of this decision was influenced by being featured in Mashable’s  5 Essential Spreadsheets for Social Media Analytics (yeah), combined with the fact that my PostRank daily email notifications are broken.

For those not familiar with PostRank their service (now owned by Google) would allow you to enter a blog RSS feed and then they would monitor ‘social engagement’ around that feed recording tweets, likes, saves etc.

Here is my solution that does something similar:

*** FeedRankSheet Google Spreadsheet v0.1 ***

This is still very beta and doesn’t entirely work as I’d like. I’ll be making improvements based on your feedback ;)

How to use it

Enter the RSS feed of the posts and comments you want to track, blog address and optionally a comma separated list of Twitter usernames you want to remove from the search. Then open the Script Editor and Run doFeedRank (you’ll need to authorise), finally add a trigger to run daily.

What it does

Example output [click to enlarge]Each time the script runs it gets the latest share counts for posts via the sharedcount.com, combines it with the comment feed and Twitter search results and generated an email to send to designated people (click on image for example output).

How it works

Most of the script is just data reading and writing. The clever bit is using Google Sites pages as a template for the email. Here is the page for the email wrapper and this page is the post share count template.

The thing that really surprised me is that SitesApp.getPageByUrl can get any public Google Sites page allowing you to do Page calls like .getHtmlContent() even if you don’t own it.

Things to improve

  • Exceeding maximum execution – I might need to optimise the code as I was getting timeouts when running as a trigger.
  • Deltas – It would be useful to include individual share count increases on daily updates (eg Twitter 9(+3))
  • I also have a sneaking suspicion that reading the posts from the spreadsheet rather than accessing the raw feed xml using apps script might be a problem. I need to run over a period of time to get data.

Dev8D Introduction to Google Apps Script Session: This is what you’ll be making (well the start of)

Published on February 15, 2012 at 9:29 am in Google Apps Script. 0 Comments By Martin Hawksey Tags: .
Share this post on: Google+ | Facebook | Twitter | LinkedIn

Today at Dev8D from 3-4pm participants of my introduction to Google Apps Script workshop will be making the beginnings of a PostRank style social engagement tracker (example report embedded below). I’ll be releasing slides and code after the session (Slides here and full code here).

Integrating Google Spreadsheet/Apps Script with Google Refine to update existing spreadsheets

Published on January 31, 2012 at 11:36 pm in Google Apps Script and Google Refine. 2 Comments By Martin Hawksey
Share this post on: Google+ | Facebook | Twitter | LinkedIn

Working on some OER Visualisation Project work today I found I needed to get some additional information from an external web service for a Google Spreadsheet I was working on. I could have of course just used Google Apps Script, a native feature within Google Spreadsheets, to do a UrlFetch and get the information but my source data was a bit messy and timeouts on Apps Script prevent you from getting lots of data in one hit. Instead it made more sense to download the data into Google Refine, clean it up, call external web services to get additional information, then somehow get it back into the original spreadsheet.

As I recently posted Integrating Google Spreadsheet/Apps Script with R: Enabling social network analysis in TAGS it wasn’t much of a cognitive leap to try a similar trick of publishing the spreadsheet as a service to allow a basic API write. In the R example, however, I used the POST method to pass data and whilst I’m pretty sure with the right Jython libraries you could do something similar in Refine, I wanted to keep it simple so here’s my method for using GET instead.

  1. In a Google Spreadsheet open Tools >  Script editor.. and paste the following code (there is some tweaking required to where you want the data to go and the secret passphrase – in this example I want to pass back a value named geo 
  2. Once tweaked Run > Setup (this get a copy of the spreadsheet id need to run as a service)
  3. Open Share > Publish as service..  and check ‘Allow anyone to invoke’ with ‘anonymous access’, not forgetting to ‘enable service’. You’ll need a copy of the service URL for later on. Click ‘Save’
    Publish as service
  4. Back in Google Refine select a column with some data you want to pass and choose Edit column > Add column by fetching URLs…
  5. The expression you use will be something like the one below where we have the service url from step 3, the ‘secret’ to match the one set in step 1 and what ever data you want to include 
    "https://docs.google.com/macros/exec?service=AKfycbw1VL_kNYEwhqELgccMgEvM9gJ3t1zhBr6bhLs84g&secret=lemon&idx="+cells['idx'].value+"&geo="+escape(value,"url")
    Add column dialog

Note: You’ll see from the script my import included an idx number which matched a row number, but as long as I didn’t add any additional rows or change the order I could have used row.index in the expression instead.

You also might want to play around with the throttling. I had problems with my first go because this was either set too low or my values weren’t url encoded.

Instead if anyone finds this useful or if they show how to do in Jython. The amount of Google Spreadsheet work I do I’m sure I’ll use this trick again ;)  

Integrating Google Spreadsheet/Apps Script with R: Enabling social network analysis in TAGS

Published on January 26, 2012 at 6:29 pm in Analytics, Google Apps Script, R and Social Networking. 8 Comments By Martin Hawksey
Share this post on: Google+ | Facebook | Twitter | LinkedIn

Increasingly I find myself creating Twitter hashtag archives using my TAGS Google Spreadsheet template as a means to identify who in that community has the most influence and ultimately use this intelligence to target people that might be able to help me disseminate my work. Marc Smith at the Social Media Research Foundation has a useful overview on ‘How to build a collection of influential followers in Twitter using social network analysis and NodeXL’.

I don’t go to the extreme of seeking people to follow and gaining influence with retweets, I usually just follow interesting people who follow me, but the post introduces the important concept of:

  “betweenness centrality” – a measure of how much a person acts a bridge between others.

(betweenness centrality (BC) was a big turning point in my interest and understanding of social network analysis, a moment captured by Sheila MacNeill)

To date the only way I could calculate BC on an archive of tweets was to download the data to my desktop, run it through NodeXL and review the data. This isn’t ideal as the data becomes orphaned. I have experimented with calculating BC using Google Apps Script using a modified version of some PHP code put together by Jonathan Cummings, but kept hitting timeouts before I could get anything back.

I forgot about pursuing this angle until that is I saw Tony Hirst’s A Quick View Over a MASHe Google Spreadsheet Twitter Archive of UKGC12 Tweets in which he uses the statistical computing and graphing tool ‘R’ to read a spreadsheet of archived tweets and produce some quick summary views (I highly recommend you read this post and also check the contribution from Ben Marwick in the comments). Reading this post made me think if it is that easy to read and analyse data using R could you also not somehow push the results back.

Fortunately, and I do mean fortunately, I have no experience of R, R Script, R Studio (I like having no preconceived ideas of what new tools can do – it far more rewarding to throw yourself into the unknown and see if you make it out the other side), but I do know a lot about Google Apps Script giving me a destination – just no way of getting there.

The idea, I think, is ingeniously simple. Read data, as Tony did, process it in R and then using Apps Script’s feature to be published as a service to simply POST the data back to the original spreadsheet.

As that is quite complicated I’ll recap. Fetch a Google Spreadsheet as a *.csv, do something with the data and then push the data back in the same way that you post a web form (and if you skipped the link the first time POST the data back to the original spreadsheet).

Having sunk a day of my own time (and it is my own time because I get paid for the OER Visualisation project for the hours I work on it), I’m not going to go into the details of how to setup R (or in my case RStudio) to do this – hey I learned it in a couple of hours so can you – instead I’ll give you the bits and pieces you need and general instructions.  Before I start you might want to see if the result is worth it so here’s a sheet of SNA stats for the #ukgc12 archive.

SNA Stats 

Playing with some test data

To make it easier I start with a partially complete dataset. The scenario is I’ve got my archive and run options 1-3 in the TAGS – Advanced menu to get an Edges sheet of friend/follower information.

  1. Open this Google Spreadsheet and File > Make a copy (this is a fully functioning – if I haven’t broken it of the next version of TAGS so if you clear the Archive and setup you can start collecting and using this with your own data).
  2. Once you’ve copied select File > Publish to the web and publish the spreadsheet
  3. In the new spreadsheet open Tools >  Script editor.. and Run > Setup (this get a copy of the spreadsheet id need to run as a service – in the normal scenario this is collected when the user authenticates the script with Twitter)
  4. Open Share > Publish as service..  and check ‘Allow anyone to invoke’ with ‘anonymous access’, not forgetting to ‘enable service’. You’ll need a copy of the service URL for later on. Click ‘Save’
    Publish as service
  5. Back in the script editor on line 57 enter a ‘secret’ – this will prevent anyone from uploading data will in anonymous mode (you can choose to only enable the service when required for extra security.
  6. Open your install of R and load a copy of this script.
  7. There are four things to edit in this script
    1. key – spreadsheet key, the bit after https://docs.google.com/spreadsheet/ccc?key= and before the &hl… junk
    2. gid – the sheet number of the Edges sheet, unless you insert/use a different sheet should always be 105 for a TAGS spreadsheet
    3. serviceUrl – the url you got in step 4
    4. secret -  the same secret you entered in step 5
  8. You might also need to install the packages used – most of them are standard but you may need to get igraph – used to get all social network data
  9. Run the R script – it may take some time to read a write to Google Spreadsheets so be patient

That’s it. If you go back to the spreadsheet (you may need to refresh) the SNA Metrics and Vertices sheets should be populated with data generated from R

The Apps Script Magic

Here’s the Google Apps Script snippet used to handle the data being pushed from R:

I’ve commented most of it so you can see what is happening. While Apps Script has a debugger which lets you monitor execution and variables it can’t intercept the POST so I used the original POST/GET code to dump the data into some cells then tweaked the script to read it from there to work out what needed to be done.

Final thoughts

I think this is a powerful model of reading selected, processing and then uploading data back to the source. I’m also only using the very basics of igraph and sure much more could be done to detect neighbourhoods, clusters and more. Also I wonder if more of the friendship data collection could be done in R with the TwitteR – (you R people really know how to make it hard to find info/help/support for your stuff ;) Right now I can get friend/follower info for a list of 250 users.

The intriguing aspect is just how much data can you push back to Apps Script and as there is a long list of Services could you also handle binary data like chart images (perhaps down the Blob and then Document Service route, or maybe just straight into Charts).

I welcome any comments you have about this technique and particularly value any feedback (I’m not a SNA expert so if there are errors in calculation or better measures I would welcome these)

Sending free SMS messages to students by mashing up Google Calendar notifications

Published on January 22, 2012 at 8:51 pm in Google Apps, Google Apps Script and Mashup. 2 Comments By Martin Hawksey Tags: .
Share this post on: Google+ | Facebook | Twitter | LinkedIn

Finding free ways to send and receive SMS messages is something I’ve covered before (Twitter Ye! Twitter Ye! Keep your students informed with free SMS text message broadcasts! and Free SMS voting using intelliSoftware SMS Gateway service). Browsing the Google Apps Script site I saw there is a new tutorial by Romain Vialard which lets you Link a Gmail Filter to Google Calendar SMS Notifications in which it “shows how to create a specific filter in Gmail and be notified by SMS when you receive an important email”. This got me thinking if there was a way of using this feature to send free SMS updates to students, say for example, when they they received some Fast-tracking feedback.

This solution needs the student to configure their calendar for SMS notifications and not all mobile operators are supported, but I will show how to make this work and you can decide if its worth the effort.

Student side setup

To enable SMS updates all the students need a Google Calendar and to have enabled SMS notifications for event invites (you may want to tell students about this anyway just so they can get SMS notifications for event reminders). This is done by:

  1. Google Calendar Settingsopening Google Calendar going into ‘Calendar settings’ from the cog icon top-right
  2. on the Mobile Setup tab the student needs to verify their mobile number (the supported UK providers are: 3; O2; Orange; T-Mobile; Virgin Mobile; and Vodafone – full international list here), then click ‘Send Verification Code’
  3. once a code has been received enter it in the ‘Verification code’ field and click ‘Finish setup’
  4. this then takes you to the ‘Notifications’ settings for your default calendar. In the New events row tick the SMS checkbox (you might want enable other SMS services), then click ‘Save’
    Google Calendar Notification Settings

Sending SMS notifications manually

To send a message to students open your Google Calendar and create an event (you might want to create a new calendar so you don’t mess up your own schedules – creating a new calendar and creating events using your current date/time also means you have a record of when a message was sent – SMS notifications appeared to only be sent for events in the future). The fields included in the SMS are the event title, location and date/times. Here’s an example message recieved:

 Example SMS notification

In the ‘Add guest’ field paste in a comma separated list of student email addresses (these addresses need to be associated with the student’s Google Calendar so if you are not using Google Apps for Education some prep is required to get this list) and click ‘Add’. I would also recommend making sure the guest options to modify, invite and see a guest list are unticked.

When you save the event shortly afterwards students should receive the SMS message and an email similar to the one shown below:  

Example email notification

You might want to agree a system with your students where they have to confirm they have seen it by clicking ‘Yes’ in the ‘Going’ option. This will give you a summary of who has seen it in the event details. You may want to utilise other Calendar features like notes.

 guest list

Using Google Apps Script to automatically send SMS notifications

This whole idea came from a Google Apps Script tutorial so it only seems fitting to show how this could modified to automate the guest list creation, which in turn would trigger the SMS notification. I’m not going to go into any detail about what Apps Script is but basically as one of its main uses in to automate tasks it’s fairly easy to write a script that will take a list of email addresses from a Google Spreadsheet, create a new event in your calendar adding those addresses as guests. And here it is:

For this to work all you need is a column of email addresses associated with personal Google Calendars and within seconds your sending  free SMS messages to people.

So what do you think, a viable solution or is student side setup/concerns over network coverage going to prevent you using it? 

Fast-tracking feedback using Google Apps Script [Release 1]

Published on January 17, 2012 at 8:45 am in Assessment, Feedback and Google Apps Script. 2 Comments By Martin Hawksey Tags: .
Share this post on: Google+ | Facebook | Twitter | LinkedIn

In October last year the Sport Learning Technologists at Loughborough College successfully won funding from the LSIS Leadership in Technology (LIT) grant scheme for the Fast-tracking feedback using Google Scripts project. Here’s an extract from the project summary:

This project will effectively combine Google Apps for Education and Google Apps Script in order to create a tool which allows tutors to enter grades and feedback in a single spreadsheet which then automatically populates individual feedback proforma, simultaneously sharing these results with students, progress tutors, and administrators as appropriate.

The benefit will be an increase in the efficiency with which assessment feedback can be shared, improving the speed and quality of paper-less student feedback. A successful conclusion to this project will be demonstrated by reduced submission turnaround times and a reduction in the errors brought about by inconsistencies in data entry.

Project funding is not just for deploying technology but also increases the capacity within the organisation at the operational level. With this in mind I have been working with Loughborough, helping them in the technical aspects of developing the Fast-Tracking Feedback System and also learn about Google Apps Script via a series of workshops. Friday was the first of these and I thought I’d share the story so far.  

The Loughborough group had already got of to a flying start successfully modifying My #eas11 world premier: Creating personalised Google Documents from Form submissions. 5 months is a long time in Google Apps Script and since then not only is there some new functionality in Apps Script, but I’ve also picked up some new tips. My own understanding has come on along way thanks to receiving a preview copy of Google Script: Enterprise Application Essentials by James Ferreira [due out 27th January]. I’ve been a regular visitor to James simpleappssolutions.com site and tutorials so wasn’t sure if his book would teach me much more, but how I was wrong. Part of the reason I believe for this is the book is geared towards ‘enterprise applications’ so concentrates on documents and workflows, just as assessment in education (for better or worse) is concentrated on documents and workflows.   

So below are two links of the current version of the Google Apps Script Spreadsheet and example Document template followed by a quick video to show how it is used. Obviously these are still work in progress as there is still 6 months to run on the project but there’s already enough there for others to benefit from and perhaps feedback on design.

Stay tuned for more developments

About

This blog is authored by Martin Hawksey+ JISC CETIS Learning Technology Advisor (OER Programme Support)
jisc cetis logo

The MASHezine (tabloid)

It's back! A tabloid edition of the latest posts in PDF format (complete with QR Codes). Click here to view the MASHezine

Preview powered by:
Bluga.net Webthumb

The MASHebook

You can also download this post as:

Subscribe to monthly email digest of posts

Loading...Loading...


Subscribe to per post email updates

Enter your email address:

Delivered by FeedBurner

Creative Commons Licence
This work is licensed under a Creative Commons Attribution 3.0 Unported License. CC-BY mhawksey