Export data to Google spreadsheet with OAuth

I was working on exporting CSV data to a Google spreadsheet recently. The first thing I had to do was implement a way to allow a user to authenticate themselves with Google from our site. There are a few ways of doing this.

I choose OAuth as I thought it would be simplest. I found it was pretty straightforward but there were gaps in Google’s documentation that made the process a tad frustrating.

Here’s a brief outline…

Couple of things you’ll need before you can start any coding.

Register with Google – You’ll need to register with Google to get an API or a consumer key/secret that will be needed to access API.

Get the OAuth Library – You will be essentially making signed HTTP requests to Google URLs and this library helps to make sure these requests are constructed properly. I used Andy Smith’s PHP library.

I have a Google PHP class that handles the authorization and uploading data to a Google Spreadsheet.

One critical step in authenticating that I couldn’t find in the Google documentation is the following step. When you get the request token, a token secret is sent in the response. You need to save the request token secret so you can use this token secret again when you request the access token. I am saving it as meta data but you can easily use a session variable.

Uploading data to Google also has a few hoops. You can only upload a max of 1MB so you may need to break up the upload into multiple documents. They recommend using Resumable Upload when uploading bigger lumps of data. This requires you to chuck the data and use the Content-Range to specify the chuck sizes.

define( 'CONSUMER_KEY', 'domain.com' );
define( 'CONSUMER_SECRET', '324lewrl34j234kp234' );

class Google_Doc {
	private $user;

	public function __construct( $user = false ) {
		global $current_user;

		$this->user = $user;
		if ( $user === false )
			$this->user = $current_user;
	}

	private function get_token() {
		return $this->user->get_meta( 'google_oauth_token' );
	}

	public function is_authorised() {
		$access_token = $this->get_token();

		if ( $access_token instanceof OAuthToken )
			return true;
		return false;
	}

	public function authorise() {
		$authorize_url = '';
		$parameters    = array();

		if ( isset( $_REQUEST['oauth_verifier'] ) ) {
			// This is the callback from Google after verifying
			$oauth_verifier = $_REQUEST['oauth_verifier'];

			$parameters[ 'oauth_verifier' ] = $oauth_verifier;
			if ( isset( $_REQUEST['oauth_token'] ) ) {
				$parameters[ 'oauth_token' ] = $_REQUEST['oauth_token'];

				$access_token = $this->get_access_token( $parameters );

				// Delete the meta secret token
				delete_metadata( 'user', $this->user->get_id(), 'google_token_secret' );

				if ( $access_token ) {
					// Store the token
					$this->user->set_meta( 'google_oauth_token', $access_token );
					return true;
				}
			}
		}
		else {
			// Unauthorised - send user to Google
			$parameters = array(
				'scope'          => 'http://docs.google.com/feeds/',
				'oauth_callback' => "http://{$_SERVER['HTTP_HOST']}{$_SERVER['REQUEST_URI']}"
			);

			$request_token = $this->get_request_token( $parameters );
			if ( $request_token ) {
				$authorize_url = 'https://www.google.com/accounts/OAuthAuthorizeToken?'.$request_token;
				pd_redirect( $authorize_url );
			}
		}

		return false;
	}

	public static function max_upload_size() {
		return 1024 * 1024;
	}

	private function get_request_token( $parameters ) {
		$consumer     = new OAuthConsumer( CONSUMER_KEY, CONSUMER_SECRET );
		$sig_method   = new OAuthSignatureMethod_HMAC_SHA1();
		$token = $key = $secret = NULL;

		$token_endpoint = 'https://www.google.com/accounts/OAuthGetRequestToken';

		$request = OAuthRequest::from_consumer_and_token( $consumer, $token, 'GET', $token_endpoint, $parameters );
		$request->sign_request( $sig_method, $consumer, $token);

		$response = send_signed_request( $request->get_normalized_http_method(), $token_endpoint, $request->to_header(), NULL, false );

		// Parse out oauth_token (access token) and oauth_token_secret
		if ( preg_match( '/oauth_token=(.*)&oauth_token_secret=(.*)&/', $response, $matches ) > 0 ) {
			$key    = urldecode( $matches[1] );
			$secret = urldecode( $matches[2] );

			$this->user->set_meta( 'google_token_secret', $secret );
			$token = new OAuthToken( $key, $secret );
		}

		return $token;
	}

	private function get_access_token( $parameters ) {
		$consumer   = new OAuthConsumer( CONSUMER_KEY, CONSUMER_SECRET );
		$sig_method = new OAuthSignatureMethod_HMAC_SHA1();
		$token      = new OAuthToken( $parameters['oauth_token'], $this->user->get_meta( 'google_token_secret' ) );

		$token_endpoint = 'https://www.google.com/accounts/OAuthGetAccessToken';

		$request = OAuthRequest::from_consumer_and_token( $consumer, $token, 'GET', $token_endpoint, $parameters );
		$request->sign_request( $sig_method, $consumer, $token );

		$response = send_signed_request( $request->get_normalized_http_method(), $token_endpoint, $request->to_header(), NULL, false );

		// Parse out oauth_token (access token) and oauth_token_secret
		$token = false;
		if ( preg_match('/oauth_token=(.*)&oauth_token_secret=(.*)/', $response, $matches) > 0 )
			$token = new OAuthToken( urldecode( $matches[1] ), urldecode( $matches[2] ) );

		return $token;
	}

