How to Create, Update & Delete Spreadsheet with Google Sheets API using PHP

php
Published on October 31, 2017

Use-Cases of this Tutorial

  • Learn how to create, update & delete a Google spreadsheet in PHP using Google Sheets API

It is possible to create Google Spreadsheeets programatically using API. This tutorial demonstrates it using the latest version 4 of Google Sheets API.

Google provides an official PHP client library that takes care of all Google APIs. The downside is that it is quite heavy in size. I think it is much better to write your own code to implement such simple API calls.

Demo

Input the name of the spreadsheet, and click on the login button.

Sample codes for download are provided at the end of this tutorial.

Getting API Keys

The first step is to get a Google application API keys. If you have an existing Google application, you can use it - just make sure that you have enabled Google Sheets API & Google Drive API in the API library.

If you don't have an existing Google application then follow the below steps to create a Google application and get API keys :

  • Go to the Google API Console
  • Create a new project by clicking "Select a project" (at the top), and then clicking on the "+" button in the dialogbox. In the next screen enter your project name, and agree with the Terms and Conditions.
  • After the project is created, select the created project from the top dropdown.
  • Click the Library tab on the left. Search for "Google Sheets API" & "Google Drive API" and enable them both.
    The reason for also enabling Google Drive API is because Google Sheets API does not contain the API calls required to update spreadsheet's properties (like its title) and deleting spreadsheet. Google Drive APIs are used to perform these 2 operations.
    FYI, spreadsheets are created and saved in Google Drive.
  • For the next step, click on Credentials tab on the left. In the next screen click on "OAuth consent screen". Fill out the mandatory fields. Save it.
  • Now click on the "Credentials" tab (just beside "OAuth consent screen"). In the screen, click on "Create credentials". Choose "OAuth Client ID" as the type.
  • In the next screen fill out the name. Under Application type select "Web application"
  • Add a redirect url in the section Authorised redirect URIs. This url should point to your redirect url script. A redirect url is the url where Google redirects the user after he authorizes and approves your Google Application.
    For testing purposes you can even use a localhost url.
    If you are using the attached codes in this tutorial, the redirect url should point to google-login.php
  • There is no need to fill Authorised JavaScript origins. Create the Google application by clicking the Create button.
  • You should be getting the App Client ID and App Secret. These will be required later.

Implement OAuth Login and Get the Access Token of the User

To create a spreadsheet for the user, you need his access token. To get the user's access token, you must implement "Login with Google" functionality in your website.

The user will use his Google credentials to login and then approve your Google application. Upon a successful login and approval, your web application can get an access token of the user. Consider this access token as a temporary key to perform actions on behalf of the user - like creating a spreadsheet. It is also possible to get a permanent key to perform actions on behalf of the user even if he is offline and not manually available.

Google OAuth in your web application would work in the following way:

  1. Your application redirects the user to a Google OAuth url. This Google OAuth url looks of the form like :

    $google_oauth_url = 'https://accounts.google.com/o/oauth2/auth?scope=' . urlencode('https://www.googleapis.com/auth/drive.file') . '&redirect_uri=' . urlencode(GOOGLE_APPLICATION_REDIRECT_URL) . '&response_type=code&client_id=' . GOOGLE_APPLICATION_CLIENT_ID . '&access_type=online';

    The scope parameter includes the permissions that your application is asking from the user. In this case the application is asking for the permission https://www.googleapis.com/auth/drive.file, which means "Viewing and managing Google Drive files and folders that user has opened or created with the application".

    The redirect_uri parameter is the redirect url that you entered while creating the Google application.

    The client_id parameter is the ID of the Google application.

    The access_type parameter is hardcoded to "online".

    You can include this OAuth url to your website in a HTML <a> element, or use PHP's header or Javascript's document.location :

    <a href="<?= $google_oauth_url ?>">Login with Google</a>
  2. This link will redirect the user to Google, where he logins with his credentials and sees the permissions that your application is asking from him (for example he sees that your application is wanting to create spreadsheets in his Google Drive). He can choose to approve or disapprove your application.

  3. If he approves, Google redirects him back to your application to the redirect url you had earlier specified, also appending an OAuth code to the url. So basically what happens is that your application redirects the user to Google. Then Google redirects the user back to your application page.

    You can exchange the OAuth code for an access token by making an API call.
    Once you get the access token, you can create a spreadsheet by making another API call. 

    // Google passes a parameter 'code' to the redirect url script
if(isset($_GET['code'])) {
	// Get the access token 
	$access_token = GetAccessToken($_GET['code']);
	
	// Now create spreadsheet with this access token
	CreateSpreadsheet($access_token);
}

To know more about "Login with Google" see the tutorial Login with Google using PHP. The tutorial also includes getting a refresh token, through which your application can create spreadsheets even if the user is offline (for example your application is required to create spreadsheets via a cron job).

OAuth Scopes Required

To create a spreadsheet, Google provides 3 scopes that you can use in the OAuth url :

  1. https://www.googleapis.com/auth/drive - Reading and writing all the files in Google Drive
  2. https://www.googleapis.com/auth/drive.file - Reading and writing files in Google Drive that have been created using the current Google application
  3. https://www.googleapis.com/auth/spreadsheets - Reading and writing all spreadsheets in Google Drive

