Google sheets as SQL backend database

Publicado el

Google Spreadsheet as a private and secure backend SQL database for a web page (with screenshots) with PHP

Introduction

Almost every application needs a database to grab data from. One of the most popular is SQL based databases as MySQL, PosgreSQL o MariaDB. The use of these servers requires the installation somewhere and enough resources. For web applications, shared hosting not always offers database servers and often, direct manipulation of the data in the server is not easy for beginners or medium skilled users.

Conversely, Google Sheets are very easy and confortable for almost every user, and they don’t have to worry about hosting these sheets.

First of all, you can read data from a Google sheet using a SQL query language which is something well documented in Google Visualization API Query Language, derived from its Google Chart API.

Google has developed various methods to read the data using its SQL Query Language:

  • By setting the query string from within JavaScript code.
  • By setting the query string as a parameter in the data source URL.

Both are described in previous links.

But if we want to be able to completely manipulate the datasheet from or app or web page, we need a specific API to achieve it. That is the reason why Google developed the Google Sheets API.

By means of this API you have total control of the datasheets, you can update, add or modify everything. Google has developed client libraries to access this API from several languages including PHP, Java, Python and also Javascript. Moreover, data can be manipulated using the REST API, as a HTTP transaction.

You can play with the APIs in the developers page. Concretely for Google Sheets API, you can go to APIs explorer page.

The problem with that API is that it hasn’t a SQL language as the previous Google Visualization API Query. So, in some cases is harder to implement.

Finally, we have to manage the identity and security. Google has several ways to strengthening indentity and security in their platform. For access any Google APIs in a secure way from a web application the protocol needed is OAuth2.

In our case of a web page, we need to use the OAuth 2.0 protocol for Server to Server Applications. In this scenario we will need a service account, which is an account that belongs to your application instead of to an individual end user.

Luckily, Google has developed several client libraries to handle the access to any Google Sheet. They are documented in their Google API Client Libraries. More specifically, OAuth2 protocol for server to server applications and PHP as the language, the documentation can be found here.

Summarizing, you can use the Google Sheets API from PHP following this guide from Google, but be aware that the updated documentation about using OAuth2 with PHP was in the link before. (I found some outdated information when connecting the client).

Finally, the last witty trick idea. May be we need our web page only read data in SQL format. May be we manipulate data directly in the Google Sheets page from Google Drive. So, we can first authenticate with the OAuth2 Library, get an access token, and then use the Google chart API with its SQL query language to retrieve the information by setting the query string as a parameter in the data source URL. the returning data can be grabbed from our web app in json format for example. Clever, isn’t it?.

So, let’s go step by step.

1. Overview of retrieving data

First of all, you need to identify the Spreadsheet ID and Sheet ID.

As mentioned before, there are two ways for accesing spreadsheets data:

  • Using the Google Visualization API (just for reading data)
  • Using the Google Sheets API (for complete data manipulation).

Spreadsheets can be public or private. You can find how to share your sheet pressing the button in the upper right side of the sheet.

If you are requesting to a public sheet, the easy way is using the Visualization API, but you can use the Google Sheets API either.

2. Overview of Google Visualization API

The most interesting thing for me now is that you can request different response formats (see request format) using the parameter tqx=out. You can choose between:

  • json – [Default value] A JSON response string.
  • html – A basic HTML table with rows and columns. This is useful for debugging.
  • csv – Comma-separated values.
  • tsv-excel – Similar to csv, but using tabs instead of commas. (At present day tsv-excel is still not working and some people continue reporting that bug).

Second important thing is: for the SQL command you need to use the tq= parameter with the value of the query SQL command as described in the Google Visualization API Query Language mentioned before.

As an example, you can run this example in the browser (Note that the sheet must be public shared):
https://docs.google.com/spreadsheets/d/1BNLFQpflymAdhi253l1n8Ch-B1W-YTcxexno88HHZdw/gviz/tq?gid=1704534235&tqx=out:html&tq=select%20B,D

You will receive a html page with the columns B and D.
I found an interesting function that sometimes you would need if working with SQL sentences. May be you need to convert a number into its column name equivalent. For example:

1=>A; 2=>B; 27=>AA; 28=>AB; 14558=>UMX

