Write data horizontally on multiple ranges on a Google Sheet Spreadsheet through Google Sheets API PHP Client using a spreadsheets.values.batchUpdate request. Use this method to situate your data to numerous specific column-row combination side by side starting from the left all at the same time in a single loop.
Be sure to arrange your ranges together with your values properly where your data values will match the position of your ranges, and you will be able to position your data to your Google Sheet correctly.
Requirements:
- Create A Google Cloud Project
- Create A Google OAuth Consent Screen
- Create A Google Credential OAuth Client ID
- Enable Google Sheets API Library
- Install The Google Client Library Specifying Google Sheets
- Obtain Google API Access Token Through Command Line Using PHP
- Create A Blank SpreadSheet Using Google Sheets API PHP Client Library
Step 1.
Prepare the parameters.
Get ready with your spreadsheetId.
We will try to write data horizontally to the following range of cells.
- A1-D1
- A5-D5
- A9-D9
We will be using RAW value for the valueInputOption because we just need to write plain text cell data.
We will be including the cells specific row and column coordinates for each cells we inteded to write on to determine if we have put the exact data where we want them to be.
- Hello A1 for Cell A1
- Hello B1 for Cell B1
- Hello C1 for Cell C1
- Hello D1 for Cell D1
- World A5 for Cell A5
- World B5 for Cell B5
- World C5 for Cell C5
- World D5 for Cell D5
- Hello World A9 for Cell A9
- Hello World B9 for Cell B9
- Hello World C9 for Cell C9
- Hello World D9 for Cell D9
<?php
// Autoload Composer.
require __DIR__ . '/vendor/autoload.php';
if (php_sapi_name() != 'cli') {
throw new Exception('This application must be run on the command line.');
}
use Google\Client;
use Google\Service\Sheets\ValueRange;
/**
* Returns an authorized API client.
* @return Client the authorized client object.
*/
function getClient()
{
$client = new Google\Client();
$client->setApplicationName('Google Sheets API PHP Quickstart');
$client->setScopes('https://www.googleapis.com/auth/spreadsheets');
$client->setAuthConfig('credentials.json');
$client->setAccessType('offline');
$client->setPrompt('select_account consent');
// Load previously authorized token from a file, if it exists.
// The file token.json stores the user's access and refresh tokens, and is
// created automatically when the authorization flow completes for the first
// time.
$tokenPath = 'token.json';
if (file_exists($tokenPath)) {
$accessToken = json_decode(file_get_contents($tokenPath), true);
$client->setAccessToken($accessToken);
}
// If there is no previous token or it's expired.
if ($client->isAccessTokenExpired()) {
// Refresh the token if possible, else fetch a new one.
if ($client->getRefreshToken()) {
$client->fetchAccessTokenWithRefreshToken($client->getRefreshToken());
} else {
// Request authorization from the user.
$authUrl = $client->createAuthUrl();
printf("Open the following link in your browser:\n%s\n", $authUrl);
print 'Enter verification code: ';
$authCode = trim(fgets(STDIN));
// Exchange authorization code for an access token.
$accessToken = $client->fetchAccessTokenWithAuthCode($authCode);
$client->setAccessToken($accessToken);
// Check to see if there was an error.
if (array_key_exists('error', $accessToken)) {
throw new Exception(join(', ', $accessToken));
}
}
// Save the token to a file.
if (!file_exists(dirname($tokenPath))) {
mkdir(dirname($tokenPath), 0700, true);
}
file_put_contents($tokenPath, json_encode($client->getAccessToken()));
}
return $client;
}
function updateValues($spreadsheetId, $ranges, $valueInputOption, $values)
{
$client = getClient();
$service = new Google_Service_Sheets($client);
try{
foreach ($ranges AS $k => $range) {
$data[] = new Google_Service_Sheets_ValueRange([
'range' => $range,
'values'=> $values[$k]
]);
}
$body = new Google_Service_Sheets_BatchUpdateValuesRequest([
'valueInputOption' => $valueInputOption,
'data' => $data
]);
$result = $service->spreadsheets_values->batchUpdate($spreadsheetId, $body);
printf("%d cells updated.", $result->getTotalUpdatedCells());
return $result;
}
catch(Exception $e) {
// TODO(developer) - handle error appropriately
echo 'Message: ' .$e->getMessage();
}
}
/**
* Set up parameters.
*/
$spreadsheetId = 'Your spreadsheetId here.';
$valueInputOption = 'USER_ENTERED';
/**
* Set cell values of A1-D1, A5-D5, and A9-D9
*
* Method 1.
*/
$ranges = ['A1', 'B1', 'C1', 'D1', 'A5', 'B5', 'C5', 'D5', 'A9', 'B9', 'C9', 'D9'];
$values = [
[['Hello A1']],
[['Hello B1']],
[['Hello C1']],
[['Hello D1']],
[['World A5']],
[['World B5']],
[['World C5']],
[['World D5']],
[['Hello World A9']],
[['Hello World B9']],
[['Hello World C9']],
[['Hello World D9']]
];
/**
* Set cell values of A1-D1, A5-D5, and A9-D9
*
* Method 2.
* Will give the same result as above.
*/
/*$ranges = [];
$values = [];
foreach (range('A', 'D') as $letter) {
array_push($ranges, $letter . '1');
array_push($ranges, $letter . '5');
array_push($ranges, $letter . '9');
array_push($values, [['Hello ' . $letter . '1']]);
array_push($values, [['World ' . $letter . '5']]);
array_push($values, [['Hello World ' . $letter . '9']]);
}*/
/**
* Attempt to write to a sheet.
*/
updateValues($spreadsheetId, $ranges, $valueInputOption, $values);
write-google-sheets-data-to-a-multiple-range-horizontally.php
Step 3.
Execute the following command to write data to multiple range horizontally in your Google Sheet.
$ php write-google-sheets-data-to-a-multiple-range-horizontally.phpcommand line
The response will be the total number of cells updated if the process was successful.
Done.
Multiple range data has been written across our calculated cells from the command line in PHP using Google Sheets API PHP Client Library.
Result.
Leave a Reply