Map a Google Spreadsheet to a PHP Array

I wrote this for work recently and I’ve been getting a lot of mileage out of it so I thought I’d share. It goes out to the Google Spreadsheet indicated by the key in the first parameter and grabs it’s data via the JSON API. The function makes the assumption that that the first row contains column headers but can be adapted pretty quickly if that’s not the case. You can also pass a GQL query as the second parameter to grab a filtered list.


function mapGoogleSheetToArray($SpreadsheetKey, $query = ''){
	$callback['name'] = "asdfasdfasdf";
	$callback["urlAppend"] = ("/gviz/tq?tqx=responseHandler:" . $callback['name'] . ";");
	$callback["jsonReplace"] = [$callback['name'], '(', ')', ';'];

	$query = empty($query) ? $query : '&tq=' . urlencode($query);

	$GoogleData = file_get_contents("https://docs.google.com/spreadsheets/d/" . $SpreadsheetKey . $callback["urlAppend"] . $query);

	$JsonData = json_decode( str_replace($callback["jsonReplace"], "", $GoogleData) );

	$colHeaders = array_shift($JsonData->table->rows);

	foreach ($colHeaders->c as $key => $value) {
		$cols[] = trim($value->v);
	}

	for ($i=0; $i < sizeof($JsonData->table->rows) ; $i++) { 
		for ($j=0; $j < sizeof($cols); $j++) { 
			$table[$i][
				$cols[$j] 
			] = @nl2br(trim(
				$JsonData->
					table->
						rows[$i]->
							c[$j]->
								v
				));
		}
		
	}

	return $table;

}

  1 comment for “Map a Google Spreadsheet to a PHP Array

  1. Dan
    June 26, 2015 at 1:38 pm

    Works great! But this script only grabs the first sheet in a spreadsheet.

    How could i request the whole spreadsheet, and then return an array of $tables ?

    Thanks

Leave a Reply

Your email address will not be published. Required fields are marked *