Editing a Google Spreadsheet using PHP and CURL

This is from an external blog. You can view the original, or subscribe, here.

I wanted to edit a Google spreadsheet, and in particular, change the title of a worksheet – although the same concepts would apply to any kind of spreadsheet edit. I am using PHP and since there’s no client API for this, I am building up the HTTP requests using CURL.

This was a nightmare to figure out. The documentation is a bit sparse to say the least, when it comes to making the raw requests using PHP. In saying that, it is important you read it all first and don’t just take my word for things.

Scroll down for the full-blown version including debugging and explanatory comments.

If you’re here via Google, I will not stand in the way of your copying and pasting the code any further:

$accessToken = "your access token";
$editUrl = "https://spreadsheets.google.com/feeds/worksheets/key/private/full/worksheetId?v=3.0&
           access_token=$accessToken";
$entry = "<?xml version='1.0' encoding='UTF-8'?>
          <entry>... snip ... </entry>"; //This is the entry element for the worksheet, 
                                         //containing any required changes.
$fh = fopen('php://temp','rw+');
fwrite( $fh, $entry);
rewind($fh);

$handle = curl_init ($editUrl);
if ($handle) {
	$curlOptArr = array(
		CURLOPT_PUT => TRUE, //It's a PUT request.
		CURLOPT_INFILESIZE => strlen($entry), //Size of the uploaded content
		CURLOPT_INFILE => $fh, //Uploaded content
		CURLOPT_RETURNTRANSFER => TRUE,
		CURLOPT_SSL_VERIFYPEER => FALSE, //Req'd if the google SSL certificate isn't installed
		CURLOPT_HTTPHEADER => Array
            ("content-type: application/atom+xml")); //Include a content-type header!
	curl_setopt_array($handle, $curlOptArr);
	$ret = curl_exec($handle);
	curl_close($handle);
}
fclose($fh);

OK, so let’s break that down, and include the full example that I used to debug the whole thing and work out exactly what CURL was doing.

Firstly, since CURL accepts a file to upload, and we’ve only got an in-memory string, we need to use PHP’s ‘temp’ file access to emulate a file handle:

$fh = fopen('php://temp','rw+');
fwrite( $fh, $entry);
rewind($fh);

Create another file handle to accept debug logging:

$debugOutput = fopen('php://temp', 'rw+');

Then, set up the CURL options with all of the debugging shenanigans:

$curlOptArr = array(
	CURLOPT_PUT => TRUE,
	CURLOPT_INFILESIZE => strlen($entry),
	CURLOPT_INFILE => $fh,
	CURLOPT_RETURNTRANSFER => TRUE,
	CURLOPT_VERBOSE => TRUE, //Ensure lots of debug output
	CURLOPT_STDERR => $debugOutput, //Writes loads of stuff to the debug file
	CURLOPT_SSL_VERIFYPEER => FALSE,
	CURLOPT_HTTPHEADER => Array("content-type: application/atom+xml")
);

Now create the CURL object, submit the request and output all the lovely debug information.

$handle = curl_init ($editUrl);
curl_setopt_array($handle, $curlOptArr);
$ret = curl_exec($handle);
$errRet = curl_error($handle);
print("Result: $ret<br>");
print("Error: $errRet<br>");
print_r(curl_getinfo($handle));

!rewind($debugOutput);
$verboseLog = stream_get_contents($debugOutput);
echo "Verbose information:\n<pre>", 
htmlspecialchars($verboseLog), "</pre>";

Don’t forget to close the handles.

curl_close($handle);
fclose($debugOutput);
fclose($fh);