Google Fusion Tables with Apps Script

I started with Apps Script in the same way many of you probably did: writing extensions to spreadsheets. When it was made available in Sites, I wondered whether it could meet our needs for gathering roadmap input from our sales engineering and enterprise deployment teams.

Gathering Roadmap Data

At Google, teams like Enterprise Sales Engineering and Apps Deployment interact with customers and need to share product roadmap ideas to Product Managers. Product Managers use this input to iterate and make sound roadmap decisions. We needed to build a tool to support this requirement. Specifically, this application would be a tool used to gather roadmap input from enterprise sales engineering and deployment teams, providing a unified way of prioritizing customer requirements and supporting product management roadmap decisions. We also needed a way to share actual customer use cases from which these requirements originated.

The Solution

This required bringing together the capabilities of Google Forms, Spreadsheets and Moderator in a single application: form-based user input, dynamically generated structured lists, and ranking.

This sounds like a fairly typical online transaction processing (OLTP) application, and Apps Script provides rich and evolving UI services, including the ability to create grids, event handlers, and now a WYSIWYG GUI Builder; all we needed was a secure, scalable SQL database backend.

One of my geospatial colleagues had done some great work on a demo using a Fusion Tables backend, so I did a little digging and found this example of how to use the APIs in Apps Script (thank you, Fusion Tables Developer Relations).

Using the CRUD Wrappers

Full sample code for this app is available and includes a test harness, required global variables, additional CRUD wrappers, and authorization and Fusion REST calls. It has been published to the Script Gallery under the title “Using Fusion Tables with Apps Script.”

The CRUD Wrappers:

/**
 * Read records
 * @param {string} tableId The Id of the Fusion Table in which the record will be created
 * @param {string} selectColumn The Fusion table columns which will returned by the read
 * @param {string} whereColumn The Fusion table column which will be searched to determine whether the record already exists
 * @param {string} whereValue The value to search for in the Fusion Table selectColumn; can be '*'
 * @return {string} An array containing the read records if no error; the bubbled return code from the Fusion query API if error
 */
function readRecords_(tableId, selectColumn, whereColumn, whereValue) {

  var query = '';
  var foundRecords = [];
  var returnVal = false;
  var tableList = [];
  var row = [];
  var columns = [];
  var rowObj = new Object();

  if (whereValue == '*') {
    var query = 'SELECT '+selectColumn+' FROM '+tableId;
  } else {
    var query = 'SELECT '+selectColumn+' FROM '+tableId+
                ' WHERE '+whereColumn+' = ''+whereValue+''';
  }

  var foundRecords = fusion_('get',query);

  if (typeof foundRecords == 'string' && foundRecords.search('>> Error')>-1) 
  {
    returnVal = foundRecords.search;
  } else if (foundRecords.length > 1 ) {
    //first row is header, so use this to define columns array
    row = foundRecords[0];
    columns = [];
    for (var k = 0; k < row.length; k++) {
      columns[k] = row[k];
    }

    for (var i = 1; i < foundRecords.length; i++) {       row = foundRecords[i];       if( row.length > 0 ) {    
        //construct object with the row fields
        rowObj = {};
        for (var k = 0; k < row.length; k++) {
          rowObj[columns[k]] = row[k];
        }
        //start new array at zero to conform with javascript conventions
        tableList[i-1] = rowObj; 
      }
    }
    returnVal = tableList;
  }

  return returnVal;
}

Now all I needed were CRUD-type (Create, Read, Update, Delete) Apps Script wrappers for the Fusion Tables APIs, and I’d be in business. I started with wrappers which were specific to my application, and then generalized them to make them more re-usable. I’ve provided examples above so you can get a sense of how simple they are to implement.

The result is a dynamically scalable base layer for OLTP applications with the added benefit of powerful web-based visualization, particularly for geospatial data, and without the traditional overhead of managing tablespaces.

