This is the second of three Google mashup articles this week for programmers who want to explore the new FormSpring API. If you haven’t seen it yet, take a look at yesterday’s Google Maps mashup.

Today’s mashup involves updating a Google Spreadsheet with collected form data. It might be advantageous to do this over regularly exporting data to Excel or another spreadsheet in a manual fashion — once the integration is setup, you can work within an online spreadsheet and see new rows added shortly after a form’s submitted.

Feel free to download the PHP code and follow along. The example code uses the Zend Framework PHP Client Library to access the Google Spreadsheets API. You’ll need to download the Zend framework in order to use this.

To get started, sign up for a Google account and create a spreadsheet that will be used to store data collected in the FormSpring form. You’ll also need to create a FormSpring API key that has data access to your form. In the spreadsheet, create a header row that matches the field labels in your form. The script will look at that header row to figure out what data should be copied. For example, if the script sees a column titled “First Name”, the data from the field labeled “First Name” will be added to the appropriate cell.

In the PHP script (example.php in the download), we’ll first use the Zend library to log in to Google using a username and password:

$authService = Zend_Gdata_Spreadsheets::AUTH_SERVICE_NAME;
$httpClient = Zend_Gdata_ClientLogin::getHttpClient(
    $google_username, $google_password, $authService);
$gdClient = new Zend_Gdata_Spreadsheets($httpClient);

The example gets a list of all the spreadsheets in the account, and finds the one we want to update. Once the spreadsheet name is found, we save the key to use later.

$feed = $gdClient->getSpreadsheetFeed();
foreach($feed->entries as $entry) {
    if ($entry->title->text == $spreadsheet_name) {
        $id = split('/', $entry->id->text);
        $spreadsheet_key = $id[5];
    }
}

We’ll need to do the same thing to find the right worksheet within the spreadsheet, but can assume that most of the time the first worksheet will be used.

$query = new Zend_Gdata_Spreadsheets_DocumentQuery();
$query->setSpreadsheetKey($spreadsheet_key);
$feed = $gdClient->getWorksheetFeed($query);
$i = 0;
foreach($feed->entries as $entry) {
    if (
        (empty($worksheet_name) && $i == 0) || 
        (!empty($worksheet_name) && $entry->title->text == $worksheet_name) 
    ) {        
        $id = split('/', $entry->id->text);
        $worksheet_id = $id[8];
    }
    $i++;
}

Once we’ve found the worksheet, we can get the header row and save the contents to try to match up to the form field labels.

$header_cells = array();
$query = new Zend_Gdata_Spreadsheets_CellQuery();
$query->setSpreadsheetKey($spreadsheet_key);
$query->setWorksheetId($worksheet_id);
$query->setMaxRow(1);
$feed = $gdClient->getCellFeed($query);
foreach ($feed->entries as $entry) {
    $header_cells[] = $entry->content->text;
}

Now we query the FormSpring API in order to get information about the form, including a list of fields on the form with their labels and id. We’ll loop over the list of fields and find the ones that exist in the spreadsheet. We’ll also store a modified version of the field name which is used by the Google Spreadsheets API to reference each column (e.g. firstname instead of First Name).

// Get information on the FormSpring form
$form = FormSpring::request($api_key, 'form', array( 'id' => $form_id ));
 
// Map form field ids to worksheet column names
$fields = array();
foreach ($form['fields'] as $field) {
    if (in_array($field['label'], $header_cells)) {
        $fields[$field['id']] = strtolower(str_replace(' ', '', $field['label']));
    }
}

After figuring out how to match up form fields with spreadsheet cells, we can start fetching submitted data. Assuming that this script will be scheduled to run in cron on a regular basis, we’ll need some mechanism to make sure that we don’t add old data to an existing spreadsheet. For the sake of simplicity, in the example we do that by downloading the prior day’s worth of submissions and assume that the script will only run once a day. The min_time and max_time parameters (which search against the submission date and time for each record) can easily be changed to do this on an hourly basis, or even better, we can locally store the unique id of the last submission so that we know the same submission is never added to the spreadsheet twice.

When downloading the submitted data only a pre-defined number of results (25 by default) are returned at each time, and we’ll need to make multiple requests to the API to download each “page” of results.

// Loop through each page, making one query per page of data
$pages = 1;
while ($parameters['page'] <= $pages) {
 
    // Get submitted data
    $data = FormSpring::request($api_key, 'data', $parameters);
 
    // Update the number of pages
    $pages = $data['pages'];
 
    // Iterate over each entry
    foreach ($data['submissions'] as $submission) {
 
        // ....
    }
 
    // Increment the page # for the next request
    $parameters['page']++;
}

Within the submission loop, we build a row of data that will get inserted into the spreadsheet. The row is stored as an array with the column name as the key and the submission data as the value.

$row = array();
foreach ($submission['data'] as $item) {
    if (isset($fields[$item['field']])) {
        $col = $fields[$item['field']];
        $row[$col] = $item['value'];
    }
}

Once the row is created, it can be added to the spreadsheet using the Zend library.

$entry = $gdClient->insertRow($row, $spreadsheet_key, $worksheet_id);
if ($entry instanceof Zend_Gdata_Spreadsheets_ListEntry) {
    print "added row {$entry->id->text}\n";
}

You should see the data update within the Google Spreadsheets interface in real time while the script runs, and can be sorted, formatted and revised just like any other spreadsheet.

Feel free to comment below if you have any questions about this example. Check back tomorrow for another Google mashup example.

Random Posts

• New Feature: Data Editing Within Your Online Forms
• Maintenance window Sunday 11pm EST
• Guy Kawasaki: The Art of the Customer Surveys

This entry was posted on Thursday, September 6th, 2007 at 3:59 pm and is filed under API. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.