This was the question that came in over the wire this morning:
@mhawksey @psychemedia Could you guys please think of a way to open a Google Spreadsheet at a specific row, preferably as a URL parameter?
— Marjolein Hoekstra (@CleverClogs) July 22, 2013
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:
… and using the query
We get … (the important bit is ‘OFFSET 3’ where offset 0 = row 2, offset 1 = row 3, etc.)
So a couple of issues. First the spreadsheet needs to be File > Published to the web and the result is also read-only:
@mhawksey @psychemedia Right, but the idea is to be able to edit that row. In my case that row typically has an error that needs fixing.Can?
— Marjolein Hoekstra (@CleverClogs) July 22, 2013
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):
… which allows you to edit a row of data
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 &gid= number. Tip: Open your spreadsheet and switching to ‘List View’ will help you build the URL.
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?
@mhawksey @psychemedia This is just, just, just PERFECT, Martin. Can I also open in edit mode straight away?
— Marjolein Hoekstra (@CleverClogs) July 22, 2013
@CleverClogs @psychemedia appears to be javascript enabled responding to click so unfortunately don't think so
— Martin Hawksey (@mhawksey) July 22, 2013
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.