2

I would like to have an URL which opens an editable Google Spreadsheet and have specified cells selected so that the user does not have to find the data in a long sheet before editing. The cells to be selected should be given in the URL. It is also good if a specific row is filtered.

It was super easy with the old Google spreadsheets. I know it is possible without editing possibility, but I need it to open the editable version.

Also I know that I could create a filter view and share that as a link, but I cannot make a filter view for each row and get the URL for it.

I have also went into web scripts (HTTP Get in Google Spreadsheet, "e undefined"), which can take variables from URL, but cannot open the sheet on the client side (I have not found it yet).

Community
  • 1
  • 1
Zs András
  • 21
  • 1
  • 4

3 Answers3

4

You can create a Google Script webapp that would store url values as User Properties which could 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 Services stored values are bound to the script. This means that if you write two different scripts and set a User Property 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. I've written a blog post which explains this in more detail. As part if this I've created a webapp anyone can use using the following url and parameters

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. Anyone is welcome fork/modify this code to get your required functionality.

mhawksey
  • 2,013
  • 5
  • 23
  • 61
0

its not possible in new sheets because they removed the listview feature from old sheets.

your only option is the one mentioned in the linked blog post you provided: "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"

Zig Mandel
  • 19,571
  • 5
  • 26
  • 36
  • 1
    Not that it's a big difference but I think you can do this without plusing someone. Any comment shows in the right hand comment list. Each comment in that list has a small arrow you can use to get a "Link to this comment" url. Faking the URL pattern didn't work but links still work if the comment is resolved. – Tom Woodward Oct 22 '15 at 12:52
  • Thanks! did not know about that! I cannot script this - so it seems for me - for each row. – Zs András Oct 22 '15 at 22:38
0

*** Update THR 20181004: In today's current version of Google Sheets, you can right-click a cell and select the "Get link to this cell" menu item, which copies a URL to the clipboard that will open the spreadsheet with the indicated cell in the upper left corner, subject to the limit of the existing rows and columns filling to the bottom and right edges respectively of the page. The URL is the same format as in the previous feature before this exposure in the Sheets UI, appending &range=<cell-A1-address> to the #gid=<tab-GID> fragment spec, eg: https://docs.google.com/spreadsheets/d/1QSAJzpdM6z4haa-ah1HKqdy8U1vUxWmPIwplej-9fsI/edit#gid=907876553&range=C7

In today's (THR 20180607) current version of Google Sheets, you can open a sheet with a specified cell shown in the top left visible corner, by specifying the cell address as the range in the URL's fragment component. For example, cell C7 in Google's [Template] Google Sheets: Project plan sheet: https://docs.google.com/spreadsheets/d/1QSAJzpdM6z4haa-ah1HKqdy8U1vUxWmPIwplej-9fsI/edit#gid=907876553&range=C7

Of course this same technique positions the top left cell of a multicell range in the top left visible corner of the sheet.

Note that the specified cell will be positioned in the window's scrolling viewport after any frozen rows/columns are applied to the view. You can get the complete URL, including the cell's fragment tuple, by right-clicking the cell, and in the popup context menu selecting "Get link to this cell".

The URL can be constructed programmatically, so any object in the sheet that can anchor a hyperlink URL can be linked to any cell/range for navigation to it. Therefore this technique is usable via the GUI, by cell formula/style, or by script - or by any external application/page.

It's disappointing that Google hasn't included in the basic Sheets GUI a field in which the user can enter a cell or range address to jump to that range by URL. Every serious spreadsheet application has had this basic feature since the 1980s.

Matthew
  • 757
  • 11
  • 19
  • Could such a field be created with AppScript, and attached as a menu item, that pops up a box in which a cell reference could be placed, and then the script could position to it? – Victoria Aug 23 '18 at 20:14
  • And then I found this: https://productforums.google.com/forum/#!topic/docs/v-e0fVMPvvM – Victoria Aug 23 '18 at 21:00
  • Neither of the sheets linked from that productforums page as examples actually work, so I don't know what you found. – Matthew Aug 25 '18 at 02:38
  • You have to make a copy of them before you can use them: the View-only status precludes their use, but in the copy, they work fine, both the menu and the buttons. – Victoria Aug 25 '18 at 19:06
  • You should post an explicit description of what you found. That's the SO policy, so these discussions aren't dependent on outside resources that are subject to change. And then SO readers would know whether what you found is even worth the effort to go through the process necessary to find out for themselves. – Matthew Aug 27 '18 at 00:09
  • I knew that was the policy for answers, didn't realize it applied to comments, which are quite length-limited. What I found and pointed to in my second comment is exactly what I was looking for which I described in my first comment, so I'm not sure I really violated the policy anyway, other than not explicitly stating that fact. – Victoria Aug 27 '18 at 05:25
  • The reason for the policy in answers holds true for comments too. If the external resource changes then your comment has no value. "And then I found this" doesn't indicate the meaningful relationship of what you found to the issue you mentioned. A reader doesn't know whether the extra required steps are worth what it will show them. FWIW I just typed this whole pedantic comment and I still have 182 characters left. – Matthew Aug 27 '18 at 19:30
  • 600 chaaracters is clearly not enough to have pasted in the whole script, though. – Victoria Aug 27 '18 at 23:31
  • But it's enough to post a clear statement of what you found. – Matthew Aug 29 '18 at 00:43