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?

1 Comment

I'm not entirely sure what this post is. I started writing it on the train down to altc2013 and think it lost its focus between York and Sheffield. Essentially I wanted to write this to highlight some of the benefits of using BuddyPress as a way to capture user activity streams but at the same time some of the challenges of achieving an integrated experience using WordPress … I'll let you decide it's value and please feel free to comment (the ‘dirty code’ post will be a lot better).

For the last couple of week I’ve been working on the altc2013 conference platform. In previous years the Association for Learning Technology (ALT) had used CrowdVine to create a conference environment which allowed delegates to connect and communicate. This worked well but had a number of data bottlenecks making administration difficult. This combined with the knowledge that ALT members already have very rich networks on other sites including their own blogs and social network sites like Twitter, it was decided to opted some of the connectivist principals used in the ocTEL. The result, hopefully, is a site that replicates the CrowdVine experience with  several additional key features. These include:

In this post I’ll outline the general recipe used in the altc2013 platform, how interoperability was achieved between some existing WordPress plugins (saving the code heavy post for a latter date).

Core plugins

Starting with a self-hosted version of the blogging platform WordPress four key plugins are the basis of the site:

  • Conferencer – used to manage programme and session information
  • BuddyPress – social networking platform
  • FeedWordPress – used to pull delegate activity from 3rd party blogs and sites
  • MailPress – for daily newsletter distribution and management

The common issue when you stray away from WordPress as a blog to WordPress as a ___ is maintaining interoperability between plugins. For example Conferencer was never designed to work with BuddyPress so interfacing these plugins is required on several levels. To illustrate this below is a general interface diagram for the altc2013 platform followed by more detail about each of the main challenges that had to be overcome:

altc2013 platform integrations

Conferencer –> BuddyPress

In Conferencer the custom post type ‘session’ is used to enter and display session information. This post type  is associated with further custom post types for rooms, timeslots, speakers, tracks and sponsors. BuddyPress on the other hand doesn’t use custom post types or taxonomies, instead extending the WordPress core functionality with it’s own custom APIs, functions and features. Out of the box BuddyPress uses Groups as a way for members to cluster and collaborate. Integration between BuddyPress and Conferencer is primarily achieved by renaming Groups to Sessions (a cheap trick but it works). Doing this means when a user tries to view a Conferencer session which automatically uses a /sessions/ in the url, they are redirected to the BuddyPress group. This is achieved by creating a new page with the slug ‘sessions’ and then using this with the BuddyPress Group component (essentially duplicating the same url endpoint but relying on BuddyPress to steal priority over how the page is displayed).

WordPress Add New Page

BuddyPress settings page

At this point all we have done is trick WordPress into displaying a BuddyPress Group page. Additional code is required to hook into Conferencer session creation to generate a group in BuddyPress and create a relationship between the custom post type and group (included in the Github code shared at the end of the post). Another aspect of the integration is the Conferencer generated programme view. This includes a ‘Follow Session’, renamed from ‘Join Group’. This is done by reusing some of the existing BuddyPress functions to render a group button within the Conferencer programme.

BuddyPress <–> FeedWordPress

The FeedWordPress plugin allows the automatic collection of posts made on 3rd party sites using RSS. FeedWordPress ingeniously uses the existing WordPress Links table to maintain a list of sites it collects data from. Meanwhile within BuudyPress members can edit their own profile using defined fields. In altc2013 we use this functionality to allow delegates to register their own blogs. An interface with FeedWordPress is achieved by associating a blog feed address with the WP Links table. The added benefit of allowing users to add their own blog feeds is that we can make an association between blog feed and author. This means when a post is collected by FeedWordPress it is associated with the delegate and consequently BuddyPress associates this with an activity stream entry. 

Example activity stream entry

Reader <–> BuddyPress

The Reader isn’t a plugin in it’s own right (but I should make it), instead it’s a theme customisation I originally developed for ocTEL. All the reader does is render data collected by FeedWordPress which are in turn are just categorised blog posts. The Reader integrates with BuddyPress by using it’s native activity favouriting and by using an addition BuddyPress compatible plugin (BP Likes) also displays and records ‘likes’. This is achieved is a similar way to adding ‘Follow Session’ buttons of the Conferencer generated programme.

