I will explain how to manage the Google sheet using a PHP API Client with code examples.

First, we need to create the Google project and configure the sheet’s API

Configure the new Google Console project to enable sheet API. In the Google Cloud console create a new project. Open the APIs and services and enable the Sheets API Service. Once services are enabled then we need to configure the services. Now we need to create the credentials. Need to create the services account.

Note: It is recommended that you save the generated email for later use.

And edit the service account to create a new key using the JSON type. Last download the key file and rename it to credentials.json.

How to create and share the Google sheet

Create the google sheet, Under the Google apps click on the sheets and create the new spreadsheet named sheet1.

To update the sheets, we must share this document with the previously generated email address for the service account.

Then install the Google PHP API Client with help of composer.

composer require google/apiclient

Using credentials.json, the client is configured.

// configure the Google Client
$client = new \Google_Client();
$client->setApplicationName('Google Sheets API');
$client->setScopes([\Google_Service_Sheets::SPREADSHEETS]);
$client->setAccessType('offline');
// credentials.json is the key file we downloaded while setting up our Google Sheets API
$path = 'data/credentials.json';
$client->setAuthConfig($path);

// configure the Sheets Service
$service = new \Google_Service_Sheets($client);

// the spreadsheet id can be found in the url https://docs.google.com/spreadsheets/d/143xVs9lPopFSF4eJQWloDYAndMor/edit
$spreadsheetId = '143xVs9lPopFSF4eJQWloDYAndMor';
$spreadsheet = $service->spreadsheets->get($spreadsheetId);
var_dump($spreadsheet);

How to Add the API data into the Google Sheets with PHP. [Code in PHP]

Here I am sharing the code in PHP to add data to Google Sheets. First I fetched the data from the API and added it to the sheet. I am only adding the data if the employee data does not exist in the sheet. I append the new row to the Google sheet. I am not adding the same users to the sheet if they are already present in the sheet.

<?php
ini_set('max_execution_time', 0);
// Import the Google Sheets API library.
require_once __DIR__ . '/vendor/autoload.php';
// Create a new Google Sheets client.
$client = new \Google_Client();
$client->setApplicationName('trackier');
$client->setScopes([\Google_Service_Sheets::SPREADSHEETS]);
$client->setAccessType('offline');
$client->setAuthConfig(__DIR__ . '/credentials.json');
$service = new Google_Service_Sheets($client);
$spreadsheetId = "1ag_kjkvK_0CfRf1FjQxRYjYefkXXXXXXXX"; //It is present in your URL

// Get the sheet name.
$sheetName = "sheet1";
// Get the column name.
$range = 'sheet1!A2:C';
function get_all_employees(){
  $curl = curl_init();
curl_setopt_array($curl, array(
  CURLOPT_URL => "https://dummy.restapiexample.com/api/v1/employees",
  CURLOPT_RETURNTRANSFER => true,
  CURLOPT_ENCODING => '',
  CURLOPT_FOLLOWLOCATION => true,
  CURLOPT_HTTP_VERSION => CURL_HTTP_VERSION_1_1,
  CURLOPT_CUSTOMREQUEST => 'GET',
));
$response = curl_exec($curl);
curl_close($curl);
$decode_response = json_decode($response,true);
foreach($decode_response['data'] as $sub_result){
  $id = $sub_result['id'];
  $employee_name = trim($sub_result['employee_name']);
  $employee_salary = $sub_result['employee_salary'];
  $append_data[] =  array($id,$employee_name,$employee_salary);
}
return $append_data;
}
  //call Employee data//
$result = get_all_employees();
foreach($result as  $vl){ 
  $exists = false;
                  $get_id = $vl[0];
                  $get_employee_name = $vl[1];
                  $get_employee_salary = $vl[2];
$data = $service->spreadsheets_values->get($spreadsheetId,$range);
// Check if the data exists in the range.
for ($i = 0; $i < count($data['values']); $i++) {
  if ($data['values'][$i][0] == $vl[0]) {
    $exists = true;
    break;
  }
}
if(empty($exists)){
  $updateBody = new \Google_Service_Sheets_ValueRange([
    'range' => 'sheet1',
    'majorDimension' => 'ROWS',
    'values' => [[$get_status,$get_id,$get_employee_name,$get_employee_salary]]
  ]);
   $end_result =  $service->spreadsheets_values->append(
    $spreadsheetId,
    'sheet1',
    $updateBody,
    ['valueInputOption' => 'USER_ENTERED']
  );
  echo'added';
}
}
?>

How to update the Google sheet Data – Column wise Dynamically with PHP. [Code in PHP]

Example: If you have 50 number of employees in the Google sheet, all have unique IDs. Employee ID in column A, Employee name in column B, Salary in column C, Employee status in column D. And you want to update the employee salary, and status automatically.

Here I will explain to you how you can easily update employee status and salary column according to the employee ID with PHP. How you can write the script that updates the employee data in the Google sheet according to the employee ID?

Below is the code that updates the Google Sheet employee data according to the employee ID.

<?php
require __DIR__ . "/vendor/autoload.php";
//Reading data from spreadsheet.

$client = new \Google_Client();
$client->setApplicationName("sheet1");
$client->setScopes([\Google_Service_Sheets::SPREADSHEETS]);
$client->setAccessType("offline");
$client->setAuthConfig(__DIR__ . "/credentials.json");
$service = new Google_Service_Sheets($client);
$spreadsheetId = " "; //It is present in your URL
//$get_range = "trackier! A2: B2";
$update_range = "sheet1";
$get_range = "sheet1!B2:B";

function get_employee_data($employee_id)
{
//create database connection//
    $append_data = [];
    $sql =
        "SELECT employee_id, employee_name, employee_status FROM employees where employee_id =" .
        $employee_id;
    $result = $conn->query($sql);
    if ($result->num_rows > 0) {
        // output data of each row
        while ($row = $result->fetch_assoc()) {
            $employee_id = $row["employee_id"];
            $employee_name = $row["employee_name"];
            $employee_status = $row["employee_status"];
            $append_data[$id] = [
                $employee_id,
                $employee_name,
                $employee_status,
            ];
        }
    }
    return $append_data;
}
$sheet_range = "sheet1!A2:A";
$sheet_data = $service->spreadsheets_values->get($spreadsheetId, $sheet_range);
foreach ($sheet_data["values"] as $key => $vl) {
    $ii = $key;
    $result = get_employee_data($vl[0]);
    $get_employee_id = $result[$vl[0]][0];
    $get_employee_name = $result[$vl[0]][1];
    $get_employee_status = $result[$vl[0]][2];

    if ($vl[0] == $result[$vl[0]][0]) {
        $update_range = "sheet1!A:C$ii";
        $updateBody1 = new \Google_Service_Sheets_ValueRange([
            "range" => $update_range,
            "majorDimension" => "ROWS",
            "values" => [
                [$get_employee_id, $get_employee_name, $get_employee_status],
            ],
        ]);
        $end_result2 = $service->spreadsheets_values->update(
            $spreadsheetId,
            $update_range,
            $updateBody1,
            ["valueInputOption" => "RAW"]
        );
        echo "<br>";
        echo "updated";
    }
}
?>

Google Sheet Helpful Formula

Here I am sharing some formulas which is used in the Google sheet.

• How to update one Google sheet column data to a second Google sheet column in real-time.
• Example: Let’s assume you are the class teacher. You want to manage the student data into multiple Google sheet data subjects Like you maintain every subject data of the student in the Google sheet. And you have one main Google sheet in which you store all student data.
• If you update any student data into the main Google sheet it will update on all other Google sheets according to the student ID

=IFERROR(ARRAYFORMULA(VLOOKUP(B,IMPORTRANGE("https://docs.google.com/spreadsheets/d/{spreadsheet id get from the url}","B:P"),{column id},FALSE)),0)

B is the column range that you will compare with other Google sheet column range.

https://docs.google.com/spreadsheets/d/{spreadsheet id get from the URL. This is the URL from where you will get data.

Paste this formula into the Google Sheets column cell where you want to copy the data.