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

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

Submit guest post

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

So, this solution encompasses the integration of 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 Sheets 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-

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
    Text written in white background
  3. Tap on Enable APIs And Services
    Text written in white background
  4. Search for Google Sheets API
    Text written in white and grey background
  5. Enable it
    Text written in grey background
  6. Now Tap on Enable Credentials
    Text written in white background
  7. Use these settings 
    Text written in white background
  8. Click on What credentials do I need?
  9. Now use these settings
    Text written in white background
  10. A file will be downloaded after clicking continue to rename it to credentials.json
    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

 

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

 

  PHP file_name.php

 

Note: You need to run this command in the directory where your PHP file is present.

What are the 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. A brief can be found here

There are a bunch of other features also provided by Google APIs which you can implement as per your requirements.

Here is a documentation link by Google where you can find various other features as well as different codes for different languages.

 

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.

Discussion

Write to us

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms Of Service apply. By submitting this form, you agree to our Privacy Policy.

See how our uniquely collaborative work style, can help you redesign your business.

Contact us