1

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();
Improve this question
4
  • Does the particular column that you mentioned, fall out of range by any chance? I see that you are doing, $col+1 when you're calling editCell so I am curious whether the range might be undefined. Commented Apr 11, 2015 at 1:36
  • No, the column index is incremented by 1 because the Google Spreadsheet API uses a 1-based number scheme and the Visualization API (which I use to display the data) is 0-based. Also, the visualization counts the header row, while the Spreadsheet API does not, so the row gets incremented by 2. Updating that column in other rows works fine; it's just a few rows where I get this error. Commented Apr 13, 2015 at 1:30
  • Works fine for me. Could it be a data volume problem? I use the batch update. Also I do have an auto retry setup as sometimes it fails. Commented Apr 20, 2015 at 3:29
  • It only ever updates one cell at a time, so I don't think it is an issue with data volume. So far I've only found it in this one column, and there are other similar columns that update fine (as do most of the rows, even for this column). Sometimes the text in the cell can be pretty long, but there are cells with more text that update fine. Having a retry is a good idea, but so far it seems that for rows that fail, it always fails. Commented Apr 22, 2015 at 15:32

1 Answer 1

Reset to default
1

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."/>
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Good answer. Doesn't fix my problem, as it occurs whether or not there are any quote marks, but that could be an issue so I am adopting your fix. Thanks
Well, there can be not only quotes, but some other html characters that should be converted to codes. Do you have examples of "problem strings"?

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.