Make use of Google Spreadsheets in your php scripts

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); curl_close($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); print_r($data);

Here is how output should look like:

Page 1 of 2 | Next page