How to Create a Sidebar in Google Sheets?

A Sidebar is a small vertical area that is displayed on the right or left of the screen and includes many of the user interface elements, such as customizing and navigation tools or an options menu. In Google Sheets, when you customize a chart, a sidebar appears on the right side of the Google Sheets with customizing tools.

Similarly, when we open Power Tools in Google Sheets, a sidebar appears on the right side with the Power Tools options menu. In addition, Google Sheets allows us to create our own sidebar with customized user interface elements.

How to Create a Sidebar in Google Sheets?

We need to write and run an Apps Script code in order to create a custom sidebar in Google Sheets. We also need an HTML file for the basic layout of our sidebar.

As we are trying to run a third-party custom code, we must grant permission to run this Apps Script in Google Sheets. Below are some steps to create a new sidebar in Google Sheets.

Step 1: Open Apps Script

Go to Extensions and click on Apps Script from the drop-down:

Step 2: An Apps Script window will open next. Now copy the following Script Code:

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);
}

Step 3: Delete the empty function and paste the above-copied script code:

Step 4: Now click on the Plus icon in front of Files to add a new file, and then click on HTML from the drop-down options:

Step 5: Rename the file as a Sidebar and do not use any other name as it has the connection with our Code.gs file:

Step 6: Copy the following HTML code and paste it between the body elements:

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

Instead, if you are not aware of the HTML, then delete the already given code and paste the following HTML code into your HTML file:

<!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>

Step 7: Now click on the Save icon at the top to save the script:

Step 8: Now back to the Google Sheets and go to Extensions and click on Macros from the drop-down then click on Import macros:

Step 9: Click on the Add function under both the onOpen and showSidebar:

Step 10: Again go to Extension and click on Macros. In the next dropdown, click on onOpen:

Step 11: It requires permissions to run the function, so click on Continue in the next pop-up window:

Step 12: Choose your account to grant permissions:

Step 13: At first, Google couldn’t verify the app, but don’t panic and click on Advance:

Step 14: Click on Go to Untitled project (unsafe) at the bottom:

Step 15: Finally, click on Allow in the next window to grant permissions to run the app:

Step 16: Now again go to Extensions and click on Macros. In the next drop-down again click on onOpen:

Step 17: This will run the function as you grant the permission in the previous, and a Sidebar will be added with the name My New Menu to the taskbar in the Google Sheets:

Step 18: Now click this sidebar option that you added in the Google Sheets and click on My sidebar 1 from the dropdown:

Step 19: The sidebar will be open on the right side of the Google Sheets:

Conclusion

In Google Sheets, the sidebar is a vertical area that appears on the right of the Google Sheets. To add a custom sidebar in Google Sheets, go to extensions and open the apps script window. Paste the above-mentioned code in the code.gs file and add a new HTML file and paste the above-mentioned HTML code and click on the save icon. Go back to Google Sheets and go to import macros and allow the permissions to run the app. Once authentication is granted, click on added functions in the macros, and the new sidebar will be added to the Google Sheets.

How to Create a Sidebar in Google Sheets?