Navigation in published Google Sheets

Question

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.

Answer 1

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.