sodcalculator

10 Tech How-To

Sidebar in Google Sheets: How to Create One

Guide 10-54351 · Updated · Step by step

This guide shows you how to add a custom sidebar to Google Sheets using Apps Script. A sidebar is a panel that stays open on the right side of your spreadsheet, giving you quick access to custom tools, buttons, or information without leaving the sheet. You’ll need a Google account and a spreadsheet to follow along.

What You’ll Need

  • A Google account (free)
  • A Google Sheets spreadsheet (create one at sheets.new if needed)
  • Basic comfort with copying and pasting code

Step-by-Step

  1. Open your Google Sheet. Go to sheets.google.com and open the spreadsheet where you want the sidebar.
  2. Open the Apps Script editor. Click Extensions in the top menu, then select Apps Script. This opens a new tab with a blank script file named Code.gs.
  3. Delete any default code. In the script editor, remove everything that’s already in the Code.gs file so it’s completely empty.
  4. Create the function that shows the sidebar. Paste this code into the Code.gs file:
    function showSidebar() {
      var html = HtmlService.createHtmlOutputFromFile('Sidebar')
          .setTitle('My Custom Sidebar')
          .setWidth(300);
      SpreadsheetApp.getUi().showSidebar(html);
    }

    This function loads an HTML file named Sidebar and displays it as a sidebar in your spreadsheet.

  5. Create the HTML file. In the Apps Script editor, click the + next to Files (or the plus icon in the left panel), then choose HTML. Name it Sidebar (exactly, without the .html extension).
  6. Add content to the HTML file. Replace the default content in the new HTML file with something simple, like:
    <!DOCTYPE html>
    <html>
      <head>
        <base target="_top">
      </head>
      <body>
        <h1>Hello from the sidebar!</h1>
        <p>This is your custom panel.</p>
        <button onclick="google.script.run.closeSidebar()">Close</button>
      </body>
    </html>

    This HTML will be shown inside the sidebar. The button calls a function named closeSidebar that you’ll add next.

  7. Add a close function to Code.gs. Go back to the Code.gs tab and add this function below the first one:
    function closeSidebar() {
      SpreadsheetApp.getUi().showSidebar(null);
    }

    This function hides the sidebar when the button is clicked.

  8. Save your project. Click the floppy disk icon or press Ctrl+S (Windows) or Cmd+S (Mac). Give the project a name like “My Sidebar” when prompted.
  9. Run the function to test it. In the toolbar, select showSidebar from the function dropdown, then click the Run button (the triangle icon).
  10. Authorize the script. The first time you run it, Google will ask for permissions. Click Review permissions, choose your Google account, and click Allow on the warning screen (it says the app isn’t verified, but that’s normal for personal scripts).
  11. See your sidebar. After authorization, the sidebar should appear on the right side of your spreadsheet. If it doesn’t, click Extensions > Apps Script again and check the execution log for errors.
  12. Add a menu item (optional but recommended). To make the sidebar easy to open later, add this function to Code.gs:
    function onOpen() {
      SpreadsheetApp.getUi()
          .createMenu('Custom Menu')
          .addItem('Show Sidebar', 'showSidebar')
          .addToUi();
    }

    Then run onOpen once to install the menu. After that, you’ll see a Custom Menu in your spreadsheet’s menu bar, and clicking Show Sidebar will open your sidebar.

Common Mistakes

  • Wrong file name. If your HTML file is named Sidebar.html but you call createHtmlOutputFromFile('Sidebar'), it works. But if you name it something else, you must match exactly, including capitalization.
  • Forgetting to save before running. If you edit the code and don’t save, the old version runs.
  • Not authorizing the script. If you skip the permission step, the sidebar won’t appear and you’ll see an error.
  • Using the wrong HTML file. Make sure you’re editing the HTML file, not the Code.gs file, when adding the sidebar content.
  • Missing the <base target="_top"> tag. This tag prevents the sidebar from opening links in a new tab inside the sidebar itself; without it, links may behave oddly.

Tips

  • Use the sidebar for quick actions. You can add buttons that run other Apps Script functions using google.script.run.myFunction().
  • Style with CSS. You can add a <style> block in the HTML file to make the sidebar look nicer, with colors, fonts, and spacing.
  • Pass data from the sheet. Use google.script.run.withSuccessHandler(callback).getSheetData() to read data from the spreadsheet and display it in the sidebar.
  • Test with a simple function first. Before building a complex sidebar, get a simple “Hello” working, then add features gradually.
  • Use the sidebar for dashboards. You can show charts, summaries, or even a form to input data that writes back to the sheet.

Troubleshooting

  • Sidebar doesn’t appear after running. Check the “Execution Log” in the Apps Script editor (View > Logs or the console icon). If there’s an error like “ReferenceError: closeSidebar is not defined”, make sure the function exists in Code.gs and is spelled correctly.
  • You get a “Permission denied” error. This usually means you haven’t authorized the script. Run the function again and click Review permissions when prompted.
  • The sidebar appears but is blank. Make sure your HTML file has content. Also, check for typos in the HTML file name in the createHtmlOutputFromFile call.
  • The button in the sidebar does nothing. Ensure the button’s onclick uses google.script.run correctly, and that the function you’re calling exists in Code.gs.
  • After adding the menu, it doesn’t show. You must run onOpen once manually to install the menu. After that, it will appear automatically when the spreadsheet is opened.
  • Changes to the HTML don’t show up. Save the file, then close and reopen the sidebar, or run showSidebar again to refresh it.

Frequently Asked Questions

Can I have multiple sidebars in one spreadsheet?

Yes, you can create multiple HTML files and different functions to show each one, but only one sidebar can be visible at a time. Showing a new sidebar replaces the current one.

Does the sidebar work on mobile devices?

No, Google Sheets mobile apps do not support custom sidebars. They only appear in the desktop browser version.

Can the sidebar access data from the spreadsheet?

Yes, you can use google.script.run to call functions in Code.gs that read or write spreadsheet data, and pass results back to the sidebar.

Is it possible to make the sidebar wider or narrower?

Yes, you can set the width in pixels using .setWidth(300) in the showSidebar function. The default is 300 pixels.

Do I need to be a programmer to create a sidebar?

No, you can copy and paste the code from this guide. Basic understanding of HTML and JavaScript helps, but you can start with simple examples and learn as you go.

Can I share a spreadsheet with a sidebar with others?

Yes, when you share the spreadsheet, the sidebar script is shared too. However, each user will need to authorize the script the first time they open it.

Related

General steps for current mainstream versions; menus can differ by device and version. See the disclaimer or report an error.

More in this bed

The whole Tech How-To bed →