Access Google Spreadsheets of any user

This

When accessing Google spreadsheets, we have two scenarios. The first one, is when your web app needs to access a spreadsheet of your own. In this case, your app needs an OAuth2 service account credential to act on your behalf, cause you are the propietary of the file. See my previous article about using Google sheets as backend database.

The second scenario is when your web app needs to access to the spreadsheets of your users. In this case, you won’t have a credential of each potencial user of your app. So you need a explicit authorization from him/her.

Different authoritation flows can be used as described here. They can be seen in the following picture:

If the Client is a regular web app executing on a server then the Authorization Code Grant is the flow you should use. Using this the Client can retrieve an Access Token and, optionally, a Refresh Token. It’s considered the safest choice since the Access Token is passed directly to the web server hosting the Client, without going through the user’s web browser and risk exposure. If this case matches your needs, then for more information on how this flow works and how to implement it refer to OAuth 2.0 for Web Server Applications. A properly authorized web server application can access an API while the user interacts with the application or after the user has left the application.

Resource Owner Password Credential Flow is not supported on Google but google suggests you use the Installed Application Flow, which is described in: https://developers.google.com/accounts/docs/OAuth2InstalledApp.

The last scenario is a Single Page Application (SPA). The Implicit Grant is similar to the Authorization Code Grant, but the main difference is that the client app receives an access_token directly, without the need for an authorization_code. In a Single Page Application this would be done using Javascript and in a Mobile Application this is typically handled by interacting with a Web View. The flow is described in OAuth 2.0 for Client-side Web Applications and it is designed for applications that access APIs only while the user is present at the application.

Each time your web app request grants, the app will be listed in the allowed apps where you can revoke access.

Implicit Grant Flow

This is used for client-side web applications. It can be only implemented in a browser environment with javascript because the access token is passed directly as a hash fragment (not as a URL parameter). One important thing about hash fragment is that, once you follow a link containing a hash fragment, only the browser is aware of the hash fragment. Thanks to Nivco.

Just one simple file. Generally documented here and with sign-in here.

File: Authoring1.html

<html lang="en">
	<head>
		<script src="https://apis.google.com/js/platform.js?onload=start" async defer></script>
		<!-- <script src="https://apis.google.com/js/api.js?onload=start" async defer></script> -->
		<!-- <script src="https://apis.google.com/js/client.js?onload=start" async defer></script> -->
		<script src="//ajax.googleapis.com/ajax/libs/jquery/1.8.2/jquery.min.js"></script>
	</head>
	<body>
		<button id="signinButton">Client Side Apps</button>
	</body>
	<script>
		var GoogleAuth;
		var googleUser;

		var clientID = '674977077017-e1e5bcgp270e576cqkcckme52pldnqug.apps.googleusercontent.com';
		function start() {
			gapi.load('auth2', function() {
				auth2 = gapi.auth2.init({
					client_id: clientID,
					// Scopes to request in addition to 'profile' and 'email'
					scope: 'https://www.googleapis.com/auth/drive.readonly'
				}).then(function() {
					GoogleAuth = gapi.auth2.getAuthInstance();
					GoogleAuth.isSignedIn.listen(updateSigninStatus);
				});			
			});
    	}		

		$('#signinButton').click(function() {
			if (GoogleAuth.isSignedIn.get()) {		//User is signed
				googleUser = GoogleAuth.currentUser.get();
				console.log(googleUser.getAuthResponse(true));
			} else {	// User is not signed in. Start Google auth flow.
				GoogleAuth.signIn({
					prompt:'consent',					
					//ux_mode: 'redirect',
					//redirect_uri: 'http://edba.xyz'
				}).then(function() {
					googleUser = GoogleAuth.currentUser.get();
					console.log(googleUser.getAuthResponse(true));
				});
			}
		});
		
		function updateSigninStatus() {
			console.log('Changed');
		}
	</script>
</html>

If ux_mode is not set to ‘redirect’, the implicit grant flow uses pop-ups instead of redirection, so it doesn’t need Authorized redirect URIs from the credentials in the google developers console.

Then you can then send the access_token to the server if you want use it from there.

var access_token = googleUser.getAuthResponse(true).access_token

Be aware of security because your may send the token in plain text over your network. The best practice is to pass the token as a post parameter to a HTTPS secured server. It isn’t a good idea to put sensitive information in the URL as a GET parameter even with HTTPS. URLs are stored in web server logs. This means that any sensitive data in the URL is being saved in clear text on the server. More details here.

Note, It can be done a page-level configuration (Google call this way). The big advantage is that if we already have an access token stored in a cookie, it will execute the callback function on page load.

Hybrid Server-Side Flow

To use Google services on behalf of a user when the user is offline, you must use a hybrid server-side flow where a user authorizes your app on the client side using the JavaScript API client and you send a special one-time authorization code to your server. Your server exchanges this one-time-use code to acquire its own access and refresh tokens from Google for the server to be able to make its own API calls, which can be done while the user is offline. This one-time code flow has security advantages over both a pure server-side flow and over sending access tokens to your server. See reference here.

