Google Sheets API (""Invalid JSON payload received. Unknown name \"\": Root element must be a message."")

Question

Trying to add data to a Google Sheet via the Google Sheets API and the BatchUpdate method, but I get this error (don't know what it refers to)

> PHP Fatal error:  Uncaught Google\Service\Exception: {   "error": {
>     "code": 400,
>     "message": "Invalid JSON payload received. Unknown name \"\": Root element must be a message.",
>     "errors": [
>       {
>         "message": "Invalid JSON payload received. Unknown name \"\": Root element must be a message.",
>         "reason": "invalid"
>       }
>     ],
>     "status": "INVALID_ARGUMENT"   } }

Here's my new code:

 $spreadsheetId = 'myspreadsheetid';
 $client = new Google_Client();
 $client->setAuthConfig('mycredntials.json');
 $client->setApplicationName('Sheet Automation');
 $client->setScopes(Google_Service_Sheets::SPREADSHEETS);
 $service = new Google_Service_Sheets($client);
 $range = 'A2:L';
 $valueInputOption = 'USER_ENTERED';
 $body = new Google_Service_Sheets_ValueRange([
            'values' => $row1
    ]);
 $params = [
      'valueInputOption' => $valueInputOption
    ];
 $result = $service->spreadsheets_values->append($spreadsheetId, $range, $body, $params);

EDIT:

The data I get from the database is put into an array ($row1) that when echoed looks like this:

(
    [cdr_id] => myid
    [calldate] => 2021-05-27
    [src] => mysource
    [accountcode] => myaccountcode
    [userfield] => disposition
    [ivr_std_txn_full] =>
)

I then grab that info and use implode to put it all in one line (this may be the issue)

echo $line1 = implode(',', $row1)

I tried setting the values to be appended to both $row1 and $line1, but still get the payload issue.

Answer 1

The solution was pretty simple once I finally figured it out. If you won't be putting the values in manually and instead have an array filled with data you just need to reference each key in the array instead of just the array.

The request body ended up looking like this:

 $body = new Google_Service_Sheets_ValueRange([
                //'values' => $row1
                'values' =>
                [
                        [
                                $row1['cdr_id'], $row1['calldate'], $row1['src'], $row1['accountcode'], $row1['userfield'], $row1['ivr_std_txn_full']
                        ]
                ]
        ]);
        $params = [
          'valueInputOption' => $valueInputOption
        ];
        $result = $service->spreadsheets_values->append($spreadsheetId, $range, $body, $params);

Answer 2

in my condition, its because some data have null value, so make sure data you want send not null, you can change it to empty string or other.

example :

[ivr_std_txn_full] => null

change to :

[ivr_std_txn_full] => ''