How to Get Something from Google Sheets and Display it in the Sidebar in Google Apps Script (Updated January 2022)

Google Apps Script: SpreadsheetApp, getUI, HTMLservice, 

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 Google’s 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. Go to the Extensions > Display Range > Display Range
    (note Extensions updated from Add-ons)

The Sidebar will load with a 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

onOpen

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

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.

Hire me for our next Google Workspace project.

onInstall

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.

dispayRange

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

For this function, we create the sidebar.

Our HTML variable calls Google’s HtmlService which essentially allows us to use HTML that is handled 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 been 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

🐐You can support me for free by using this Amazon affiliate link in your next tech purchase :Computers & Stuff! 🐐

index.html

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 the range we selected in Google Sheets.

Jumping down to line 15, we see:  google.script.run.withSuccessHandler(addRange).makeSelection(); The google.script.run 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 code.gs 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.

makeSelection

Finally, let’s go back to code.gs and see what makeSelection does when google.script.run.withSuccessHandler(addRange).makeSelection(); 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.

Create and Publish Google Workspace Add-ons with Apps Script Course 300px

 

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

Full Code

code.gs

index.html

Create and Publish a Google Workspace Add-on with Apps Script Course

Need help with Google Workspace development?

Go something to solve bigger than Chat GPT?

I can help you with all of your Google Workspace development needs, from custom app development to integrations and security. I have a proven track record of success in helping businesses of all sizes get the most out of Google Workspace.

Schedule a free consultation today to discuss your needs and get started or learn more about our services here.


3 thoughts on “How to Get Something from Google Sheets and Display it in the Sidebar in Google Apps Script (Updated January 2022)”

  1. Thanks so much for this – very clear! This code works for me when i call it from another function on debug mode, but when i just run it from that function, it doesn’t show the text. Any idea why i appear to need the code to run more slowly to work?! (ps. i’m fairly new to google scripts and javascript)

Leave a Reply