How to Create a Sidebar in Google Sheets [AppScript]

In this article, we will learn how to create a sidebar in Google Sheets. Well, we all are aware of the sidebar and we also know that there is no built-in sidebar or its functionality in Google Sheets, then why this article? Well, you must have known or heard about App Script, really appreciable they are. And, yes you can do anything in Google Sheets, I repeat anything using the app script properly. Google has changed Sheets into Programmable software for anyone for free. Yes, you can use the original Google Sheets version, and if you need any additional function, you can create a custom function, custom formulas can also be made in Sheets, and guess what any feature you have seen anywhere in the world, you can create it into Google Sheets programmatically using app script.

So, that’s something very interesting and crazy, today we will learn how to create a sidebar in Google Sheets, and it will be very easy I promise.

Importance of using a Sidebar in Google Sheets

A sidebar is a visual element, it comes under so many of the user interface elements, it’s a small vertical area that appears normally to the left or the right in larger windows and on the top of the screen in narrow screens. You must have seen it on your laptop and mobile phone. Sidebars are pretty common they help to navigate users throughout a complete design, it can be a website, a series of tutorials, or anything that has more out of a single page view. The sidebar may have related content, may have resources to read, may have menu-like items, or anything that a developer wants to keep as per the owner’s guidelines. So, knowing that it’s a visual element, so how we are going to create it? Well, again app script can do more than this, believe me.

The sidebar in Google sheets is a user interface element, you can call it a panel that is displayed on the right-hand side of Google Sheets. Just like an add-on sidebar, you may have seen when using an add-on. Or when you customize charts, you have a sidebar on the right side where all the charts’ details are presented in a very organized manner.

Google Sheets provide an in-built script editor called Apps Script which can create various add-ons and elements for G-Suite applications. It’s a free tool developed by Google Developers to give everything they can imagine. It’s not wrong to know app script as a complete programming or scripting language, and a good thing is that it is pretty similar to JavaScript syntax and very easy to learn as there are so many resources online, and a large community that is increasing every day. So, let’s move ahead and learn how to create a sidebar in Google Sheets.

How to Create a Sidebar in Google Sheets?

We need an app script code to write the functionality of the sidebar, and an HTML file to make the basic layout and a little design for our sidebar. So, from this section, we will learn and implement the steps and methods required to create a sidebar in Google Sheets. So, let’s get started.

How to Create a Sidebar in Google Sheets – App Script Code

In this first section, we will learn how to create a sidebar in Google Sheets, we will cover the app script code part, and then we will move forward. So firstly, for this step, we need a new spreadsheet file and an internet connection, and of course, I will provide you the code to copy and paste into your code editor.

Step 1

Open a Google Sheets file, or launch a new Sheet with a blank template

Step 2

Go to Extensions > Apps Script

Step 3

A new tab will open, and you will see your code editor here, with a blank function initiator.

Step 4

Remove the empty function, and copy and paste the below-provided app script code into your editor

Code for App Script code.gs file:

</pre>
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);

}

Pro Tip: Make sure you are in the code.gs tab from the sidebar.

Step 5

Save the code, and now we need one HTML file as well. So, let’s see that in the next section.

In the above app scrip file or the code in the Code.gs script; OnOpen() function creates a custom menu called ‘My Menu’ in the google sheets main menu bar. That menu can contain various items, but here I have added a single item which is a sidebar named “My Sidebar 1“. So, when you click this menu item, the showAdminSidebar() function the second function of our code will execute and it will open the sidebar we have created on the right-hand side of your Google Sheets file

Now, it’s 50% work done, now we need an HTML file to complete the sidebar, for that please see the next section and continue creating a sidebar in Google Sheets.

How to Create a Sidebar in Google Sheets – HTML Code

In this section, we will learn how to create a sidebar in Google Sheets, and we are learning how to integrate an HTML code with your app script code. So, for this, you need to have the previous section done, and you should have an HTML file. So, let’s see how to do that.

Step 1

Click on the Plus sign + in the sidebar of the code editor

Step 2

Click on the HTML

Step 3

It will ask to name your file, Name it “Sidebar“, and it’s crucial, if you use a different name then it will not work because this name has a connection with the previous code file in code.gs.

Step 4

Here, is your HTML file

Step 5

Add the following code inside the body element of your HTML file

<p> This is my new Sidebar</p>

<input type="button" value="Close" onclick="google.script.host.close()" />

Step 6

If you are not aware of basic HTML, you can copy the entire code below and paste it 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

Click on the save button, and there will no errors.

Note that when you try to run this program, it will require access to your account, so you have to log in it with your Google Account. I have shown you so many times, how to authorize your Google account with the app script code editor, still you can check out the below steps to see how to authorize it.

How to Create a Sidebar in Google Sheets – Authorize Google Account with Apps Script

In this section, we will quickly learn how to create a sidebar in Google Sheets and how to authorize the app script code with your Google Account to continue executing the app script code.

Step 1

Click on the review permissions

Step 2

Choose a Google Account to continue

Step 3

It’s an unsafe page, the browser will block access to this site, if you want to proceed unsafe, please click on the Advanced button

Step 4

Then click on go to untitled project unsafe

Step 5

Click on allow after reading the privacy terms

As you click on the allow, you will be automatically synced to your current project with your Google Account.

I hope you find this section helpful.

How to Create a Sidebar in Google Sheets – Access Sidebar in Google Sheets

We have done all the required things to create a sidebar in Google Sheets. Now, it’s time to access our sidebar from the Sheets.

Step 1

A new menu item is added to your main menu with the name of the sidebar you chose in the code.

Step 2

Click on the sidebar name and then click on the first menu item “My Sidebar 1”

Step 3

You can see your sidebar is opened on the right-hand side, you can see there is nothing inside the sidebar, only a button.

We only learned how to create a sidebar in Google Sheets, and not about its content and elements. That’s why it looks empty, but we can write some more codes to add content and beautiful elements inside our sidebar in some other tutorial.

Important Notes

Frequently Asked Questions

Is it possible to add a Sidebar in Google Sheets?

Yes, It is possible to add a sidebar in Google sheets using AppScript.

How to add content to the Sidebar in Google Sheets?

You can add content inside the sidebar using an app script code only, there is no other way. Or, if you are a programmer, you can write entire new code that covers the code of the sidebar and also its components. We have added a single button here, you can add more buttons and beautify them using inline CSS.

Conclusion

Wrapping up how to create a sidebar in Google Sheets. We learned how to use code to create the logic of the sidebar in Google Sheets, and then how to use or connect an HTML file with your app script code to create the body, visual elements, and buttons-like things inside your sidebar. I hope you find this article interesting and helpful. I will see you soon with more interesting and useful tutorials. Have a nice day!