Need help updating a google spreadsheet cell using Zend gdata with PHP. I keep getting an error with the updateCell function

2
votes

I have been trying to update a cell in a google spreadsheet. I'm using ZendFramework-1.12.6 and PHP Version 5.5.9. The response I get using the code is:

ERROR: Expected response code 200, got 400 The spreadsheet at this URL could not be found. Make sure that you have the right URL and that the owner of the spreadsheet hasn't deleted it.

And if I remove the error trapping I get this message:

Fatal error: Uncaught exception 'Zend_Gdata_App_HttpException' with message 'Expected response code 200, got 400 Missing resource version ID' in /volume1/web/Zend/Gdata/App.php:717 Stack trace: #0 /volume1/web/Zend/Gdata/App.php(933): Zend_Gdata_App->performHttpRequest('PUT', '', Array, 'put(Object(Zend_Gdata_Spreadsheets_CellEntry), NULL, NULL, NULL, Array) #2 /volume1/web/Zend/Gdata/App/Entry.php(209): Zend_Gdata_App->updateEntry(Object(Zend_Gdata_Spreadsheets_CellEntry), NULL, NULL, Array) #3 /volume1/web/Zend/Gdata/Spreadsheets.php(307): Zend_Gdata_App_Entry->save() #4 /volume1/web/gss.php(19): Zend_Gdata_Spreadsheets->updateCell('1', '1', 'test', '1zoCs23pKzQXPtB...', 'od6') #5 {main} thrown in /volume1/web/Zend/Gdata/App.php on line 717

It seems like everything works great until I try writing the string 'test' to the spreadsheet with the updateCell call. Can anyone help?

<?php
// load Zend Gdata libraries
require_once 'Zend/Loader.php';
Zend_Loader::loadClass('Zend_Gdata_Spreadsheets');
Zend_Loader::loadClass('Zend_Gdata_ClientLogin');
// set credentials for ClientLogin authentication
$user = "[email protected]";
$pass = "mypassword";
// connect to API
$service = Zend_Gdata_Spreadsheets::AUTH_SERVICE_NAME;
$client = Zend_Gdata_ClientLogin::getHttpClient($user, $pass, $service);
$service = new Zend_Gdata_Spreadsheets($client);
// set target spreadsheet and worksheet
$ssKey = '1zoCs23pKaQXPtB2rT5Rn6KVzJalIoR5VYBnlURJRRLs';
$wsKey = 'od6';
$service->updateCell('1', '1', 'test', $ssKey, $wsKey);   
?>
2
phpzend-frameworkgoogle-sheets

2 Answers

1
votes

After several hours of troubleshooting this same problem, I eventually figured out that this is a bug or a missing feature with the "new" Google Spreadsheets.

See the comments on this post: Updating cell in Google Spreadsheets returns error "Missing resource version ID" / "The remote server returned an error: (400) Bad Request."

Information on the "new" Google Sheets: https://support.google.com/drive/answer/3541068

On that page is a link that allows you to create a new spreadsheet using the "old" Sheets: https://g.co/oldsheets

My solution was to create a new "old" sheet. I'm now able to use updateCell with no problems!

0
votes

I just found your question and with some additional information I found a really easy solution so I thought I'd share that too.

The problem seems to be, that there needs to be an Etag set for the skript to perform correctly. This Etag is 

array('If-Match'=> '*')

To add this as a header to every UpdateCell request go into the source file in the library path: 

library/Zend/Gdata/Spreadsheets.php

and change the UpdateCell method definition from

    $entry = $this->getCellEntry($query);
    $entry->setCell(new Zend_Gdata_Spreadsheets_Extension_Cell(null, $row, $col, $inputValue));
    $response = $entry->save();

to

    $entry = $this->getCellEntry($query);
    $entry->setCell(new Zend_Gdata_Spreadsheets_Extension_Cell(null, $row, $col, $inputValue));
    $response = $entry->save(null,null,array('If-Match'=> '*'));

Requests are now working for me as previously.