Wednesday, February 20, 2013

GUI Basics - Write Form Data To A Spreadsheet

Note Added 2013-06-02: The GUI Builder is being phased out, Html Service is now the preferred GUI tool, to see how it used to replicate the functionality developed here  see blog entry on this.

(Added 17th May 2013): Simple GUI creation is described in much greater detail with additional examples in my leanpub book "Google Spreadsheet Programming" in chapter 7 .

Learn How To:
  • Create a GUI form without the GUI Builder
  • Create and configure textbox, label and button widgets
  • Create handlers
  • Link handlers to widgets and callbacks
  • Write the form text box input to a spreadsheet

Two earlier blog entries covered use of the GUI Builder for construction of a simple user interface:
  1. Blog Entry 1
  2. Blog Entry 2

The GUI Builder is very convenient. However, to really learn how to build GUIs in GAS there is no substitute for hand-coding the application.  This entry builds a really simple GUI to demonstrate how to:
  • Create and place GUI components -widgets- on the user interface (what is known as a form in VB).
  • Associate callbacks with widget actions such as button-clicking.
  • Extract values from text box widgets.
  • Write text input values to a spreadsheet.

The code example is quite long so it has not been pasted in here but it can be retrieved from this GitHub link.

As an experiment, I have put the code in Pastebin so it can be viewed here in an IFrame.  I tried to do this with the GitHub page but the IFrame remains empty.

Here is the code as formatted by Pastebin:


To run the code, copy it from above or from Github and paste it into the Script Editor. Then execute the function guiDemo(). Switch to the spreadsheet to see the GUI, add some text to the text boxes and click the button labelled "Add Row". You will then see your input written to the spreadsheet.

The GUI should display like this:

To close it, simply click the button labelled "Exit".

It is probably easier to follow along if you paste the code into a Script Editor.

Code Notes
  • The GUI is built by the function guiDemo() (defined in lines 32 - 74).
  • The GUI resides in the spreadsheet and cannot be run standalone in its current for.
  • Three lines are common to all of these spreadsheet-hosted GUIs in GAS:
    1. Create a UiApp instance (line 34).
    2. Create a SpreadsheetApp instance (line(35).
    3. Attach the UiApp instance to the SpreadsheetApp instance using the latter's show() method (line 73).
  • The GUI layout is achieved by adding so-called horizontal panels to a top-level vertical panel.
  • The label-text box pairs are then added to the horizontal panels and their order and their containing-panel order is determined by the sequence of calls to the panel add() method.
  • The button click events do the real work of taking text box input and writing it to the spreadsheet.
  • The button click events are linked to handler function as follows (taking the "Add Row" button as an example:
    • The handler object is created: addRowHandler = ui.createServerHandler('addRow'); passing it the function name as an argument. The function name given is that of the event handler (aka callback).
    • The click event is associated with the handler: btnAddRow.addClickHandler(addRowHandler);
    • Other widgets that need to be involved when the event fires need to be registered (lines 65 and 66 that call the handler object method addCallbackElement).
  • When the event handler is called, it takes the event object as an argument (named "e" in this example).
  • In order for the event to be able to access the text box values entered by the user, the text box name must have been assigned when the GUI was created. This was done in lines 67 and 68.
  • In order for the event to be able to alter a widget, e.g. re-set text box contents to an empty string, it needs an instance of the widget. To get the widget instance, the widget ID must have been set when the GUI was created. This was done in lines 69 and 70.

Some GAS GUI "Gotchas"
I've sometimes found that the GUI displays without error but does not do as I want. Here are a few of the reasons for this that I've encountered:
  • Forgetting to call the add() method on the panel or form so the widget never appears.
  • Confusing widget IDs and names. They are required for different things, see above!
  • Trying to change a GUI property in the event handler but then forgetting to return the UiApp instance (line 28 in the example).
  • Forgetting to register other widgets with the handler object (try commenting out lines 65 and 66 to see the effect).

This is a very simple example but it is worth playing with and extending to learn more.  More detailed examples and discussions are given in my book leanpub book in chapter 7 (just published)

No comments:

Post a Comment