Update Google Sheet Data With PHP

Hey, Welcome Innocent Coder recently I just decided to write useful post for programmer so i just started this blog, and in my first post we gonna be working with google sheet API with PHP and also we are going to use this library to make thing easier, Okay so let's get dig into the code..

Requirement

•  PHP (Latest version will be good)
•  Google Sheet API Token
• Library
• Composer

And that's it we are ready to go.

Intialize

First headover to this url  https://developers.google.com/sheets/api/quickstart/php and click on
"Enable The Google Sheets API"


Now create project and this will give download link credentials.json. save it to root of your project folder. and open terminal and install Dependency (if you don't have installed composer get it from getcomposer.org and install it ) now to install required Dependency go to project directory and type following commands in terminal

composer require google/apiclient:^2.0

Now in your project directory there will be composer.json file add this line after google's dependancy

 asimlqt/php-google-spreadsheet-client": "3.0.* 

and now again in terminal type

composer install && update

okay we are good to go ...

Source Code

Okay so first of all create simple class called (Whatever you want) and write this 

This is the Panel constructor:

require 'vendor/autoload.php';
use Google\Spreadsheet\DefaultServiceRequest;
use Google\Spreadsheet\ServiceRequestFactory;

class APIClass {
    function __construct($token){
        
        try{
            $serviceRequest = new DefaultServiceRequest($token);
            ServiceRequestFactory::setInstance($serviceRequest);
        }catch(UnauthorizedException $e){
            echo "Invalid Access Token";
            exit;
        }
        
    }
    function updateNow($sku,$quantity){
        $spreadsheetService = new Google\Spreadsheet\SpreadsheetService();
        $spreadsheetFeed = $spreadsheetService->getSpreadsheetFeed();

        $spreadsheet = $spreadsheetFeed->getByTitle('product');
        $worksheetFeed = $spreadsheet->getWorksheetFeed();
        $worksheet = $worksheetFeed->getByTitle('Sheet2');
        $listFeed = $worksheet->getListFeed();

        $Entries = $listFeed->getEntries();

        $i = 0;
        $update = false;
        foreach ($listFeed->getEntries() as $entry) {
            $values[$i] = $entry->getValues();
            $i++;
        }
        for($k=0;$k<=$i;$k++){
            for($j=0;$j<=16;$j++){
                if($values[$k]['sku'] == $sku){
                    $listEntry = $Entries[$k];
                    $values['quantity'] = $quantity;
                    $listEntry->update($values);
                    $update = true;
                }
            }
        }
        if($update){
            echo "Value Updated";
        }else { echo "No Value Updated"; }
    }
}
function getClient()
    {
        $client = new Google_Client();
        $client->setApplicationName('Google Sheets API PHP Quickstart');
        $client->setScopes(Google_Service_Sheets::SPREADSHEETS);
        $client->setAuthConfig('credentials.json');
        $client->setAccessType('offline');
        $client->setPrompt('select_account consent');

        // Load previously authorized token from a file, if it exists.
        $tokenPath = 'token.json';
        if (file_exists($tokenPath)) {
            $accessToken = json_decode(file_get_contents($tokenPath), true);
            $client->setAccessToken($accessToken);
        }

        // If there is no previous token or it's expired.
        if ($client->isAccessTokenExpired()) {
            // Refresh the token if possible, else fetch a new one.
            if ($client->getRefreshToken()) {
                $client->fetchAccessTokenWithRefreshToken($client->getRefreshToken());
            } else {
                // Request authorization from the user.
                $authUrl = $client->createAuthUrl();
                printf("Open the following link in your browser:\n%s\n", $authUrl);
                print 'Enter verification code: ';
                $authCode = trim(fgets(STDIN));

                // Exchange authorization code for an access token.
                $accessToken = $client->fetchAccessTokenWithAuthCode($authCode);
                $client->setAccessToken($accessToken);

                // Check to see if there was an error.
                if (array_key_exists('error', $accessToken)) {
                    throw new Exception(join(', ', $accessToken));
                }
            }
            // Save the token to a file.
            if (!file_exists(dirname($tokenPath))) {
                mkdir(dirname($tokenPath), 0700, true);
            }
            file_put_contents($tokenPath, json_encode($client->getAccessToken()));
        }
        return $client;
    }

$client = getClient();
$obj = new APIClass($client->getAccessToken()['access_token']);
$obj->updateNow('M542',122);

I Know what you are thinking..Let's understand the code

First of all add namespace to project that we downloaded using composer then add simple class called APIClass with constructor and in it there is DefaultServiceRequest class in which we have to pass our token to access our google spreadshee and set it, super easy right !!

 now google's token expires after 1 hour so we need new token that's where getClient() method comes in part.in which it will search file for token.json and get access_token from at and return it, if token expires it will regenerate by accessing refereshToken and this is what we have to pass in APIClass.

Note : You may not have token.json file. to get that just run quickstart.php php in terminal and first time it will give you URL, open in browser select account and you see hash string copy it and paste in terminal. now you have token.json file 

Now in next we have updateNow method which wil update the row data after searching proper  for value the code is simple just look at below

• getByTitle() pass the name of your google sheet
• then after i get all the worksheet in main sheet so again pass worksheet name which is located in bottom left of google sheets 
• next count available  data with foreach 
• and next step search for particular data in my case i have product list and i want to update 'product quantity' of particular product so i searched for that product and then update quantity 
• at last call update method with new array 

  That's it, If you don't understand write down comment i will be glad to help you...