There are two different algorithms depending if the number is based in 0-indexed (0=>A) or 1-indexed (1=>A). Thanks to ircmaxell.

  • zero indexed:
function getNameFromNumber($num) {
    $numeric = $num % 26;
    $letter = chr(65 + $numeric);
    $num2 = intval($num / 26);
    if ($num2 > 0) {
        return getNameFromNumber($num2 - 1) . $letter;
    } else {
        return $letter;
    }
}
  • one indexed:
function getNameFromNumber($num) {
    $numeric = ($num - 1) % 26;
    $letter = chr(65 + $numeric);
    $num2 = intval(($num - 1) / 26);
    if ($num2 > 0) {
        return getNameFromNumber($num2) . $letter;
    } else {
        return $letter;
    }
}

3. Overview of Google APIs

The first thing you should know is that Google Sheets API v4 as most of the Google APIs must be enabled in the user’s developer console. In order to do that, you need to create or select a project in the Google Developers Console and  turn on the API required in the library. In our case, we can automatically create or select a project and enable the Google Sheets API v4 in the library by using this magic wizard.

You will see this screenshot in the APIs library menu. Remember the APIs are enabled or disabled in certain project, so if is the first time you use an API, you will be prompted to create a new project.

You must use either an OAuth 2.0 access token or an API key for . Not all APIs require authorized calls

Second important thing is that all requests to Google APIs represented in the API Console must include a unique identifier. Unique identifiers enable the console to tie requests to specific projects to monitor traffic and enforce quotas. So, every request your application sends to the Google APIs needs to identify your application to Google. There are two ways to identify your application: using an OAuth 2.0 token and/or using the application’s API key:

  • If the request requires authorization (such as a request for an individual’s private data), then the application must provide an OAuth 2.0 token with the request. The application may also provide the API key, but it doesn’t have to.
  • If the request doesn’t require authorization (such as a request for public data), then the application must provide either the API key or an OAuth 2.0 token, or both. Using an API key does not require user action or consent. API keys do not grant access to any account information, and are not used for authorization.

If your sheet is public shared, then you can use an API key which is the easiest way to access data using Google Sheets API.

Anyway, let’s see both credentials.

4. Credentials

API Key

The API key is the easiest way to identify your application to Google, and it is used when there is no need to authorize the app.

You create a key with some permissions and you simply need to send the key as a parameter in every request to an API in order to use it. The creation of an API key is also simple.

Go to the developer’s console. Select a project (by default, the last one will be selected). Go to credentials menu -> Create credentials -> API key.

A new API key will be created. You can also configure some restrictions to the use of the key.

For the time being, none restrictions will be fine, but, notice that the key identifies your project and provides API access, quota, and reports of your account. When you use API keys in your applications, take care to keep them secure. Publicly exposing your credentials can result in your account being compromised, which could lead to unexpected charges on your account. See best practices for securely using API keys.

Service Account Key

This credential is the one which not only identifies but also authenticates an app. Not all APIs require authorized calls, but we do require it in the case of Google Sheets API v4 for accessing private spreadsheets as we will see later.

Service accounts belong to your applications instead of to individual end users. Your application calls Google APIs on behalf of the service account, so users aren’t directly involved.

So, again, to create a new service account we need to go to the credentials page, go to create credentials and select service account key.

You will be prompted to select a service account. You can simply select new service account and select JSON as key type.

You need also give a service account name, and select a role. As owner of the project you will have full access to all resources of the project.

When pressing create button, a new new public/private key pair is generated and downloaded to your machine; it serves as the only copy of this key. You are responsible for storing it securely. You can generate multiple public-private key pairs for a single service account. In case you lose the file, you can generate a new key and download only once again. You can do it in the link «manage service accounts» which will redirect you to the «IAM & admin» menu of the console in the «service account» submenu.

It’s a recommended to rename the file as secret_file.json because it will be used later with this name to when using the Client API library from Google.

5. Overview of Google Sheets API with API key credential

As mentioned before, you can use Google Sheets API with API key only for public shared spreadsheets.

Note, you can use your API key with whatever public Google Sheet, it doesn’t have to belong to you. But each time you use an API, it will be reflected in your quota.

You can see the REST API resources of the API to get whatever data information about the spreadsheet. the only thing you should add is the parameter ?key=<API KEY>

