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() {
 rngToChange.clearFormat();
}

function makeRed() {
 rngToChange.setBackgroundColor("red");
}

function makeBlue() {
 rngToChange.setBackgroundColor("blue");
}


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.

2 comments:

  1. Hi Mike,

    I just came across your blog, and it's similar to what am looking for. Am new to programming in Ruby, but this is what I intend to do

    I want to create a button that when clicked on will clear the colour of the targeted cells so if the cell has Red, Green and Yellow.
    Will you be able to assist me with this at all


    Thanks in advance

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

    ReplyDelete