Saturday, April 4, 2015

Using Google Spreadsheet Named Ranges To Dynamically Populate Html Service GUI Elements

Introduction

In Excel VBA it is quite easy to transfer values from spreadsheet cells to form controls such as listboxes and comboboxes. This is a useful feature because the developer can hide and protect the ranges that provide the values. This blog entry demonstrates how to achieve a similar effect with Google Apps Script (GAS) and Google Spreadsheets using Html Service.
The code given below also employs some useful GAS tricks for dynamically re-assigning a name to a range as the rows are added. In addition it demonstrates how client-side JavaScript can interact with GAS and use values returned by GAS functions. The source code is available on GitHub (link below)

The Code

There are two source files, one called Code.gs that contains the GAS code and another called index.html that contains the GUI-building code (HTML) and the client side JavaScript.

The code is available on GitHub at this location. Either check out the code using Git or copy the two source code files into a GAS Script Editor (better to use GitHub raw format as this preserves indentation)

Screenshot Showing Input Spreadsheet And GUI


 

How To Run The Code

  • Create a new Google Spreadsheet, give it any name you wish
  • Create a sheet named "DropdownValues" (or re-name an existing sheet to this).
  • Add some values in column A of this sheet starting at cell A2. Cell A1 is assumed to be the column name.
  • Go to the script editor and copy in the two files from GitHub at this location. You will end up with one file called Code.gs and the other called index.html.
  • Give the project any name you like when prompted to do so in the Script Editor.
  • In the script editor select the function named displayGUI() and execute it. 
  • Switch to the spreadsheet view and you should see the GUI with the dropdown list populated as in screenshot above.
  • To see how the named range expands dynamically when new data rows are added in column A of sheet "DropdownValues", close the GUI and add some more values to this column. When you re-execute function displayGUI(), you should see the new values in the dropdown list.

Code Discussion

You will need to have gotten the code from the two source files from the GitHub link above to follow along with this discussion. The code is extensively documented so if you know GAS,  you should be able to follow along. The most difficult part of the example is understanding how the GAS code gets the range values from the spreadsheet and passes them to the client-side JavaScript code that then, through Document Object Model manipulation, builds and populates the HTML select (that is drop-down) element.  In this example, there are two types of JavaScript in action. The first, GAS in Code.gs, runs on the server. The second is the client-side JavaScript in the file index.html. The client-side JavaScript wil be familiar to anyone who has done front-end web development.

 Here is my explanation of how it all works (make sure you have the code open to follow along):
  • When the GAS function displayGUI() is executed it first calls another GAS function, setRngName(), that sets the name Cities for all cells in column A, starting with and including cell A2, that contain values.
  • The remainder of the code in displayGUI() is Html Service code that creates the GUI from the HTML file called index.html.
  • When the GUI loads, it fires an event called "load", this event causes the client-side JavaScript function called populate to fire. This line in question window.addEventListener('load', populate);). This is at the bottom of the index.html file.
  • The function populate is the part of the code that ties the two types of JavaScript together:
function populate(){
  google.script.run.withSuccessHandler(onSuccess)
    .getValuesForRngName('Cities');
} 

  • This function executes the GAS function getValuesForRngName() passing it a hard-coded string argument "Cities" that is the named defined for the spreadsheet range that contains the values we want to transfer to the GUI drop-down.
  • The code to call server-side GAS looks complicated. We need to define another client-side JavaScript function to execute when the call to the GAS function succeeds. The function that is passed in is called onSuccess() in this example.
  • The onSuccess() function receives the array of arrays returned by the GAS function getValuesForRngName(). The onSuccess() function uses the given argument values to add options to the select element. The code here is straightforward JavaScript client-side code that uses DOM methods to get and manipulate DOM elements (document.getElementById() and document.createElement()).

Conclusion

The example here shows how values can be transferred dynamically from a Google Spreadsheet to a Html Service GUI by the interaction between server-side GAS and client-side browser JavaScript. The sheet can even be hidden and the code will still work. The example given was for demonstration purposes only and the application does not do anything with the selected value from the GUI drop-down. In addition, the GUI is launched from the GAS Script Editor; A menu dynamically added by the GAS onOpen() trigger would improve this aspect/ Earlier blog entries provide example code that can be used to enhance and extend this example:
  1. Html Service Basics - Write Form Data To A Spreadsheet 
  2. Automated Generation Of SQL From Google Spreadsheets Using The Revealing Module Pattern
The code examples here also over-use hard-coded values, for example, the range name "Cities" is hard-coded. However, despite these shortcomings, the example application does provide a good example of how GAS and client-side JavaScript can work together to provide dynamic functionality. I am revising my Leanpub book and will go into more detail in this type of example in the revised version of the book.
Let me know through a comment or an email if any of this is unclear or if you have some suggestions or corrections. Meanwhile,  happy coding!



13 comments:

  1. Exactly what I was after, thanks :)

    ReplyDelete
  2. I could not able to get in google spreadsheet.

    ReplyDelete
  3. I downloaded the code from Github and changed "function setRngName()" to get values from a range from an existing sheet on a spreadsheet and the log shows values are being obtained for setRngName. But there is no dialog box being generated. I changed the code in "function displayGUI()" to the current code shown in the Google Apps documentation and still no dialog box. I know this is being forward, but is the current code functional? I know Google Apps has made a lot of changes recently. I would really like to adapt this for a current project. TIA

    ReplyDelete
    Replies
    1. Did you downloaded index.html?

      Delete
    2. Yes, I've been able to make it work after some tweaking. As Julio alludes, the .gs and the .html files work in tandem -- to get the dialog box to open, you need both.

      Delete
  4. This comment has been removed by the author.

    ReplyDelete
  5. I was wondering if you'd be able to provide information on how to add additional drop down lists to add to the one in Column A, how to add an element etc.

    ReplyDelete
  6. video tutorial would further ease it

    ReplyDelete
  7. thanks! :D was wondering is there a way to limit the data that is pulled on a condition? Such as using the WHERE in SQL?

    ReplyDelete
  8. This comment has been removed by a blog administrator.

    ReplyDelete
  9. This comment has been removed by a blog administrator.

    ReplyDelete
  10. Hi Michael,

    Just curious what you see in the future for Google Sheets.

    ReplyDelete