Make use of Google Spreadsheets in your php scripts

April 8th, 2011 by bananos

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

Page 2 of 2 | Previous page

Posted in dev | 6 comments


Next post:

  • 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.