← Google Apps Script

Advanced · A2

HTML sidebars and dialogs

Alerts and prompts only ask one question at a time. With HtmlService you build a real interface (buttons, lists, forms) inside the Sheet. The key idea is that your code now lives in two places that talk to each other.

Server and client

🖥️ Server: Code.gs

Runs on Google's servers. Can use SpreadsheetApp, GmailApp and the rest. Can't see the page.

🌐 Client: Sidebar.html

Runs in the user's browser. Can change the page and react to clicks. Can't touch the sheet.

They talk through one bridge:

browser                                  Google
───────                                  ──────
google.script.run                 ──▶    getStrugglingStudents()
   .withSuccessHandler(showList)          reads the sheet
                                  ◀──    return ['Mint (45)']
showList(['Mint (45)'])

Everything in this chapter, and the web apps in A3, is built on that picture. If something doesn't work, first ask: which side is this code running on?

Next: a sidebar →

The sidebar's HTML

Sidebar.html 
<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <style>
      body { font-family: Arial, sans-serif; padding: 12px; }
      button { padding: 8px 12px; margin-top: 8px; }
      li { margin: 4px 0; }
    </style>
  </head>
  <body>
    <h3>Students below 50</h3>
    <button id="load">Check now</button>
    <ul id="list"></ul>
    <p id="status"></p>

    <script>
      document.getElementById('load').addEventListener('click', () => {
        document.getElementById('status').textContent = 'Loading…';
        google.script.run
          .withSuccessHandler(showList)
          .withFailureHandler((err) => {
            document.getElementById('status').textContent = 'Error: ' + err.message;
          })
          .getStrugglingStudents();            // runs the server function in Code.gs
      });

      function showList(names) {
        const ul = document.getElementById('list');
        ul.innerHTML = '';
        names.forEach((n) => {
          const li = document.createElement('li');
          li.textContent = n;                  // textContent, never innerHTML, for data
          ul.appendChild(li);
        });
        document.getElementById('status').textContent = names.length + ' found';
      }
    </script>
  </body>
</html>

The server side

Code.gs 
// Called from the sidebar with google.script.run. Whatever it returns
// is passed to the success handler in the browser.
function getStrugglingStudents() {
  const rows = SpreadsheetApp.getActiveSpreadsheet()
    .getSheetByName('Marks').getDataRange().getValues().slice(1);
  return rows.filter((r) => r[2] < 50).map((r) => `${r[0]} (${r[2]})`);
}

// Called from the dialog form.
function addStudent(form) {
  const name = String(form.name).trim();
  const mark = Number(form.mark);
  if (!name) throw new Error('Please type a name.');
  if (!(mark >= 0 && mark <= 100)) throw new Error('Mark must be 0–100.');
  SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Marks').appendRow([name, form.room, mark]);
  return `${name} added`;
}

Click Check now in the sidebar. The browser asks the server, the server reads the sheet, and the list fills in.

  • google.script.run.getStrugglingStudents() calls the server function with that name.
  • The call is asynchronous: the page keeps working while it waits. The answer arrives in the withSuccessHandler function.
  • If the server throws an error, withFailureHandler gets it, so the user sees a message instead of nothing.
  • Return plain data only: strings, numbers, booleans, arrays and plain objects. A Date or a Range can't cross the bridge.

Data is not HTML

The list uses li.textContent = n. With innerHTML, a "name" like <img src=x onerror=…> typed into the sheet would run as code in your sidebar. Show data with textContent, always.

Next: a form dialog →

Collect input in a dialog

