Blog

You are currently viewing Extracting Data from Google Drive Spreadsheet into different formats

Extracting Data from Google Drive Spreadsheet into different formats

To simplify collaborators working with iOS development, I’ve decided that Google Spreadsheet is a good way of handling simple data collection. Which would then be exported to XML format for download, useful for updating applications on the fly.

Advantages for collaborators:

  • Spreadsheet operate within browser
  • Edit spreadsheet in real time, no modification conflict
  • Spreadsheet upload new revision (Publishes every 5 minute)
  • No learning of XML or PLIST
  • Set validation for each cell, restrict user error
  • User interface
Disadvantages
  • Require PHP server
  • Ensure your PHP filesystem configuration of allow_url_fopen = “1”, to accept external links (Google domain). If not you can download and upload the CVS on your domain

Google has removed support for outputting RSS which was useful in creating XML. But there is an tutorial in exporting using CSV:

http://www.ravelrumba.com/blog/json-google-spreadsheets/

Rob has provided an brilliant example converting CSV into array, which can be used to convert it into JSON, XML, PLIST format

Would this be useful for your project?, can just check out this online example.

https://docs.google.com/spreadsheet/ccc?key=0As2k7pb_ovxKdFhLcHNPRlVjU21iYW9LUGw4YU9kaEE&usp=sharing

Encode:

 

csv.php
Use to retrieve the Google CSV file, separating each cell into arrays.

<?php
class CSV {
		// Function to convert CSV into associative array
		public function getArray($file, $delimiter = ",") {
			if (($handle = fopen($file, 'r')) !== FALSE) {
				$i = 0;
				while (($lineArray = fgetcsv($handle, 4000, $delimiter, '"')) !== FALSE) {
					for ($j = 0; $j < count($lineArray); $j++) {
						$arr[$i][$j] = $lineArray[$j];
					}
					$i++;
				}
				fclose($handle);

				return $this->setKeys($arr);
			}
		}

		private function setKeys($data){
			$newArray = array();
			//Use first row for key value and remove from array
			$keys = array_shift($data);
			for ($j = 0; $j < count($data); $j++) {
				$tempArray = array_combine($keys, $data[$j]);
				// Remove values with no key, useful for commenting cells
				unset( $tempArray[null ] );

				$newArray[$j] = $tempArray;
			}
			return $newArray;
		}
    }
?>

plist.php
Similar to XML but uses predefined variables in the XML tag, commonly used for Mac and iOS.

<?php
class PLIST {
        public function encodeObj($obj, $isDict = false) {
			if (is_bool($isDict) == false) {
				$isDict = false;
			}
			$xml = "<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n";
			$xml .= "<!DOCTYPE plist PUBLIC \"-//Apple//DTD PLIST 1.0//EN\" \"http://www.apple.com/DTDs/PropertyList-1.0.dtd\">\n";
			$xml .= "<plist version=\"1.0\">\n";
            $xml .= self::encode($obj, "array", $isDict);
			$xml .= "</plist>";
            return $xml;
        }

        private function encode($data, $node, $isDict, $depth = 0) {
            $xml .= str_repeat("\t", $depth);
            $xml .= "<$node>\n";
            foreach($data as $key => $val) {
                if(is_array($val) || is_object($val)) {
					$tempNode;
					if($isDict){
						$tempNode = "dict";
					} else {
						$tempNode = "array";
					}
					$xml .= self::encode($val, $tempNode, $isDict, ($depth + 1));
                } else {
					if($isDict){
						$xml .= str_repeat("\t", ($depth + 1));
						$xml .= "<key>" . htmlspecialchars($key) . "</key>\n";
					}
					$xml .= str_repeat("\t", ($depth + 1));
					if(strtolower($val) == "yes" || strtolower($val) == "true") {
						$xml .= "<true/>\n";
					} else if(strtolower($val) == "no" || strtolower($val) == "false") {
						$xml .= "<false/>\n";
					} else if($val == ""){
						$xml .= "<string/>\n";
					} else {
						if(is_numeric($val)){
							if(strpos($val, ".") !== false){
								$variable = "real";
							} else {
								$variable = "integer";
							}
						} else {
							$variable = "string";
						}
						$xml .= "<$variable>" . htmlspecialchars($val) . "</$variable>\n";
					}
				}
            }
            $xml .= str_repeat("\t", $depth);
            $xml .= "</$node>\n";
            return $xml;
        }
    }
?>

encode.php
Useful in testing different type of format, depending on the format chosen.

<?php
include("csv.php");
include("xml.php");
include("plist.php");
include("json.php");

$format = ""; // hardcoding the type of format "json" "xml" "plist" "plist_dict"
$feed = "https://docs.google.com/spreadsheet/pub?key=EXAMPLE=true&gid=0&output=csv"; // Google CSV weblink

$obj = new csv();
$array = $obj->getArray($feed);

if (!$array){
	echo "\n############# CSV File is missing ##############\n";
} else if ($format == "json"){
	header("Content-type: text/json");
	echo json_encode($array),"\n";
} else if ($format == "xml"){
	header("Content-type: text/xml");
	$obj = new xml();
	echo $obj->encodeObj($array);
} else if ($format == "plist"){
	header("Content-type: text/xml");
	$obj = new plist();
	echo $obj->encodeObj($array);
} else if ($format == "plist_dict"){
	header("Content-type: text/xml");
	$obj = new plist();
	echo $obj->encodeObj($array, true);
} else {
	echo "\n############# Incorrect format ##############\n";
}
?>

To change the format dynamically, you can use the address bar to pass the format value.
Modify the encode.php line:7

$format = $_GET["format"];

encode.php?format=xml

I have currently over 1000 entries within the Google Spreadsheet for a iPhone application project, takes about 0-2 seconds to download and replace the existing file. You would obviously have to re-organise this one level array into a more structured array for faster operations (Such as finding, removing, editing, moving …)