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.

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:{$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 = "{$sp_key}/1/public/basic?alt=json-in-script&callback=_";

        // UA
        $userAgent = "Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US; rv: 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);

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

        $id_marker = "{$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);


Here is how output should look like:

    [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
  • 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:

    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!

      • 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 ( ) 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 ""
          --2012-03-29 01:17:05--
          Resolving,,, ...
          Connecting to||: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.