<?php
/*

https://www.nidup.io/blog/manipulate-google-sheets-in-php-with-api

*/

require './vendor/autoload.php'; 

// 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 = '***'; //google console admin need to generate this file
$client->setAuthConfig($path);

// configure the Sheets Service
$service = new \Google_Service_Sheets($client);
$spreadsheetId = '***'; // get with share with and see to url



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


// all values
$range = 'List 1'; // here we use the name of the Sheet to get all the rows
$response = $service->spreadsheets_values->get($spreadsheetId, $range);
$values = $response->getValues();
var_dump($values);



// rows by range
$range = 'List 1!A1:F10';
$response = $service->spreadsheets_values->get($spreadsheetId, $range);
$values = $response->getValues();
var_dump($values);



// cells of column
$range = 'List1!B1:B21'; // the column containing the movie title
$response = $service->spreadsheets_values->get($spreadsheetId, $range);
$values = $response->getValues();
var_dump($values);



// rows into json objects
$range = 'Sheet1';
$response = $service->spreadsheets_values->get($spreadsheetId, $range);
$rows = $response->getValues();
// Remove the first one that contains headers
$headers = array_shift($rows);
// Combine the headers with each following row
$array = [];
foreach ($rows as $row) {
    $array[] = array_combine($headers, $row);
}
var_dump($array);
/*
$jsonString = json_encode($array, JSON_PRETTY_PRINT);
print($jsonString);

*/


// append new row
$newRow = [
    '456740',
    'Hellboy',
    'https://image.tmdb.org/t/p/w500/bk8LyaMqUtaQ9hUShuvFznQYQKR.jpg',
    "Hellboy comes to England, where he must defeat Nimue, Merlin's consort and the Blood Queen. But their battle will bring about the end of the world, a fate he desperately tries to turn away.",
    '1554944400',
    'Fantasy, Action'
];
$rows = [$newRow]; // you can append several rows at once
$valueRange = new \Google_Service_Sheets_ValueRange();
$valueRange->setValues($rows);
$range = 'List 1'; // the service will detect the last row of this sheet
$options = ['valueInputOption' => 'USER_ENTERED'];
$service->spreadsheets_values->append($spreadsheetId, $range, $valueRange, $options);



// update existing
$updateRow = [
    '456740',
    'Hellboy Updated Row',
    'https://image.tmdb.org/t/p/w500/bk8LyaMqUtaQ9hUShuvFznQYQKR.jpg',
    "Hellboy comes to England, where he must defeat Nimue, Merlin's consort and the Blood Queen. But their battle will bring about the end of the world, a fate he desperately tries to turn away.",
    '1554944400',
    'Fantasy, Action'
];
$rows = [$updateRow];
$valueRange = new \Google_Service_Sheets_ValueRange();
$valueRange->setValues($rows);
$range = 'Sheet1!A2'; // where the replacement will start, here, first column and second line
$options = ['valueInputOption' => 'USER_ENTERED'];
$service->spreadsheets_values->update($spreadsheetId, $range, $valueRange, $options);



// delete some row
$range = 'Sheet1!A23:F24'; // the range to clear, the 23th and 24th lines
$clear = new \Google_Service_Sheets_ClearValuesRequest();
$service->spreadsheets_values->clear($spreadsheetId, $range, $clear);


/** with text formatting */
$range = 'List 1!B4';
$params = [
    'ranges' => $range,
    'fields' => 'sheets.data.rowData.values',
    'includeGridData' => true,
];

$response = $service->spreadsheets->get($spreadsheetId, $params);
$data = $response->getSheets()[0]['data'][0]['rowData'][0]['values'][0];

$formattedValue = $data['formattedValue'];
$hyperlink = $data['hyperlink'];
$userEnteredFormat = $data['userEnteredFormat'];

var_dump($formattedValue);
var_dump($hyperlink);
var_dump($userEnteredFormat);