I've been loathed to type anything here for a while, cos it means shunting down that lovely still from Desk Set. But we can't stay stuck in the past forever, now, can we? [cue obligatory Librarian hand-wringing]...
An empty spreadsheet
My love of Excel is well documented. My disregard for the Google Suite is also quite well attested. At the centre of this Venn Diagram is Google Sheets. At first glance, it's an inferior Excel clone: inferior because there's clearly rather a lot of stuff missing, and because it works in a web-browser (or doesn't work in a web-browser, depending on the browser). But while there's a lot Excel can do that Google Sheets can't, there's also a bit that Google Sheets can do that Excel can't. Here's some of the more entertaining bits.
Query
The most obvious thing is the interaction with Google Forms. Excel is not incapable of harvesting live data but it's bloody hard work. Google Sheets has been designed to be fed this hot, juicy data. And, because of the collaborative nature of Google Sheets, it's also been designed to make it possible to query that data without screwing everything up.
=query() is something approximating magic. Excel's pivot tables excite me immensely (now I know how to use them), but that joy is but a fraction of =query(). =query() will run a search query on your data and then produce a whole new array based on what it finds. The SQL syntax it uses takes a bit of getting used to, but once you get the basics you'll find yourself building wonderful query engines. Google's =query() bible explains the basics, but doesn't even begin to express its greatness. Imagine you've got a spreadsheet pulling in data on all tweets ever, but you're only interested in the ones I've made where I use a rude word. You could set up a spreadsheet that queried this master spreadsheet and returned just the tweets by me that say "bum", "knickers" or "trump". You could then play with that to your heart's content. You could even set up an email alert to let you know whenever I tweeted filth.
And because it's Google Sheets, you can even use the query function in a HTML context: Here's a simple javascript webform I built that generates a query function, applies it to a Google Sheet and brings back the results as a webpage. This form is just looking at two columns (date and tweet) and returning the same fields for any matching data), but it could be far more elaborate, and can return any part of the data you want. In this case the form is generating a URL as follows:
https://docs.google.com/a/york.ac.uk/spreadsheets/d/
[ID OF THE SHARED SPREADSHEET]/
gviz/tq?tqx=out:html&tq=
select+D,F+where+D+starts+with+'2014-12-[DATE]'
+and+upper(F)+contains+upper('[FREETEXT]')
+and+upper(F)+contains+upper('[FREETEXT]')
...so it's bringing back columns D and F of my sheet for any row where the contents of D starts with "2014-12-"... and the entered date, and an uppercase version of the contents of F contains an uppercase version of what was entered in the freetext field.
The spreadsheet equivalent of this (having created a form whereby the date goes in A2 and the freetext goes in B2) is:
=query(Sheet1!A1:J583,"select D,F where D starts with '2014-12-"&A2&"' and upper(F) contains upper('"&B2&"')")
Join, Split, Unique and Sort
Before you go formula-blind, let's jump from =query() to something else quite fun. I had a spreadsheet containing a list of names (sometimes more than one name in the same cell, comma separated), and I wanted to collate them into something useful. In Excel, I'd have to mess about concatenating, substituting, lefting, righting, mid-ing, or even reprocessing the data (CSV-style) using the "Text to Columns" tool on the Data tab. But Google have come up with a new pair of functions: =join() and =split() which have the benefit of being something you can apply in a formula rather than a stand-alone gadget. They do exactly what you might expect, and save an awful lot of effort.
My list of names was in column A, so I cobbled together the following:
=sort(unique(proper(transpose(split(substitute(join(",",trim(A2:A)),", ",","),",")))))
Then I'm joining the trimmed cells using a single comma to delineate them. =join() is basically a posh concatenation.
Next I use substitute to get rid of any space characters that may have been entered after the commas in the cells that contained multiple names. I could potentially rely on trim again later, but this seemed as well as anything. It's given me a single cell full of names, each separated by a single comma.
Now I need to break this cell up to give one name per cell, so I use =split(): it will transform any comma into a cell division (much as if you were to open a CSV file in Excel).
The cells run across the row, but I want them all in a single column, so I transpose the array.
Some of the names have been entered entirely in lower case, so I use =proper() to change them all to Proper Case.
My list of names is looking good, but now I want to weed out the duplicates. Fortunately, Google have considered that too: =unique() will return only one of everything!
Finally, I pop all the names in alphabetical order by first name using another flash Google function: =sort().
The result is a neat, organised list of names. If I wanted to sort on the surname I've got problems, of course, and I've lost the capitalised second "C" in McCoy. There are things I can do to deal with these issues, but we're going to need a bigger spreadsheet. Let's save that for another day.
The result is a neat, organised list of names. If I wanted to sort on the surname I've got problems, of course, and I've lost the capitalised second "C" in McCoy. There are things I can do to deal with these issues, but we're going to need a bigger spreadsheet. Let's save that for another day.
The Advent Calendar
At Christmas I built a spreadsheet Advent Calendar. Here's how it worked:
Each cell on the home sheet references an equivalent cell on a hidden sheet. This was not the original plan, but a flaw of Google Sheets is that it loads a snapshot view of the sheet before it updates the formulae, and unfortunately, the snapshot it took was for the end of the month, when the picture was at its most complete! This was quite a flaw, hence the dramatic rebuild.
The principle taking place on the hidden sheet is really rather simple: each cell has a character which maps to a conditional format. So "A" gives the wall colour, "2" gives the green of the grass and the trees, etc. A basic conditional formula checks the current date and displays the appropriate character: for instance, V30 (a patch of grass) has =if($B$39<17,2,""), so is green before the 17th and white thereafter (B39 contains =day(now()) to give the day's date).
There are a couple of more-complicated things going on:
The sky has =if($A$38="N","H",3), relying on A38's =if(hour(now())>16,"N", if(hour(now()<8),"N","D")). This changes the sky colour depending on the time of day. I'd've liked to have added more shades to this, but the snowflake glyphs limited the conditional formatting options available.
The Christmas Cards use Google's =image() function (e.g. image("http://www.avwoman.co.uk/advert/skaterx.jpg",2)). This is another place in which Google has the upper hand over Excel, as it makes a formal link between an image and its cell location. It's still a bit more limited than would be ideal (I'd've loved to have put an animated .gif in the fireplace, but that's beyond its capabilities), but it's a massive improvement on the mess you get in Excel.
That, then, is an overview of some of the fun you can have with Google Sheets that you can't have with Excel. For a full list of Google Sheets functions, see this full list of Google Sheets functions.
