Javascript Disabled!

Please Enable Javascript if you disabled it, or use another browser we preferred Google Chrome. Please Refresh Page After EnablePowered By UnCopy Plugin.

Source Code How To Reading and Writing Google Sheets Using PHP

Install and Configure the PHP New Client

Let’s install the Google PHP API client:

				
					composer require google/apiclient
				
			
Install and Configure the PHP New Client Code With Code
				
					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);
				
			
New Row Inserted In Google Sheet API
				
					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);
				
			

5 Comments

  1. 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

  2. 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

  3. 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

  4. 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

  5. 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.

Leave a Reply

Your email address will not be published. Required fields are marked *

Ads Blocker Image Powered by Code Help Pro

Ads Blocker Detected!!!

We have detected that you are using extensions to block ads. Please support us by disabling these ads blocker.

Powered By
Best Wordpress Adblock Detecting Plugin | CHP Adblock