For example:

https://sheets.googleapis.com/v4/spreadsheets/1BNLFQpflymAdhi253l1n8Ch-B1W-YTcxexno88HHZdw/values/Test1!A1:B10?key=AIzaSyDM9wKd7gtBJZq64hgUHlO_XgZtwluEftk

You will get the values of the range A1:B10 for the sheet ‘Test1’.

With all the possibilities of the API you will have total control over the spreadsheet.

Summarizing, we have seen two ways of accessing spreasheets. But they were using just public shared spreadsheets:

  • Using the Google visualization API without any credential.
  • Using the Google Sheets API with API key credential.

With the first one, we can get the data in SQL format but we are not able to get spreadsheet properties, only retrieve data. In contrast, with the second one, we have total control over the sheet, but it can not be done in SQL style and we need an API key credential.

Ok. But, What happen if the spreadsheet is private?. We are reaching the main porpouse of this article.

6. Overview of private spreadsheets

Private spreadsheet are those whose shared link is disabled, so only certain users have access to them.

Accessing private spreadsheet data requires passing explicit authorization credentials, so it is mandatory the use of OAuth2 authentication through service account credentials as documented here and here.

Again, after having the OAuth2 token, we can use whatever of the two APIs explained: Google Visualization API or Google Sheets API.

But first of all, we need to authorize the account service to use our Google Sheet. So we need to go to share button as explained in point 1: Overview of retrieving data. We have to share the Spreadsheet with the service account ID (you can see it in the screenshot before, or in the client_email from the secret_file.json, donwloaded previously).

7. How to get the OAuth2 token

Next, we need to get the access token. Although we can programatically get it by ourselves, it is encouraged to do it with the help of the Google API Client Libraries which will explained later.

I have made a php script to get the token programatically without Google API Client Services for easy undesrtanding the proccess. The script just follows the guidelines explained in preparing to make an authorized API call with HTTP/REST. We just need three parameters to configure:

  • client_email: We have talked about it before. You can grab from the secret_file.json file previously downloaded.
  • private_key: Again, it is included in the secret_file.json file.
  • scope: a list of scopes that your application should be granted access to.

For Google Sheets API v4, the scopes are explained here.

file: getToken.php

<?php
// Modified script from Joseph Shih. https://stackoverflow.com/questions/11818441/jwt-computing-the-signature-sha256withrsa

