Google Script pulling dynamic HTML iFrame from Google Sheet Cell

0
votes

I am working on a incorporating a Google Form as an iFrame in a Modal Dialog box on my Google Spreadsheet (it is already accessible through a customer menu) with iFrame code that dynamically changes based on what's going on in the spreadsheet.

My Google Script to reference the HTML file is:

    //Help Guide 
    function openHelp() { 
    var html = HtmlService.createHtmlOutputFromFile('Help Guide').setHeight(560).setWidth(814.81).setSandboxMode(HtmlService.SandboxMode.IFRAME); 
    SpreadsheetApp.getUi().showModalDialog(html, ' ');
}

When I paste the generated iFrame code into the HTML file 'Help Guide', it loads as desired. However, I would like to instead reference my iFrame code that is housed on Sheet1!C1 when I run the openHelp() function from the menu. This would enable me to dynamically change the iFrame based on the spreadsheet. I have explored using Google's HTML Template service, but haven't been able to figure it out.

I am sure the script is quite easy, but I am unfamiliar with what code needs to be written in the HTML file to pull that generated iFrame code from Sheet1!C1.

Thanks!

1
google-apps-scriptgoogle-sheets

1 Answers

0
votes

Get some HTML code from a Cell in a Spreadsheet

  1. You need to update the variables iframeSheet and iframeCell.
  2. Create the iframe statement.
  3. Deploy as a web app. The doGet is already there. You can also run the showIframeDialog() function to see it work.

The Code:

function getIframeCode() 
{

    var iframeSheet='Sheet1';
    var iframeCell='A1';
    var ss=SpreadsheetApp.getActive();
    var sh=ss.getSheetByName(iframeSheet);
    var hl=sh.getRange(iframeCell).getValue();
    return hl;
}

function showIframeDialog()
{
  var ui=HtmlService.createHtmlOutputFromFile('iframecode');
  SpreadsheetApp.getUi().showModelessDialog(ui, 'Dynamic Iframe')
}

function doGet()
{
  var ui=HtmlService.createHtmlOutputFromFile('iframecode');
  return ui.setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
}

The iframecode.html file:

<!DOCTYPE html>
<html>
  <head>
    <script src="//ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>
  <script>
    $(function() {
        google.script.run
          .withSuccessHandler(updateIframeCode)
          .getIframeCode();
      });
    function updateIframeCode(hl)
    {
      document.getElementById("iframe").innerHTML=hl;
    }    
    console.log("My code");
  </script>
  </head>
  <body>
  <div id="iframe"></div>
 </body>
</html>