The client file is used to get the one-time authorization code. No access_token or client_secret is exposed to your network. In this example the access_token value is returned to the client from the ajax just for illustrative purposes. In real world, the access_token is not returned from the ajax call.

File: Authorization1.html

<!-- Sign in without page-level configuration -->
<html lang="en">
	<head>
		<script src="https://apis.google.com/js/platform.js?onload=start" async defer></script>
		<!-- <script src="https://apis.google.com/js/api.js?onload=start" async defer></script> -->
		<!-- <script src="https://apis.google.com/js/client.js?onload=start" async defer></script> -->
		<script src="//ajax.googleapis.com/ajax/libs/jquery/1.8.2/jquery.min.js"></script>
	</head>
	<body>
		<button id="signinButton">Server Side Apps</button>		
	</body>
	<script>
		var clientID = '674977077017-e1e5bcgp270e576cqkcckme52pldnqug.apps.googleusercontent.com';
		function start() {
			gapi.load('auth2', function() {
				auth2 = gapi.auth2.init({
					client_id: clientID,
					// Scopes to request in addition to 'profile' and 'email'
					scope: 'https://www.googleapis.com/auth/drive.readonly'
				});
			});
    	}
    
		$('#signinButton').click(function() {
			auth2.grantOfflineAccess({
				prompt: 'select_account'	// Don't prompt for permissions if app is already allowed
				//prompt : 'consent'		// Always prompt for permissions
			}).then(function(resp) {
				var auth_code = resp.code;
				console.log('Exchange Code: '+resp.code);
				$.ajax({
					type: 'POST',
					url: 'http://edba.xyz/tutorials/authoring/authoring1.php',
					// Always include an `X-Requested-With` header in every AJAX request to protect against CSRF attacks.
					headers: {'X-Requested-With': 'XMLHttpRequest'},
					contentType: 'application/octet-stream; charset=utf-8',
					processData: false,				
					data: resp.code,
				}).done(function(result) {
					console.log('Access_Token: '+result);
					// Handle or verify the server response.
				});
			});
		});		
	</script>
</html>

The php file used to exchange the authorization code for an access token is the following. Remember, in the real world the access_token is not returning to the client for security reasons.

File: Authorization2.php

<?php

$code = file_get_contents('php://input');
$client_id = '674977077017-e1e5bcgp270e576cqkcckme52pldnqug.apps.googleusercontent.com';
$client_secret = 'I9JFDo2ATOsVw28NAv6vyEWX';

$url = 'https://www.googleapis.com/oauth2/v4/token';
$data = array(
	'code' => $code,
	'client_id' => $client_id,
	'client_secret' => $client_secret,
	'redirect_uri' => 'http://edba.xyz',
	'grant_type' => 'authorization_code'		
);
// use key 'http' even if you send the request to https://...
$options = array(
	'http' => array(
		'header'  => "Content-type: application/x-www-form-urlencoded\r\n",
		'method'  => 'POST',
		'content' => http_build_query($data)
	)
);

$context  = stream_context_create($options);
$result = json_decode(file_get_contents($url, false, $context));
$access_token = $result->access_token;

echo $access_token;
?>

The hybrid server-side flow uses pop-ups instead of redirection, so it doesn’t need Authorized redirect URIs from the credentials in the google developers console neither.

Using advanced library methods

For both flows, impplicit grant and hybrid server-side, the Google Javascript libraries allows another advanced method to implement the flows. The method is gapi.auth2.authorize.

Some use cases where this method is useful include:

  • Your application only needs to requests a Google API endpoint once, for instance to load the user’s favorite YouTube videos the first time they sign in.
  • Your application has its own session management infrastructure, and it only requires the ID Token once to identify the user in your backend.
  • Several Client IDs are used within the same page.

File: Authorization3.html

<html lang="en">
	<head>
		<script src="https://apis.google.com/js/platform.js?onload=start" async defer></script>
		<!-- <script src="https://apis.google.com/js/api.js?onload=start" async defer></script> -->
		<!-- <script src="https://apis.google.com/js/client.js?onload=start" async defer></script> -->
		<script src="//ajax.googleapis.com/ajax/libs/jquery/1.8.2/jquery.min.js"></script>
	</head>
	<body>
		<button id="signinButton">Client Side Apps</button>
	</body>
	<script>
		var GoogleAuth;
		var googleUser;
		var clientID = '674977077017-e1e5bcgp270e576cqkcckme52pldnqug.apps.googleusercontent.com';
		var scope = 'https://www.googleapis.com/auth/drive.readonly';
		
		function start() {
			gapi.load('auth2', function() {
				
			});
    	}
		$('#signinButton').click(function() {
			gapi.auth2.authorize({
				client_id: clientID,
				scope: scope,
				response_type: 'id_token token code',
                                prompt: 'select_account'
			}, function(response) {
				if (response.error) {
					// An error happened!
					return;
				}
				// The user authorized the application for the scopes requested.
				console.log('Access Token: '+ response.access_token);				
				console.log('Authentication code: '+ response.code);
                                console.log('ID Token: '+ response.id_token);
				// You can also now use gapi.client to perform authenticated requests.
			});
		});
	</script>
