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.
Frequently Asked Questions
How do I create a custom sidebar in Google Sheets?
To create a custom sidebar in Google Sheets, you need to write and run an Apps Script code along with an HTML file for the layout. Follow the provided steps to create a new sidebar in Google Sheets.
What is the purpose of a sidebar in Google Sheets?
A sidebar in Google Sheets is a vertical area displayed on the right or left side of the screen that contains user interface elements like customization tools or options menu. It allows for easier navigation and access to specific features.
Can I customize the appearance of a sidebar in Google Sheets?
Yes, you can customize the appearance of a sidebar in Google Sheets by modifying the HTML code used for the sidebar layout. You can design it to match your preferences and needs.
Is it necessary to grant permission to run Apps Script in Google Sheets for creating a sidebar?
Yes, it is essential to grant permission to run Apps Script in Google Sheets when creating a sidebar. This permission ensures that the custom code functions smoothly within Google Sheets.
How can I add a new menu to my custom sidebar in Google Sheets?
You can add a new menu to your custom sidebar in Google Sheets by including the necessary script code within the Apps Script. This menu can contain various items that provide additional functionality.
What are the advantages of having a sidebar in Google Sheets?
Having a sidebar in Google Sheets offers quick access to tools and options, simplifies navigation, and enhances the overall user experience. It provides a convenient way to interact with different features of Google Sheets.
Can I create multiple custom sidebars in a single Google Sheets document?
Yes, you can create multiple custom sidebars in a single Google Sheets document by writing separate Apps Script codes for each sidebar. Each sidebar can serve different purposes and provide distinct functionalities.
Are there predefined templates available for creating a sidebar in Google Sheets?
Google Sheets does not offer predefined templates for creating a sidebar. However, you can find sample codes and tutorials online to help you create customized sidebars according to your specific requirements.