1

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? https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values

enter image description here

// 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

BlueDogRanch
  • 721
  • 1
  • 16
  • 43

2 Answers2

1

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:

Tanaike
  • 181,128
  • 11
  • 97
  • 165
  • Thank you! Both methods work well. I can see the difference between the two. – BlueDogRanch Dec 10 '21 at 00:23
  • @BlueDogRanch Thank you for replying and testing them. Both patterns use one API call. So please use one of them for your actual situation. – Tanaike Dec 10 '21 at 00:24
0

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:

Reading and Writing Values

CMB
  • 4,950
  • 1
  • 4
  • 16