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.

About your request body, I think that it is required to modify it. But, unfortunately, I cannot understand about your goal from your question and your script. By this, I cannot propose the modified script. I apologize for this. Can I ask you about the detail of your goal? — Tanaike
@Tanaike Yeah, basically I'm grabbing data from a database to input into a Google sheet. That part of the code isn't shown here, but $line1 is the array with the data I got from the DB. With the request body I want to do 2 things: append the data to a new row and split the newly appended data in the F column Into separate columns since it's one long line with data separated by commas and I need some data from there so doing so is the best option — UzZzi
Thank you for replying. I have to apologize for my poor English skill. Unfortunately, I couldn't image your goal from your replying. But I would like to try to understand it. When I could correctly understand it, I would like to think of the solution. I deeply apologize I cannot resolve your issue soon. — Tanaike
Hi there @UzZzi! That error declares that the JSON doesn't fulfill the required format. To discard a misconfigured JSON, could you please test a valid known JSON and see if it generates the error? — Jacques-Guzel Heron
Hey @Jacques-GuzelHeron So after changing my code a bit to start simple and find the problem my goal is to just append new data. Even so it still gives me the same issues so the payload problem is my data not being in the proper format. I edited my question along with the code and added more info as to what the data that is being sent in the request looks like. Hopefully it helps to identify the problem. Thanks for your help — UzZzi

UzZzi UzZzi · Accepted Answer · 2021-05-27T18:59:46

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);

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

1 Answers