Updating Google Sheets manually has been a tedious task always as the data to be worked upon is supposed to be fetched from a third-party source. However, it also made an important step for keeping track of certain information.
Since nothing is unworkable, we came up with a solution for managing sheets automatically- to simply reduce efforts and save time. This solution encompasses integrating PHP with Google Sheets API.
This blog will give you insights on what is Google Sheets API, how to integrate PHP with Google Sheets API, and how it can reduce your manual efforts.
What is Google Sheets API?
Google Sheets API is an online spreadsheet app that allows users to create, edit, and format spreadsheets simultaneously with other users. The new API lets users access data by code as well.
Sheets API lets developers programmatically perform the following tasks:
- Read and write data
- Format text and numbers
- Build pivot tables
- Enforce cell validation
- Set frozen rows
- Adjust column sizes
- Apply formulas
- Create charts and many more!
How to Get Started?
Firstly, we will get a quick check on the pre-requisite to work with Google Sheets API.
You’ll need to install composer for getting started-
- Composer
A composer is a tool for dependency management in PHP. It allows you to declare the libraries your project depends on to manage installations and updates automatically on their own.
Steps to Integrate Google Sheets API with PHP
These are the following steps that you need to follow for successful integration-
Step 1
Turn on the Google Sheets API and get credentials for your project.
- Visit https://console.developers.google.com
- Create a new project
- Tap on Enable APIs And Services
- Search for Google Sheets API
- Enable it
- Now tap on Enable Credentials
- Use these settings
- Click on - what credentials do I need?
- Now use these settings
- A file will be downloaded after clicking continue to rename it to credentials.json
-
- Run this command in your working directory to access google sheets API.
composer require google/apiclient:^2.0
-
Copy the credentials file which was downloaded after step 1 and place it inside the working directory also rename it to credentials.json.
- Get client_email by using the following command
cat credentials.json
- Share your Google Sheet to the provided email address by step 3.
- Create a text file with .php extension, this will contain all the required code.
- Run this command in your working directory to access google sheets API.
How did I implement it?
- Reading the data of your google sheet
-
- First, we will load the libraries which the composer installed to access Google API services.
require __DIR__ . '/vendor/autoload.php';
- Now we need to read the data from the spreadsheet.
//Reading data from spreadsheet.
$client = new \Google_Client();
$client->setApplicationName('Google Sheets and PHP');
$client->setScopes([\Google_Service_Sheets::SPREADSHEETS]);
$client->setAccessType('offline');
$client->setAuthConfig(__DIR__ . '/credentials.json');
$service = new Google_Service_Sheets($client); - Now we need to provide our SpreadsheetID and range.
$spreadsheetId = "Your SpreadsheetID"; //It is present in your URL
$get_range = "Your Sheet Name! Range”;Note: Sheet name is found in the bottom of your sheet and range can be an example
”A2: B10” or “A2: C50” or “B1: B10” etc.
- Fetching data from your spreadsheet and storing it.
- First, we will load the libraries which the composer installed to access Google API services.
//Request to get data from spreadsheet.
$response = $service->spreadsheets_values->get($spreadsheetId, $get_range);
$values = $response->getValues();
2. Updating the data in your google sheet
-
- Set your update range like earlier
$update_range = "Your Sheet Name! Range”;
- Store your values in an array of arrays.
$values = [[$value1, $value2]];
- Creating a request.
$body = new Google_Service_Sheets_ValueRange([
'values' => $values
]);
$params = [
'valueInputOption' => 'RAW'
]; - Calling update service.
$update_sheet = $service->spreadsheets_values->update($spreadsheetId, $update_range, $body, $params);
- Set your update range like earlier
How to run your script?
Open your terminal and run this command
Note: You need to run this command in the directory where your PHP file is present.
Other available features that you can use
Conditional Formatting was one of the features that Google Sheets API provides. I honestly love this feature because you can manage things dynamically using your codebase.
There are a bunch of other features also provided by Google APIs which you can implement as per your requirements.
Conclusion
The integration of PHP script with Google Sheets API facilitates users in updating Google Sheets automatically - be its rows or columns, whatever instructions you provide. This way, an entire sheet can be updated within a few minutes.
You can also use its various features to avoid manual labor and re-organize things with as little efforts as possible.
Our Services
Customer Experience Management
- Content Management
- Marketing Automation
- Mobile Application Development
- Drupal Support and Maintanence
Enterprise Modernization, Platforms & Cloud
- Modernization Strategy
- API Management & Developer Portals
- Hybrid Cloud & Cloud Native Platforms
- Site Reliability Engineering