range response have difference with range request when last range have empty value

user_12235731 0
Viewed 31

i try get data from google sheet using method spreadsheets.values.get.

sheet tabel

1 A B C
2 One Two Three
3 One Two
4 One Two Three

the problem is when making a request like : 'range' => 'A2:C', return value data on range 3 only give values ['one', 'two'] not ['one', 'two', ''].

Any solution or references?

google-sheets-api php
1 Answers

Unfortunately, in the current stage, when the values are retrieved with spreadsheets.values.get, your situation is obtained as the default situation. In your sample Spreadsheet, it becomes [["One","Two","Three"],["One","Two"],["One","Two","Three"]]. So in this case, it is required to prepare a script for achieving your goal. In this answer, I would like to propose the script.

Sample script:

// Use spreadsheets.values.get. Please replace this for your actual script.
$res = $service->spreadsheets_values->get($spreadsheetId, $range);

// I added below script.
$values = $res['values'];
$maxLen = max(array_map(function ($v) { return count($v); }, $values));
foreach($values as $i => $v) {
    $len = count($v);
    if ($len < $maxLen) {
        $values[$i] = array_merge($v, array_fill(0, $maxLen - $len, ''));
    }
}

  • In this modification, "" is added for the row when the length of the row is less than the maximum length of the array.

  • When your sample values are used, in this modification, $values retrieved by $service->spreadsheets_values->get($spreadsheetId, $range), it's [["One","Two","Three"],["One","Two"],["One","Two","Three"]]. And, when my additional script is used, $values becomes [["One","Two","Three"],["One","Two",""],["One","Two","Three"]].

Reference:

user_71086530
Related