Google Workspace

Build a Sidebar App in Google Sheets with Apps Script and HTML

Learn how to build a sidebar app in Google Sheets using Apps Script and HtmlService. Covers HTML templates, showSidebar(), google.script.run, CSS styling, and three practical examples.

Ash Ganda

— 14 min read

Google Sheets is already capable of a great deal on its own, but there are workflows where a purpose-built interface embedded directly inside the spreadsheet would be far more efficient than clicking around cells and menus. A quick data entry form that validates inputs before writing to the sheet. A lookup tool that pulls a customer record and displays it neatly. A formatting toolbar that applies your house style with a single click. All of these are possible with Apps Script's HtmlService, which lets you build HTML-based sidebars that open inside the Sheets interface and communicate directly with your spreadsheet data.

A sidebar is a panel that appears on the right-hand side of the Google Sheets window, roughly 300 pixels wide, containing whatever HTML, CSS, and JavaScript you choose to put in it. It stays open while you work with the sheet, and two-way communication between the sidebar and your Apps Script server code happens through the google.script.run API — no external hosting, no APIs, no subscriptions. Everything runs inside the Google Workspace environment you already have.

This guide walks through HtmlService from first principles, building up to three practical sidebars you can deploy and use immediately. By the end you will understand how HTML templates work in Apps Script, how to open a sidebar with showSidebar(), how to call server-side functions from HTML, how to pass data back to the server, and how to style a sidebar so it looks professional inside Sheets.

Understanding HtmlService

HtmlService is the Apps Script service that generates and serves HTML content. It has two main methods for creating content:

  • HtmlService.createHtmlOutput(htmlString) — Creates an HtmlOutput object from a raw HTML string. Useful for simple cases where you want to build HTML programmatically.
  • HtmlService.createHtmlOutputFromFile(filename) — Creates an HtmlOutput object from a separate .html file stored in your Apps Script project. This is the preferred approach for anything beyond a few lines of HTML because it keeps your HTML and your server-side code separate and readable.

Once you have an HtmlOutput object, you pass it to SpreadsheetApp.getUi().showSidebar() to display it as a sidebar in the current spreadsheet.

A minimal working example looks like this in your Code.gs file:

function openSidebar() {
  var html = HtmlService.createHtmlOutput('<h2>Hello from the sidebar!</h2>')
    .setTitle('My Sidebar');
  SpreadsheetApp.getUi().showSidebar(html);
}

Run openSidebar() and a narrow panel will appear on the right side of your sheet with the heading "Hello from the sidebar!" and a title bar showing "My Sidebar". That is the complete basics of HtmlService.

For anything more complex, use .createHtmlOutputFromFile() with a separate HTML file in your project. To add an HTML file to your Apps Script project, click the + icon next to Files in the left sidebar of the editor, choose HTML, and name the file (for example, Sidebar). The file will appear as Sidebar.html and you reference it without the extension:

function openSidebar() {
  var html = HtmlService.createHtmlOutputFromFile('Sidebar')
    .setTitle('My Sidebar');
  SpreadsheetApp.getUi().showSidebar(html);
}

Adding the Menu Entry

Rather than running openSidebar() manually from the editor every time, add it to a custom menu that appears when the spreadsheet opens:

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('My Tools')
    .addItem('Open Sidebar', 'openSidebar')
    .addToUi();
}

function openSidebar() {
  var html = HtmlService.createHtmlOutputFromFile('Sidebar')
    .setTitle('My Sidebar');
  SpreadsheetApp.getUi().showSidebar(html);
}

The onOpen() function is a special trigger that Apps Script runs automatically every time the spreadsheet is opened. After the first authorisation, the "My Tools" menu will appear in the Sheets menu bar and clicking "Open Sidebar" will launch your panel.

Infographic

Creating HTML Templates

For sidebars with dynamic content — content that changes based on spreadsheet data — Apps Script provides HTML templates via HtmlService.createTemplateFromFile(). Templates allow you to embed Apps Script expressions directly inside HTML using scriptlet tags.

There are two types of scriptlet tags:

  • <? ... ?> — Executes code without printing output. Used for conditionals and loops.
  • <?= ... ?> — Evaluates an expression and prints the result as a string. Used for inserting values.

Here is a simple example. In Code.gs:

function openDynamicSidebar() {
  var template = HtmlService.createTemplateFromFile('DynamicSidebar');
  template.sheetName = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName();
  template.rowCount = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getLastRow();

  var html = template.evaluate().setTitle('Sheet Info');
  SpreadsheetApp.getUi().showSidebar(html);
}

In DynamicSidebar.html:

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
    <h3>Active Sheet</h3>
    <p>Sheet name: <strong><?= sheetName ?></strong></p>
    <p>Rows with data: <strong><?= rowCount ?></strong></p>
  </body>
</html>

When template.evaluate() is called, Apps Script processes the scriptlet tags and returns a standard HtmlOutput object. The properties you set on the template object (template.sheetName, template.rowCount) become variables available inside the HTML.

Note the <base target="_top"> tag in the <head>. This is required in Apps Script HTML files to ensure that any links inside the sidebar open in the parent browser window rather than inside the sandboxed iframe that hosts the sidebar.

The google.script.run API: Server-Client Communication

Static HTML sidebars have limited usefulness. The real power comes from two-way communication between the sidebar (running in the user's browser) and your Apps Script server code (running on Google's servers). This is handled by the google.script.run API.

google.script.run is a JavaScript object available inside any Apps Script HTML page. It lets you call any Apps Script function from your HTML's client-side JavaScript. Calls are asynchronous — you provide callback functions to handle the result when it arrives.

The basic pattern is:

google.script.run
  .withSuccessHandler(function(result) {
    // Do something with the returned value
    console.log('Server returned: ' + result);
  })
  .withFailureHandler(function(error) {
    // Handle errors gracefully
    console.error('Error: ' + error.message);
  })
  .yourServerFunction(argument1, argument2);

withSuccessHandler and withFailureHandler are optional but strongly recommended for production sidebars. Without them, silent failures can make debugging extremely difficult.

Returning Data from Server to Sidebar

Server-side functions called via google.script.run can return values to the success handler. The returned value must be a primitive type (string, number, boolean) or a JSON-serialisable object (plain object or array). You cannot return Apps Script objects like Range or SpreadsheetApp.

// In Code.gs
function getActiveSheetData() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lastRow = sheet.getLastRow();
  var data = sheet.getRange(1, 1, lastRow, 3).getValues();
  return data; // Returns a 2D array — serialisable as JSON
}

<!-- In Sidebar.html -->
<script>
  google.script.run
    .withSuccessHandler(function(data) {
      // data is the 2D array returned from getActiveSheetData()
      data.forEach(function(row) {
        console.log(row[0], row[1], row[2]);
      });
    })
    .getActiveSheetData();
</script>

Passing Data from Sidebar to Server

Data travels in the opposite direction by passing arguments to the server function call:

// Client-side: call server with form values
google.script.run
  .withSuccessHandler(function(response) {
    document.getElementById('status').textContent = response;
  })
  .saveFormData(name, email, amount);

// Server-side: receive and write the data
function saveFormData(name, email, amount) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  sheet.appendRow([new Date(), name, email, amount]);
  return 'Saved successfully';
}
Contextual Image

Styling the Sidebar with CSS

Apps Script sidebars support standard CSS. You can write styles inline, in a <style> block, or load Google's official stylesheet for Apps Script UIs — google.visualization.load patterns are unnecessary; the simpler approach is to reference the Google Workspace add-on CSS classes directly, or simply write your own CSS to match your preferred aesthetic.

A practical baseline style block for a clean, professional sidebar:

<style>
  * {
    box-sizing: border-box;
    font-family: 'Google Sans', Arial, sans-serif;
    font-size: 13px;
  }

  body {
    margin: 0;
    padding: 12px;
    background: #fff;
    color: #3c4043;
  }

  h3 {
    font-size: 14px;
    font-weight: 600;
    margin: 0 0 12px 0;
    color: #1a73e8;
  }

  label {
    display: block;
    font-weight: 500;
    margin-bottom: 4px;
    margin-top: 10px;
  }

  input[type="text"],
  input[type="number"],
  input[type="email"],
  select,
  textarea {
    width: 100%;
    padding: 6px 8px;
    border: 1px solid #dadce0;
    border-radius: 4px;
    font-size: 13px;
    color: #3c4043;
  }

  input:focus, select:focus, textarea:focus {
    outline: none;
    border-color: #1a73e8;
  }

  button {
    margin-top: 14px;
    width: 100%;
    padding: 8px;
    background: #1a73e8;
    color: #fff;
    border: none;
    border-radius: 4px;
    font-size: 13px;
    font-weight: 500;
    cursor: pointer;
  }

  button:hover {
    background: #1557b0;
  }

  button:disabled {
    background: #dadce0;
    color: #80868b;
    cursor: default;
  }

  .status {
    margin-top: 10px;
    font-size: 12px;
    color: #137333;
    min-height: 16px;
  }

  .error {
    color: #c5221f;
  }
</style>

The sidebar is 300 pixels wide and cannot be resized by the user. Inputs set to width: 100% fill the available space cleanly. The Google Sans font matches the surrounding Sheets interface. The 1a73e8 blue is Google's standard interactive blue — using it on your buttons and headings gives the sidebar a native feel.

Practical Example 1: Quick Data Entry Form

The most common sidebar use case is a data entry form that appends a new row to the sheet. This is faster than clicking into cells and more reliable because you can add validation before the data is written.

Code.gs:

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('My Tools')
    .addItem('Open Entry Form', 'openEntryForm')
    .addToUi();
}

function openEntryForm() {
  var html = HtmlService.createHtmlOutputFromFile('EntryForm')
    .setTitle('Add New Record');
  SpreadsheetApp.getUi().showSidebar(html);
}

function addRecord(name, category, amount, notes) {
  if (!name || !category || !amount) {
    return { success: false, message: 'Name, category, and amount are required.' };
  }

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var timestamp = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'dd/MM/yyyy HH:mm');
  sheet.appendRow([timestamp, name, category, parseFloat(amount), notes || '']);

  return { success: true, message: 'Record added successfully.' };
}

EntryForm.html:

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <style>
      /* Paste the full CSS block from above here */
      * { box-sizing: border-box; font-family: 'Google Sans', Arial, sans-serif; font-size: 13px; }
      body { margin: 0; padding: 12px; background: #fff; color: #3c4043; }
      h3 { font-size: 14px; font-weight: 600; margin: 0 0 12px 0; color: #1a73e8; }
      label { display: block; font-weight: 500; margin-bottom: 4px; margin-top: 10px; }
      input, select, textarea { width: 100%; padding: 6px 8px; border: 1px solid #dadce0; border-radius: 4px; font-size: 13px; }
      button { margin-top: 14px; width: 100%; padding: 8px; background: #1a73e8; color: #fff; border: none; border-radius: 4px; font-size: 13px; cursor: pointer; }
      button:hover { background: #1557b0; }
      button:disabled { background: #dadce0; color: #80868b; cursor: default; }
      .status { margin-top: 10px; font-size: 12px; color: #137333; min-height: 16px; }
      .error { color: #c5221f; }
    </style>
  </head>
  <body>
    <h3>Add New Record</h3>

    <label for="name">Name *</label>
    <input type="text" id="name" placeholder="e.g. Acme Corp">

    <label for="category">Category *</label>
    <select id="category">
      <option value="">-- Select --</option>
      <option value="Invoice">Invoice</option>
      <option value="Expense">Expense</option>
      <option value="Refund">Refund</option>
    </select>

    <label for="amount">Amount (AUD) *</label>
    <input type="number" id="amount" placeholder="0.00" step="0.01" min="0">

    <label for="notes">Notes</label>
    <textarea id="notes" rows="3" placeholder="Optional notes..."></textarea>

    <button id="submitBtn" onclick="submitForm()">Add Record</button>
    <div id="status" class="status"></div>

    <script>
      function submitForm() {
        var name     = document.getElementById('name').value.trim();
        var category = document.getElementById('category').value;
        var amount   = document.getElementById('amount').value;
        var notes    = document.getElementById('notes').value.trim();

        var statusEl = document.getElementById('status');
        var btn      = document.getElementById('submitBtn');

        statusEl.className = 'status';
        statusEl.textContent = '';

        if (!name || !category || !amount) {
          statusEl.textContent = 'Please fill in all required fields.';
          statusEl.className = 'status error';
          return;
        }

        btn.disabled = true;
        btn.textContent = 'Saving...';

        google.script.run
          .withSuccessHandler(function(result) {
            btn.disabled = false;
            btn.textContent = 'Add Record';
            if (result.success) {
              statusEl.textContent = result.message;
              document.getElementById('name').value = '';
              document.getElementById('category').value = '';
              document.getElementById('amount').value = '';
              document.getElementById('notes').value = '';
            } else {
              statusEl.textContent = result.message;
              statusEl.className = 'status error';
            }
          })
          .withFailureHandler(function(err) {
            btn.disabled = false;
            btn.textContent = 'Add Record';
            statusEl.textContent = 'Error: ' + err.message;
            statusEl.className = 'status error';
          })
          .addRecord(name, category, amount, notes);
      }
    </script>
  </body>
</html>

When a user clicks "Add Record", the form validates inputs client-side before sending them to the server. The server performs a second validation pass (important because client-side validation can always be bypassed), appends the row, and returns a result object. The success handler updates the status message and clears the form for the next entry. The button is disabled during the server call to prevent duplicate submissions.

Practical Example 2: Customer Lookup Tool

A lookup sidebar lets a user type a name or ID and instantly see matching data from the sheet displayed in the sidebar — useful for support teams, sales reps, or anyone who frequently needs to reference records without scrolling through rows.

Code.gs additions:

function openLookupSidebar() {
  var html = HtmlService.createHtmlOutputFromFile('LookupSidebar')
    .setTitle('Customer Lookup');
  SpreadsheetApp.getUi().showSidebar(html);
}

function lookupCustomer(searchTerm) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Customers');
  if (!sheet) return { found: false, message: 'Customers sheet not found.' };

  var data = sheet.getDataRange().getValues();
  var headers = data[0];
  var results = [];

  var term = searchTerm.toLowerCase().trim();

  for (var i = 1; i < data.length; i++) {
    var row = data[i];
    // Search across all columns
    var rowText = row.join(' ').toLowerCase();
    if (rowText.indexOf(term) !== -1) {
      var record = {};
      headers.forEach(function(header, index) {
        record[header] = row[index];
      });
      results.push(record);
    }
  }

  return {
    found: results.length > 0,
    count: results.length,
    records: results.slice(0, 5) // Return max 5 results
  };
}

LookupSidebar.html (key section):

<h3>Customer Lookup</h3>
<input type="text" id="searchInput" placeholder="Name, email, or ID...">
<button onclick="doSearch()">Search</button>
<div id="results"></div>

<script>
  function doSearch() {
    var term = document.getElementById('searchInput').value.trim();
    if (!term) return;

    document.getElementById('results').innerHTML = '<p style="color:#5f6368">Searching...</p>';

    google.script.run
      .withSuccessHandler(showResults)
      .withFailureHandler(function(err) {
        document.getElementById('results').innerHTML =
          '<p style="color:#c5221f">Error: ' + err.message + '</p>';
      })
      .lookupCustomer(term);
  }

  function showResults(response) {
    var container = document.getElementById('results');
    if (!response.found) {
      container.innerHTML = '<p style="color:#5f6368">No matches found.</p>';
      return;
    }

    var html = '<p style="color:#137333;font-size:12px">' + response.count + ' result(s)</p>';
    response.records.forEach(function(record) {
      html += '<div style="border:1px solid #dadce0;border-radius:4px;padding:8px;margin-top:8px;">';
      Object.keys(record).forEach(function(key) {
        if (record[key]) {
          html += '<div><span style="font-weight:500">' + key + ':</span> ' + record[key] + '</div>';
        }
      });
      html += '</div>';
    });

    container.innerHTML = html;
  }

  // Allow Enter key to trigger search
  document.getElementById('searchInput').addEventListener('keypress', function(e) {
    if (e.key === 'Enter') doSearch();
  });
</script>

The lookup function searches across all columns in the Customers sheet, returns up to five matching records as plain objects, and the sidebar renders each record as a small card. This approach is flexible — changing the sheet structure does not require changing the HTML because the record keys come directly from the header row.

Practical Example 3: Formatting Toolbar

A formatting toolbar sidebar lets users apply consistent styling to selected cells without navigating through Sheets' format menus. This is particularly useful for teams that need to enforce style guides — quarterly reports that always use specific colours, client deliverables that need consistent heading formats, or data tables where certain row types should always be a specific shade.

Code.gs additions:

function openFormattingToolbar() {
  var html = HtmlService.createHtmlOutputFromFile('FormattingToolbar')
    .setTitle('Formatting Toolbar');
  SpreadsheetApp.getUi().showSidebar(html);
}

function applyHeadingStyle() {
  var range = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getActiveRange();
  range.setFontWeight('bold')
       .setFontSize(12)
       .setBackground('#1a73e8')
       .setFontColor('#ffffff')
       .setHorizontalAlignment('left');
  return 'Heading style applied to ' + range.getA1Notation();
}

function applyHighlight(colour) {
  var range = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getActiveRange();
  range.setBackground(colour);
  return 'Background set to ' + colour + ' on ' + range.getA1Notation();
}

function clearFormatting() {
  var range = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getActiveRange();
  range.clearFormat();
  return 'Formatting cleared on ' + range.getA1Notation();
}

FormattingToolbar.html (key section):

<h3>Format Selected Cells</h3>
<p style="font-size:11px;color:#5f6368">Select cells in the sheet, then apply a style below.</p>

<button onclick="runFormat('applyHeadingStyle')">Apply Heading Style</button>

<label style="margin-top:14px;display:block;font-weight:500">Highlight Colour</label>
<div style="display:flex;gap:6px;flex-wrap:wrap;margin-top:6px;">
  <button style="background:#fce8b2;color:#333;width:40px;height:30px;border-radius:3px;border:1px solid #dadce0" onclick="highlight('#fce8b2')"></button>
  <button style="background:#d9ead3;color:#333;width:40px;height:30px;border-radius:3px;border:1px solid #dadce0" onclick="highlight('#d9ead3')"></button>
  <button style="background:#cfe2f3;color:#333;width:40px;height:30px;border-radius:3px;border:1px solid #dadce0" onclick="highlight('#cfe2f3')"></button>
  <button style="background:#f4cccc;color:#333;width:40px;height:30px;border-radius:3px;border:1px solid #dadce0" onclick="highlight('#f4cccc')"></button>
</div>

<button style="margin-top:14px;background:#f1f3f4;color:#3c4043;border:1px solid #dadce0"
  onclick="runFormat('clearFormatting')">Clear Formatting</button>

<div id="status" class="status"></div>

<script>
  function runFormat(funcName) {
    document.getElementById('status').textContent = 'Applying...';
    google.script.run
      .withSuccessHandler(function(msg) {
        document.getElementById('status').textContent = msg;
        document.getElementById('status').className = 'status';
      })
      .withFailureHandler(function(err) {
        document.getElementById('status').textContent = 'Error: ' + err.message;
        document.getElementById('status').className = 'status error';
      })
      [funcName]();
  }

  function highlight(colour) {
    document.getElementById('status').textContent = 'Applying...';
    google.script.run
      .withSuccessHandler(function(msg) {
        document.getElementById('status').textContent = msg;
      })
      .withFailureHandler(function(err) {
        document.getElementById('status').textContent = 'Error: ' + err.message;
      })
      .applyHighlight(colour);
  }
</script>

This pattern demonstrates calling different server functions from a single sidebar. The runFormat helper uses JavaScript bracket notation ([funcName]()) to call a function by name dynamically, which keeps the client-side code concise when multiple buttons map to different server functions.

Passing Data Back from the Sidebar to the Sheet

Beyond appendRow and setBackground, a sidebar can write structured data anywhere in the spreadsheet. Here is a pattern for writing form data to a specific row that a user selects:

// Server-side: write data to the row of the currently selected cell
function writeToSelectedRow(values) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var activeRow = sheet.getActiveCell().getRow();

  if (activeRow < 2) {
    return { success: false, message: 'Please select a data row (not the header).' };
  }

  // values is an array: [status, assignee, dueDate]
  sheet.getRange(activeRow, 4).setValue(values[0]); // Column D: Status
  sheet.getRange(activeRow, 5).setValue(values[1]); // Column E: Assignee
  sheet.getRange(activeRow, 6).setValue(values[2]); // Column F: Due Date

  return { success: true, message: 'Row ' + activeRow + ' updated.' };
}

The sidebar calls writeToSelectedRow([status, assignee, dueDate]) and the server writes to whichever row is currently active in the sheet. This context-awareness — the sidebar knowing which row is selected — is what makes sidebars feel integrated rather than separate from the spreadsheet.

Common Gotchas and Tips

Sandboxing and IFRAME_SANDBOX mode. Apps Script HTML runs inside a sandboxed iframe. External scripts and styles (CDN-hosted libraries like jQuery or Bootstrap) load more slowly or may be blocked in certain contexts. Prefer vanilla JavaScript and inline CSS for simpler, more reliable sidebars.

google.script.run calls are always asynchronous. You cannot write synchronous code like var result = google.script.run.myFunction(). The result is always delivered through a callback. Design your UI to handle the waiting state — disable buttons, show loading messages.

Server functions must be explicitly public. Only top-level functions (not nested inside other functions) can be called via google.script.run. Private helper functions that are only used server-side should still be top-level; they just cannot be called from the client.

Closing the sidebar. You can close a sidebar programmatically from the client side using google.script.host.close(). Add a "Close" button to your sidebar for a polished user experience:

<button onclick="google.script.host.close()" style="background:#f1f3f4;color:#3c4043;border:1px solid #dadce0">
  Close
</button>

Data size limits. google.script.run can pass and return data up to about 10MB per call. For most sidebar use cases this is more than sufficient, but avoid passing entire large spreadsheet datasets to the client. Filter and summarise server-side before returning results to the HTML.

Affiliate & Partner Programs

If you are looking to upgrade your Google Workspace plan or explore Apps Script capabilities at scale, here is the official referral link:

Conclusion

Apps Script sidebars bridge the gap between raw spreadsheet functionality and a purpose-built interface. Everything you have seen in this guide — opening a sidebar from a menu, writing HTML templates, calling server functions with google.script.run, returning data from the server, and applying CSS styling — is available to any Google Workspace user without additional tools, subscriptions, or hosting costs.

The three practical examples cover the majority of real-world sidebar use cases: a validated data entry form for fast, accurate input; a customer lookup tool for quick record retrieval; and a formatting toolbar for enforcing consistent styles. Each follows the same architecture: HTML renders in the sidebar, client-side JavaScript handles user interaction and calls google.script.run, and server-side Apps Script functions read or write spreadsheet data and return results.

The next natural steps from here are using HTML templates to pre-populate sidebars with existing sheet data, building multi-step forms that guide a user through a workflow, or integrating with external APIs via UrlFetchApp to pull live data into the sidebar. The foundation in this guide supports all of those extensions.

For Australian businesses running operational workflows in Google Sheets — client management, project tracking, financial records, scheduling — a custom sidebar turns a general-purpose spreadsheet into something that behaves like a bespoke tool built for your specific process.

Need help with your Google Workspace setup? Contact our team for a free consultation.

Ash Ganda is the founder of CloudGeeks, a Google Workspace consultancy helping Australian SMBs get more from their cloud tools. He writes about practical technology strategies at insights.cloudgeeks.com.au.

Read more