embed html inside google sheet

13
votes

I want to display HTML at the top of my spreadsheet by creating an html element and putting it at the top of my spreadsheet sheet.

For example, if I created one large cell at the top of my sheet by merging A1:G5, would it be possible to embed html within it:

<div>
 <h1>"Hello World"?</h1>
</div>

I notice from within script editor you can go file > new > html file.

But I don't really get it's purpose.

I just tried this: From script editor new script:

function addSomeHTML() {
  var html = HtmlService.createHtmlOutputFromFile('cabbages')
      .setSandboxMode(HtmlService.SandboxMode.IFRAME);
}

Cabbages is an html file:

<div>
  <h1>Hello, world!</h1>
</div>

I then saved and navigated to my sheet. I selected a cell and typed =addSomeHTML()

The "loading" message appeared then an empty cell was shown. I was hoping to see "Hello World!" within the cell.

I've looked at the following documentation:

https://developers.google.com/apps-script/guides/html/templates#printing_scriptlets

https://developers.google.com/apps-script/guides/dialogs

1
google-apps-scriptgoogle-sheets

1 Answers

8
votes

You can use either a Modal or Modeless dialog box.

The Modal dialog uses the showModalDialog() method of the Ui Class.

Guide to Dialogs

Google Documentation - Modal Dialog

Add a custom menu to the spreadsheet

// This will run when the spreadsheet is opened or the browser page is refreshed
function onOpen() {
  SpreadsheetApp.getUi()
      .createMenu('Custom Menu')
      .addItem('Open Dialog Box', 'openDialog')
      .addToUi();
}

Create the function that runs from the menu

function openDialog() {
  var html = HtmlService.createHtmlOutputFromFile('index');

  SpreadsheetApp.getUi()
    .showModalDialog(html, 'Correct Postcode Errors');
}

index.html

<div>
 <h1>"Hello World"?</h1>
</div>