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:
- Select the desired range by clicking the cell and dragging over with the mouse.
- 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.
onOpen
To start, we need to create the menu item that will call the sidebar.
6 7 8 9 10 11 12 |
//Create Menu function onOpen(e){ var ui = SpreadsheetApp.getUi(); ui.createAddonMenu() .addItem("Display range", "displayRange") .addToUi(); }; |
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.
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).
14 15 16 |
function onInstall(e){ onOpen(e); }; |
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.
18 19 20 21 22 23 |
function displayRange(){ var html = HtmlService.createHtmlOutputFromFile('index.html') .setTitle("Display Range"); SpreadsheetApp.getUi().showSidebar(html); }; |
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
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
<!DOCTYPE html> <html> <head> <base target="_top"> <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script> </head> <body> <div id="rangeResult"></div> <script> function addRange(rangeStartEnd){ $('#rangeResult').text(rangeStartEnd); }; google.script.run.withSuccessHandler(addRange).makeSelection(); </script> </body> </html> |
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.
24 25 26 27 28 |
function makeSelection(){ var range = SpreadsheetApp.getActiveRange() var rangeStartEnd = range.getA1Notation() return rangeStartEnd; }; |
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
code.gs
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
/* *Gets the active range of some thing *and displays it on the sidebar. */ //Create Menu function onOpen(e){ var ui = SpreadsheetApp.getUi(); ui.createAddonMenu() .addItem("Display range", "displayRange") .addToUi(); }; function onInstall(e){ onOpen(e); }; function displayRange(){ var html = HtmlService.createHtmlOutputFromFile('index.html') .setTitle("Display Range"); SpreadsheetApp.getUi().showSidebar(html); }; function makeSelection(){ var range = SpreadsheetApp.getActiveRange() var rangeStartEnd = range.getA1Notation() return rangeStartEnd; }; |
index.html
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
<!DOCTYPE html> <html> <head> <base target="_top"> <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script> </head> <body> <div id="rangeResult"></div> <script> function addRange(rangeStartEnd){ $('#rangeResult').text(rangeStartEnd); }; google.script.run.withSuccessHandler(addRange).makeSelection(); </script> </body> </html> |
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.
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)
This is very helpful, and just what I needed. Thank you very much!