Integrating Google Sheets With PHP is This Easy- Know How!

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.

1. Visit https://console.developers.google.com
2. Create a new project
   
3. Tap on Enable APIs And Services
4. Search for Google Sheets API
   
5. Enable it
   
6. Now tap on Enable Credentials
   
7. Use these settings 
   
8. Click on - what credentials do I need?
9. Now use these settings
   
10. A file will be downloaded after clicking continue to rename it to credentials.json

1. 1.  Run this command in your working directory to access google sheets API.

      composer require google/apiclient:^2.0

   2. Copy the credentials file which was downloaded after step 1 and place it inside the working directory also rename it to  credentials.json.
   3. Get client_email by using the following command
      

      cat credentials.json

   4. Share your Google Sheet to the provided email address by step 3.
   5. Create a text file with .php extension, this will contain all the required code.

How did I implement it?

1. Reading the data of your google sheet
2. 1. First, we will load the libraries which the composer installed to access Google API services.
      

      require __DIR__ . '/vendor/autoload.php';

   2. 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);

   3.  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.

       

   4. Fetching data from your spreadsheet and storing it.

       //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

1. 1. Set your update range like earlier
      

      $update_range = "Your Sheet Name! Range”; 

   2. Store your values in an array of arrays.
      

      $values = [[$value1, $value2]];

   3. Creating a request.

      $body = new Google_Service_Sheets_ValueRange([
      
            'values' => $values
      
          ]);
      
          $params = [
      
            'valueInputOption' => 'RAW'
      
          ];

   4. Calling update service.
      
      

      $update_sheet = $service->spreadsheets_values->update($spreadsheetId, $update_range, $body, $params);

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.