Reader - Favourite/Like Favourite in Activity Stream

BuddyPress <–> MailPress

MailPress is a plugin which manages the distribution of a daily newsletter of latest conference activity. The two interfaces with this and BuddyPress is the addition of link within the members notification settings to control their newsletter subscription. Given the way BuddyPress uses WordPress functionality to add additional information to various interfaces this was achieved by matching the WordPress/BuddyPress user id with a table of users maintained by MailPress. The second layer of integration was to include highlights of the BuddyPress activity stream in each newsletter. This was achieved by using existing BuddyPress functionality to render and display a custom activity summary (as used also on the homepage of the conference site.

Summary

Hopefully this post has given you some insight into what was required to create the altc2013 conference platform. Using existing open source plugins, we’ve interfaced them to create new functionality. Whilst the effectiveness of the new altc2013 conference platform is still to be evaluated we now have a basic platform to agilely respond to the needs of delegates.

A reminder that the code we’ve developed is on Github so feel free to peruse over and take in your own direction and comment on, if your an altc2013 platform user the feedback button is the best way to suggest improvements or highlight bugs, and if you are generally interested in this area the comments on this post are open

4 Comments

Talking dirty codeOn Tuesday (10th September, 2013) I’ll be giving a short presentation at altc2013 on using WordPress as a course platform for ocTEL. You can read the full presentation abstract here. It’s been useful to think about what it all means. WordPress is a fantastic platform for putting things together. The diversity of plugins and themes, the majority of which are open source, means you can quickly put together a powerful platform. The huge headache having now gone through this process twice is integration.

For example, in ocTEL learners had their profile which they could edit using the forum plugin bbPress. Separately the FeedWordPress plugin was used to pull content from spaces being used by students such as Twitter and Google+. To allow students to administer their own blog feed as a source for this required this, this and this (and no doubt some other code I’ve forgot to highlight). If you were wondering what the image down the right hand side of this post is it’s the functions.php code written for ocTEL. This along is over 400 lines and there is more for page customisation.

In some ways this extra code isn’t needed, it’s there for a better user experience. Other staff including Martin Weller have very successfully used WordPress/FeedWordPress as a component of their online courses. The downside is that administrative burden is placed on the tutor rather than distributing to students (this burden could actual be a positive thing. If you are asking students to self register feeds there is a good chance it won’t happen. If you are is the position to directly ask or make response a requirement, say at entry, then it’ll get done.)

So do we wrap these experiments in a box or do we stand back, admire the fine lines and let the next platform author decide what ideas, snippets of code they want to reuse? This is the question Alan Levine (You Don’t Get ds106 in a Box) and Jim Groom (ds106: Will Work for Feed Syndication Framework), and I’m sure others, have been recently asking.

Boxing some of this would be good but whereas previously I was thinking about big boxes I’m more inclined to keep the boxes small and interoperable. Already Alan is extracting the ds106 assignment bank into a theme others can use. I recon my offer should be to turn the Course/Conference Reader into a plugin and revisit the Feed+ Machine to plug some of the syndication holes. 

Anyway I hadn’t planned to talk dirty code and I’m sure anyone who was planning on coming to my session is now having second thoughts. I promise to keep it light, to prove it my current slide desk is here (work in progress).

3 Comments

I’m a self-confessed RSS junkie, I ❤ RSS, so when topping up my feeds I wanted a quick way to add to Feedly (my current RSS reader of choice). I already use the Chrome RSS Subscription Extension (by Google) which gives me a handy icon in my address bar when a feed is detected.

RSS Subscription Extension (by Google)

Unfortunately though it doesn’t have Feedly as a service …

Manage feeds

Clicking on ‘Manage…’ gives the rather daunting

Edit feed reader dialog

A quick search for a Feedly specific url turned up Add Feedly to Firefox's Feed Handlers List and I recognised that the browser.contentHandlers.types.#.uri would do the trick. So using the following in the Edit feed reader dialog:

I back to my feed munching ways

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

Here's some posts which have caught my attention this month:

Automatically generated from my Diigo Starred Items.
Share this post on:
| | |
Posted in Starred on by .

4 Comments

Recently I gleefully exclaimed on Twitter “exciting news” and despite the interest I didn’t feel until now that I could reveal what had happened, Google were looking for a hire … Google were looking to hire me:

Hi Martin,
I am a technical recruiter within Developer Relations at Google and I wanted to get in touch. Based on your background and postings, I feel you could be a great fit, as we are hiring for multiple positions within Developer Relations in NYC and Mountain View, CA locations specifically. Are you currently entertaining new opportunities? If so, I would like to get in touch at your earliest convenience to discuss your background and active opportunities. Looking forward to hearing back!

Ah finally the 100+ blog posts I’ve written on Google, countless presentations on hacking stuff together with Google Spreadsheets (a couple here) and I finally got noticed.  What was most interesting about this message came from … LinkedIn!!!

Yes that’s right despite having a decent presence of Google+ it appears Google do some of their recruiting through someone else's social network. This initially led me to question if it was a genuine approach or just some recruiting agent phishing for CVs spoofing a Google connection. The only thing that gave comfort was the inclusion in the message of the sender’s @google.com email address and I opted to reply via this instead. Still though LinkedIn! Why not Google+ or even Gmail. If Google are looking for hires through LinkedIn that’s a pretty big argument to make sure your graduates have a presence there … right?.

Fortunately the message wasn’t a phish and the recruiter got back to me and we arranged a phone call. The call was primarily a chance for the recruiter to find out if I was suitable to be put forward for one of the posts and included the basics: what programming languages do you use, experience of public speaking etc. As I later found out the recruiter is essentially your handler, making sure you are aware of the next steps, providing a friendly interface to what can be a daunting experience. At this point the expectation of getting a job in developer relations began to slip. As someone who prides themselves on being a hacker, often even using ‘I’m not a developer’ in my introduction - primarily because I’m often talking to novices and I want to make a connection with the audience - my lack of formal IT qualification and experience was going to be a handicap, but this is Google they pride themselves on innovation … right?

Regardless of this the recruiter saw enough to put me through to the next phase which was a 45 minute call with a Google software developer (not HR person, Google use employees to benchmark candidates), which was a mixture of ‘why do you want to work for Google? … hmmm you called me’ and a programming problem to solve. I’m not sure if part of me wanted to sabotage my opportunity but I completely tanked at this. This left me feeling both angry and disappointed. I was mainly angry for pretending to be something I’m not … a software developer. I’m a hacker, an innovator, a scamp, a scallywag. I betrayed my original calling as a Structural Engineer long ago to search of the next novelty, the next shiny thing to play with, the next idea to stretch until it breaks. No I’m not a software developer.  And thankfully Google agreed, which I sure comes as a relief for a number of people in this sector … right?

The recent news that LinkedIn has dropped their minimum age to 13 to entice school kids has extra resonance for me because now I know even Google use it for recruitment. It reassuring to know people like Sue Beckingham, Matt Lingard and others recognise the importance of students having an awareness of LinkedIn.

So folks I‘m afraid you're stuck with me ;)

Some things I learned along the way

Share this post on:
| | |
Posted in failed, Google, Oddment on by .

1 Comment

You may have seen from my colleagues Lorna Campbell (In the meantime…) and Sheila MacNeill (Sideways) that the University of Strathclyde office for Cetis is closing at the end of the month. Things are slightly up in the air right now but we are hopefully the ‘Glasgow based supergroup’ will reform soon. In the meantime we’ve found ‘other projects’ to keep us busy. In my case the Association for Learning Technology (ALT) are looking to utilise some of my ‘production’ talents helping them create their conference site for ALT-C 2013. Given Stephen Downes is keynoting this year it’s rather fitting that we are going down a connectivist inspired route for this. Just as in my earlier work with the ocTEL open course platform we’ll be deploying the lean mean aggregation machine (FeedWordPress) to pull/push delegate activity with some added situational awareness/organisational features. As with ocTEL we’ll be using the WordPress platform as a base sourcing and joining selected plugins to hopefully give delegates a seamless experience. Given the tight deadline I’m not sure how much I’ll be able to share as I go along but at the very least we’ll publish our recipe under an open source license and you can follow some of the commits to the code repo.

To give you a flavour of what is to come below is a list of the main plugins we are using and some early screenshots (skinning is still on the to do).  

Main plugins (so far)

  • Conferencer – for conference programme (inc. tracks, rooms and sessions)
  • BuddyPress – Delegate profiles (inc member search), conference session groups (allows users to follow and discuss sessions)
  • MailPress – daily email push
  • FeedWordPress – to aggregate conference activity from 3rd party networks

Early Screenshots

Programme view
Programme view

Individual session
Individual session

Here's some posts which have caught my attention this month:

Automatically generated from my Diigo Starred Items.
Share this post on:
| | |
Posted in Starred on by .

1 Comment

From the postbag Marjolein Hoekstra (CleverClogs) writes:

Short description
Can you make a Google Script for me that compares two strings character by character? If differences are found, the script should point these out. If no differences are found at all, the script should put out the text "[ id. ]" .

Detailed description
I have two columns containing lists of horizontally identical, but sometimes almost identical text strings. This is on purpose. Each row has another couple of words that need to be compared.

I'd like to compare them on a character by character basis, and then point out in the second column at which positions it differs from the first, for example like this:

A2: ABCDE

B2: ABKDE

If you compare these two, you'll see that cell B2[3] has 'K' where A2[3] reads 'C'.

My envisioned formula would then populate cell C2 with: "[ – – K – – ]"

As far as I can tell, I'd need a Google Script that parses both strings character by character and output "–" when they are identical, and output the value of the character string from B2. It should be relative simple, with a FOR loop. Thing is, I've never written a Google Script, and it's a bit daunting for me to start on my own.

Note that LEN (A) is always identical to LEN (B)

Background info
In case you're interested in the actual use case: I want to use this formula to compare strings of Chinese characters, where the first column contains the traditional writing of these characters (typically requiring more strokes) and the second column containing the simplified writing of those same characters. Sometimes the characters are different, sometimes they are not. You can see this clearly in the screenshot below.

The Google Spreadsheet is used as input for a flashcard deck I'm building, using the iPhone app Flashcard Deluxe (top-notch system, highly flexible) [also available for Android].

Screenshot:

Google Spreadsheet Example Flashcard Deluxe

There's no need to use Chinese characters to test the formula, I'm just providing this so that you know in what context the formula will be used.

The Solution

My initial thought was to use existing formula to SPLIT the cell text into individual character values and then do a comparison but unfortunately the SPLIT formula requires a character to split on. So instead I turned to Google Apps Script and wrote the following custom formula:

function stringComparison(s1, s2) {
  // lets test both variables are the same object type if not throw an error
  if (Object.prototype.toString.call(s1) !== Object.prototype.toString.call(s2)){
    throw("Both values need to be an array of cells or individual cells")
  }
  // if we are looking at two arrays of cells make sure the sizes match and only one column wide
  if( Object.prototype.toString.call(s1) === '[object Array]' ) {
    if (s1.length != s2.length || s1[0].length > 1 || s2[0].length > 1){
      throw("Arrays of cells need to be same size and 1 column wide");
    }
    // since we are working with an array intialise the return
    var out = [];
    for (r in s1){ // loop over the rows and find differences using diff sub function
      out.push([diff(s1[r][0], s2[r][0])]);
    }
    return out; // return response
  } else { // we are working with two cells so return diff
    return diff(s1, s2)
  }
}

function diff (s1, s2){
  var out = "[ ";
  var notid = false;
  // loop to match each character
  for (var n = 0; n < s1.length; n++){
    if (s1.charAt(n) == s2.charAt(n)){
      out += "–";
    } else {
      out += s2.charAt(n);
      notid = true;
    }
out += " ";
  }
  out += " ]"
  return (notid) ? out :  "[ id. ]"; // if notid(entical) return output or [id.]
}

One of the things to be aware of is Google Apps Script formulas are associated with a spreadsheet. You can't globally use a custom formula unless the script is attached. Fortunately when copying a spreadsheet you also get a copy of the script, so providing templates is a way around this.

With this limitation in mind I thought I’d have another go  at cracking this with built-in formula … and guess what it is possible. The key to unlocking this was when playing with the REGEXREPLACE formula I accidentally turned ‘ABCDE’ into ‘,A,B,C,D,E,’ by using =REGEXREPLACE(A20,"(.*?)",","). My RegEx is terrible so I’ll let someone else explain how this works in the comments, but getting to this point meant I could use a combination of SPLIT and REGEXREPLACE to do a character by character comparison on two cells of text. The final version of the formula goes (comparing cell A14 to B14):

=IF(EXACT(A14,B14),"[ id. ]","[ "&JOIN(" ",ARRAYFORMULA(REGEXREPLACE(SPLIT(REGEXREPLACE(B14,"(.*?)",","),","),SPLIT(REGEXREPLACE(A14,"(.*?)",","),","),"–")))&" ]")

My rough workings are embeded below. You can also make a copy of the entire project including the Apps Script solution here.

Update: Bruce Mcpherson has posted an alternative formula to do this which goes like:

"[ " & CONCATenate(ARRAYFORMULA(if(mid(A31, row(indirect("x1:x"&len(A31))) ,1)=mid(B31,row(indirect("x1:x"&len(A31))),1)," – "," "&mid(B31,row(indirect("x1:x"&len(A31)))&" ",1) ))) &" ]"

As you will see from the comments thread on that post Marjolein was having problems using my version with a Chinese characterset. Adding this to the example spreadsheet I'm unable to replicate the error but have encountered the problem here. If anyone can spot the difference I'd welcome your thoughts?

Update 2: Bruce pointed out that "the likely issue is that the columns with the problem are times - the characters mean AM. The same thing would probably happen with numbers. Have you tried wrapping the cell references in concatenate() to convert to a string?"

I said: ah I see what you mean 时 is being interpreted as 上午12:00:00. Not sure how I'd wrap the concatenate with my regexreplace. Your solution looks better all round so rather than loosing sleep I'd go with that

5 Comments

This was the question that came in over the wire this morning:

My first thought was using the Google Visualisation API Query Language which can would let you do a SQL type query and  LIMIT the response to 1 record and OFFSET to a particular row. The Google Code Playground has a nice example of this to play with to see what you can do, even better Tony Hirst’s Guardian Data Explorer helps you generate the query to generate an html view (although Tony hasn’t implemented to LIMIT and OFFSET.  So below is an example spreadsheet:

Example Spreadsheet

… and using the query

https://spreadsheets.google.com/tq?tqx=out:html&tq=select * LIMIT 1 OFFSET 3&key=rYQm6lTXPH8dHA6XGhJVFsA&gid=0

We get … (the important bit is ‘OFFSET 3’ where offset 0 = row 2, offset 1 = row 3, etc.)

Google Visualization html row

So a couple of issues. First the spreadsheet needs to be File > Published to the web and the result is also read-only:

Thinking about the other view offered by Google on Spreadsheets it occurred to me the mobile view might be a solution. The mobile view if you don’t use a native app is List view (here is more about List View):

List View 

… which allows you to edit a row of data

Edit row in List View

the final trick was to change the rows per page to 1 and then use the page query to select the row e.g. to open the spreadsheet to edit row 5 we set &page=4 (the header isn't counted as a row). You can also select the sheet by changing the &ampgid= number. Tip: Open your spreadsheet and switching to 'List View' will help you build the URL.

https://docs.google.com/spreadsheet/lv?key={your_key}&type=view&gid=0&f=true&sortcolid=-1&sortasc=true&page=4&rowsperpage=1

Edit single row in List View

So there you go how to open a Google Spreadsheet at a specific row for editing for Marjolein to use. The perfect solution … well almost?

Update: Saqib Ali has kindly passed on this trick from one of the Google team (I don't think it would work in the original scenario, but still very useful to know

Insert/Comment at a specific cell, and, in that comment "plus someone" - that is, type "+" and then the person's email or name NOTE: you will get a list of people in your domain as soon as you type the "+", filtered as you start typing their name/email the person you "plussed" will get an email with both the contents of the cell and your comment the link in that email will take them DIRECTLY TO THE TARGET CELL with the comment activated.

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