PHP: How to get Google Drive spreadsheet

Task

We have: a spreadsheet at Google Drive.
We need: to get that spreadsheet as a PHP array.

CSV format seems to be a suitable one for this task, though it allows to download one sheet only.

One-liner

Wget should be installed for this to work:

	<?php
	$url = "https://docs.google.com/...";
	$rows = array_map(
	'str_getcsv',
	explode(
	"\n",
	`wget --no-check-certificate -O - -nv -q "$url"`
	)
	);
	?>

view raw short.php hosted with ❤ by GitHub

Expanded solution

Alternatively, we can use cURL:

	<?php
	function get_goodle_drive_spreadsheet($url) {
	try {
	// We are going to use temporary file:
	$fh = fopen("php://temp", "w+");
	if (!$fh) {
	throw new Exception("Can't open temporary file");
	}
	$ch = curl_init($url);
	if (!$ch) {
	throw new Exception("Can't open cURL session");
	}
	curl_setopt_array($ch, array(
	// Skip SSL havoc
	CURLOPT_SSL_VERIFYPEER => false,
	// Allow redirects (there will be loads!)
	CURLOPT_FOLLOWLOCATION => true,
	// Enable cookies (but use memory as storage)
	CURLOPT_COOKIEFILE => "",
	// Download to our temporary file
	CURLOPT_FILE => $fh,
	));
	$r = curl_exec($ch);
	if ($r === false) {
	throw new Exception(
	"Failed downloading spreadsheet: "
	. curl_error($ch)
	);
	}
	// Check if MIME is something CSV-related (to handle wrong document
	// key, invalid permissions, invalid sheet index)
	$mime_type = curl_getinfo($ch, CURLINFO_CONTENT_TYPE);
	if (!preg_match("/\bcsv\b/", $mime_type)) {
	throw new Exception("Invalid MIME type: $mime_type");
	}
	curl_close($ch);
	// Now parse downloaded content as CSV
	rewind($fh);
	$result = array();
	while (($row = fgetcsv($fh)) !== false) {
	$result[] = $row;
	}
	fclose($fh);
	return $result;
	} catch (Exception $e) {
	// Free resources in case of exception
	$ch && curl_close($ch);
	$fh && fclose($fh);
	throw $e;
	}
	}
	try {
	$url = "https://docs.google.com/...";
	$data = get_goodle_drive_spreadsheet($url);
	print_r($data);
	} catch (Exception $e) {
	echo get_class($e) . ": " . $e->getMessage() . "\n";
	}
	?>

view raw long.php hosted with ❤ by GitHub

How to get spreadsheet URL

Spreadsheet can be either shared via secret link or published. In both cases you should add &output=csv. Also, you can add &gid=N to get N^th sheet.

References

• Similar approach (discovered too late, doh!)