//helper function
function base64url_encode($data) { 
	return rtrim(strtr(base64_encode($data), '+/', '-_'), '='); 
}
//Google's Documentation of Creating a JWT: https://developers.google.com/identity/protocols/OAuth2ServiceAccount#authorizingrequests
function getToken() {
	// Required Params
	$client_email = "xxxxxxxxx@spring-asset-175411.iam.gserviceaccount.com";
	$scope = "https://www.googleapis.com/auth/spreadsheets https://www.googleapis.com/auth/spreadsheets.readonly";
	$private_key = "-----BEGIN PRIVATE KEY-----\nMIIEvgIBADANBgkqhkiG9w0BAQEFAASCBKgwggSkAgEAAoIBAQCYk8i0Jmmjngia\nn/+xnVTCQY6bydpaJ/TtacQTQK4D2lyOlMw2cwOq8qGg5j6xkaV8pg+j8t2DzNrA\nm/5Au2e149MerZjlSmOVs5zrxni2SnS9tevBCL3endk03zAVhavTCcEQ8IRDeH4N\nOzOhtEHQ0cJ/WhvmMUeUk6iD3JJXQ7zDaRcxuAhuDro8nt6PJIheYuL9OTs6Yths\nFaY9czLlibYva09UrUhK7tWqJWr8EtrM5fcED7Vhh2vOq0dmi8ielX5K7EPj5m/Y\nYqo7rtk6TNcrnAhUClUA5RNRBR3LUi5549L32fFJ9ZZ2qE1hKl822MRn/W90U+OP\n8jq5sEcfAgMBAAECggEAHcpdGa+WWLf2fhpXVlqdvgAG1CwpUgkTLXfFLO0oDOCJ\nyd0LsmXqhWoyXLQX8Zu6cAgMbbpxvdY1r/3FOuZcbYs6IXdLrxlDMGuNbAuobg9D\nk/b9PQdz6w9u5X/eZ5Vd4yq4Y5JSgMn58qNohV4oq5kJHMx/PgbJLx1VfcmHWTo3\nLfKrzoge450x00t7TBTVQgMwvGfObejj0VX0v76XnVB+imF6zHbGJVSKdwY2YVUc\nxWye/PurXua+5RPM8krr1KEIAnDBiCTxuYuMqlUy6fiEGIEetSqlvxBdrn+G6rk1\n8UqLB46buXAGH5zPkzNmEotjlU92uVXGWuyP5PcJwQKBgQDVU02iY3pf/UU+phUJ\nnViN84MIWsnDwdLgZH+Bf5ffz1W57EVLuuLb77L6POpMepgP7pj+57rz0T/FOnTy\n/uaU+NbGEQX4pAqCBxjJIdLl/OcogYqUS4Z8Izi3BY0eBmzhanjZpXouVeiN7Fgy\n9HgkNl60vO6JEXAUqNvLZMTq2QKBgQC3GXuw9drMdQbVSUnCjCmUphV+7xapiELK\nSnOw6VLQkVwANL5bq11ggrZhKDxfGFZCS4HHBfNwSWpYemPnSfVsol3gaMdno+u8\njWbiWABWKtfBsJscriPCQ74w8agQ2XWnHkr1HlM3BLidold7VatnIwSztJmpoZ6X\nwGCPypPWtwKBgGxhNkAaakH6WwPJPUb1Odhkr12/QmLCRnDani0ctfxvSg57i3k+\nKaOz1CpuaFsSrc7qWbCn6sOUu+FoIaKS/nMROYzHeVEphm0pTd5h4NQX+RkLN2zA\nMkM/YcEagOeQPCEKBvwMplaELvJr8SNyMNe1B8irdEUPJvJYF9LuD12BAoGBAJqR\no66uzcd+H4ecd+0JX+zCDDF86rexdRjKo0G8ppG1tGHkJLLERm67yfTY/6YTqHYM\nwgDekDTBdUo9/+p3r/zGUNOqAHQImHAzOqBnQ9l6D2vOiBM9GqjbgUpWJtgai8zJ\nsOOedwdiL+u66XkPyEOn90cxdesSqgxXdezNSeKfAoGBAMeyBJnEmyJm6pQEECBf\n7jXlXVh0LE197Hh0QSjiJLkdMfoMeHEQAAujLNeqrnQs4oWV+5B7zk+gPu2nTe8z\nUZPbE1VPyv6Xi2Fb5m/eFjtFRN4Am2WII/UO4Py4VtCRLLvZsFs4GPE74aAKEC5I\nm/oUnHrG7aWp7AatUieiALxr\n-----END PRIVATE KEY-----\n";
    //{Base64url encoded JSON header}
	$jwtHeader = base64url_encode(json_encode(array(
		"alg" => "RS256",
		"typ" => "JWT"
	)));

	//{Base64url encoded JSON claim set}    
	$jwtClaim = base64url_encode(json_encode(array(
		"iss" => $client_email,
		"scope" => $scope,
		"aud" => "https://www.googleapis.com/oauth2/v4/token",
		"exp" => time() + 3600,
		"iat" => time()
	)));

	//The base string for the signature: {Base64url encoded JSON header}.{Base64url encoded JSON claim set}
	$success=openssl_sign(
		$jwtHeader.".".$jwtClaim,
		$jwtSig,
		$private_key,
	"sha256WithRSAEncryption"
	);        
	$jwtSign = base64url_encode($jwtSig);
	//{Base64url encoded JSON header}.{Base64url encoded JSON claim set}.{Base64url encoded signature}
	$jwtAssertion = $jwtHeader.".".$jwtClaim.".".$jwtSign;

	$url = 'https://www.googleapis.com/oauth2/v4/token';
	$data = array(
		'grant_type' => 'urn:ietf:params:oauth:grant-type:jwt-bearer',
		'assertion' => $jwtAssertion
	);
	// 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 = file_get_contents($url, false, $context);
	if ($result === FALSE) {
		/* Handle error */        
	}
	/*
	// If you want to use cURL instead of file_get_contents
	$url = 'https://www.googleapis.com/oauth2/v4/token';
	$fields = array(
		'grant_type' => 'urn:ietf:params:oauth:grant-type:jwt-bearer',
		'assertion' => $jwtAssertion
	);
	//url-ify the data for the POST
	$fields_string='';
	foreach($fields as $key=>$value) { $fields_string .= $key.'='.$value.'&'; }
	$fields_string = rtrim($fields_string,'&');
	//open connection
	$ch = curl_init();
	//set the url, number of POST vars, POST data
	curl_setopt($ch,CURLOPT_URL,$url);
	curl_setopt($ch,CURLOPT_POST,count($fields));
	curl_setopt($ch,CURLOPT_POSTFIELDS,$fields_string);
	//execute post
	$result = curl_exec($ch);    
	*/
	return $result;
}
$token = getToken();
$access_token = json_decode($token)->{"access_token"};
print 'Token = '.$token.'<br><br>';
print 'Access token = '.$access_token;
?>

