How to Create a Sidebar in Google Sheets

A sidebar is a user interface element (a small vertical area) that appears either to the left or the right of the larger window or on the user screen to display related information or a list of choices or navigation options.

The sidebar in Google sheets is a user interface panel that is displayed on the right-hand side of Google Sheets. Google provides an in-built script editor called Apps Script which can create various add-ons and elements for G-Suite applications. It can also be used for building your own custom sidebars in Google sheets.

This article will show you how to make a custom sidebar in Google Sheets using Google Apps Script editor.

Creating a SideBar in Google Sheets using Apps Script

If you want to make a custom sidebar, you need to enter and run certain code in the Apps Script editor. Then you can build your own widgets within the sidebar using HTML, CSS, and Javascript codes.

First, open the Google sheets. In the Google sheets menu, click ‘Tools’ and select ‘Script editor’.

That will open the Apps Script editor in a new tab of your browser where you can write your user interface code.

Write following code in Code.gs page:

function onOpen() {
  SpreadsheetApp.getUi() 
      .createMenu('My New Menu')
      .addItem('My sidebar 1', 'showSidebar')
      .addToUi();
}

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

In the above Code.gs script code, the OnOpen() function creates a custom menu called ‘My New Menu’ in the google sheets menu bar. That menu will contain a menu item called ‘My sidebar-1’. When you click this menu item, the showAdminSidebar() function (the second part of the code) will be run and the sidebar will be shown at the right-hand side of the Google sheet window.

Next, we need to create an HTML file in the Script editor, and then with this file, you can create the sidebar.

To create the HTML file, click the plus (+) icon next to Files in Apps Script editor and select ‘HTML’.

This will create an HTML file below Code.gs. Rename the file as ‘Sidebar’. This name should be same as the one added in the showSidebar() function (var html = HtmlService.createHtmlOutputFromFile(‘Sidebar’)).

Write the following code within the <body> section of the Sidebar.html file:

<p> This is my new Sidebar</p>
<input type="button" value="Close" onclick="google.script.host.close()" />

The above code displays the text string ‘This is my new Sidebar’ and ‘Close’ button which closes the sidebar when clicked on it.

After you’re finished writing the above code in the <body> section of the Sidebar.html, it should contain the following code:

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
    <p> This is my new Sidebar.</p>
    <input type="button" value="Close" onclick="google.script.host.close()" />
  </body>
</html>

The screenshot:

When you’re done entering both codes, save the project by clicking the save icon in the toolbar (see the below screenshot). Then run the functions by clicking the ‘Run’ icon.

Whether you run the script here or you select the custom menu item in the Google sheets toolbar (for the first time), Google will ask you to authorize the script to run. Since you’re running a third-party custom widget, google will request your authorization. Once you authorize the script, it will display the sidebar within your Google sheet.

How to Authorize Apps Script Code in Google

To authorize your custom script follow these steps:

Once you run the script, Google will ask you to select your google account. After you selected it, a small pop-up will appear, in that click ‘Review permissions’.

Another pop-up will appear, here select ‘Show Advanced’ and click ‘Go to Untitled project (unsafe)’ (It will show your project name).

In the next window, click ‘Allow’ and google sheet will run your script.

Once you have done that, go back to your Google sheet and refresh it. The new custom menu (My New Menu) will be added to your Google sheet toolbar, which we added through Code.gs script. Click the ‘My New Menu’ and select the menu item ‘My sidebar 1’ to display the sidebar.

Now your custom sidebar will show up at the right-hand side of your google sheet with the text and the button we added (as shown below). When you click the button, the sidebar will be closed.