Dialog.html 
<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <style>
      body { font-family: Arial, sans-serif; }
      label { display: block; margin-top: 8px; }
      .error { color: #c5221f; }
    </style>
  </head>
  <body>
    <form id="f">
      <label>Name <input name="name" required></label>
      <label>Room
        <select name="room">
          <option>M.5/1</option><option>M.5/2</option><option>M.5/3</option>
        </select>
      </label>
      <label>Mark <input name="mark" type="number" min="0" max="100"></label>
      <button type="submit">Add</button>
      <p id="msg"></p>
    </form>

    <script>
      document.getElementById('f').addEventListener('submit', (e) => {
        e.preventDefault();                    // stop the page from reloading
        const msg = document.getElementById('msg');
        google.script.run
          .withSuccessHandler(() => google.script.host.close())   // close the dialog
          .withFailureHandler((err) => { msg.className = 'error'; msg.textContent = err.message; })
          .addStudent(e.target);               // a <form> is sent as {name, room, mark}
      });
    </script>
  </body>
</html>

Passing the <form> element itself to a server function sends its fields as an object: { name: "Tee", room: "M.5/2", mark: "90" }. The addStudent function in the server code above checks the input and throws an Error if it's bad. The message appears in the dialog in red. On success, google.script.host.close() closes the dialog.

Check on the server too

The browser's required and min/max help honest users, but anyone can bypass them. The real checks belong in addStudent on the server, where the data is written.

Next: templates →

Build the page on the server

Instead of loading data after the page opens, a template can put the data into the HTML before it is sent. You met scriptlets in I4's email template; here they draw a table:

Code.gs 
// Server-side templating: build the HTML with data before it reaches the browser.
function showReport() {
  const t = HtmlService.createTemplateFromFile('Report');
  t.rows = SpreadsheetApp.getActiveSpreadsheet()
    .getSheetByName('Marks').getDataRange().getDisplayValues();
  const html = t.evaluate().setWidth(500).setHeight(400);
  SpreadsheetApp.getUi().showModalDialog(html, 'Marks report');
}

// Lets one HTML file pull in another: <?!= include('Styles') ?>
function include(name) {
  return HtmlService.createHtmlOutputFromFile(name).getContent();
}
Report.html 
<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <?!= include('Styles') ?>
  </head>
  <body>
    <table>
      <? rows.forEach((row, i) => { ?>
        <tr class="<?= i === 0 ? 'head' : '' ?>">
          <? row.forEach((cell) => { ?>
            <td><?= cell ?></td>
          <? }); ?>
        </tr>
      <? }); ?>
    </table>
  </body>
</html>
Styles.html 
<style>
  body { font-family: Arial, sans-serif; }
  table { border-collapse: collapse; width: 100%; }
  td { border: 1px solid #dadce0; padding: 6px; }
  tr.head td { background: #e8f0fe; font-weight: bold; }
</style>
  • t.rows = … makes rows a variable inside the template.
  • <? … ?> runs code such as loops; <?= … ?> prints an escaped value.
  • <?!= include('Styles') ?> pastes another file in raw, so CSS and shared parts live in their own files.

Which approach? A template is simplest when the data is ready at the start. google.script.run is better for buttons, live refreshes and anything the user triggers later. Most real tools use both.

Next: practice →

Practice

1. Row counter Easy

A sidebar with a Refresh button that shows how many rows the active tab has.

Show a solution
Code.gs 
// Code.gs
function showCounter() {
  SpreadsheetApp.getUi().showSidebar(HtmlService.createHtmlOutputFromFile('Counter').setTitle('Row counter'));
}

function countRows() {
  return SpreadsheetApp.getActiveSheet().getLastRow();
}
Counter.html 
<!-- Counter.html -->
<!DOCTYPE html>
<html>
  <head><base target="_top"></head>
  <body style="font-family: Arial, sans-serif">
    <p>This tab has <b id="n">?</b> rows.</p>
    <button onclick="google.script.run.withSuccessHandler(n => document.getElementById('n').textContent = n).countRows()">
      Refresh
    </button>
  </body>
</html>

2. Room filter Medium

Add a drop-down of rooms to the student panel. Pass the chosen room to the server (google.script.run.getRoom('M.5/3')) and list only that room's students with their marks.

3. Email from the sidebar Challenge

Add a "Email this student" button next to each name. It calls a server function that sends a pre-written reminder, then shows ✓ next to the name. Disable the button while waiting so it can't be double-clicked.

Next: quiz →

Check yourself

1. Code inside a <script> tag in Sidebar.html runs…
2. How does the sidebar call a function in Code.gs?
3. What does google.script.run.getNames() return in the browser?
4. Which values can a server function send back to the browser?
5. In a template, what is the difference between <?= x ?> and <?!= x ?>?
6. Why use textContent instead of innerHTML to show a student name?