Install and Configure the PHP New Client
Let’s install the Google PHP API client:
composer require google/apiclient
Configure the Google Sheet API New Client In PHP
You need to include autoload.php to automatically load the required classes.
// Don`t Forgot to Include autoload.php
//Initialize Google Client
$client = new Google_Client();
//Set Application Name
$client->setApplicationName('Google Sheets');
//Set Scopes
$client->setScopes([Google_Service_Sheets::SPREADSHEETS]);
$client->setAccessType('offline');
// credentials.json is the key file we downloaded while setting up our Google Sheets API And Making Google Service Account.
//Set Authentication Configuration
$path = 'inc/credentials.json';
$client->setAuthConfig($path);
Congratulations, You have successfully setup Google Client now Let's Set Up Service
// configure the Google Service Sheets Account.
$service = new \Google_Service_Sheets($client);
// the spreadsheet id can be found in the url https://docs.google.com/spreadsheets/d/15kVW4pOpn9FWOotL_V9eobgbR_un8GC1m1EhKU6mnuY/edit#gid=0
$spreadsheetId = '15kVW4pOpn9FWOotL_V9eobgbR_un8GC1m1EhKU6mnuY';
$spreadsheet = $service->spreadsheets->get($spreadsheetId);
print_r($spreadsheet);
You Will Get This Result Like
Google\Service\Sheets\Spreadsheet Object ( [internal_gapi_mappings:protected] => Array ( ) [modelData:protected] => Array ( ) [processed:protected] => Array ( ) [collection_key:protected] => sheets [dataSourceSchedules] => [dataSourceSchedulesType:protected] => Google\Service\Sheets\DataSourceRefreshSchedule [dataSourceSchedulesDataType:protected] => array [dataSources] => [dataSourcesType:protected] => Google\Service\Sheets\DataSource [dataSourcesDataType:protected] => array [developerMetadata] => [developerMetadataType:protected] => Google\Service\Sheets\DeveloperMetadata [developerMetadataDataType:protected] => array [namedRanges] => [namedRangesType:protected] => Google\Service\Sheets\NamedRange [namedRangesDataType:protected] => array [properties] => Google\Service\Sheets\SpreadsheetProperties Object ( [internal_gapi_mappings:protected] => Array ( ) [modelData:protected] => Array ( ) [processed:protected] => Array ( ) [autoRecalc] => ON_CHANGE [defaultFormat] => Google\Service\Sheets\CellFormat Object ( [internal_gapi_mappings:protected] => Array ( ) [modelData:protected] => Array ( ) [processed:protected] => Array ( ) => [backgroundColorStyle] => Google\Service\Sheets\ColorStyle Object ( [internal_gapi_mappings:protected] => Array ( ) [modelData:protected] => Array ( ) [processed:protected] => Array ( ) [rgbColor] => Google\Service\Sheets\Color Object ( [internal_gapi_mappings:protected] [textDirection] => [textFormat] => [collection_key:protected] => themeColors [primaryFontFamily] => ........
// get all the rows of a given sheet name
$range = 'Sheet1 or sheet2'; // here we use the name of the Sheet to get all the rows of particular rows
$response = $service->spreadsheets_values->get($spreadsheetId, $range);
$values = $response->getValues();
print_r($values);
This is Result in
Array ( [0] => Array ( [0] => name [1] => email [2] => mobile ) [1] => Array ( [0] => Amit Gupta [1] => amit@gmail.com [2] => 8238928323 ) [2] => Array ( [0] => Suman Jha [1] => suman@gmail.com [2] => 6545454554 ) [3] => Array ( [0] => John [1] => john@gmail.com [2] => 8238928323 ) )
Fetch a Few Rows by Using a Range
We read the ten first lines of our Google Sheets:
// we define here the expected range, columns from A to C and lines from 1 to 3
$range = 'Sheet1!A1:C3';
$response = $service->spreadsheets_values->get($spreadsheetId, $range);
$values = $response->getValues();
print_r($values);
Fetch Only Cells of a Given Column
// We read the cells of a given column to avoid fetching everything:
$range = 'Sheet1!B1:B3'; // the column containing the movie title
$response = $service->spreadsheets_values->get($spreadsheetId, $range);
$values = $response->getValues();
print_r($values);
Convert Rows into JSON Objects
//It's usually simpler to work with each row as a separate item. Now let's create an associative array for each row.
// Fetch the rows
$range = 'Sheet1';
$response = $service->spreadsheets_values->get($spreadsheetId, $range);
$rows = $response->getValues();
// Remove the first one
$headers = array_shift($rows);
// Add the headers with each following row
$array = [];
foreach ($rows as $row) {
$array[] = array_combine($headers, $row);
}
print_r($array);
Array ( [0] => Array ( [name] => CodeWithCode [email] => info@codewithcode.com [mobile] => 6267465901 ) )
Append a New Row(s)
//We write a new row at the end of the sheet:
$newRow = [
'Amit Gupta',
'amit@gmail.com',
'8238928323'
];
$rows = [$newRow]; // you can append several rows at once
$valueRange = new \Google_Service_Sheets_ValueRange();
$valueRange->setValues($rows);
$range = 'Sheet1'; // the service will detect the last row of this sheet
$options = ['valueInputOption' => 'USER_ENTERED'];
$service->spreadsheets_values->append($spreadsheetId, $range, $valueRange, $options);
Update/Modify an Existing Row
//We replace an existing row by new values for its cells:
$updateRow = [
'Amit Gupta',
'amit@gmail.com',
'8238928323'
];
$rows = [$updateRow];
$valueRange = new \Google_Service_Sheets_ValueRange();
$valueRange->setValues($rows);
$range = 'Sheet1!A3'; // where the replacement will start, here, first column and second line
$options = ['valueInputOption' => 'USER_ENTERED'];
$service->spreadsheets_values->update($spreadsheetId, $range, $valueRange, $options);
You can also delete desired row.
//We delete some rows by specifying a range of cells to clear:
$range = 'Sheet1!A2:C3'; // the range to clear, the 23th and 24th lines
$clear = new \Google_Service_Sheets_ClearValuesRequest();
$service->spreadsheets_values->clear($spreadsheetId, $range, $clear);
Hi my family member I want to say that this post is awesome nice written and come with approximately all significant infos I would like to peer extra posts like this
Wow wonderful blog layout How long have you been blogging for you make blogging look easy The overall look of your site is great as well as the content
I simply could not go away your web site prior to suggesting that I really enjoyed the standard info a person supply on your guests Is going to be back incessantly to investigate crosscheck new posts
Hi i think that i saw you visited my web site thus i came to Return the favore I am attempting to find things to improve my web siteI suppose its ok to use some of your ideas
Although I enjoy your website, you should proofread a few of your pieces. Many of them have serious spelling errors, which makes it difficult for me to convey the truth. Nevertheless, I will definitely return.