Google

1 Comment

Do more with DocsGoogle recently announced (11th March 2014) the release of add-ons for Google Docs and new Sheets. This opens the opportunity for third-party developers, big and small, to create custom functionality within these products. This isn’t entirely new as for many years developers have been able to use Google Apps Script, which is also at the heart of add-ons, to create and distribute custom Google Apps enhancements. What add-ons do create is an a streamlined user experience fronted with a Chrome Web Store like interface. This post provides some notes for Google Apps admins about Google Docs add-ons.  You can see more of this in action in this video.

Add-ons store

Default on

The announcement from Google marked the immediate availability of add-ons for Google users as well as Google Apps accounts on rapid release (more on Google Apps release process). For Google Apps accounts on ‘scheduled release’ add-ons will appear “on Tuesdays only, at least one week after the feature was released to the Rapid Release track” giving time for admins to make local arrangements such as training and support. When Google activated add-ons the default was to have them enabled.

To disable Google Docs add-ons:

  1. Sign in to the Google Admin console.
  2. Click Google Apps > Drive > General.
  3. Deselect the Allow users to install Google Docs add-ons check box.
  4. Click Save changes.

The support for add-ons in Google Docs notes:

After you enable or disable Google Docs add-ons, it may take up to an hour before the change takes effect in previously opened documents, and the change won't be seen until the documents are refreshed or reopened

The support also notes:

The Admin console setting for add-ons controls both Docs and the new version of Sheets; there are not separate settings for each document type. (Add-ons are not available for other document types.)

Tip: If you are having trouble with an add-on in your document, you can force it to load without any add-ons by adding ?addon_free_mode=true

Google new Sheets are rolling out as the default

It’s worth noting that add-ons are only available for new Sheets. To support add-ons on the 21st March 2014 Google announced new Sheets are now the default, stating:

Over the next couple of weeks, rapid release domain users will automatically be upgraded to the new Sheets. Spreadsheets created after the upgrade will use the new version. Users may opt-out of this experience through the settings menu in Sheets.

The new Sheets will be available to Schedule release domains in the next four to six weeks.

Some Google Apps users have been already using new Sheets in their domain by creating a new Sheet with their Gmail account and sharing it with their Google Apps account, then using this as a template for all their spreadsheets. Users on domains on scheduled release will not see the add-ons menu even with a new Sheet. This will change as add-ons are rolled out and admins don’t enable add-ons to:

If you disable Docs add-ons in your organization, users will still see the add-ons menu in their documents and can browse the store, but they can't install any add-ons from the store.

Add-ons whitelist and authorization

A concern I’ve seen raised is the control domain admins have in approving add-ons for use. Currently the switch provided by Google is all on or all off. The current safeguard against malicious add-ons is that these need approval from Google before publication. Already some of Google’s partners have released services for providing per add-on control and it’ll be interesting to see if Google bake this feature in.

Something else for Google Apps admins consider is support informing users about add-on authorization. One of the features of add-ons is the source code is not visible to the end-user. Where as on Google Apps Scripts installed from the Script Gallery or copied elsewhere the code could be viewed before run. Admittedly the majority of script end users wouldn’t be able to understand the code but there is a degree of transparency. This shifts focus on the authorization process which warns users about what the script can do. Historically given the range of service interaction available in Apps Script these messages have been very broad. For example running the following code in the script editor which only gets a document on your Google Drive:

function myFunction() {
  var test = DriveApp.getFileById("FILE_ID");
}

results in:

Add-on authorisation

Click on the more information button ‘i’ reveals:

More information

Upload, download, update and delete files in your Google Drive Create, access, update and delete native Google documents in your Google Drive Manage files and documents in your Google Drive (e.g. search, organise and modify permissions and other metadata, such as title)

This problem isn’t unique and anyone installing apps on your phone will be presented with similar daunting messages. For me this partly comes down to digital literacy, educating users about how identify malicious programs by, such as, checking ratings/reviews and exploring the publisher websites, which are a requirement for add-on publication. For example, the Remove Blank Rows add-on support site highlights the authorisation requests used. Ultimately though better permissions scoping in add-ons would be useful.

