Friday, August 10, 2012

Adding Buttons And Menus For User Interaction

All the code examples given so far on this blog are either executed as User-Defined Functions (UDF) from cells on sheets or are executed by calling a function from the Script Editor.  UDFs are clearly an acceptable way of providing functionality to users since they can be called and used just like the built-ins.  However, as discussed previously, UDFs are limited in what they can do; they can only return results to a cell.  This is fine for use in spreadsheet templates but many real spreadsheet applications need to do stuff like add sheets, change formats, insert/delete ranges, etc.  Expecting end-users to fire up the Script Editor and execute JavaScript directly from there is unrealistic and more likely to scare them off than to encourage them to use functionality you have laboured to provide.  Spreadsheet and JavaScript wizardry will be lost on end users if the interface to use it is not easy to use.  Excel provides mechanisms such as buttons that can be added directly to sheets, menus that can be added programmatically and graphical user interfaces that provide text entry boxes, radio buttons, drop-down lists etc on on forms.  Google spreadsheets can also implement these three features and the subject of this posting is the first two; Adding buttons to sheets directly and programmatically adding menus to spreadsheets.

Adding a button to a spreadsheet is very clearly explained here.   I followed the steps they detailed , very easy and very well explained.  This entire tutorial is well worth reading and the specific part for adding the button is entitled Section 4: Creating a button to run the code

Adding a menu requires some JavaScript but the code is very simple.  
The example given below adds a useless menu to its containing spreadsheet every time the spreadsheet is opened.  To use simply create a spreadsheet, open the code editor and paste in the code, save and close.  When you re-open the spreadsheet a new menu entitled “My Menu” will appear on the right hand side that contains three items.  The menu can sometimes take a number of seconds to appear so be patient!

Programmatically Adding A Menu To A Google Spreadsheet

// Being laxy here by creating these variables in the global namespace (demo purposes).
var activeSS = SpreadsheetApp.getActiveSpreadsheet();
var activeSh = activeSS.getActiveSheet();
var rngToChange = activeSh.getRange('A1:C10');

// "onOpen()" is one of Google Spreadsheets pre-defined "trigger" events and whatever code it contains is
// executed whenever the spreadsheet in which it is defined is opened.
function onOpen() {
 var menuItems = [{name: "Clear Color", functionName: "clearColor"},
                    {name: "Make Red", functionName: "makeRed"},
                    {name: "Make Blue", functionName: "makeBlue"}
 activeSS.addMenu("My Menu", menuItems);

// Three useless demo functions whose actions are assigned to menu items.
// The functions respectively, clear color, make red, or make blue the cells in range A1:C10
function clearColor() {

function makeRed() {

function makeBlue() {

Code Notes

  • The key function here is the one named “onOpen()”  This is a default trigger (Google’s term) function that is fired when the spreadsheet opens.  It is in this function that the menu is created, the items are added, and script actions are assigned to the menu items.
  • The three functions that perform the actions simply set or clear colors in a pre-defined range on the active sheet.
  • Three global variables for the active spreadsheet, active sheet, and range A1:C10 of the active sheet are created at the top of the script.  This is not good practice and is not how production code should be written where an application object would be created as described in earlier posts.
  • When writing this code I noticed that errors in the “onLoad()” appear to be ignored.  The spreadsheet opened but the menu did not appear, it was only when I tried to run the “onLoad()” function from the script editor that I noticed the coding error.
  • The code here is for demonstration purposes only but it does suggest how one could use it as a starting point to provide a user-friendly interface to a Google spreadsheet application.

Tuesday, August 7, 2012

User-Defined Functions Re-Visited: Use The Buit-Ins When They Are Provided

I’ve noticed that the blog entry here on user-defined functions is the most popular of the entries in this blog based on the number of page views it receives so there is clearly some interest in this topic.  This fact as well as having gained more experience in JavaScript and Google Spreadsheets has prompted me to revisit this topic.
Writing back then, I stated:

The Google spreadsheet applications has a very large number of built-in functions, check to ensure a required function does not already exist before writing a customised version.”.  

I should have heeded my own advice because one of the examples I gave could have been done using a Google Spreadsheet built-in function and JavaScript could have been avoided altogether, namely the one that concatenated a list of strings.  Google Spreadsheets come with a very neat built-in string function called JOIN which is tailor-made for this purpose.

JOIN Example
I’ve got the following entries in range A1:A5:


I want to create a single string from each of the cell entries with the individual entries surrounded by a single quote.  The JOIN function can do most of this and the task can simply be completed by prepending and appending single quote characters to the output of the JOIN function like so:

=CONCATENATE("'", JOIN("','",A1:A5), "'")

This produces the desired output:


The Google code on the JOIN function is brief: “Concatenates a one-dimensional array using a specified delimiter.”  The delimiter can be one or more characters but the input range must be one-dimensional, i.e. one row or one column.

Another Google Spreadsheet text function that performs the opposite task to JOIN is SPLIT which takes a string input and a delimiter to split on and returns an array of values.

SPLIT Example
Given this text in cell A1:
We would like to use the colon as a delimiter to break it up into separate cells in row 1 with the first value in cell B1
To do this, enter in the following formula into B1 and hit RETURN


The output, ranging from cell B1 to F1 is
this is colon separated text

The SPLIT function is clearly very useful and provides a means of implementing the very useful Excel Text to Columns... functionality.  The Google documentation for SPLIT is considerably more informative than that for JOIN:
Splits text based on the given delimiter, putting each section into a separate column in the row. The string is the text you want to split. Treat_delimiters_individually is an optional True/False parameter. If omitted or True, the function will use each character within the delimiter string as a separate delimiter. If False, the function will use the entire delimiter string only to split your text.
In addition, there is a Learn More link for SPLIT that is worth reading. An important point to note on SPLIT is the possibility of defining more than one delimiter character so if our input was sloppy:
and nd used both colons and semi-colons as delimiters, we could use the following call to SPLIT to get tha same output as first time:


Google Spreadsheets implements most of the built-in Excel functions but also adds some of its own such as JOIN and SPLIT shown above.

A few more text functions not found in Excel that are well worth investigating are those that use regular expressions:


I’ll cover these functions and JavaScript regular expressions in a future blog entry.

In addition to these Google-specific text functions, there is also a very useful filter function called UNIQUE that can be used to extract, as its name indicates, a unique list.

UNIQUE Example
Given these entries in range A1:A9
and the formula =UNIQUEA1:A9) in cell B1 produces the following output in range B1:B7
This is much more convenient than Excel’s menu-driven UNIQUE filter.  The input and output given above demonstrate how the UNIQUE filter is case-sensitive.

Here is a link to a full list of Google Spreadsheet built-in functions.

Writing user-defined functions is both a good way to learn spreadsheet programming and to extend functionality but if the tool you are using and programming provides built-ins that will do the job, always use them. The built-ins should be bug-free (we hope) and should perform better. Also, as I have learned, Google Spreadsheets has useful functions lacking in Excel so it is worth perusing the link above carefully.