CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-sheets-apiembedded-documents
Ber
Ber

Reputation: 31

Link to specific sheet in embedded Google Sheets

Due to the great number of sheets I will have in my Google Sheets I want to create links to all sheets on the first sheet. This works within Google Sheets with the following code:

function goToSheet2() {
     goToSheet("Sheet2");
}
function goToSheet(sheetName) {
    var sheet = SpreadsheetApp.getActive().getSheetByName(sheetName);
    SpreadsheetApp.setActiveSheet(sheet);
}

Further explanation on exactly how to in this other stack overflow post.

However, once you've embedded the sheet, it doesn't work anymore. I suppose this has to do with the fact that this script makes your button/image work like a link (the script just helps you find the appropriate #gid=) so it actually just reloads the whole page, which is not possible in the embedded version.

Any pointers towards the right direction would be greatly appreciated.

Edit: I now know why this doesn't work. As stated on this page:

Only users who have permission to edit a spreadsheet, document, or form can run its bound script. Collaborators who have only view access cannot open the script editor, although if they make a copy of the parent file, they become the owner of the copy and will be able to see and run a copy of the script.

I however can't seem to understand how to make a bound script into a standalone script that works with the embedded file. I've tried publishing the script by "deploying it as a webapp", but that doesn't work.

Edit 2: I now have a very inelegant way to get the script to work on an embedded version of the spreadsheet, but the spreadsheet isn't the way I want it.

Explanation: Basically I change the iframe code from this:

<iframe src="https://docs.google.com/spreadsheets/d/SPREADSHEET-ID/pubhtml?widget=true&amp;headers=false&amp;chrome=false" height="500" width="500"></iframe>

To this:

<iframe src="https://docs.google.com/spreadsheets/d/SPREADSHEET-ID/edit?usp=sharing?widget=true&amp;headers=false" height="500" width="500"></iframe>

This forced me however to show the whole Google Sheets page with the menu and everything. The script works however, I can change to another sheet by clicking on an image.

Now onto the funny part: I can hide the menu by adding &rm=minimal to the link, but now the script no longer works.

Upvotes: 1

Views: 1638

Answers (1)

Karl_S
Karl_S

Reputation: 3554

You are better served creating code to display the data as HTML and deploying it as a web app. From that you could have the web app show the appropriate data based on a selection made. You may want to start here: Extending Google Sheets and then look at HTML Service: Create and Serve HTML as a starting point. You can now embed the web app in any web page via an iFrame. This will keep it in a display only mode and the viewer does not need to have permissions to the file as you can run the web app as yourself.

Upvotes: 0

Related Questions