1

I need to publish a report I have in Google Sheets, but since the report is very big, with many sheets and tables, I have hyperlinks set up for easy navigation, that take you to ranges in other sheets.

When I publish the sheet, the hyperlinks stop working (they take you to the first sheet in a new browser tab). I also tried with a script to change the pages with a button, but the button is not clickable in the published page.

Thanks for any tips you might have.

EDIT:

I've prepared a test sheet to see if I find a solution for this. This is a link with permissions to edit:

https://docs.google.com/spreadsheets/d/1ZGw_6WjrkcNKdFvS8gIG46gEMfMuw7ex86SR9C7qXTU/edit?usp=sharing

And this would be the published version:

https://docs.google.com/spreadsheets/d/e/2PACX-1vTa8JDNMzwdvk87kCvbjJXYgK2RGiKy503eJn6eEjxbyU8oIsuvuKTNXCM6yRP16KXrnD9yvLV3J488/pubhtml

This actually works in Excel, I can embed the report and the hyperlinks still work fine, but I have everything else in Google Sheets, so I'd like to find a workaround.

Danf
  • 1,409
  • 2
  • 21
  • 39

2 Answers2

2

You need to use proper query parameters. The sheet id, gid must be set to navigate properly. You cannot use rangeid. You can however use range.

/pubhtml?chrome=false&gid=[YOUR_SHEET_ID]&range=A1:B1

You can get your sheet id by visiting your sheet(tab) in your spreadsheet (edit version) and inspecting the url.

TheMaster
  • 45,448
  • 6
  • 62
  • 85
  • The example in your answer, would that be the URL parameter? – Danf Jun 21 '19 at 19:44
  • @Danf yes. The url in your question ending in `/pubhtml` – TheMaster Jun 21 '19 at 20:00
  • Ok, I added that to the link to sheet 2 and it opens a new tab in sheet 2, so it works. However, do you know if it's posible to have it open the link in the same browser tab, or not reloading the whole page? I suppose the behaviour would be different in an iframe. – Danf Jun 21 '19 at 20:09
  • That's excaclty what I did: =HYPERLINK("https://docs.google.com/spreadsheets/d/e/2PACX-1vTa8JDNMzwdvk87kCvbjJXYgK2RGiKy503eJn6eEjxbyU8oIsuvuKTNXCM6yRP16KXrnD9yvLV3J488/pubhtml?chrome=true&gid=1021791342";"Link to Sheet 2"). I'll try in an iframe and see how it behaves. – Danf Jun 21 '19 at 20:25
  • I continue trying to make this work in an iframe. I asked a new question here: https://stackoverflow.com/questions/56738202/hyperlinks-to-ranges-and-sheets-in-embedded-google-spreadsheet, if you have any further thought it would be great. – Danf Jun 24 '19 at 14:00
  • @Danf Tried a couple of things. Don't think this is possible. – TheMaster Jun 24 '19 at 15:34
0

Your published sheet probably retains the original #gid (which is Google's sheet ID used for local links). This will fail because it is linked to a Spreadsheet that is inaccessible to the new report. Please check if this is the case (you'll see in the links you've created). I can understand that you won't want to share the original reports but you can set up a small test Spreadsheet with a few linked sheet ranges to test the process - please share that.

DeeKay789
  • 353
  • 2
  • 4
  • 8