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)

Friday, February 8, 2013

MySQL Re-Visited - Metadata

Set Up a Free Cloud MySQL Instance 

I used Xeround.  Other options include Google and Amazon and many others.
Install the mysql command line client on own machine.
Log in to Xeround MySQL cloud instance using local mysql client.
Download and unzip the world.sql file
While logged in to Cloud mysql database instance from local machine:
-- Login looks something like this for Xeround
$ mysql --user=tigger --host=instance<some number> --port=<portno> --password
-- Create a database
mysql> CREATE DATABASE world;
-- Switch to the newly created database ,
mysql> USE DATABASE world;
-- Populate the database,
mysql> SOURCE /<path_to_sql_file>/world.sql; 

Once the above SQL script is executed, the database is populated.

Extracting Metadata From The Database

Important metadata can be retrieved using mysql commands but this entry is concerned with how JDBC can be used to obtain the same information. Important metadata about MySQL itself, tables, views, and stored procedures can be retrieved from the JdbcConnection object using the getMetaData() method which returns a JdbcDatabaseMetaData object. This object can be interrogated via its methods to retrieve database metadata.

Function To Print Metadata To The Log
function printMySQLMetadata() {
  var conn = Jdbc.getConnection('jdbc:mysql://instance<num><port>/world', <usename>, <pwd>),
      dbMetadata = conn.getMetaData(),
      tableNames = [],
  Logger.log('Major Version:' + dbMetadata.getDatabaseMajorVersion());
  Logger.log('Minor Version: ' + dbMetadata.getDatabaseMinorVersion());
  Logger.log('Product Name: ' + dbMetadata.getDatabaseProductName());
  Logger.log('Product Version: ' + dbMetadata.getDatabaseProductVersion());
  Logger.log('Supports transactions: ' + dbMetadata.supportsTransactions());
  rsTables = dbMetadata.getTables(null, null, null, ['TABLE']);
  while ( {
  tableCount = tableNames.length;
  Logger.log('The Table Names And Their Columns Are:');
  for (i = 0; i < tableCount; i += 1) {
    rsColumns = dbMetadata.getColumns(null, null, tableNames[i], null);
    while ( {
      Logger.log(tableNames[i] + ':' + rsColumns.getString(4));

Code Notes

  • Establish a connection to the database (reader will need to fill in the details in the connection string to match their own database).
  • Return the metadata as a JdbcDatabaseMetaData object by calling the getMetaData() method of the Connection object.
  • Call various JdbcDatabaseMetaData object methods to retrieve metadata and print the output to the log.
  • Surprisingly, the method supportTransactrions() reports true even though the tables are MyISAM type.  I assumed you needed InnoDB table types to get this feature in MySQL.  I'll check that the tables do indeed support transactions as this code suggests.
  • A list of tables is extracted using the record set (Recordset) returned by the method getTables() (record sets were covered in the earlier blog entries).  The three nulls are required in this method call.  The table type argument has to be passed as an array even though it has only one element.  Other table types that could be passed includes views.
  • The column index argument used in the record set getString() method to extract the table name is 1-based.  The argument given, that is 3, was discovered by trial-and-error.   The table names are then stored in an array.
  • The column names for each table are extracted by processing the columns record set returned by the getColumns() method with the table name as an argument.  Once again, three nulls are required in this method call.
  • The column names are in the fourth column of the record set (argument = 4)  in the columns record set getString() method call. Once again, this was found by trial-and-error.
  • Once the above function has executed, go to  Views->Logs... in the Script Editor to see the output which should begin something like this:
Major Version:5
Minor Version: 1
Product Name: MySQL
Product Version: 5.1.42
Supports transactions: true
The Table Names And Their Columns Are:

This was just an introduction to what is a large and important area.  I will write a full chapter on Google Spreadsheets/JDBC in this leanpub book in the coming weeks.  So if interested, keep a watch on that.  I'll also post more in future blogs.

Wednesday, February 6, 2013

Manage Google Drive Files and Folders

Update August 8th 2013: There is chapter 8 in this book that covers Google Drive files and folders extensively and their manipulation using Google Apps Script.



You can manage your Google Drive files and folders using Google Apps Script.  The top-level "class" which implements this functionality is called DocsList.  The class exposes a large number of methods that can be used to manage Google Drive folders and files.  To see a full list, create a new spreadsheet and go to the Script Editor and type DocsList followed by period (".").

In addition to showing how to manipulate DocsList, the code given here also provides an example of how structured exception handling can be used to test for a specific error and respond appropriately depending on the error.

The code below demonstrates some of the main methods provided by DocsList.  To use, create a new spreadsheet and paste the code into the Script Editor. The code has been subjected to JSLint and is available here on GitHub.

/*global DocsList: false */

// Manage Google Drive Folders.
// Check if the given folder name exists.
// If yes, return the folder object for the pre-existing folder.
// If not, create the folder using the given folder name.
// Return the folder object for the newly created folder with the given folder name.
function createFolder(folderName) {
    'use strict';
    var folder,
        errMsg = 'Cannot find folder';
    try {
        folder = DocsList.getFolder(folderName);
    } catch (Error) {
        if (Error.message.indexOf(errMsg) > -1) {
            folder = DocsList.createFolder(folderName);
        } else {
            throw {'name': Error,
                   'message': 'Unable to create folder ' + folderName};
    return folder;
// Manage Google Drive files in folders.
// Add files of file type 'spreadsheet' to folder called 'spreadsheets'
// Add files of file type 'document' to folder called 'documents'.
// Leave all other file types alone.
function arrangeDocsToFolders() {
    'use strict';
    var ssFolder = createFolder('spreadsheets'),
        docFolder = createFolder('documents'),
        allFiles = DocsList.getAllFiles(),
        allFilesCount = allFiles.length,
        rootFolder = DocsList.getRootFolder();
    for (i = 0; i < allFilesCount; i += 1) {
        if (allFiles[i].getFileType().toString() === 'spreadsheet') {
        } else if (allFiles[i].getFileType().toString() === 'document') {

The function to execute is "arrangeDocsToFolders()".  However, before executing any code, first beware that the code does the following:
  • It checks to see if two folders called "spreadsheets" and "documents" already exist in the user's Google Drive.  If not, it creates them.
  • It then gets a list of all files in the user's Google Drive, loops over them, and determines their file type. File types "spreadsheet" and "document" are added to the folders "spreadsheets" and "documents", respectively.
  • Execution of the indicated function may require "authorization".
  • Google Drive is different from a standard Windows/Unix directory system in a number of respects.  Firstly, the folders created are organizational rather than physical.  Secondly, files can also belong to multiple folders.  Lastly, there can be more than one folder with the same name in the same Google Drive!
  • The code removes files from the so-called root folder if they have been copied into either the "documents" or "spreadsheet" folders.

Code Notes:

These notes use line numbers so to follow along, view the code in the Script Editor or in GitHub to follow along.
  • The function createFolder() takes a folder name as an argument and uses this argument to get a folder object (line 13). It assigns the folder object to the variable folder.
  • This call is wrapped in a try - catch structure. If the given folder name does not exist, the catch part (lines 14 - 20) checks the error message and if this message indicates that the given folder does not exist, it creates the folder and stores the object returned by  DocsList.createFolder in the variable folder. Otherwise, if it cannot create the folder, it throws an error.
  • The Folder object is returned (line 22).
  • Function "arrangeDocsToFolders()" calls "createFolder()" to either create new folders and return them as Folder objects or else just return the Folder objects for the pre-existing folder names.
  • It then loops over the array of File objects returned by the DocsList.getAllFiles() and tests each element to determine if it is a spreadsheet or a document.
  • The toString() method calls on lines 37 and 39 are required for the string equality test.  It took me a little time to determine why the return values from the File method getFileType() were not behaving as expected.  I assumed they were strings but they are in fact objects.
  • The File method addToFolder() is then called with a Folder object as an argument (lines 38 and 41).  Initially, I tried this code using the folder name string.  The code ran without error but did not do anything.  I would have expected an error but not so!
  • Lines 39 and 42 remove files from the root folder that have been added to the target folders.
Re-running the code has no effect.  Once files have been added to a folder, they are not re-added and  no error is generated if the addToFolder() method is called for a file already in the folder.  However, with folders, as mentioned earlier, multiple folders can have the same name in Google Drive and that's why the code checks if they already exist before it tries to make a new one.

Generating A JavaScript Object Mapping File Names To File IDs

Added Content 2013-02-20: I have added a function to the GitHub code, name getFileNameIdMap() that when executed returns a JavaScript object literal that maps the file names to the file IDs for all files in a user's Google Drive.  File IDs are useful because many GAS methods use the ID to manipulate files.  The IDs are less user-friendly than the file names so this function can be used to generate the desired mapping and calling code can then query the returned object using file names to retrieve the corresponding file IDs.

Here is the code:
// Loop through all the user's Google Drive files and
// return a JavaScript object literal mapping the file names to
// the file IDs.
function getFileNameIdMap() {
    var files = DocsList.getAllFiles(),
        fileNameIdMap = {},
        fileCount = files.length;
    for (i = 0; i< fileCount; i += 1) {
        fileNameIdMap[files[i].getName()] = files[i].getId();
    return fileNameIdMap;

There are many other DocsList, File, and Folder methods that have not been covered here but this topic will be re-visited in future blog entries.

In time, the topics discussed here will be discussed further in this Leanpub book.

Monday, February 4, 2013

Google Spreadsheet Programming - Book On Leanpub

 Added Chapter 8 on Google Drive Files, Folders and Permissions

Added content to appendix A on VBA Google Apps Script. New examples and corrected formatting, spelling and added some more notes. This appendix is part of the free sample and can be downloaded from the leanpub book site.

Regarding the MySQL chapter, Xeround that was used for the examples has gone out of business.  Will revise that chapter using Google Cloud SQL this time round.

Preparing Chapter 9 on Email and Calendars, hope to post a draft soon.

Trying different covers!

UPDATE 14-MAY-2013
Added a chapter on Google Apps Script-JDBC-MySQL
Added an appendix with side-by-side examples in Excel VBA and Google Apps Script

UPDATE 18-FEB-2013:
Two new chapters have been added to the book.  The cover Spreadsheet, Sheet and Range objects.  There are also lots of practical examples for these objects, see:

See details here

I am now working on chapter 6 which is on GUIs.  I'll also post some new blog entries on this topic as I prepare the chapter.


I have decided to try publishing a book on Google Spreadsheet programming.  There are just three chapters at the moment (~20%) of the total and this is just a rough draft.  The content so far is all free see Leanpub.

I will continue to update this blog and post entries in parallel with adding to the book.  I would greatly appreciate feedback on the contents. Negative feedback is just as useful to me as the positive variety!

The next two chapters are in preparation and will  cover the SpreadsheetApp, Spreadsheet, and Range objects.  I plan to add these in the next week or so.  The style is very much example-driven and all the examples will be subjected to JSLint and published on GitHub.