Weird rocketry

Ever wanted to integrate Google Spreadsheets with your application? This post describes a simple way of how you can do it using simple php script.

Step 1. Create sample google spreadsheet with random data

Here is how it might look like:

Step 2. Publish it on the web & obtain a secret key for your spreadsheet

Click on “Share” button at top right section, and make sure that your spreadsheet will be available to anyone, i.e. “Public on the web – Anyone on the Internet can find and edit”

See that URL? Copy-paste it & extract “key” from there, this will be required for programmatic access to your spreadsheet.

https://spreadsheets.google.com/ccc?key=0Am9NwGgzBIuBdDhSQ3FKMjRDZjAyYlZscUhmNUdKQnc&hl=en

Mine access key is 0Am9NwGgzBIuBdDhSQ3FKMjRDZjAyYlZscUhmNUdKQnc

Step 3. Retrieve JSONP data from Google API

By using special key from above you can download your data from following URL:

https://spreadsheets.google.com/feeds/cells/{$your_key}/1/public/basic?alt=json-in-script&callback=_

Google API does not support pure JSON, so in order to parse it using standard tools you need to strip extra JSONP overhead from the response.
Here a simple php code which downloads data & parses it using json_decode

   /**
     * Download & parse Google spreadsheet content
     *
     * @param unknown_type $link
     */
    function download_sheet($sp_key) {

        // construct Google spreadsheet URL:
        $url = "https://spreadsheets.google.com/feeds/cells/{$sp_key}/1/public/basic?alt=json-in-script&callback=_";

        // UA
        $userAgent = "Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US; rv:1.9.1.9) Gecko/20100315 Firefox/3.5.9";
        $curl = curl_init();
        // set URL
        curl_setopt($curl, CURLOPT_URL, $url);

        // setting curl options
        curl_setopt($curl, CURLOPT_RETURNTRANSFER, 1);// return page to the variable
        curl_setopt($curl, CURLOPT_FOLLOWLOCATION, 1);// allow redirects
        curl_setopt($curl, CURLOPT_RETURNTRANSFER, 1); // return into a variable
        curl_setopt($curl, CURLOPT_TIMEOUT, 30000); // times out after 4s
        curl_setopt($curl, CURLOPT_SSL_VERIFYPEER, FALSE);
        curl_setopt($curl, CURLOPT_SSL_VERIFYHOST, 2);
        curl_setopt($curl, CURLOPT_USERAGENT, $userAgent);

        // grab URL and pass it to the variable
        $str = curl_exec($curl);
        curl_close($curl);

        // extract pure JSON from response
        $str  = substr($str, 2, strlen($str) - 4);
        $data = json_decode($str, true);

        // https://spreadsheets.google.com/feeds/cells/0Am9NwGgzBIuBdDhSQ3FKMjRDZjAyYlZscUhmNUdKQnc/1/public/basic/R1C2
        $id_marker = "https://spreadsheets.google.com/feeds/cells/{$sp_key}/1/public/basic/";
        $entries   = $data["feed"]["entry"];

        $res = array();
        foreach($entries as $entry) {
           $content = $entry["content"];
           $ind = str_replace($id_marker."R", "", $entry["id"]['$t']);
           $ii  = explode("C", $ind);
           $res[$ii[0]-1][$ii[1]-1] = $entry["content"]['$t'];
        }

        return $res;
    }

$SP_KEY = "0Am9NwGgzBIuBdDhSQ3FKMjRDZjAyYlZscUhmNUdKQnc";
$data = download_sheet($SP_KEY);

print_r($data);

Here is how output should look like:

Array
(
    [0] => Array
        (
            [0] => header1
            [1] => header2
            [2] => header3
        )

    [1] => Array
        (
            [0] => value1
            [1] => test1
            [2] => result1
            [3] => yetty1
        )

    [2] => Array
        (
            [0] => value2
            [1] => test2
            [2] => result2
        )

    [3] => Array
        (
            [0] => value3
            [1] => test3
            [2] => result3
        )

)

Of course, this is not an ideal solution:

• There is security through obscurity, by giving someone a key you can’t deny access to document in future
• Huge amount of data can’t be downloaded using this approach

One of possible applications could be publishing changelogs for your product:

• Product manager maintains changelog in Google spreadsheet
• An automation script pulls data from there & publishes it to your application build