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.

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:

Page 1 of 2 | Next page