Link to a google sheets value

geofflittle 0
Viewed 38

Background

I have a google sheet to which I regularly add rows. Each row has an identifier column that remains constant (e.g. the row's "Title"). Rows change positions as I sort the spreadsheet on a column (e.g. the row's "Value").

For each row, I have an associated google doc with extended narrative about the content of the row. The content doesn't belong in the sheet.

The ask

How do I add a link to the google doc such that when I click it, I'm taken to the corresponding row in the google sheet?

What I've tried

I can create a link that specifies a cell in the sheet (like "A1"), however as I add rows and re-sort the sheet, the link no longer points to the correct row.

Thoughts

  • Can I create a google sheet link that performs a query or filter? In this case a query that searches for a title. I expect it to look something like,

    https://docs.google.com/spreadsheets/d/ssid/edit#gid=sid?query=equals('Title', 'MyTitle')

  • Does this need to be something for which I create a google apps script that's present in the google doc?

Thanks

google-docs google-query-language google-sheets google-apps-script
1 Answers

Can I create a google sheet link that performs a query or filter?

Yes, but it won't take you to the Google sheets file itself. You can create a custom html link. The end point is /gviz/tq. Query can be added with url query parameter tq(say "where A="mytitle") and format can be added with tqx(say html). The link should look like:

https://docs.google.com/spreadsheets/d/${SPREADSHEET_ID}/gviz/tq?tqx=out:html&tq=where%20A%3D%22mytitle%22

out:html uses cookie authentication and should work with private files(when logged in), while out:json requires oauth access tokens.

References:

TheMaster0
Related