Obviously to just create a spreadsheet, you don't need access to all of the user's files in Google Drive. This may discourage the user to approve your application. Ask for the least permissions that your application requires, in this case I think https://www.googleapis.com/auth/drive.file is sufficient.

API Call to Get an Access Token from the OAuth Code

When Google redirects the user to your application after he approves your application, it also passes an OAuth code as a GET parameter. You can get the access token of the user by making an API call utilizing this OAuth code.

// client_id : Google App Client ID
// redirect_uri : Google App redirect url
// client_secret : Google App Secret
// code : OAuth code
function GetAccessToken($client_id, $redirect_uri, $client_secret, $code) {	
	$url = 'https://accounts.google.com/o/oauth2/token';			
	
	$curlPost = 'client_id=' . $client_id . '&redirect_uri=' . $redirect_uri . '&client_secret=' . $client_secret . '&code='. $code . '&grant_type=authorization_code';
	$ch = curl_init();		
	curl_setopt($ch, CURLOPT_URL, $url);		
	curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);		
	curl_setopt($ch, CURLOPT_POST, 1);		
	curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, FALSE);
	curl_setopt($ch, CURLOPT_POSTFIELDS, $curlPost);	
	$data = json_decode(curl_exec($ch), true);
	$http_code = curl_getinfo($ch,CURLINFO_HTTP_CODE);		
	if($http_code != 200) 
		exit('Error : Failed to receieve access token');
		
	return $data;
}

Creating a Spreadsheet

A spreadsheet with a title can be created by making an API call.

// spreadsheet_title : Title of the spreadsheet
// access_token : access token of the user
function CreateSpreadsheet($spreadsheet_title, $access_token) {
	$curlPost = array('properties' => array('title' => $spreadsheet_title));
	
	$ch = curl_init();		
	curl_setopt($ch, CURLOPT_URL, 'https://sheets.googleapis.com/v4/spreadsheets');		
	curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);		
	curl_setopt($ch, CURLOPT_POST, 1);		
	curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, FALSE);
	curl_setopt($ch, CURLOPT_HTTPHEADER, array('Authorization: Bearer '. $access_token, 'Content-Type: application/json'));	
	curl_setopt($ch, CURLOPT_POSTFIELDS, json_encode($curlPost));	
	$data = json_decode(curl_exec($ch), true);
	$http_code = curl_getinfo($ch,CURLINFO_HTTP_CODE);		
	if($http_code != 200) 
		exit('Error : Failed to create spreadsheet');

	return array('spreadsheet_id' => $data['spreadsheetId'], 'spreadsheet_url' => $data['spreadsheetUrl']);
}

You can also give a few other properties to the spreadsheet like locale, timezone etc and also create specific worksheets in the spreadsheet. See the full spreadsheet object here.

Updating the Spreadsheet's Properties

Google Sheets API version 4 does not include the API call to edit spreadsheet's properties. So you have to use a Google Drive API call to update the spreadsheet's title & other properties (Spreadsheets are basically files saved in Google Drive).

// spreadsheet_id : ID of the spreadsheet
// spreadsheet_title : Title of the spreadsheet
// access_token : access token of the user
function UpdateSpreadsheetProperties($spreadsheet_id, $spreadsheet_title, $access_token) {
	$curlPost = array('name' => $spreadsheet_title);
	
	$ch = curl_init();		
	curl_setopt($ch, CURLOPT_URL, 'https://www.googleapis.com/drive/v3/files/' . $spreadsheet_id);		
	curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);		
	curl_setopt($ch, CURLOPT_CUSTOMREQUEST, 'PATCH');	
	curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, FALSE);
	curl_setopt($ch, CURLOPT_HTTPHEADER, array('Authorization: Bearer '. $access_token, 'Content-Type: application/json'));	
	curl_setopt($ch, CURLOPT_POSTFIELDS, json_encode($curlPost));	
	$data = json_decode(curl_exec($ch), true);
	$http_code = curl_getinfo($ch,CURLINFO_HTTP_CODE);
	if($http_code != 200) 
		exit('Error : Failed to update spreadsheet properties');
}

Deleting a Spreadsheet

You can delete a spreadsheet by making another Google Drive API call.

// spreadsheet_id : ID of the spreadsheet
// access_token : access token of the user
function DeleteSpreadsheet($spreadsheet_id, $access_token) {
	$ch = curl_init();		
	curl_setopt($ch, CURLOPT_URL, 'https://www.googleapis.com/drive/v3/files/' . $spreadsheet_id);		
	curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);		
	curl_setopt($ch, CURLOPT_CUSTOMREQUEST, 'DELETE');		
	curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, FALSE);
	curl_setopt($ch, CURLOPT_HTTPHEADER, array('Authorization: Bearer '. $access_token, 'Content-Type: application/json'));		
	$data = json_decode(curl_exec($ch), true);
	$http_code = curl_getinfo($ch,CURLINFO_HTTP_CODE);
	if($http_code != 204) 
		exit('Error : Failed to delete spreadsheet');
}

Download Codes

Download

In this Tutorial