Usually it is not useful to directly copy the params $client_email and $private_key. Instead, it is better to grab directly from the secret_file.json

$path = dirname(__FILE__) . '/secret_file.json'; 
$jsonKey = file_get_contents($path); 
$client_email = json_decode($jsonKey, true)["client_email"];
$private_key = json_decode($jsonKey, true)["private_key"];

Access tokens issued by the Google OAuth 2.0 Authorization Server expire one hour after they are issued. When an access token expires, then the application should generate another JWT, sign it, and request another access token.

Once you have the access OAuth2 token, you can proceed with accessing spreedsheet data by using either:

  • Google Visualization API, or
  • Google Sheet API v4.

8. Google Visualization API with OAuth2 token

If you have the token this can not be so easy.

Just append the parameter &access_token= to the URL we have seen before. An example of a formed URL could be the following:

https://docs.google.com/spreadsheets/d/1BNLFQpflymAdhi253l1n8Ch-B1W-YTcxexno88HHZdw/gviz/tq?gid=1704534235&tqx=out:html&tq=select%20B,D&access_token=ya29.c.ElxlBexI2vTCtqR8NKAX3RL6KqluDmGONA-UtJUgpWSAX21gjeHsaX81nBcnlaUgzCmXYTV_fYm1RECgLACL-btsAI_ZmXfgi_hgzzVmXCpx6kM-wkA2dSGDFPxJIA

If you are retrieving the data from php you probably want to get the data in csv format instead of html format, so the parameter tqx=out: should be csv. The php coomands should be:

$url = 'https://docs.google.com/spreadsheets/d/1BNLFQpflymAdhi253l1n8Ch-B1W-YTcxexno88HHZdw/gviz/tq?gid=1704534235&tqx=out:csv&tq=select%20B,D&access_token=ya29.c.ElxlBexI2vTCtqR8NKAX3RL6KqluDmGONA-UtJUgpWSAX21gjeHsaX81nBcnlaUgzCmXYTV_fYm1RECgLACL-btsAI_ZmXfgi_hgzzVmXCpx6kM-wkA2dSGDFPxJIA';
$pag = file_get_contents($url);
$response = str_getcsv($pag);

For parsing the received csv, you have a easy command in php: str_getcsv, which converts csv in array.

In case you received data in json format, I have used a parseGV function for parse the Google Visualization returning json data (Thanks to Jason Maggard).

function parseGV($gv) {
    $response = substr($gv, strpos($gv, "\n") + 1);		// Delete first line
    $data = preg_replace('/google.visualization.Query.setResponse\(/', '', $response);
    $data = preg_replace('/\)\;/', '', $data);
    $data = json_decode($data);
    $data_keys = $data->table->cols;
    $data_rows = $data->table->rows;
    $arr = array();
    foreach ($data_rows as $key => $row) {
        $a = array();
        foreach ($row->c as $k => $value) {
            if(isset($value->v)) {
                $a[$data_keys[$k]->label] = $value->v;	
            }
            else {
                $a[$data_keys[$k]->label] = null;
            }
        }
        $arr[] = $a;
    }
    return ($arr);
}

Let’s see how to do it with the Google Sheets API.

9. Google Sheets API with OAuth2 token

