doulao3905 2019-03-13 15:03
浏览 144

无效的JSON Payload Google表格API附加字段

So what I am trying to do is get the values of a range of cells on a google sheet.

I have successfully managed to do that.

Then with the array of values I make another API request to our CRM system that too returns me a set of results. The set of results that are returned to me from our CRM system I want to update the cells in a different range from where I read the values. It's all on the same google sheet.

However I seem to encounter an error when attempting to append the fields.

"Invalid JSON payload received. Unknown name \"0\" at 'data.values[0]'

I am running a foreach loop on the array I created just simply printing out the values I need so I know it is not an empty array and in fact does contain the data I am after. So what is it I am missing?

This is the code I am working with so far.

require __DIR__ . '/vendor/autoload.php';

if (php_sapi_name() != 'cli') {
    throw new Exception('This application must be run on the command line.');

 * Returns an authorized API client.
 * @return Google_Client the authorized client object
function getClient()
 $client = new Google_Client();
 $client->setApplicationName('Google Sheets API PHP Quickstart');
$client->setPrompt('select_account consent');

// Load previously authorized token from a file, if it exists.
// The file token.json stores the user's access and refresh tokens, and is
// created automatically when the authorization flow completes for the first
// time.
$tokenPath = 'token.json';
if (file_exists($tokenPath)) {
    $accessToken = json_decode(file_get_contents($tokenPath), true);

// If there is no previous token or it's expired.
if ($client->isAccessTokenExpired()) {
    // Refresh the token if possible, else fetch a new one.
    if ($client->getRefreshToken()) {
    } else {
        // Request authorization from the user.
        $authUrl = $client->createAuthUrl();
        printf("Open the following link in your browser:
", $authUrl);
        print 'Enter verification code: ';
        $authCode = trim(fgets(STDIN));

        // Exchange authorization code for an access token.
        $accessToken = $client->fetchAccessTokenWithAuthCode($authCode);

        // Check to see if there was an error.
        if (array_key_exists('error', $accessToken)) {
            throw new Exception(join(', ', $accessToken));
    // Save the token to a file.
    if (!file_exists(dirname($tokenPath))) {
        mkdir(dirname($tokenPath), 0700, true);
    file_put_contents($tokenPath, json_encode($client->getAccessToken()));
     return $client;
// Get the API client and construct the service object.
$client = getClient();
$service = new Google_Service_Sheets($client);
$spreadsheetId = '1UOfOdjGTWXir4pGNKtb7MRJSnFlJvOqr_CBZtkQUGxA';
$response = $service->spreadsheets_values->get($spreadsheetId, $range);
$values = $response->getValues();

if (empty($values)) {
print "No data found.
} else {
foreach ($values as $row) {
    //creating XML to pass to FLG
    $xmldata = '<?xml version="1.0" encoding="ISO-8859-1"?>
    //using curl to send XML data to FLG via API            
    $ch = curl_init();
    curl_setopt($ch, CURLOPT_URL, "" );
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
    curl_setopt($ch, CURLOPT_POST, 1 );
    curl_setopt($ch, CURLOPT_POSTFIELDS, $xmldata); 
    curl_setopt($ch, CURLOPT_HTTPHEADER,     array('Content-Type: text/plain')); 
    $returnedresult=curl_exec ($ch);
    $xmlresult = simplexml_load_string($returnedresult);
    $companyBalance[] = $xmlresult->balance;
foreach ($companyBalance as $row) {
    echo $row . "
$spreadsheetId = '1UOfOdjGTWXir4pGNKtb7MRJSnFlJvOqr_CBZtkQUGxA';
$body = new Google_Service_Sheets_ValueRange([
'values' => $companyBalance
$result = $service->spreadsheets_values->append($spreadsheetId, $range, 
    printf("%d cells appended.", $result->getUpdates()->getUpdatedCells());

So basically it is just appending the cells that doesn't seem to be working. Is it the format in which I am passing the array across? Is it the fact I haven't defined it to be a JSON array? Does it require a specific format I just don't know and I can't seem to find the right answer in the documentation as it just seems you can pass it an array when appending multiple values.

Thanks in advance!

  • 写回答

1条回答 默认 最新

  • doushi8599 2019-03-14 10:51

    So in the end it was a couple of things.

    Firstly I was trying to pass and XML object and convert it into a PHP array. Sheets API didn't like this so to get around that I had to create the array like this. Not the prettiest but it works

    $companyBalance[] = sprintf("%s",$xmlresult->balance);

    This allowed me to create an array that was made up of strings.

    Then because I was building this array dynamically I then used a for loop to access each part of the array and update the cells individually. Again not the most elegant solution but I just don't have time to go back and change it right this second. However this might help someone who needs to be pointed in the right direction.

    for ($x = 0; $x <= 36; $x++) {
        $y = $x + 2;
        $values = [[$companyBalance[$x]]];
        $options = array('valueInputOption' => 'RAW');
        $body   = new Google_Service_Sheets_ValueRange(['values' => $values]);
        $result = $service->spreadsheets_values->update('SHEET_ID', 'COMPANY FORMULAS - MARKETING!D'.$y.':D'.$y.'', $body, $options);
        print($result->updatedRange. PHP_EOL);
    本回答被题主选为最佳回答 , 对您是否有帮助呢?



  • ¥20 cad图纸,chx-3六轴码垛机器人
  • ¥15 移动摄像头专网需要解vlan
  • ¥15 对于这个问题的算法代码
  • ¥20 access多表提取相同字段数据并合并
  • ¥20 基于MSP430f5529的MPU6050驱动,求出欧拉角
  • ¥20 Java-Oj-桌布的计算
  • ¥15 powerbuilder中的datawindow数据整合到新的DataWindow
  • ¥20 有人知道这种图怎么画吗?
  • ¥15 pyqt6如何引用qrc文件加载里面的的资源
  • ¥15 安卓JNI项目使用lua上的问题