CODEWITHSUNDEEP
phpgoogle-sheets-api
ScottG
ScottG

Reputation: 674

I get a 'Error in Google Request' (400) error using spreadsheet API for some rows, but not for most

I have a webpage that posts requests to update a Google spreadsheet cell to a php script. It is using the asimlqt/php-google-spreadsheet-client/ PHP wrapper for the Google Spreadsheet API. Most of the time, it works fine. For a few cells, though, it gives me an 'Error in Google Request' error. I investigated and found that the http_code being returned is 400.

I can't figure out what could be different about the cells where update doesn't work. They hold free-form text entered through a Google form, but the cells that have problems are not the ones with the longest text, and there aren't any strange characters. There are more than 100 rows, and I've only found a few where I get this problem, and so far I've only seen the problem with cells in one column, so I'm pretty sure the credentials are ok.

The error comes from the function:

function updateCell($row, $col, $val) {
    global $cellFeed;
    try {
        $cellFeed->editCell($row+2, $col+1, $val);
    } catch (Exception $e) {
        output('ERROR (updateCell): ' . $e->getMessage());
        return false;
    }
    return true;
}

and the $cellFeed comes from

$serviceRequest = new Google\Spreadsheet\DefaultServiceRequest($accessToken);
Google\Spreadsheet\ServiceRequestFactory::setInstance($serviceRequest);
$spreadsheetService = new Google\Spreadsheet\SpreadsheetService();
$spreadsheet = $spreadsheetService->getSpreadsheetById($SPREADSHEET_ID);
$worksheetFeed = $spreadsheet->getWorksheets();
$worksheet = $worksheetFeed->getByTitle($WORKSHEET_NAME);
$cellFeed = $worksheet->getCellFeed();

Upvotes: 1

Views: 2075

Answers (1)

Andres
Andres

Reputation: 11

I have had the same error, when there were double quotes in the cell value. It was fixed by replacing them by &qout;.

It happens becuse of this code in CellFeed.php:

public function editCell($rowNum, $colNum, $value)
{
    $entry = sprintf('
        <entry xmlns="http://www.w3.org/2005/Atom" xmlns:gs="http://schemas.google.com/spreadsheets/2006">
          <gs:cell row="%u" col="%u" inputValue="%s"/>
        </entry>',
        $rowNum,
        $colNum,
        $value
    );

    ServiceRequestFactory::getInstance()->post($this->getPostUrl(), $entry);
}

As you can see, there's no transformation of $value there, so when there are double quotes XML tag become corrupted like:

<gs:cell row="%u" col="%u" inputValue="my value with "quotes" in it."/>

Upvotes: 1

Related Questions