This API can be used to retrieve information but also to modify the spreadsheet. After your application obtains an access token, you can use the token to make calls to a Google API on behalf of a given user account or service account. To do this, include the access token in a request to the API by including either an access_token query parameter or an Authorization: Bearer HTTP header (documented here). When possible, the HTTP header is preferable, because query strings tend to be visible in server logs.

The HTTP method used to make the API calls can be: GET, PUT, POST, DELETE or PATCH. You can see for example the documentation about reading and witing values.

The  easiest call is reading data because it uses a simple GET call. This can be typed directly in the url bar of the browser:

https://sheets.googleapis.com/v4/spreadsheets/1BNLFQpflymAdhi253l1n8Ch-B1W-YTcxexno88HHZdw/values/A1%3AB10?access_token=ya29.c.ElxnBQOjVxicnA6hcp8-s-_vzwgcEV872sV4t68gIhm2pe0elvFqH1O35kkQJh_UOA2YqfG9DNyptFlGXX-4j_wE7D3j5BBKMRsg7PSpEBnkULaIamx7I4RLaFKtgQ

In order to know which protocol and what parameters does a call needs, the best way to know is directly in the REST resources reference. In this page you have also the choice of try the call method at the  right side of the page. Also, if you maximize the window you can see the cURL commands to shape it.

Another way to see how to construct the calls is by the APIs explorer. When you execute the command you will wee the request, including the HTTP method used, and the response.

In PHP you can construct the calls with the commands you like better. An example to with the method: spreadsheets.values.update, updating only the cell Test1!A20 can be done in different ways:

  • With file_get_contents using the access_token in the url:
function updateData($access_token) {	
	$url = 'https://sheets.googleapis.com/v4/spreadsheets/1BNLFQpflymAdhi253l1n8Ch-B1W-YTcxexno88HHZdw/values/Test1!A20?includeValuesInResponse=true&responseValueRenderOption=UNFORMATTED_VALUE&valueInputOption=RAW&access_token='.$access_token;
	$data = array(
		'values' => [["this is a test"]]
	);	
	// use key 'http' even if you send the request to https://...
	$options = array(
		'http' => array(
			'header'  => 'Content-Type: application/json',
			'method'  => 'PUT',
			'content' => json_encode($data)
		)
	);	
	$context  = stream_context_create($options);
	$result = file_get_contents($url, false, $context);	
	return ($result);
}
  • With file_get_contents using the access_token in the header:
function updateData($access_token) {	
	$url = 'https://sheets.googleapis.com/v4/spreadsheets/1BNLFQpflymAdhi253l1n8Ch-B1W-YTcxexno88HHZdw/values/Test1!A20?includeValuesInResponse=true&responseValueRenderOption=UNFORMATTED_VALUE&valueInputOption=RAW';
	$data = array(
		'values' => [["this is a test"]]
	);
	$headers = array(
		'Accept: application/json',
		'Content-Type: application/json',
		'Authorization: Bearer '.$access_token
    );
	// use key 'http' even if you send the request to https://...
	$options = array(
		'http' => array(
			'header'  => $headers,	// When php is compiled --with-curlwrappers
			//'header'  => implode('\r\n', $headers).'\r\n',	// // When php is not compiled --with-curlwrappers
			'method'  => 'PUT',
			'content' => json_encode($data)
		)
	);
	var_dump($options['http']['header']);
	$context  = stream_context_create($options);
	$result = file_get_contents($url, false, $context);	
	return ($result);
}

Please, note how the header is constructed becasue it depends on how php is compiled.

  • With cURL using access_token in the url:
function updateDatacURL($access_token) {
	$url = 'https://sheets.googleapis.com/v4/spreadsheets/1BNLFQpflymAdhi253l1n8Ch-B1W-YTcxexno88HHZdw/values/Test1!A20?includeValuesInResponse=true&responseValueRenderOption=UNFORMATTED_VALUE&valueInputOption=RAW&access_token='.$access_token;
	$fields = array(
		'values' => [["this is a test"]]		
	);	
	$headers = array(		
		'Content-Type: application/json'
    );	
	$ch = curl_init();
	//set the url, number of POST vars, POST data
	curl_setopt($ch,CURLOPT_URL,$url);
	curl_setopt($ch,CURLOPT_HTTPHEADER, $headers);
	curl_setopt($ch,CURLOPT_CUSTOMREQUEST, "PUT");
	curl_setopt($ch,CURLOPT_POST,count($fields));
	curl_setopt($ch,CURLOPT_POSTFIELDS,json_encode($fields));
	//execute post
	$result = curl_exec($ch);
	return $result;
}
  • With cURL using the access_token in the header:
