30

One can link to a Google Sheets via key, and via key and worksheet id:

  • https://docs.google.com/spreadsheet/ccc?key=SOME_KEY
  • https://docs.google.com/spreadsheet/ccc?key=SOME_KEY#gid=WORKSHEET_ID

Is it also possible to link to a certain row or even cell via URL query parameters or fragments?

Similar to the anchor link mechanism in HTML?

17

Now this feature is implemented, try:

https://docs.google.com/spreadsheets/d/13PuCx8zKUjXvofFYBGzoOYog7UHpvLzCgxMLF9INnr8/edit#gid=0&range=D10

right click any cell and try get link to this cell

  • Nice, but doesn't seem to work in the Android Sheets App. Is there as solution that works there, too? Or any clue if the Sheets App will get this feature? – Victoria Apr 1 '18 at 1:13
  • At present, the Sheets App will open the right Sheet, but with the last used sheet instead of the one specified by gid, and no attempt to get to the right cell. – Victoria Apr 1 '18 at 3:32
  • That is, using such a link from outside the App, it loads the Sheets App, but doesn't go to the right gid or range. – Victoria Apr 1 '18 at 3:41
  • @Victoria It's much easier for the Android app : =HYPERLINK("#gid=0&range=Sheet1!B5") gid will always be 0 and range parameter accepts sheet name instead of a complicated id like the desktop. – TheMaster Nov 18 '18 at 15:15
  • 1
    This is obviously selecting by cell id (e.g. D10) but is there any way to reference a cell that will follow the data? In my sheets i frequently sort the table and it would be great if I could keep track of where a particular row is even after the sort. – Michael Feb 4 at 23:35
16
+100

Notes:

*Spreadsheets created in the old version of Sheets will eventually be converted to the new Sheets, which won’t require any action on your part. More details coming soon.


Yes, it is possible to link to a row by using the following link.

Link

https://docs.google.com/spreadsheet/lv?
  key={your_key}&                // spreadsheet key
  type=view&                     // view (list under menu view)
  gid=0&                         // sheet number
  f=true&                        // markup parameter
  sortcolid=-1&                  // sort parameter
  sortasc=true&                  // sort parameter (asc/desc)
  page=4&                        // row to edit (counts for 5)
  rowsperpage=1                  // number of rows per page (needs to be n=1)

Screenshot

enter image description here

Example

The following link opens the file, created in this answer, and catches the 5th row: example link

Reference

Blogpost:
http://mashe.hawksey.info/2013/07/how-to-open-a-google-spreadsheet-at-a-specific-row-for-editing/

Martin Hawksey:
is an active blogger, predominantly writing about Google Apps Scripts in combination with Google Spreadsheets. See his blog for more interesting stuff or follow him on Google+.

  • 2
    This link opens a list view - any possibility for linking directly to a cell in edit view (as I suspect the OP wants)? – Vidar S. Ramdal Nov 18 '13 at 9:17
  • 2
    Not that this trick is not a good one (it is), but it gives a completely different view of the spreadsheet data, with only the single row visible. The OP's example links is for /ccc, which is the full edit view, so I suspect that's what he (and I) would prefer. Maybe the OP could clarify. – Vidar S. Ramdal Nov 18 '13 at 9:45
  • 1
    Yes, I would prefer a solution, where the spreadsheet "jumps" to the specified position, like anchors in HTML. – miku Nov 21 '13 at 13:04
  • 5
    According to support.google.com/docs/answer/139561?rd=1 the list view got removed in the "New" Google spreadsheets. – bgoodr Nov 15 '14 at 18:11
  • 2
    The document in the "example link" was migrated to the New Google Sheets and now it doesn't work as was intended when the answer was posted. – Rubén Aug 17 '15 at 17:53
9

As another user answered, Google finally added a feature to Google Sheets (GS) spreadsheets to allow linking directly to cells. The feature is intended for linking to ranges of cells, but specifying single cells works, too. GS also supports linking to named ranges of cells in a spreadsheet, but that feature requires a little more work to use than the range of cells by address feature. The extra effort may be worthwhile, though.

💡 Note:

  • A random spreadsheet is used for the examples below.
  • Google Docs URLs usually include /edit immediately after the long document ID string. Those are annoying, because they take up space and they're unnecessary. If the URL doesn't include /edit, Google Docs will usually open the document in edit mode anyway. They've been left out of the URLs in the examples below.

Link to: A single cell in a sheet

  1. Click on the spreadsheet cell to be referenced. For example, cell C7.
  2. Open the contextual menu (using secondary click: right-click, ctrl-click, etc.).
  3. Scroll down the menu and click on the "Get link to this cell" item. The message "The link has been copied to the clipboard" will be shown.
  4. Paste the link's URL from the clipboard anywhere it's needed. It will be similar to:

    https://docs.google.com/spreadsheets/d/1yk6W3iyZM7JKffsRTjPhm9I_PWerrJbltoHZ37Tqdh0#gid=2021414981&range=C7

Some things to notice about the arguments contained in the fragment identifier of this URL:

  1. gid= – This is the ID of a sheet in the spreadsheet. New spreadsheets have one sheet with the ID 0. The ID of the currently visible sheet is usually shown in the browser's URL bar.
  2. range= – This is the address of the referenced cell, in "A1 format" (column letter and cell number).

The contextual menu seems to be the only way in the GS UI to get cell URLs. There aren't any other menu items that do this.

Link to: A range of cells in a sheet

  1. Highlight a range of spreadsheet cells to be referenced. For example, cells C7 through D13.
  2. Open the contextual menu.
  3. Scroll down the menu and click on the "Get link to this range" item. The message "The link has been copied to the clipboard" will be shown.
  4. Paste the link's URL from the clipboard anywhere it's needed. It will be similar to:

    https://docs.google.com/spreadsheets/d/1yk6W3iyZM7JKffsRTjPhm9I_PWerrJbltoHZ37Tqdh0#gid=2021414981&range=C7:D13

💡 Note: To select a group of non-empty, contiguous cells in a sheet, click on one cell in the group, then press the "select all" keyboard shortcut (command-A, ctrl-A, etc.). Depending on the contents of the nearby cells, either a range of cells or all cells in the sheet will be selected. For example, in cell C7 of the example spreadsheet, this method selected cells in the range A1:K28.

Link to: All cells in a sheet

💡 In this and the following sections, instructions for getting the URL are abbreviated. Only the first step, the selection of cells, is described. See the previous section for detailed instructions.

On the box to the left of column letter "A" and row number "1", open the contextual menu, then click on the "Get link to this range" item.

The URL will be similar to:

https://docs.google.com/spreadsheets/d/1yk6W3iyZM7JKffsRTjPhm9I_PWerrJbltoHZ37Tqdh0#gid=2021414981&range=1:605

Note that the range is only row numbers, starting with 1 and going to the highest row number. When a range of only row numbers is given, all columns within those rows are included in the range.

Link to: Range of either columns or rows

Select one or more column letter or row number labels.

The URL will be similar to:

Link to: A named range

Linking to a named range is trickier than getting the link to a range of cells by address. GS doesn't have a feature to link directly to the named range. Using the name of a named range in the range= argument doesn't work. Follow these steps to get a working link URL for a named range:

  1. Be sure the spreadsheet has a named range in it. If it doesn't, create one. (E.g., in the example spreadsheet, the range named "judgenames" was defined as "C1:C", which GS saves as "C1:C605".)
  2. Click on an empty cell of the spreadsheet.
  3. Use the "Insert link" feature (i.e., from the Insert > Link... menu item, from the "Insert link" contextual menu item, the keyboard shortcut command-K or ctrl-K, etc.).
  4. In the link editor box that appears, click "Named ranges in this spreadsheet", then click the name of a range shown below it (e.g., "judgenames")
  5. Click the "Apply" button.
  6. Move the pointer over the new link. A popup showing part of the URL will appear (e.g., #rangeid=1332253898).
  7. Copy that new link from the popup by either:

    1. Copying the link text and using it in place of the fragment identifier in the URL of the current spreadsheet.
    2. Copy the link address. (The wording used by Google Chrome.)

    These methods will vary from browser to browser.

The URL will be similar to:

https://docs.google.com/spreadsheets/d/1yk6W3iyZM7JKffsRTjPhm9I_PWerrJbltoHZ37Tqdh0#rangeid=1332253898

Some things to notice about the arguments contained in the fragment identifier of this URL:

  1. rangeid= – This argument is used instead of the range= one found in links to a range of cell addresses.
  2. gid= - This argument isn't used. That's probably because the ID of a named range is sufficient to specify both a sheet ID and a range of its cells.

💡 Why is it worth the effort to use a named range?

When a URL refers to a GS named range, the spreadsheet will open and focus will be placed on whichever cell range the named range refers at the present time. When the "judgenames" range was originally defined, it was for the range "C1:C605", which will receive focus when the spreadsheet is opened. However, imagine the "judgenames" range is redefined, to say "D1:D5". The next time this same URL is used to open the spreadsheet, the new range will receive focus, not the old one.

Link to: A sheet

Finally, the simplest link of all, a specific sheet in a spreadsheet. The simplest way to get this is to select a sheet in the spreadsheet, then copy the URL from the address bar of the web browser.

The URL for a sheet is similar to that of a cell range. It only lacks the range= argument of the fragment identifier:

https://docs.google.com/spreadsheets/d/1yk6W3iyZM7JKffsRTjPhm9I_PWerrJbltoHZ37Tqdh0#gid=583749522

Linking to a nonexistent sheet will usually cause GS to open the default sheet of the spreadsheet, the first sheet.

  • Crazy question, Anyone know of a way to return the result of the spreadsheet link to text? Perhaps in a BBCode where you'd have [url][/url]? I know its unlikely because I doubt they'd give us the power to consume web services.... Essentially rendering the result of that cell into the page. – HumbleWebDev Nov 17 '17 at 20:15
  • Nice overview, thanks a lot! I would just like to point out that there is an easy additional workaround using comments in this answer. It takes a little less time to implement then using named ranges if you are looking to link to a "dynamic" cell address (instead of a "static" one like "A3") – Albin Aug 16 '18 at 11:44
  • Any way to name a "range" that will automatically follow that range when the table is sorted? (I see it already follows when rows are inserted or deleted) – Michael Feb 4 at 23:39
  • I wish the named range id would be easier to find – Ivan Dossev Mar 4 at 19:54
6

One workaround is to add a comment to the cell. In the comment write +email-address, for e.g. "This comment is to link to the cell for +me@emailaddress.com".

"Me@emailadress.com" will then receive an email with a link, that if you click it, will automatically highlight that particular cell+comment in the spreadsheet.

I'm not sure, but you theoretically ought to be able to share that link with others who have access to the spreadsheet and the cell should highlight for them as well.

  • 2
    More than that, after you left comment, it appears in "Comments" dropdown (besides "Share" buton). Each comment there has timestamp and small arrow for menu. There is "Link to this comment..." item that opens the comment link dialog box for copy and paste. – myroslav Oct 31 '14 at 8:05
  • As of right now (2014-11-15), the "will then receive an email with a link" does not happen (maybe it once did). I tested this by changing the "+me@emailaddress.com" to my own email address, then clicked on the link to it inside the comment, and examined the email that was sent back to me. No such URL or link was found in the email that was sent. Perhaps I'm misinterpreting this, or Google changed the behavior. – bgoodr Nov 15 '14 at 18:08
  • As of 2015-02-16, I was able to get an email occasionally that provided a link. It is in the first blue highlighted text that was sent, the link was in the hypertext titled with the name of the spreadsheet, as in: <my name> added a comment to <title of my spreadsheet> where <title of my spreadsheet> had in it a link ending in 'disco=...' which highlighted the cell commented on, in yellow. However, and this is the crazy part, no longer does Google send these emails, I got several, maybe 3-4 emails at first, but now no matter how many times I try, no go. Maybe spam prevention? – likethesky Feb 16 '15 at 15:04
  • 2
    @likethesky I found that this trick doesn't send me an e-mail if I mention myself in the comment, but if I mention a colleague who has access to the sheet, he gets a notification. – CupawnTae Jul 15 '15 at 8:36
  • I have used this method for a long time. It still works with the current version of Google Sheets. One of its drawbacks is that opening the comment's link causes the comment to be shown. That's not all bad, but it may hide the target cell. Sometimes I want to direct the user to a cell and not show the comment. So, as detailed in my answer, the new linking features of GS are much more flexible. Comments aren't required and links can be made to ranges (and named ranges, with a little more work). – L S Jul 26 '17 at 19:18
0

Another common use case is that you want to link to a particular cell/range, but the data may be sorted, added and filtered and your reference will be lost. You can work around this by creating a view and linking within a view. Steps:

  1. Create a new view that you would like to use. Remove all filters and sorts (or set some default sequential sort where data can't be inserted).
  2. On the sheet, right click on the cell/range that you would link to and click on "Get link to this cell/range" menu. Paste this value somewhere, like in Notepad.
  3. Look at the URL and get the View ID from the address, usually at the end, that looks like &fvid=32010312357 or some other number. Paste this value at the end of the link that we got in the previous step
  4. Your link should now look like this: https://docs.google.com/spreadsheets/d/[SHEET_ID]/edit#gid=0&range=A40:H40&fvid=[VIEW_ID]

protected by Community Oct 16 at 18:42

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.