How to Get Something from Google Sheets and Display it in the Sidebar in Google Apps Script

What if you want to get a value or a range from Google Sheets and show it in your sidebar using Google Apps Script?

First you will need to get the value or range by using Googles server-side script. Then you will have to display it client-side in your HTML document.

Documentation on getting the server-side and client-side talking nicely to each other is a little vague. Hopefully, this very basic tutorial will help clear things up.

In this tutorial I will also be using Jquery.

Let’s get started.

Display a Selected Range on the Sidebar

For this task all we are trying to do is display a selected range to the sidebar. The user will:

  1. Select the desired range by clicking the cell and dragging over with the mouse.
  2. Got to the Add-On > Display Range > Display Range

The Sidebar will load with a the selected range as the standard column-row notation. To get another selection Display Range will have to be run again.

Display Selected Range On Sidebar in Google Sheets with Google Apps Script


To start, we need to create the menu item that will call the side bar.

When the spreadsheet opens (onOpen) , it will ask the user interface (ui) to create a sub menu called Display Range in the add-ons menu.

When this menu item is clicked it will run the displayRange function.


We’ll also call the function onInstall that will essentially just run the onOpen function when the script is run by the user for the first time (Just in case we want to publish this at a later date).

The onOpen and onInstall functions are called when the Google sheet is loaded.


When the user clicks the Display Range button in the add-ons menu, the displayRange function is executed.

For this function we create the side bar.

Our html variable calls Google’s HtmlService  which essentially allows us to use HTML that is handles client-side.

We first let Google Apps Script know what file we intend to run for this purpose with createHtmlOutputFromFile('index.html'). We will be creating the HTML in a bit. Link 

We can also set a title for our sidebar here with .setTitle("Display Range") . This is optional. setTitle will allow you to set a title for any HTML page you create. Because we are creating a sidebar it will set the title for the sidebar. Link

You might find in some examples scatter across the net that you have to Set Sandbox Mode. This has since be depreciated and HtmlService is constantly set to IFRAME. Link

Once you set up your HtmlService we call the spreadsheets user interface again and ask it to show the sidebar with our html inside with SpreadsheetApp.getUi().showSidebar(html);. Link


There is one more function in our code.js file but we will call it from the index.html file so it is probably better to talk about what is in this html file first.

Let’s take a look at the whole thing:

As you can see we have some pretty standard HTML here. The one thing you might not be aware of is the <base target="_top"> on line 4 which allows us to open the body of the html inside the sidebar.

On line 5 I call jquery so I can run my functions a little more neatly.

In the body all I am displaying is a div with the id rangeResult we’ll use this div to display our range we select in Google Sheets.

Jumping down to line 15, we see:; The part allows our client-side html and javascript to talk with the server-side google apps script. It will run our makeSelection function in our file. Link

The withSuccessHandler checks to see if the server-side function runs successfully and returns a value. In our case it is checking to see if makeSelection() runs as it should and returns a value. If all runs as planned it will allow us to put the returned value into the addRange function in our index.html file on line 11.

On line ll addRange takes the returned value of makeSelection() as the argument rangeStartEnd .

Line 12 then looks for the div with the index ‘rangeResult’ and adds the rangeStartEnd value as text inside the div.


Finally, lets go back to and see what makeSelection does when; is called in the index.html file.

When makeSelection is called, the first thing it does is gets the active range. The active range is the range the user selects with their mouse by clicking and dragging over the cells. We put this in the variable, range here.

We want to know where the selection starts and finishes by telling us the column letter and row numbers. To do this we use getA1Notation() on our range in our rangeStartEnd variable.

Finally we return rangeStartEnd which will give us our start and end column and row.


That’s it. A very very simple example of displaying server-side google sheets data on a client-side sidebar.

Full Code



One thought on “How to Get Something from Google Sheets and Display it in the Sidebar in Google Apps Script”

Leave a Reply

Your email address will not be published. Required fields are marked *