Google Sheets offers various tools and features that can enhance your workflow, one such great add-on is known as the Google Sheets sidebar.
If you are wondering, “How do I create a sidebar in Google Sheets?” Then look no further! In this article, we will show you how to use the Google Apps Script editor to create a custom sidebar in Google Sheets.
If you’re already feeling overwhelmed just by the thought of that, don’t worry it isn’t as hard as it sounds. Although it will be very useful to have familiarity with the fundamentals of coding, it isn’t absolutely necessary.
Following the step-by-step tutorial on how to create a sidebar in Google Sheets provided below should help you set up a custom spreadsheet sidebar in no time.
Let’s begin by answering the first question in most people’s minds.
Table of Contents
What is a Google Sheet Sidebar?
A sidebar is a type of user interface element that appears to the left or right of the main window or on the user screen to display related information or a list of options or navigation options.
You can create a custom sidebar using Google Apps Script, it is a built-in script editor that allows you to create various add-ons and elements for G-Suite applications.
These custom sidebars essentially allow you to design your own user interfaces within Google Sheets, which can be very useful in a variety of situations.
For example, let’s assume you’ve created custom data entry forms and calculators in HTML or any other program, and you want an effective way of viewing them. Such data can be displayed in a sidebar. You can also modify the advanced formatting menu in your spreadsheet.
This is just one Google Sheets sidebar example. There are tons of useful things you can do with a Google Sheets sidebar!
How to Create a Custom Google Sheets Sidebar
Some Google Sheets features seem a bit complicated to work with initially but it can be very easy once you get an idea of the basics.
To create a custom sidebar, you must write and run some Apps Script code. Since this step involves running custom third-party code, the user must grant permission to run the sidebar script. Sidebars can only be displayed when users perform an action, such as clicking on a custom button or selecting a menu item from a custom menu.
In this tutorial, we’ll make a custom sidebar form, that allows you to enter Lead details such as First name, Last name, Lead Status, and Contact into the spreadsheet. Follow the steps below to create your very own custom sidebar:
Step 1: Create a new spreadsheet and go to extensions and click on Apps Script.
Step 2: In Google Apps Script, create a code.gs file and paste the following code into it:
```
function onOpen() {
SpreadsheetApp
.getUi()
.createMenu("Sidebar")
.addItem("Display Sidebar", "showAdminSidebar")
.addToUi();
}
function showAdminSidebar() {
var widget = HtmlService.createHtmlOutputFromFile("Form.html");
widget.setTitle("Leads Form");
SpreadsheetApp.getUi().showSidebar(widget);
}
function appendRowFromFormSubmit(form) {
var row = [form.firstName, form.lastName, form.status, form.contact];
SpreadsheetApp.getActiveSheet().appendRow(row);
}
```
Step 3: Next, create a file named Form.html and paste the following code into it:
```
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<script>
function submitForm() {
google.script.run.appendRowFromFormSubmit(document.getElementById("leadsForm"));
}
</script>
</head>
<body>
<h1>Enter Lead details</h1>
<form id="leadsForm">
<label for="firstName">First name</label>
<input type="text" id="firstName" name="firstName"><br><br>
<label for="lastName">Last name</label>
<input type="text" id="lastName" name="lastName"><br><br>
<label for="contact">Contact</label>
<input type="number" id="contact" name="contact"><br><br>
<div>
<label for="status">Status:</label><br>
<input type="radio" id="warm" name="status" value="Warm">
<label for="warm">Warm</label><br>
<input type="radio" id="cold" name="status" value="Cold">
<label for="cold">Cold</label><br>
<input type="button" value="Submit" onclick="submitForm();">
</form>
</body>
</html>
```
Step 4: After creating both code.gs and Form.html files, save the changes.
Step 5: Head back to Google Sheets, and you’ll see the new Sidebar menu.
When you try to run this new Sidebar, here’s the message that will show up:
How to Authorize Apps Script Code in Google Sheets
When you run a script written in Google Apps Script that requires authorization in Google Sheets for the first time, you will see an ‘Authorization Required’ message.
But don’t worry; simply click Continue and follow these simple steps to authorize Apps Script and have your script running in under a minute.
Step 1: A new window will appear, prompting you to select an account to which you are currently logged in. From this window, click on the relevant account.
Step 2: Click BACK TO SAFETY if you are unsure about the safety of the script. Otherwise, you can proceed by clicking the Advanced link in the bottom left corner.
Step 3: This will open a new section at the bottom of the window where you can click on the Go to Script Name (unsafe) link.
Step 4: Click ‘Allow’ to grant the necessary permissions and run the script.
Once you have authorized the Apps Script, you’re all set! You can now access the Sidebar at any time. You can now enter data into the form and then click on Submit to add it to the sheet.
Here’s a look at the final product:
Conclusion
Google Sheets features like the Google Sheets slicer, subscript & superscript, and the sidebar makes working with spreadsheets much more fun and easier.
Especially a sidebar, as it is not only useful in getting to know the right information quickly but also makes for a very visually pleasing addition to a spreadsheet.
There are many ways in which Google Sheets users can customize their sidebar. One such way is to build custom user interfaces within a sidebar using CSS and JavaScript. Customizing your sidebar isn’t very hard once you have successfully set it up.
In this tutorial, I showed you how to use Apps Script to create a custom sidebar in Google Sheets. I hope this article has effectively answered your question “how do I create a sidebar in Google Sheets”.
Related: How to Make a Google Sheets Button
Frequently Asked Questions
What is a sidebar in Google Sheets?
A sidebar in Google Sheets is a type of user interface element, a small vertical area, that displays related information, options, or navigation options. The sidebar can prove to be very useful in various situations.
Google Apps Script, a built-in script editor that allows you to create various add-ons and elements for G-Suite applications, can be used to create a custom sidebar. These custom sidebars allow you to create your own user interfaces within Google Sheets, which can be very useful.
What is a sidebar layout?
The sidebar layout in Google Sheets is how the sidebar appears in your spreadsheet. You can change the layout from the Apps Script by editing the code.
Related:
