In this Google Sheet using the Sheets API, what's a simple example of excluding columns B and C from either retrieval from the Sheet during the request, or display via PHP?
Can columns B and C be excluded from the $service request?
Or, can columns B and C be excluded from display in the foreach loop?
Use R1C1 notation? Or majorDimension? 
// Usual suspects
require_once __DIR__ . '/vendor/autoload.php';
$googleAccountKeyFilePath = __DIR__ . '/service_key.json';
putenv('GOOGLE_APPLICATION_CREDENTIALS=' . $googleAccountKeyFilePath);
$client = new Google_Client();
$client->useApplicationDefaultCredentials();
$client->addScope('https://www.googleapis.com/auth/spreadsheets');
$service = new Google_Service_Sheets($client);
$spreadsheetId = '';
$response = $service->spreadsheets->get($spreadsheetId);
$spreadsheetProperties = $response->getProperties();
//The $range where columns could be excluded?
$range = 'Sheet1!A2:I';
$response = $service->spreadsheets_values->get($spreadsheetId, $range);
$values = $response->getValues();
//The foreach loop where columns could be excluded?
foreach($values as $val) {
$valuespan = "<span>".implode('</span> <span>', $val)."</span>";
echo $valuespan;
}
This is the output right now:
Joe Null blah blah Smith 123 Dogpatch Lane Dog Town Alabama 34567
John Blah Jones 456 Cat Hollow Cat Town Arkansas 12345
Joe Not blah Johns 99 Cow Hollow Cow Town Arkansas 12345
Hoe can I exclude Columns B and C so I get:
Joe Smith 123 Dogpatch Lane Dog Town Alabama 34567
John Jones 456 Cat Hollow Cat Town Arkansas 12345
Joe Johns 99 Cow Hollow Cow Town Arkansas 12345
CodePudding user response：
I believe your goal is as follows.
- You want to retrieve the values from Spreadsheet excluding the columns "B" and "C".
- You want to achieve this using googleapis for PHP.
- You have already been able to get values from Spreadsheet using Sheets API.
In this case, how about the following patterns?
Pattern 1:
In this pattern, the method of "spreadsheets.values.get" is used. At first, the values are retrieved from "Sheet1!A2:I", and the result values can be retrieved by transposing.
From:
$range = 'Sheet1!A2:I';
$response = $service->spreadsheets_values->get($spreadsheetId, $range);
$values = $response->getValues();
To:
$range = 'Sheet1!A2:I';
$response = $service->spreadsheets_values->get($spreadsheetId, $range);
$transpose = array_map(null, ...$response->getValues());
array_splice($transpose, 1, 2);
$values = array_map(null, ...$transpose);
Pattern 2:
In this pattern, the method of "spreadsheets.values.batchGet" is used. At first, the values are retrieved from "Sheet1!A2:A" and "Sheet1!D2:I", and retrieved values are merged as an array.
From:
$range = 'Sheet1!A2:I';
$response = $service->spreadsheets_values->get($spreadsheetId, $range);
$values = $response->getValues();
To:
$response = $service->spreadsheets_values->batchGet($spreadsheetId, array('ranges' => ['Sheet1!A2:A', 'Sheet1!D2:I']));
$values = [];
foreach($response['valueRanges'][0]['values'] as $i => $v) {
array_push($values, array_merge($v, $response['valueRanges'][1]['values'][$i]));
}
// In this case, you can retrieve the result values as "$values".
References:
CodePudding user response：
As per documentation, spreadsheets.get requests are not able to exclude defined ranges from the parameters, only include them.
Instead, you can use a spreadsheets.batchGet request so multiple ranges can be entered as parameters:
Parameters:
ranges[] string
The A1 notation or R1C1 notation of the range to retrieve values from.
Sample:
$ranges = array('Sheet1!A2:A','Sheet1!D2:I');
$response = $service->spreadsheets_values->batchGet($spreadsheetId, $ranges);
The response is this object, in which you can iterate through the ValueRange array, for example valueRange[i].values to get the actual cell values.
{
"spreadsheetId": string,
"valueRanges": [
{
object (ValueRange)
}
]
}
Additional Reference: