Code Samples: google-sheet-api

Create New Project on the Google Cloud Platform 

  The first step to creating a new project to be able to access Google’s resources is to go to the Google Cloud Console
https://console.cloud.google.com/ and New Project as below: 

Fill in the basic details that are asked on the form you get when you click New Project and submit it and you should be done.
  
After create navigtate to project, like - https://console.cloud.google.com/welcome?project=project-spreadsheet-xxx

  In our case, since we plan to use the Google Sheets API, 
we will have to first enable the said API. 
To do that, check below screenshot click Enabled APIs & Services and then, from the top, click ENABLE APIS AND SERVICES.
Or directly go to https://console.cloud.google.com/apis/library?project=project-spreadsheet-xxx to enable APIs you need.
  
  Find  Google Sheets API and enable like below:
  
Once enabled, we now have to create a credentials. For our case, we want to create a Service Account.

Here, make sure you choose Owner as your role and then finally move ahead to create the service account.

After the successful creation of the service account, you will be redirected to the list of credentials. 
On that page, by clicking the Edit icons as shown below, you will be redirected to another page from where we can download the credentials as a JSON file. 

Just follow along. Also, don’t forget to copy the email id that should be listed below the Service Accounts section.
  
  Use this link https://console.cloud.google.com/iam-admin/settings to delete any existing project.

  Use this link https://console.cloud.google.com/cloud-resource-manager for all of your projects.

Install the Google API Client Package
In order to interact with a google sheet using Composer, we will need to first bring in the Google API Client package. 
Let’s do that by running the following command:

  composer require google/apiclient

ont forget to copy the email id that should be listed below the Service Accounts section. 
We will need this email id. So, go to your Google Cloud Console and from under Service Accounts, copy this email id.

Now, let’s create a new Google sheet by going to docs.google.com and create a new spreadsheet. 
Then click the Share button on the top right of the newly created Google sheet and paste your service account email id here, 
to share the sheet with this email id (which is essentially your service account).
like below 

Below is full PHP script to get Google Sheet data and update accordingly:

<?php

use Google\Client;
use Google\Service\Drive;
use Google\Service\Sheets;
use Google\Service\Sheets\ValueRange;

set_time_limit(60 * 60 * 12);
ini_set("memory_limit", "-1");

ini_set('display_errors', 1);
error_reporting(E_ALL);
ini_set('display_startup_errors', 1);

$client = new Client();
$client->setAuthConfig('creds.json');
$client->setScopes(['https://www.googleapis.com/auth/spreadsheets']);
$client->useApplicationDefaultCredentials();
$service = new Sheets($client);

$spreadsheetId = "1AN34n84blDoH_xxxxxxxxxx_faP6S5pjO5xaGdk7t77qPAgQY";

$ranges = 'Sheet1!A1:C20';
$params = array(
    'ranges' => $ranges, 'majorDimension' => 'ROWS'
);
//execute the request
$result = $service->spreadsheets_values->batchGet($spreadsheetId, $params);
if (!$result) {
    return [
        'error' => true,
        'message' => 'missing row data'
    ];
}
$values = $result->getValueRanges();
echo "<pre>";
print_r($values);
echo "</pre>";

$updateRow = [];
$updateRow[] = ["Name1", "Id1", "Group1-".time()];
$valueRange = new ValueRange();
$valueRange->setValues($updateRow);
$range = 'Sheet1!A2:C'.(count($updateRow) + 1); // where the replacement will start, here, first column and second line
$options = ['valueInputOption' => 'USER_ENTERED'];
$service->spreadsheets_values->update($spreadsheetId, $range, $valueRange, $options);

Respective Google Sheet is as below after update:
Pages

Thursday, July 4, 2024

How To Read and Update Data From Google Sheets Using PHP
