Many years ago I posted a way you could open a Google Sheet at a specific row for editing. This let you do some url wrangling where you could direct someone to open a Google Sheet in what was ‘List View’ and edit the row:
Sadly List View disappeared in the Google Sheets update announced in December 2013. At the time an alternative way of doing this was to use cell comments to create a permalink. This is a nice but one of the things I liked about the ‘List View hack’ was you could use the url pattern to select a row with no additional work.
Reviving with Google Script
I hadn’t thought any more about this problem until I came across a post in the Google Apps Script Community:
I linked to my Google Sheet, using Script Editor, javascript code that makes the sheet jump to the row that contains today’s date. However, the jump to today’s date row feature only works for people who have edit access to sheet. – Ellie Cohen, Google+
There was no code shared with the post but it got me thinking about how it might be done. As directly wrangling a Google Sheet url is no longer possible the idea was to expose a Google Script webapp that would store values as User Properties or User Cache which would then be used in an onOpen trigger. To make this work there is a couple of things to be aware of. Firstly, like other Properties/Cache Services stored values are bound to the script. This means that if you write two different scripts and set a User Property or User Cache in one script you can’t get it in another:
The Properties Service lets you store simple data in key-value pairs scoped to one script, one user of a script, or one document in which an add-on is used. … Properties are never shared between scripts. – Google Script Properties Service Guide
A way around this is you have a library which is used to store and return properties, a feature I use in a number of my script projects. Add-ons are also single script projects. This means you can have run add-ons executing in multiple documents and access the same user property between documents. Deploying this solution as an add-on may be problematic as it would potentially run on every Google Sheet the user opened. With this in mind I’m going to show you how you can achieve an open row for editing behaviour using a library:
Library Code
To expose a url we can customise with row/sheet information we are going to deploy a Google Script webapp. This webapp is also going to be the library we attach to each Google Sheet we want to enable this open row functionality. For the webapp the script project has two files: code.gs (server side). and index.html (client side), they both start like this:
code.gs
The way this works is in code.gs we pass the querystring parameters into index.html. When index.html renders as a webapp the querystring parameters are passed to setCache(), which when executes successfully generates a url to the Google Sheet which is automatically opened using window.top.location.href
When deploying the webapp it’s set to execute the app as the user accessing the web app and to anyone who has access to the app. This way the user cache is stored as the user.
The code.gs has a coupe more lines used to return an onOpen behaviour:
code(onOpen).gs
The complete code.gs file is available on Github. The final piece is to include the library in the Google Sheet we want the open row functionality to happen. To do this create a new Google Sheet and open Tools > Script editor. In the Script editor open Resources > Libraries and in the Find Libraries box enter the script id 11ZaVyzV5H3yp5f-MxAlY04Z6FTJ-sYbkGZKNUqu8pCVLSVofUeIZA9Oa:
Google Sheet code
In the script editor we need to add an onOpen function passing the active spreadsheet to the library which has the user cache stored:
function onOpen(){
var ss = SpreadsheetApp.getActiveSpreadsheet();
OpenRow.showRow(ss);
}
To open your sheet at a row for editing direct users to the following url replacing the values as indicated: https://script.google.com/macros/s/AKfycbzG9_2mWd6x7zFf9V6PH33ORRzZWcVGi_pQs500nbs5Pbdkwy26/exec?id=YOUR_SHEET_ID&sheet=YOUR_SHEET_NAME&row=YOUR_ROW_NUMBER
Optionally if you wish to include a header row when the sheet opens use &header=NUMBER_OF_ROWS_FOR_HEADER
Note: This solution only works for users who have edit access to the sheet. You may also have to modify the code if you use protected ranges. As this solution relies on hiding rows it will cause problems if people are simultaneously opening particular rows for editing.
If you’d like to see this in action try https://script.google.com/macros/s/AKfycbzG9_2mWd6x7zFf9V6PH33ORRzZWcVGi_pQs500nbs5Pbdkwy26/exec?id=1P4Lt0kEqzn2BDkK43GxChUebBR0Af3biwcT9ja4aCWg&sheet=Sheet1&row=5&header=1 (this is a sheet shared with anyone having edit access so it may get messy)
You are welcome to fork this library and modify the functionality to meet your needs (source on Github). If you prefer not to use the library and deploy as a container bound webapp for a sheet you can. If you open a copy of this example you can publish as a webapp from Tools > Script editor
Lessons learned along the way
CacheService proved to be to unreliable for this. I’m not sure if it was down to the speed I was writing/reading values. It’s a shame because CacheService doesn’t require any permissions and values can have an expiration.