function updateDatacURL($access_token) {
	$url = 'https://sheets.googleapis.com/v4/spreadsheets/1BNLFQpflymAdhi253l1n8Ch-B1W-YTcxexno88HHZdw/values/Test1!A20?includeValuesInResponse=true&responseValueRenderOption=UNFORMATTED_VALUE&valueInputOption=RAW';
	$fields = array(
		'values' => [["this is a test"]]		
	);	
	$headers = array(
		'Accept: application/json',
		'Content-Type: application/json',
		'Authorization: Bearer ' . $access_token
    );	
	$ch = curl_init();
	//set the url, number of POST vars, POST data
	curl_setopt($ch,CURLOPT_URL,$url);
	curl_setopt($ch,CURLOPT_HTTPHEADER, $headers);
	curl_setopt($ch,CURLOPT_CUSTOMREQUEST, "PUT");
	curl_setopt($ch,CURLOPT_POST,count($fields));
	curl_setopt($ch,CURLOPT_POSTFIELDS,json_encode($fields));
	//execute post
	$result = curl_exec($ch);
	return $result;
}

10. The Google API client library

As I said before, Google says when using OAuth 2.0 to Access Google APIs: «Given the security implications of getting the implementation correct, we strongly encourage you to use OAuth 2.0 libraries when interacting with Google’s OAuth 2.0 endpoints. It is a best practice to use well-debugged code provided by others, and it will help you protect yourself and your users». In any case, the libray is 8 MBytes and it has more than 7000 files. It is a little heavy because it has implemented code for access almost all Google APIs. So, if you are only accessing Google Sheets without a lot of calls, may be you can be comfortable with the snippets explained before.

Anyway, let’s obey to Google, and let’s see an overview about how to use this library in PHP.

Installation

First of all, you need to install the libraries. You can download the library from here. The easiest way is by using composer. But if you abhor using composer, you can download the package in its entirety. The Releases page lists all stable versions. Download any file with the name google-api-php-client-[RELEASE_NAME].zip for a package including this library and its dependencies.

Uncompress the zip file you download, and include the autoloader in your project:

require_once '/path/to/google-api-php-client/vendor/autoload.php';

Build the authorized Client Object

Next, firts you need to do is build an authorized client object. This client object is the primary container for classes and configuration in the library. This object includes your servcice account credentials and the scope os use. You need a proper build of this object in order make any call to Google APIs.

you need to prepare to make an authorized API call and use your service account credentials to authenticate. We have mentioned before that Google calls it «authorized Google_Client object».

<?php
require dirname(__FILE__).'/google-api-php-client-2.2.1/vendor/autoload.php';
$secret_file_path = dirname(__FILE__).'/secret_file.json';
putenv('GOOGLE_APPLICATION_CREDENTIALS='.$secret_file_path);
$client = new Google_Client();
$client->addScope(array(Google_Service_Sheets::SPREADSHEETS_READONLY, Google_Service_Sheets::SPREADSHEETS_READONLY));
$client->useApplicationDefaultCredentials();
?>

The handling of the access_token (including refreshing when it expires) is made automatically by the library. But the library give you a function to grab the granted access token:

$access_token = $client->fetchAccessTokenWithAssertion()["access_token"];

This can be interesting if you are going to use API visualization query hereinafter.

Once we have configured the authorized client, we are prepared to make API calls to any API by building the service object.

Build the service object

Making calls to a APIs is done through service objects. These objects are created by passing an instance of Google_Client to it. Google_Client contains the IO, authentication and other classes required by the service to function.

$service = new Google_Service_Sheets($client);

Services are called through queries to service specific objects

Calling an API

Each API provides resources and methods, usually in a chain. These can be accessed from the service object in the form $service->resource->method(args). Most method require some arguments, then accept a final parameter of an array containing optional parameters.

There are two main types of response – items and collections of items. Each can be accessed either as an object or as an array. Collections implement the Iterator interface so can be used in foreach and other constructs.

