Make use of Google Spreadsheets in your php scripts
Friday, April 8th, 2011 06:59 pm GMT +2

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
  • http://mynewsitepreview.com/ Shaun Scovil

    This is EXACTLY what I’ve been trying to do for a week now. Thank you. It it the solution to the problem I posted here: 
    http://stackoverflow.com/questions/9890033/how-can-i-get-the-contents-of-a-google-spreadsheet-as-csv-data-in-the-simplest-w/9915327#9915327

    I am adapting this function for use in my WordPress plugin, WP-SortTable (coming soon), and will credit you in the code and readme.txt.

    • Anonymous

      Thanks! glad it helped you!

      • http://mynewsitepreview.com/ Shaun Scovil

        Oops! I spoke to soon…this actually didn’t work. I just fooled myself by accidentally re-displaying another variable. :-/

        I cannot load the contents of a public Google Spreadsheet (https://docs.google.com/spreadsheet/ccc?key=0Aj-ZlsTpsY_wdEhIdTdhYzlmTmdEMXhjVEJaWERtUFE ) no matter what I try. Any thoughts?

        • Anonymous

          I wrote this article about a year ago, google APIs may have been changed since that time. 
          I tried to download data URI for your spreadsheet and here’s what it says:

          wget -S "https://spreadsheets.google.com/feeds/cells/0Aj-ZlsTpsY_wdEhIdTdhYzlmTmdEMXhjVEJaWERtUFE/1/public/basic?alt=json-in-script&callback=_"
          --2012-03-29 01:17:05--  https://spreadsheets.google.com/feeds/cells/0Aj-ZlsTpsY_wdEhIdTdhYzlmTmdEMXhjVEJaWERtUFE/1/public/basic?alt=json-in-script&callback=_
          Resolving spreadsheets.google.com... 209.85.148.101, 209.85.148.102, 209.85.148.113, ...
          Connecting to spreadsheets.google.com|209.85.148.101|:443... connected.
          HTTP request sent, awaiting response... 
            HTTP/1.1 400 Bad Request
            Content-Type: text/html; charset=UTF-8
            x-chromium-appcache-fallback-override: disallow-fallback
            Date: Wed, 28 Mar 2012 22:17:07 GMT
            Expires: Wed, 28 Mar 2012 22:17:07 GMT
            Cache-Control: private, max-age=0
            X-Content-Type-Options: nosniff
            X-Frame-Options: SAMEORIGIN
            X-XSS-Protection: 1; mode=block
            Server: GSE
            Transfer-Encoding: chunked
          2012-03-29 01:17:06 ERROR 400: Bad Request.

          I would consider using Gdata library, there should be some demos of working with spreadsheets. 
          Google changes their APIs too often :(

  • Troy Kelly

    Just had to make a minor modification to the substr line – I am using the below and it is working

    // extract pure JSON from response
    $str = mb_ereg_replace(“// API callbackn_(“, “”, $str);
    $str = mb_ereg_replace(“);$”, “”, $str);
    $data = json_decode($str, true);

  • geetika

    How can i use private google spreadsheet.