If you are interested in developing your own add-ons Google have this introductory post with more information. You can also get support from Stackoverflow  or this dedicated ‘developing add-ons’ Google+ Community.

5 Comments

GDE Apps ScriptSo as well becoming ALT’s Chief Innovation, Technology and Community Officer I've recently been recognised as a Google Developers Experts (GDE).

Google Developers Experts (GDEs) are experts in one or more Google developer technologies. The GDE program recognizes the exemplary work done by these rock stars for the Google Developers worldwide by inviting them to be part of the growing GDE community. GDEs are gurus, mentors and friends; they are developers just like you. Visit the member directory to find an expert in the products you care about. Google Developers Experts speak in local and global events, have a strong online presence and an excellent technical background in their field. These independent developers bring their real-world experience and knowledge working with Google technologies to developer communities worldwide.

It was a pleasant surprise to be approached by Google to become a GDE and throughout the interview process I highlighted that I didn't consider myself to be a ‘developer’. If I was to give myself a label it would be ‘hacker’ … in the non-pejorative sense:

A hacker is someone who loves to program or who enjoys playful cleverness, or a combination of the two.[3] The act of engaging in activities (such as programming or other media[4]) in a spirit of playfulness and exploration is termed hacking. However the defining characteristic of a hacker is not the activities performed themselves (e.g. programming), but the manner in which it is done: Hacking entails some form of excellence, for example exploring the limits of what is possible,[5] thereby doing something exciting and meaningful.[4] Activities of playful cleverness can be said to have "hack value" and are termed hacks[5] – from Wikipedia

The product I've mostly be ‘hacking’ with is Google Apps Script and having picked up this early after launch in 2010 it's been useful to develop my own skills as the product develops as well. Its interesting times for Apps Script and it's noticeable in my circles that latest developments like add-ons has ignited the interest in using this tool to personalise the way teachers educate. Even more inspiring is it's not just the teachers as Scripts are for Kids, too!

So there you go those 100 posts, over 450 scoops, numerous presentations and other community activity have finally paid off. And guess what I'm really looking forward to the next 100 hacks, scoops and presentations...

Finally, remember I'm available for a speaking engagement near you… ;)

4 Comments

Google Docs Analytics Tracking - CC-BY-NC Tony Ruscoe
Google Docs Analytics Tracking - CC-BY-NC Tony Ruscoe

There was a time when you could enable Google Analytics tracking in what was Google Docs and is now Google Drive. Sadly the feature was removed and Google now recommend “embed them in your web pages, and then use Analytics to track the pages in which they're embedded”. For someone who has a number of Google Sheet templates this isn’t entirely convenient and workable. I can embed a link to a template in a site and attach an event to track the number of times clicked, but given these templates can easily be copied and recopied there’s no way to monitor use.

Part of the problem is that the Google Analytics predominantly relies on some embedded JavaScript to communicate when a page has been viewed.  Given the increasing range of interactions Google Analytics also provides a Measurement Protocol for developers to send tracking data in other ways using a HTTP POST or GET request. Without going too deep into the technical side this actual opens a way for including tracking in Google Documents, Sheets and Forms by using Google Apps Script.

Apps Script includes both triggers like onOpen and a URL Fetch service which would allow you to send data to the Measurement Protocol (and this little gist gives you the code to do it). Before you go implementing this in all your projects there are two issues to be aware of:

  1. URL Fetch calls are quotaed by fetches per day and overall runtime (current Google Apps Script Quotas).
  2. URLFetch Service requires authorisation before it can run. This means it cannot send data unless the user has given permission. So if you are viewing a Sheet template Google Analytics will only be pinged after you’ve File > Make a copy and authorised it.

Authorization for Google Services

Throwing up a beacon instead

All is not lost. Recently I came across the Google Analytics Beacon:

