How to read 2 dimensional array data using Google Sheets API?

Question

i'm new to Google Sheets API, currently i am develop a system to send data to google sheets. And while im trying to send the data, it shows error like: Invalid values [0][0]

Below is my code line.

`

     $client = getClientAuth();

        $spreadsheet_ID = $this->spreadsheet->spreadsheetId; 
        
        $range = "Sheet1";

        $values = $this->sheetData;

        $this->service = new Sheets($client);


        $body = new ValueRange([
            'majorDimension' => 'ROWS',
            'values' => [array_map(function($nv) {
                return (is_null($nv)) ? "" : $nv;
              },$values)]
        ]);

        $params = ['valueInputOption' => 'RAW'];

        $insert = ['insertDataOption' => 'INSERT_ROWS'];

        //executing the request
        $data = $this->service->spreadsheets_values->append($spreadsheet_ID, $range,
        $body, $params, $insert);
    
        return $data;

`

Tanaike · Accepted Answer · 2022-11-14 06:55:14Z

2

From your error message and your showing script, I thought that in your situation, $values might be a 2-dimensional array. I think that if $values is a 1-dimensional array, your script works. And also, please include 'insertDataOption' => 'INSERT_ROWS' in $params. So, in this case, how about the following modification?

From:

$body = new ValueRange([
    'majorDimension' => 'ROWS',
    'values' => [array_map(function($nv) {
        return (is_null($nv)) ? "" : $nv;
      },$values)]
]);

$params = ['valueInputOption' => 'RAW'];

$insert = ['insertDataOption' => 'INSERT_ROWS'];

//executing the request
$data = $this->service->spreadsheets_values->append($spreadsheet_ID, $range,
$body, $params, $insert);

To:

$body = new ValueRange([
    'majorDimension' => 'ROWS',
    'values' => array_map(function($row) {
      return array_map(function($col) {
        return (is_null($col)) ? "" : $col;
      }, $row);
    }, $values)
]);
$params = ['valueInputOption' => 'RAW', 'insertDataOption' => 'INSERT_ROWS'];
$data = $service->spreadsheets_values->append($spreadsheet_ID, $range, $body, $params);

or

$body = new Google_Service_Sheets_ValueRange([
    'majorDimension' => 'ROWS',
    'values' => array_map(function($row) {
      return array_map(function($col) {
        return (is_null($col)) ? "" : $col;
      }, $row);
    }, $values)
]);
$params = ['valueInputOption' => 'RAW', 'insertDataOption' => 'INSERT_ROWS'];
$data = $service->spreadsheets_values->append($spreadsheet_ID, $range, $body, $params);

Note:

When the arrow function is used, I think that the following modification can be used.

From

'values' => array_map(function($row) {
  return array_map(function($col) {
    return (is_null($col)) ? "" : $col;
  }, $row);
}, $values)

To

'values' => array_map(fn($row) => array_map(fn($col) => (is_null($col)) ? "" : $col, $row), $values)

edited Nov 14, 2022 at 6:55

answered Nov 14, 2022 at 5:08

Tanaike

204k12 gold badges126 silver badges222 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

benzi Over a year ago

Hi @Tanaike , after i try your code lines, it shows this error: "message": "Invalid JSON payload received. Unknown name \"9\" at 'data.values[2]': Cannot find field.\nInvalid JSON payload received. Unknown name \"10\" at 'data.values[2]': Cannot find field.\nInvalid JSON payload received. Unknown name \"11\" at 'data.values[2]': Cannot find field.\nInvalid JSON payload received. Unknown name \"13\" at 'data.values[2]': Cannot find field.\nInvalid JSON payload received. Unknown name \"14\" at 'data.values[2]'

Tanaike Over a year ago

@benzi Thank you for replying. I apologize for the inconvenience. In this case, I think that it is required to confirm your $values. Can you provide the value of $values? By this, I would like to confirm it.

benzi Over a year ago

Yes sure Mr @Tanaike. i will put it on the question section.

Tanaike Over a year ago

@benzi Thank you for replying. In this case, can you provide the value of $values as text?

benzi Over a year ago

Okay @tanaike, Here is the value of $values : array ( 0 => array ( 0 => 'HQ', 1 => 44837.0, 2 => 'SI', 3 => 'SI 00006', 4 => '0001', 5 => '0', 6 => 'Twitter', 7 => 'GS002', 8 => 'Gum Strawberry'), )

|

Collectives™ on Stack Overflow

How to read 2 dimensional array data using Google Sheets API?

1 Answer 1

From:

To:

Note:

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

From:

To:

Note:

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related