1

I am collecting data from a sheet using PHP. I am losing information because if there is any empty cell, Google removes the whole row.

$array refers to some data collected from Google Sheets:

$range = "Sheet1!A1:C99";
$response = $service->spreadsheets_values->get($spreadsheetId, $range);
$array = $response->getValues();

But some cells in the sheet are empty, and if any of those are in the left or right border of the row, Google will remove all the data in that row. [2] => [3].

There is a question similar to this one, however, it is not a PHP example and I cannot apply it here.

riqui
  • 13
  • 6
  • What method are you using to get $array? Please see [how-to-ask-a-good-question](https://stackoverflow.com/help/how-to-ask) – Aerials Sep 16 '20 at 08:13
  • Done. Sorry @Aerials – riqui Sep 16 '20 at 09:40
  • It would work for me to add a space character in any empty cell where its row already has data, but I don't know if that is feasible. – riqui Sep 16 '20 at 13:02

2 Answers2

0

The Range.getValues() method will return a 2D array of values. You could look at it as a matrix more than an array, because all the rows will have the same number of "column" elements (Even if they are empty in the sheet). So the culprit is somewhere else XD.

With that in mind, try something like the following:

$keys = array_shift($array); // Extract & exclude header row from $array
/*
Combine keys to each row in $array */
foreach ($array as &$row){
  $row = array_combine($keys,$row);
};
print_r($array);

Update:

As stated in Resource: ValueRange's values[] field: "For output, empty trailing rows and columns will not be included." So you really can't count on using that method for the purpose you are looking for.

As an alternative, you may want to use Apps Script, and the Range.getValues() method I mentioned above.

Aerials
  • 4,231
  • 1
  • 16
  • 20
  • Thank you. It returns exactly the same. Anyway, the main point is that Google is removing everything in a row, even cells that had data inside, just because there is one empty cell in that row. – riqui Sep 16 '20 at 12:21
0

I paid someone to fix this and this is what he did:

    $range = "Sheet1!A2:C99";
$response = $service->spreadsheets_values->get($spreadsheetId, $range);
$values = $response->getValues();


$response = [];

foreach ($values as $value) {

    $data = [
        'animal' => null,
        'descripcion' => null,
        'peso' => null
    ];

    if (isset($value[0])) {
        $data['animal'] = $value[0];
    }

    if (isset($value[1])) {
        $data['descripcion'] = $value[1];
    }

    if (isset($value[2])) {
        $data['peso'] = $value[2];
    }

    $response[] = $data;
}

echo '<pre>';
print_r($response);
echo '</pre>';

It did work!

Thank you

riqui
  • 13
  • 6