Sometimes it is impossible to embed the JavaScript tracking code provided by Google Analytics: the host page does not allow arbitrary JavaScript, and there is no Google Analytics integration. However, not all is lost! If you can embed a simple image (pixel tracker), then you can beacon data to Google Analytics.

This project by Googler Ilya Grigorik means if you can embed an image a Google App Engine service has been configured to make a hit against the Measurement Protocol for you. For this to work when you view the page the image needs to be served from the App Engine service. There are also limitations to this approach in that visitor and referral data is lost.

In Google Drive it’s easy for us to Insert > Image in various applications including Documents and Presentations and even specify these as ‘by URL’. Unfortunately these applications also create copies of the inserted image rather than using the image specified by URL. An anomaly to this is Google Sheets. Sheets permits Insert > Image and a cell function IMAGE. In both these cases the image is served from the URL you specify meaning we can track Google Sheets*.

*New Sheets appears to serve Insert > Image in the same way as Documents and Presentations but the IMAGE formula method outlined below still works.

Using Insert > Image

Using the GA Beacon Setup Instructions will walk you through creating a Google Analytics account and making an image URL like

https://ga-beacon.appspot.com/UA-XXXXX-X/sheets/UNIQUE_ID

Remember to use your own tracking ID. The ‘sheets’ and ‘UNIQUE_ID’ can also be whatever you like.

Using Insert > Image and selecting ‘By URL’ you can add a GA Beacon to a sheet (you can check you are collecting data by logging into Google Analytics and looking at Real-Time reporting). Now every time the Sheet is opened and the image is viewable the visit will be counted in Google Analytics. The url for the image is fixed so even if a copy is made of the spreadsheet as long as the image isn’t deleted you will get tracking data. Remember this way won’t work for New Sheets but the next method does and in my opinion is better.

Using IMAGE formula better tracking information

Using the IMAGE formula would work in exactly the same way using the same image url as above. There is something else we can do. Because it’s a formula the image url could have an identifier that is in some way calculated. As Google Apps Script permits creating your own custom formula there is even scope to use this as part of the calculation.  For example, using little Apps Script will include the sheet key and locale in the image url (to include this you your own project open you Sheet and then Tools > Script editor and paste the code in):

function getGABeacon(tid){
  var id = SpreadsheetApp.getActiveSpreadsheet().getId();
  var locale = SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetLocale();
  return 'https://ga-beacon.appspot.com/'+tid+'/sheets/'+id+'/'+locale;
}

In the Sheet we can then use the cell formula like =image(getGABeacon("UA-48225260-1"))

Image with GA Beacon

You can see this better in-situ in this Google Sheet which you are free to File > Make a copy to see how it works. As an added bonus the Apps Script methods used in this example don’t require authorisation so tracking data (limited to view count) is recorded for anyone opening the Sheet.

This is what the result looks like in Google Analytics Real-Time.

Google Analytics Real-Time

So there you go I can now track views of my Google Sheets by including an image in a cell!

7 Comments

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

...continue reading

4 Comments

Recently

Happy New Year to you too Simon! Having worked with Twitter and Google Maps API I was aware that their terms are becoming increasingly restrictive making the environment for 3rd party services for doing this increasingly difficult. There is a solution for doing using a modification of my Twitter Archiving Google Spreadsheet (TAGS) project (the guerrilla approach so to speak). The result for #rhizo14 is here (only viewable in non-mobile app versions of Google Maps and not the current preview version) and this post outlines how it was done. ...continue reading

1 Comment

It’s been a while since I’ve done a review post but as this year has been a blur if for no one else I wanted to look at my posts from 2013. Rather than a look at everything I wanted to extract some themes and for this first review I wanted to look back at some of the work I’ve done around Google Apps Script and Google Spreadsheets/Sheets. ...continue reading

2 Comments

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

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

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

YouTube: The Online Revolution: Education at Scale

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

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

Examples include:

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

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

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

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

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

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

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

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

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

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

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

Segmentation and cohort analysis

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

GA Filtering

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

GA Motion Chart

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

GA custom conditions and sequences for segmentation

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

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

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

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

Distributing data

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

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

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

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

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

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 .