A Yii Framework CActiveRecord behaviour that pushes a row to a remote Google Doc spreadsheet.
Fetching latest commit…
Cannot retrieve the latest commit at this time.
Failed to load latest commit information.


Push To Google Docs Spreadsheet (Yii CActiveRecord Behavior)

TODO: All the things.


You need to do two things to make this work:


See the examples/ directory for an example model and synchronisation script.

TODO: Fleshed-out descriptions, maybe with headings rather than as comments.

public function behaviors()
    return array(
        // ... other behaviors ...

        'SMASHPushToGoogleDocSpreadsheetBehavior' => array(
            // Change this 'class' to wherever you've put this Behavior component
            'class' => 'ext.behaviors.smash.push.SMASHPushToGoogleDocSpreadsheetBehavior',

            // Regular or Google Apps Credentials
            'googleUsername' => 'billy@bob.com',
            'googlePassword' => 'iskybibble',

            // The ID of the document (spreadsheet) and worksheet to put new records
            // as they come in. See "Document and Worksheet IDs" for more details as
            // to how to obtain these.
            'documentID'     => 'LONGstreamOFcharactersFROMgoogle',
            'sheetID'        => 'SAMEwithTHISoneITsometimesHASnumbers',

            // One of two things:
            // 1) The mapping of Spreadsheet Column Titles to ActiveRecord fields:
            'columnValueMapping' => array(
                'submitted' => 'create_date',
                'name'      => 'full_name',
                // ...

            // 2) A callback to a function that returns a list of values for a given model record:
            'columnValueMapping' => function($model) {
                return array(
                    'submitted' => $model->create_date,
                    'name'      => $model->full_name,
                    'portfolio' => Yii::app()->createAbsoluteUrl('/uploads').'/'.$model->portfolio_upload_filename, // Dynamic!
                    // ...
            // ... or in PHP < 5.3:
            'columnValueMapping' => array($this, 'getSpreadsheetValueMapping'), // method that exists in $this, that takes $model

Example pushes:

Entry::model()->with('category','options')->pushToRemote(); // When you know Entry's columnValueMapping uses relations
Entry::model()->pushToRemote('full_name LIKE :n', array('n'=>$name_filter)); // Same arguments as findAll()

$entry = new Entry();
$entry->attributes = $_POST['Entry'];
if ($entry->save()) {

Document and Worksheet IDs

A quick script (scripts/get_doc_ids.php) is provided that can retrieve a list of spreadsheet Document IDs from Google Docs.


This script requires that the PHP cURL library be installed (apt-get install php5-curl on Ubuntu/Debian; it should already be present on RedHat/Fedora/Centos installs).


The script supports both CLI and Web usage:

CLI / Console

First up, make sure you have php accessible via the console with php -v.

With that working, run the following command (filling in the email and password details from 'googleUsername' and 'googlePassword' in the behaviors configuration above TODO):

$ ./get_doc_ids.php -u 'yourgoogledocsusername@yourdomain.com' -p 'anexamplepassword'

This will print a list of Document IDs, in a format that looks similar to:

EKvzZkVNEnNEXAMPLE-34FF - "My List of Pets"             <https://spreadsheets.google.com/ccc?key=fi4KvozK9021j4J24isIz938f7f3jJSJJJZZZZZZ9999>
ThiSisnTR3ALdontTry-ITa - "Itemised Toenail Collection" <https://spreadsheets.google.com/ccc?key=DKFzZZZZffffffJJJJJJ9993frjfuvjvzvhioh412132>

Pick the ID of the document you want to grab the Sheet IDs for, and run the command again with a new option:

$ ./get_doc_ids.php -u 'yourgoogledocsusername@yourdomain.com' -p 'anexamplepassword' -d 'EKvzZkVNEnNEXAMPLE-34FF'

This will now return a list of Sheets and their IDs:

Sheets for EKvzZkVNEnNEXAMPLE-34FF:
od6 - "Dogs"
od7 - "Cats"

You can then plug a Document ID and Sheet IDs into 'documentID' and 'sheetID' in the behavior configuration.

If you get confused, run ./get_doc_ids.php -h for help.


Copy the script to somewhere web-accessible, such as the web root of your Yii install, or another site entirely (it's completely independent of the Yii install).

Visit the script in your browser, eg. http://localhost/get_doc_ids.php

Fill in the Email / Password fields with the same 'googleUsername' / 'googlePassword' (see the CLI / Console section), and hit Submit. You should get back a list of Document IDs, Titles and URLs. Clicking one of the Document "Get Sheets" buttons will return back a list of sheets for that document.

Use these Document and Sheet IDs as per the CLI section earlier.