Notes on Google Apps Script (GAS) for programming Google Spreadsheets.
Leanpub Book: https://leanpub.com/googlespreadsheetprogramming
Currently updating this book, will release updated version this year (2016). I will review the current content and plan to add two new chapters. If you buy, or have bought the current version, you get all updated versions for free.

Friday, November 23, 2012

Creating a GUI Application (Part 1)

Note Added 2013-06-02: The GUI Builder is being phased out, Html Service is now the preferred GUI tool, see blog entry on this.

Summary

Use the Google GUI Builder tool and JavaScript to build a web application that implements the functionality of a well-known single page application that does mortgage calculations.

Demonstrate how input values are retrieved from GUI textboxes and how JavaScript-calculated values are written back to other textboxes.

Highlight a few “gotchas”.

Show how the application deals with bad input.

Show the differences between deploying the application as a web application as against a spreadsheet-bound one (next blog entry).

An earlier blog post covered the basics of adding buttons and menus to Google Spreadsheets in order to improve the user experience. However, the Google web toolkit allows us to do better and provides a GUI builder. We can use this GUI builder to quickly knock up an application which can be run either:1: From the containing Google spreadsheet where we can use a menu item to display the application. This approach is closely analogous to how we can build a GUI form in Excel VBA and display it using a menu.2: As a web service where the application can be built from either Google Sites or Google Spreadsheets. The important difference here is that the application can run entirely independently of the spreadsheet. (The next blog entry)

If you’ve used VB/VBA to build GUIs, you will appreciate how convenient these tools are, especially if you have also tried something like Tkinter in Python as I have. I found Tkinter usable but very frustrating and felt very much spoiled by the VB drag-and-drop approach However, desktop applications are not nearly as important as they once were and the web is undoubtedly now the dominant platform for delivering functionality to users.

To use, you simply enter the figures in the four textboxes in the top part of the form and the results are posted to the three lower textboxes. The link above gives the actual JavaScript code that does the calculations.

The Google web service application is here (To view, you'll be asked to authorize it):https://script.google.com/a/macros/javascript-spreadsheet-programming.com/s/AKfycbxjeVl4oyH5QXXmPwD68qJBAWP7mXNCRN7Ltpns9nlNyy6_waQ/execand looks like this:It wins no design awards but it does implement the basic functionality of the web page and it was easy create with the GUI builder.To build the above, start with a Google Spreadsheet and in its Script Editor select menu item File->Build a user interfaceThe GUI shown above is composed of four Vertical Panels with the two on the left containing labels only and the two on the right containing textboxes only.At the top, above the panels, there is one long label with the text “MORTGAGE MINDER” and the bottom there is a single button that when clicked calls code to do the mortgage calculations and writes the output to the textboxes in the bottom right panel.The application now has two components:

The JavaScript code

The GUI

Linking the two together is not difficult but there are a few traps for the unwary:1: To retrieve input values from GUI textboxes, you need to give the textboxes names! The ID and the name are not the same, see this screenshot:2: To link a callback to button action where the callback needs to be able to read input values in a textbox, the GUI builder needs to be filled in as in this screenshot:Under the Mouse Click event for the button labelled calculate select the function named “calculate”, this function had already been written in the Script Editor and the GUI Builder is clever enough to load all available functions into the drop-down list. To the right of the box above containing “calculate”, there is a +/- toggle. To enable the callback to be able to access textbox input values, each of the input textbox names must be entered here as a comma-separated list. For this application, although not visible because the box is not expandable, there are four, they are txtHousePrice,txtDownPayment,txtAnnualInterest,txtTerm. These are the names of the four input textboxes on the top-right panel in the GUI. The Callback CodeNotes on Callback Function “calculate()”

Line number 19 gets an instance of the active application

Lines 20 and 21 set the text and color of the top-most label. Code further down will set res-set the color to red and display a label indicating if the user input is “bad” so these lines just ensure that after each click of the button “calculate” that the label is re-set to its defaults.

Lines 22-25 retrieve the textbox user inputs. To do this, the name of the textbox is required as described above. The call to parseFloat() also includes the second radix parameter to ensure input numbers are interpreted as base 10.

Lines 26-29 use the “isNaN()” function to check that the four calls to parseFloat() returned numbers, if not the top-most label color is set to red and the text “BAD INPUT” is displayed. The GUI does not disappear, however, so the user can fix the input and click the “calculate” button again.

Lines 30-33 do the calculations and assign the values to variables.

Lines 35-37 assign the variable values to the output textboxes.

Line 39 can be omitted and no error is generated. However, the output will not appear in the GUI so forgetting to include it means the application will not work.

Displaying The GUI From a SpreadsheetThe allows the GUI to be displayed from a spreadsheet and when the spreadsheet is opened a menu is added that has one item that, when selected, loads the GUI.GUI Display CodeCode Notes

Line 1: ECMAScript5 includes a strict mode that is highly recommended, I now always use. To learn why it’s a good idea, consult this authoritative source (Nicholas Zakas).

The “onOpen()” function was discussed in a previous post and allows us to add a menu item dynamically when the spreadsheet is opened.

The single menu item is used to call the “showGui()” function.

Line 12 above is where the form created using the GUI builder is loaded into the application.

This GUI runs but is tied to a spreadsheetas shown below. The code itself in the "calculate()" function is very simple and the calculations could of course have been done in the spreadsheet itself. However, in the next blog entry, I will demonstrate how this spreadsheet application can be very easily turned into a web application that is entirely independent of the spreadsheet.

The application looks like this when loaded from the spreadsheet:The added GUI menu is just visible on the right-hand side. (Drop me an email if you'd like the full spreadsheet from which this was built!)

About Me

I work in medical informatics and have used spreadsheets, databases and statistical software applications for over 10 years.
I have published an early partial version of a book on Google Spreadsheet programming at https://leanpub.com/googlespreadsheetprogramming