We are reaching the end of the article. Let’s see some general issues about the Google Sheets API.

11. The Google Sheets API v4

We have 4 resources in the API:

  • spreadheets: Represents a spreadsheet. You can view/edit data such as: spreadsheet id, number and title of inlcuiding sheets, language, font, etc.
  • spreadheets.developerMetadata: Developer metadata associated with a location or object in a spreadsheet. Not very useful for our objectives.
  • spreadheets.sheets: Currently, just for copy a sheet to another spreadsheet.
  • spreadheets.values: Data within a range of the spreadsheet. Here is where we can view/add/edit all the data. This is the most important resource for us right now.

Aside from the data contained in its cells, a spreadsheet includes many other types of data, such as: cell formats, cell borders, named ranges, protected ranges or conditional formatting that can be updated.

Here, we are describe only two operations: basic reading, and basic writing.

Basic Reading

In order to read a single range of data out of a spreadsheet, use the get method from the spreadsheets.values resource. An example of the syntax is the following:

<?php
require dirname(__FILE__).'/google-api-php-client-2.2.1/vendor/autoload.php';
$secret_file_path = dirname(__FILE__).'/secret_file.json';
putenv('GOOGLE_APPLICATION_CREDENTIALS='.$secret_file_path);
$client = new Google_Client();
$client->addScope(array(Google_Service_Sheets::SPREADSHEETS_READONLY, Google_Service_Sheets::SPREADSHEETS_READONLY));
$client->useApplicationDefaultCredentials();

$service = new Google_Service_Sheets($client);
$gsheetid = '1BNLFQpflymAdhi253l1n8Ch-B1W-YTcxexno88HHZdw';
$range = 'Test1!A1:B10';
$params = [
	'dateTimeRenderOption' => 'FORMATTED_STRING',
	'majorDimension' => 'ROWS'
];
$valueRange = $service->spreadsheets_values->get($gsheetid, $range, $params);
$listValues = $valueRange['values'];

var_export($listValues);
?>

Basic Writing

In order to write a single range of data, use the update method from the spreadsheets.values resource. An example of the syntax is the following:

<?php
require dirname(__FILE__).'/google-api-php-client-2.2.1/vendor/autoload.php';
$secret_file_path = dirname(__FILE__).'/secret_file.json';
putenv('GOOGLE_APPLICATION_CREDENTIALS='.$secret_file_path);
$client = new Google_Client();
$client->addScope(array(Google_Service_Sheets::SPREADSHEETS_READONLY, Google_Service_Sheets::SPREADSHEETS));
$client->useApplicationDefaultCredentials();

$access_token = $client->fetchAccessTokenWithAssertion()["access_token"];
print 'Access token = '.$access_token.'<br><br>';

$service = new Google_Service_Sheets($client);
$gsheetid = '1BNLFQpflymAdhi253l1n8Ch-B1W-YTcxexno88HHZdw';
$range = 'Test1!A20:C24';
$data = [["Added1","Added2"],["Added3","Added4"]];
$options = [
	'valueInputOption' => 'RAW'
];

$valueRange = new Google_Service_Sheets_ValueRange();
$valueRange->setValues($data);
$valueRange->setMajorDimension('COLUMNS');

$result = $service->spreadsheets_values->update($gsheetid, $range, $valueRange, $options);

print($result->updatedRange. PHP_EOL);
?>

As you can see, data can be provided in rows or in columns. Note that the range is the beggining of the update, but the updated range doesn’t need to coincide.

If you need to read or write for several ranges, you should use batch methods.

12. SQL with Google Sheets API

And finally … one last trick.

There is a formula in Google Sheets called QUERY. It allows you to use pseudo-SQL code to manipulate your data in Google Sheets. The query to perform has to be written in the Google Visualization API Query Language.

The trick is easy. Append a formula in a cell containing the query command and then read the resulting cells. All made with the methods provided by the Google Sheets API. After grabbing the results, you can delete the resulting cells to revert the sheet to the previous state.

That’s all folk. I hope you have enjoyed reading this, and I hope it can be useful for you in your next projects.

I would appreciate any comments below. Thank you.

About the Author

César Morillas Barrio (cmorillas1@gmail.com)

UMH University
Spain 

Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *