// Sheets · Apps Script

Add a custom sidebar in Google Sheets.

How to build and open a custom HTML sidebar in Google Sheets using Apps Script HtmlService, including how google.script.run's async callbacks actually work.

“I want to show a custom panel alongside my spreadsheet that can read and write cell data without leaving the sheet.”

The script

copy · paste · trigger

sidebar.gs

Apps Script

// Opens a sidebar; sidebar HTML calls back into this file via google.script.run
function showSidebar() {
  var html = HtmlService.createHtmlOutputFromFile('sidebar')
    .setTitle('My Sidebar')
    .setWidth(300);
  SpreadsheetApp.getUi().showSidebar(html);
}

// Called BY the sidebar over google.script.run
function getActiveRange() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var range = sheet.getActiveRange();
  return range ? range.getA1Notation() : 'nothing selected';
}

function writeToCell(value) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  sheet.getActiveCell().setValue(value);
}

Generate yours

Need a variant? Gnaw writes a custom version from one sentence — fields, triggers, edge cases handled.

Walkthrough

Two files, one sidebar

A sidebar is two pieces: a .gs file that opens it and exposes server functions, and an .html file that renders inside the panel. In the Apps Script editor, create sidebar.gs with the code above, then add a new HTML file named sidebar (no extension needed in the editor; it saves as sidebar.html). The name you pass to createHtmlOutputFromFile must match that file name exactly, or you get a silent failure with no useful error in the console.

To wire a menu item that opens it, add an onOpen trigger: function onOpen() { SpreadsheetApp.getUi().createMenu('Tools').addItem('Open sidebar', 'showSidebar').addToUi(); }. That runs automatically whenever the sheet loads, putting your entry in the top menu bar.

Why google.script.run trips everyone up

The number-one confusion with sidebars is that google.script.run is asynchronous. When the sidebar HTML calls google.script.run.getActiveRange(), execution continues immediately on the next line. The return value is not available there. It arrives later, via a callback you register with .withSuccessHandler(fn) before the call.

The pattern looks like this inside your sidebar HTML: google.script.run.withSuccessHandler(function(result) { document.getElementById('range-display').innerText = result; }).withFailureHandler(function(err) { console.error(err.message); }).getActiveRange(); — success and failure are separate handlers chained before the function name. I keep a small wrapper in a utils file that pre-attaches a default failure handler logging to the browser console, because silent failures with no withFailureHandler are surprisingly easy to produce and painful to debug.

A second gotcha: the return value must be JSON-serializable. You cannot return a Range object, a Sheet object, or a Date directly. Convert to a primitive or a plain object on the server side before returning. getA1Notation() is the canonical way to hand back range information.

The sandbox and what it blocks

Sidebar HTML runs inside a sandboxed iframe with a strict Content Security Policy. External CDN scripts — jQuery from a CDN, for example — are blocked by default. You have two options: copy the library source directly into the HTML file, or use the IFRAME sandbox mode (which is the default since 2020 and the only mode still supported). Older code you find online may reference HtmlService.SandboxMode.NATIVE or EMULATED; those are deprecated and do nothing now.

External fonts from Google Fonts work because Apps Script explicitly allowlists fonts.googleapis.com. Other third-party origins do not get that treatment. If you need a charting library or a component framework, inline it. The sidebar size cap is 300px wide; you can set a smaller width but not larger. For a full-width panel you want showModalDialog instead, which takes a pixel-specified width and height.

Want a custom version?

Describe your sheet and the rule you want. Gnaw writes the Apps Script — fields, triggers, edge cases — in one shot.

Generate yours Browse BullPack

FAQ

4 questions

Why does google.script.run return undefined instead of my data?: Because it's asynchronous. The return value only arrives in the function you pass to .withSuccessHandler(). Reading it on the line after the call always gives undefined.
Can I use React or Vue inside a sidebar?: Yes, but you have to inline the built bundle into the HTML file. You cannot load it from a CDN due to the Content Security Policy on the iframe. A compiled single-file bundle pasted into a script tag works fine.
How do I pass data from the sidebar back to the spreadsheet?: Call a server-side function via google.script.run that accepts the value as a parameter and uses setValue() or setValues() on the sheet. The sidebar cannot touch the spreadsheet DOM directly; all writes go through the server bridge.
The sidebar closes every time I reload the sheet. How do I keep it open?: You cannot. The sidebar does not persist across page loads; it must be reopened. A common pattern is to auto-open it from onOpen() so it appears immediately each time the sheet loads.

// one good script a week