	public function upload( $data, $slug ) {
		// Squirt it over to Google
		$consumer       = new OAuthConsumer( CONSUMER_KEY, CONSUMER_SECRET );
		$sig_method     = new OAuthSignatureMethod_HMAC_SHA1();
		$token_endpoint = 'http://docs.google.com/feeds/upload/create-session/default/private/full';

		$access_token = $this->get_token();

		$filesize = strlen( $data );

		$request = OAuthRequest::from_consumer_and_token( $consumer, $access_token, 'POST', $token_endpoint, NULL );
		$request->sign_request( $sig_method, $consumer, $access_token );

		$headers = array(
			'Content-Type: text/csv',
			'Content-Length: 0',
			"Slug: ".$slug,
			'GData-Version: 3.0',
			'X-Upload-Content-Type: text/csv',
			"X-Upload-Content-Length: {$filesize}",
			$request->to_header()
		);

		$response = send_signed_request( $request->get_normalized_http_method(), $token_endpoint, $headers, NULL, true );

		if ( preg_match( '/Location:(.*)/', $response, $matches ) > 0 ) {
			$url = trim( $matches[1] );

			$chunk_size = 256 * 1024;
			$start      = 0;

			while ( $start < $filesize ) {
				$chunk = substr( $data, $start, min( $filesize - $start, $chunk_size ) );

				$headers = array(
					'Content-Type: text/csv',
					"Content-Length: ".strlen( $chunk ),
					sprintf( "Content-Range: bytes %d-%d/%d", $start, strlen( $chunk ) - 1, $filesize )
				);

				$curl = curl_init( $url );

				curl_setopt( $curl, CURLOPT_RETURNTRANSFER, true );
				curl_setopt( $curl, CURLOPT_FAILONERROR, true );
				curl_setopt( $curl, CURLOPT_SSL_VERIFYPEER, false );
				curl_setopt( $curl, CURLOPT_HTTPHEADER, $headers );
				curl_setopt( $curl, CURLOPT_CUSTOMREQUEST, 'PUT' );
				curl_setopt( $curl, CURLOPT_POSTFIELDS, $chunk );
				curl_setopt( $curl, CURLOPT_TIMEOUT, 10 );

				$response = curl_exec( $curl );
				$http_status = curl_getinfo( $curl, CURLINFO_HTTP_CODE );

				curl_close( $curl );

				if ( $response !== false ) {
					// Is this the last chunk?
					if ( $http_status == 201 ) {
						// Parse out the doc URL
						$xml = simplexml_load_string( $response );
						if ( $xml ) {
							foreach ( $xml->link AS $link ) {
								if ( $link['rel'] == 'alternate' )
									return (string)$link['href'];
							}
						}

						return false;
					}

					$start += strlen( $chunk );
				}
				else
					return $this->failed();
			}
		}

		return $this->failed();
	}

	/**
	 * Operation failed so delete the auth token
	 */
	private function failed() {
		delete_metadata( 'user', $this->user->get_id(), 'google_oauth_token' );
		return false;
	}
}
/**
 * Makes an HTTP request to the specified URL
 *
 * @param string  $http_method           The HTTP method (GET, POST, PUT, DELETE)
 * @param string  $url                   Full URL of the resource to access
 * @param string  $auth_header           (optional) Authorization header
 * @param string  $postData              (optional) POST/PUT request body
 * @param bool    $returnResponseHeaders True if resp. headers should be returned.
 * @return string Response body from the server
 */
function send_signed_request($http_method, $url, $auth_header=null, $postData=null, $returnResponseHeaders=true)
{
	$curl = curl_init($url);
	curl_setopt($curl, CURLOPT_RETURNTRANSFER, true);
	curl_setopt($curl, CURLOPT_FAILONERROR, false);
	curl_setopt($curl, CURLOPT_SSL_VERIFYPEER, false);

	if ($returnResponseHeaders) {
		curl_setopt($curl, CURLOPT_HEADER, true);
		curl_setopt($curl, CURLINFO_HEADER_OUT, true);
	}

	if ($auth_header) {
		if ( is_string( $auth_header ) )
			$auth_header = array( $auth_header );
		curl_setopt($curl, CURLOPT_HTTPHEADER, $auth_header);
	}

	if ($http_method == 'POST') {
		curl_setopt($curl, CURLOPT_POST, 1);
		curl_setopt($curl, CURLOPT_POSTFIELDS, $postData);
	}
	$response = curl_exec($curl);
	if (!$response) {
		$response = curl_error($curl);
	}
	curl_close($curl);
	return $response;
}

To use this class, just place the following code in a page.

$google = new Google_Doc();

if ( $google->is_authorised() ) {
	$google->upload( $data, $slug );
}
else {
	if ( $google->authorise() ) {
		$google->upload( $data, $slug );
	}
}

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s