Adding a custom sidebar to Google Sheets allows you to integrate personalized tools and interfaces directly into your spreadsheets. Google Sheets provides an in-built script editor called Apps Script, which enables you to create custom sidebars using HTML, CSS, and JavaScript.

Creating a sidebar in Google Sheets using Apps Script

Step 1: Open your Google Sheets document. From the menu bar, click on Tools and select Script editor.

This will open the Apps Script editor in a new tab, where you can write your custom code.

Step 2: In the Code.gs file, replace any existing code with the following script:

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

The onOpen() function in this script adds a custom menu called My New Menu to the Google Sheets menu bar. Inside this menu, there's an item labeled My sidebar 1. Clicking this item will execute the showSidebar() function, which displays the sidebar on the right side of your sheet.

Step 3: In the Apps Script editor, create a new HTML file for your sidebar content.

To do this, click the + icon next to the Files section and select HTML from the dropdown menu.

Rename the new HTML file to Sidebar. Ensure that this name matches the one specified in the showSidebar() function in your script.

Step 4: Inside the Sidebar.html file, add the following code within the <body> tags:

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

This code will display the text "This is my new Sidebar" and a Close button that will close the sidebar when clicked.

Your complete Sidebar.html file should now look like this:

<!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 code above sets up the basic structure of your sidebar.

Step 5: Save your project by clicking the Save icon in the toolbar. Then, run your script by clicking the Run icon.

When you run the script for the first time, Google Sheets will prompt you to authorize the script.

How to authorize Apps Script code in Google Sheets

Step 6: Upon running the script, a dialog will appear asking you to select your Google account. Choose your account to proceed.

A warning message may appear stating that the app isn't verified. Click on Advanced, then select Go to Untitled project (unsafe) or your project's name.

In the next window, click Allow to grant the necessary permissions for your script to run.

Step 7: After authorizing, return to your Google Sheet and refresh the page. You will now see a new menu item called My New Menu in the menu bar. Click on it and select My sidebar 1 to display your custom sidebar.

Your custom sidebar will now appear on the right side of the Google Sheets window, displaying the text and the Close button you added. Clicking the button will close the sidebar.

By following these steps, you've successfully added a custom sidebar to your Google Sheet using Apps Script, enhancing your spreadsheet with additional functionality.