</html>

Remember 3 types of tokens asre involved in authentication:

  • Access Token: The one needed for using any Google API. (Normally 1 hour expiration time)
  • Authentication Code: The one used for exchange for an Access Token in the server side.
  • ID Token: The one used to identify the currently signed-in user on the server. This token must be verified in the backend. (One of the main uses is replace the user/password authentication in a web page with Google Sign-In).
  • Refresh Token: Used for get a new Access Token upon expiration. (Assume this refresh token never expires but it has its limits)

Pure Server Side Flow

It is not as secure as hybrid server side flow as explained here and here. Basically is due to the Cross Site Request Forgery (CSRF in short).

We can use the Google API Client Libraries or direct HTTP/REST. The documentation is here.

File: Authorization4.php

<?php
session_start();
$code = $_GET["code"];

if(!isset($code)) {		// Phase 1: Get authentication code
	$scope = 'https://www.googleapis.com/auth/drive.readonly';
	$access_type = 'offline';
	$include_granted_scopes = 'true';
	$state = 'state_parameter_passthrough_value';
	$redirect_uri = 'http://edba.xyz/tutorials/authoring/authoring4.php';
	$response_type = 'code';
	$client_id = '674977077017-e1e5bcgp270e576cqkcckme52pldnqug.apps.googleusercontent.com';
	$prompt = 'consent';
	
	$data = array(
		'scope' => $scope,
		'access_type' => $access_type,
		'include_granted_scopes' => $include_granted_scopes,
		'state' => $state,
		'redirect_uri' => $redirect_uri,
		'response_type' => $response_type,
		'client_id' => $client_id,
		//'prompt' => $prompt	// Needed for getting a new refresh_token
	);
	$url = 'https://accounts.google.com/o/oauth2/v2/auth?'.http_build_query($data); 
	die('<script type="text/javascript">window.location=\''.$url.'\';</script>');

} else {		// Phase 2: Exchange the auth code for an Access Token
	$client_id = '674977077017-e1e5bcgp270e576cqkcckme52pldnqug.apps.googleusercontent.com';
	$client_secret = 'I9JFDo2ATOsVw28NAv6vyEWX';
	$url = 'https://www.googleapis.com/oauth2/v4/token';
	$data = array(
		'code' => $code,
		'client_id' => $client_id,
		'client_secret' => $client_secret,
		'redirect_uri' => 'http://edba.xyz/tutorials/authoring/authoring4.php',		// Must much the previous phase 1 $redirect_uri
		'grant_type' => 'authorization_code'		
	);
	
	$options = array(
		'http' => array(
			'header'  => "Content-type: application/x-www-form-urlencoded\r\n",
			'method'  => 'POST',
			'content' => http_build_query($data)
		)
	);

	$context  = stream_context_create($options);
	$result = json_decode(file_get_contents($url, false, $context));
	
	$access_token = $result->access_token;
	$refresh_token = $result->refresh_token;

	$_SESSION['access_token'] = $access_token;		// With the access token in a session variable you can safely redirect if desired	
}

?>

The refresh_token is only provided on the first authorization from the user. Subsequent authorizations will not return the refresh_token again. Alternatively, you can add the query parameter prompt=consent to the OAuth redirect. This will prompt the user to authorize the application again and will always return a refresh_token. Thanks to Rich Sutton.

Google recommends implementing with their libraries. For example:

File: Authorization5.php

<?php
require dirname(__FILE__).'/google-api-php-client-2.2.1/vendor/autoload.php';

session_start();

$client = new Google_Client();
$client->setAuthConfigFile('client_secret.json');
$client->setRedirectUri('http://' . $_SERVER['HTTP_HOST'] . '/tutorials/authentication.php');
$client->addScope(Google_Service_Drive::DRIVE_METADATA_READONLY);

if (! isset($_GET['code'])) {
	$auth_url = $client->createAuthUrl();
	header('Location: ' . filter_var($auth_url, FILTER_SANITIZE_URL));
} else {
	$client->authenticate($_GET['code']);
	$_SESSION['access_token'] = $client->getAccessToken();	
}

With the libraries you don’t have to worry about refreshing tokens. The client object will refresh the access token as needed.

In this flow, the authorization is done by redirections no by pop-ups, so it needs Authorized redirect URIs in the credential configuration from the google developers console

 

You have a web application that wants to access private users data as for example their Google spreadsheets.

The users access our web which request an authorization to view some spreadsheet data. Moreover,  we want the user can select which spreadsheet applies for.

For request permission to the user we have different options as described here:

  • You can use javascript library to get an “authorized” access to a Google API using OAuth 2.0.