I’m a Fusion tables beginner, so I can’t wait to see what you can build with Apps Script and Fusion Tables. You can get started here: Importing data into Fusion Tables, and Writing a Fusion Tables API Application.

Tips:

  • Fusion Tables is protected by OAuth.This means that you need to authorize your script to access your tables. The authorization code uses “anonymous” keys and secrets: this does NOT mean that your tables are available anonymously.
  • Some assumptions were made in the wrappers which you may wish to change to better match your use case:
    • key values are unique in a table
    • update automatically adds a record if it’s not already there, and automatically removes duplicates
  • Characters such as apostrophes in the data fields will be interpreted as quotation marks and cause SQL errors: you’ll need to escape these to avoid issues.
  • About”) and column names to construct your queries

    via Ferris Argyle

Google Apps at I/O 2011: A Lots of Excitement

It’s been only two short weeks since Google I/O 2011! There was fantastic energy at the event, and developers had their choice of over 100+ sessions on topics ranging from Google Apps to Android to App Engine to HTML5 to Cloud Robotics.


Here are the highlights from the Google Apps track:

Sessions

In the Google Apps track, we had 8 sessions on a variety of topics of interest to Google Apps Marketplace developers, systems integrators and customers alike. All of the sessions are available in HD on YouTube and we’ve also posted many of the session slides and notes.

Google Apps Marketplace:

  • Launch and Grow your Business App on the Google Apps Marketplace provided an intro to the Apps Marketplace, but most of the session was third-party developers telling the story of their businesses, demoing their integrations and providing guidance for other developers looking for success on the Marketplace. Teaser: 30% free->paid conversion rates from GQueues on the Google Apps Marketplace.
  • Apps Marketplace: Best Practices and Integrations covered a wealth of best practices for business app development and Google Apps integrations based on experience working with hundreds of developers building applications for the Google Apps Marketplace.

Google Apps Script:

  • Enterprise Workflow with Apps Script showed how Google Apps Script can be used to build complex workflows using simple server-side JavaScript code. The speakers built on several examples for document publishing approval, showing lots of code for how it’s done.
  • Developing Apps, Add-Ins and More stepped through building Add-Ins with deep integration into the Google Apps UI and full applications. The team announced the Apps Script GUI Builder to drag and drop UI components and full Apps Script APIs for Gmail and Google Docs.

Application APIs:

Solutions and Administration:

  • Developing Innovative Custom Business Solutions with Google Apps covered how web solution providers are driving us towards the goal of 100% web. Included many real-world examples from a variety of companies who are extending Google Apps using Apps Script, Google Sites, gadgets, Data APIs, App Engine, GWT and more.
  • Compliance and Security in the Cloud talked about the suite of APIs and tools available for Google Apps customers to handle policy compliance, audit, incident response and more. Very helpful session for IT administrators, CTOs and CIOs using Google Apps, with much of the session diving into several examples using real-world use cases.

Developer Sandbox

We had 24 fantastic companies in our Developer Sandbox this year, showcasing the applications they built for the Google Apps Marketplace and the services they provide Google Apps customers as system integrators or VARs. We were excited to see many of the companies talking about new integrations they have recently built with Google Apps.

Parties and Fun

The official After Hours event celebrated technical and artistic innovation and included robots, games and transforming vehicles in addition to a live performance from Jane’s Addiction. Many Google teams and companies attending I/O also threw plenty of great parties at nearby bars and restaurants.

How to Autocomplete Email Addresses in Apps Script

When composing Gmail conversations, the auto-complete feature allows us to see our matching personal contacts as we type and quickly make our contact selections. This time-saving feature can be duplicated when creating Google Apps Script applications. For instance, if you design an application that requires sending emails, you can leverage this auto-complete feature by using a personal contact list.

Defining the Requirements

By observing the behavior while composing Gmail conversations, we can define the requirements of our application.

1. As the user begins typing, a list of matches based on first and last name and email address need to appear under the text box. In other words, the user can begin typing the contacts first name, last name, or their email address.

