How to open a Google Spreadsheet at a specific row for editing

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.

chevron_left
chevron_right

Join the conversation

comment 5 comments
  • Saqib Ali

    Martin,
    This is an awesome hack! 🙂
    Saqib

  • Saqib Ali

    Martin,
    Just got an email from the Google Docs PM, and he offered another solution:

    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.

    I use this when to draw someones attention to enter a value in a particular cell or range, or just want them to comment. This works in Docs and Slides too.

    • Martin Hawksey

      In the original scenario I think something generic was required so that edit links could be generated allowing a group of people to edit potentially 100s of rows of data. Great trick though so thanks for passing on, I’ll add it to the post 🙂

      • Saqib Ali

        True, the @mention comment will not work for 100s of rows 🙂

Comments are closed.

css.php