Showing posts with label Fusion Tables. Show all posts

Crowd Sourcing with Google Forms and Fusion Tables

Friday, April 13, 2012 | 9:39 AM

Labels: , ,

Crowd sourcing has been growing substantially in popularity. More and more businesses and individuals are interested in gathering data from the general public for real-time data analysis and visualization. The concept is being adopted in several fields, including journalism, public health and safety, and business development. During this election year, for example, a journalist might be interested in learning what candidate his or her readers support, and the reasons why they support this candidate.

Google Forms, Fusion Tables, and Apps Script make both data collection and analysis super simple! Using Google Forms, a journalist can quickly create an HTML form for readers to submit their opinions and feedback. Fusion Tables make data analysis easy with several cool data visualization options. Apps Script acts as the glue between Google Forms and Fusion Tables, enabling the Form to send data directly to Fusion Tables.

Let’s take a look at how our journalist friend would use all these tools to collect her reader’s candidate preferences.

Google Forms

Google Forms provides a simple UI tool to develop forms perfect for collecting data from readers. Here’s an example of a simple form the journalist can create to get information from her readers:

Once the form has been created, it can be embedded directly into the journalist’s website or blog using the embeddable HTML code provided by Google Forms.

Google Fusion Tables

Google Fusion Tables makes data analysis simple with its visualization capabilities. Using Fusion Tables, the journalist can create maps and charts of the collected data with just a few clicks of the mouse!

Using some fake data as an example, here’s a pie chart that can be created using Fusion Tables to show the the results of the survey:

With Fusion Tables, it’s also easy to filter data and create a pie chart visualization showing why people like Mitt Romney:

These visualizations can also be embedded in the journalist’s website or blog, as Fusion Tables provides embeddable HTML code for all its visualizations. Now, any time someone visits the webpage with the embedded visualization, they will see the current poll result!

Apps Script

Finally, Apps Script acts as the glue between the Google Form and the Fusion Table, since there is currently no direct way to send Google Form submissions to a Fusion Table. During a hack event last year, I took some time to write an Apps Script script that submits the form data to Fusion Tables. The script uses the onFormSubmit Apps Script functionality as described in this blog post. The Fusion Tables code is based on the code described in this blog post.

To learn how to set up your own Google Form to collect data and save that data in a Fusion Table, please see these instructions.


Kathryn Hurley profile

Kathryn is a Developer Programs Engineer for Fusion Tables at Google. In this role, she helps spread the word about Fusion Tables by presenting at conferences and developer events. Kathryn received an MS in Web Science from the University of San Francisco. Prior work experience includes database management, web production, and research in mobile and peer-to-peer computing.

Using Fusion Tables with Apps Script

Friday, September 30, 2011 | 1:56 PM

Labels: ,

Editor’s Note: This post written by Ferris Argyle. Ferris is a Sales Engineer with the Enterprise team at Google, and had written fewer than 200 lines of JavaScript before beginning this application. --Ryan Boyd

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


Ferris Argyle

Ferris is a Sales Engineer with the Enterprise team at Google.