Google Apps script not working in an embedded iframe

0
votes

I have a Google sheet where I have placed a few buttons linking to the script. It works fine when I use it on the sheets page. But when I publish it and embed the sheet on my website, the button doesn't work. It's not performing. Below is the sample code I use to move from one sheet to another with the click of the button and the iframe code I am using.

function goToInward() {
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
spreadsheet.setActiveSheet(spreadsheet.getSheetByName("Inward"));
}

<iframe src="https://docs.google.com/spreadsheets/d/e/xxxxxxxxxxxxxxxxx/pubhtml?widget=true&amp;headers=false" frameborder="0" style="overflow:hidden;overflow-x:hidden;overflow-y:hidden;height:100%;width:100%;position:absolute;top:55px;left:0px;right:0px;bottom:0px" height="100%" width="100%"></iframe>
1
google-apps-scriptgoogle-sheets

1 Answers

0
votes

When you publish a a Google spreadsheet to the Web - it is view only access

  • Indeed, if you type into your browser address bar the direct publish link: https://docs.google.com/spreadsheets/d/e/xxxxxxxxxxxxxxxxx/pubhtml?widget=true&amp;headers=false, you will see that you likewise obtain only a view version of the spreadsheet
  • You cannot perform edits in the spreadsheets, and consequently you cannot run an Apps Script - since the latter requires edit access

WORKAROUND

  • When publishing a spreadsheet, you can specify in the URL which sheet shall be shown
  • By default, it is the first sheet, but if you specofy e.g. https://docs.google.com/spreadsheets/d/e/xxxxxxxxxxxxxxxxx/pubhtml?widget=true&amp;headers=false#gid=yyyyyyyyy, then the sheet with the sheet id yyyyyyyyy will be opened
  • Consequently, you can create your own sheet changing script within your Website
  • For this, simply create an html button and specify that on button click the user shall be redirected to the sheet

Sample

    <iframe id="frame" src="https://docs.google.com/spreadsheets/d/e/xxxxxxxxxxxxxxxxx/pubhtml?widget=true&amp;headers=false" frameborder="0" style="overflow:hidden;overflow-x:hidden;overflow-y:hidden;height:100%;width:100%;position:absolute;top:55px;left:0px;right:0px;bottom:0px" height="100%" width="100%"></iframe>
    <button onclick="myFunction()">Change tabs</button>
    <script>
      function myFunction(){
        document.getElementById("frame").src = "https://docs.google.com/spreadsheets/d/e/xxxxxxxxxxxxxxxxx/pubhtml?widget=true&amp;headers=false#gid=yyyyyyyyy";
      }
    </script>