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;

}