2. If the desired contact email is listed at the top of the matching list, the user can simply press the Enter key to select it.

3. Another option is to click on any of the contacts in the list.

4. Just in case the user would like to enter an email that is not in their contact list, they may enter the email and press the Enter key.

As an added feature if the email is not formatted correctly, then the invalid email is ignored and not selected. For our application when emails are selected, they will be compiled in a separate list on the right where only the email address is stored. If an email is selected by accident, the user can remove the email by clicking on it.

Designing the Application

The application was designed to mimic the behavior of composing Gmail messages. By doing so, the application avoided the use of buttons, providing an improved user experience.

1. Apps Script Services

The Apps Script’s Spreadsheet Service was used to store a user’s contact data. The Ui Service provided the application interaction with the user, and the Contacts Service was leveraged to gather all the user’s contacts. You may apply a Google Apps domain only filter for the contacts by changing the global variable to “true” in the script.

2. Visualize the Layout

Before writing code, the layout was sketched out to include a text box, some space beneath to list matches, and an area to the right to display the selected emails.

3. Choose your widgets

A text box widget was chosen to allow email entry, and two open list boxes were leveraged to display contact matches and selected emails. List boxes provided the use of click handlers to process email selections.

4. Challenges

To mimic the Gmail auto-complete behavior, the text box needed the ability to handle both keystrokes and a pressed Enter key. To accomplish this, a KeyUpHandler calls a function to identify contact matches via a search. The same function used an e.parameter.keyCode == 13 condition to determine when the enter key is pressed.

[php]<span>//create text box for auto-complete during email lookup in left grid</span>
var textBox = app.createTextBox().setName(<span>’textBox'</span>)
.setWidth(<span>’330px'</span>).setId(<span>’textBox'</span>);  
var tBoxHandler = app.createServerKeyHandler(<span>’search_'</span>);
tBoxHandler.addCallbackElement(textBox); textBox.addKeyUpHandler(tBoxHandler);
…function search_(e){ var app = UiApp.getActiveApplication(); app.getElementById(<span>’list'</span>).clear();
var searchKey = new RegExp(e.parameter.textBox,<span>"gi"</span>); 
<span>if</span> (searchKey == "") app.getElementById(<span>’textBox'</span>).setValue(<span>”</span>);
var range = sheetOwner.getRange(1, 1, sheetOwner.getLastRow(), 2).getValues(); var listBoxCount = 0;
var firstOne = <span>true</span>; <span>for</span> (var i in range){  
// if first/last name available, display name and email address <span>if</span>
(range[i][0].search(searchKey) != -1 || range[i][1].search(searchKey) != -1){ <span>if</span>
(range[i][0].toString()){ app.getElementById(<span>’list'</span>).addItem(range[i][0].toString()+ 
‘ .. ‘+range[i][1].toString(), range[i][1].toString()); 
var listBoxCount = listBoxCount + 1; } <span>else</span> { // else just display the email address 
app.getElementById
(<span>’list'</span>).addItem(range[i][1].toString()); var listBoxCount = listBoxCount + 1; }  
<span>if</span> (firstOne) var firstItem = range[i][1].toString(); var firstOne = <span>false</span>;
} }  
// set the top listbox item as the default <span>if</span> (listBoxCount &gt; 0) app.getElementById(<span>’list’
</span>)
.setItemSelected(0, true);
<span>
// if enter key is pressed in text box, assume they want to add</span> <span>// the email that’s not in the list</span> <span>if</span>
(e.parameter.keyCode==13 &amp;&amp; listBoxCount &lt; 1 &amp;&amp; searchKey !== <span>""</span>) { …[/php]

As this application shows, Apps Script is very powerful. Apps Script has the ability to create applications which allow you to integrate various Google services while building complex user interfaces.

 

You can find Dito’s Email Auto-Complete Script here. To view a video demonstration click here. You can also find Dito Directory on the Google Apps Marketplace.