AKA: Sheets API access with PHP part 3

Continuing my series on using PHP to manipulate Google Spreadsheets, I’ve added a section to my code that reads the XML representation of a spreadsheet and then parses out the rows and columns.  Note that whenever you’re working with list-based feeds, we’re assuming that the first row contains column headings.

Using SimpleXML, it wasn’t difficult to parse out the cell data.  The biggest hurdle was learning about XML namespaces — at first, I thought that the XML had tags like “gd:etag” or “gsx:quantity” and had to learn that gd and gsx were namespaces.  In SimpleXML, several of the methods take a namespace as a parameter.  You can specify the prefix or namespace URL as described in the doc for the children method here.

I think it’s also possible to get the spreadsheet data as json, but I haven’t tried that yet.

My previous Sheets API post demonstrated how to add rows to Google Sheets with PHP.  Some readers were interested in editing or deleting rows.

Google has a very informative reference here that describes the list-based feed for manipulating sheets.  All of my scripts have been using this method, which requires the sheet to have a single header row (I’d been lucky to use that format without knowing the requirement).  I use the example HTTP code from Google’s reference pages to give me guidance for my PHP.

I’ve added a file to the gist demonstrating how to edit a row.  Here’s what you need to know:

1. When you get the sheet list feed (Section 3 of the first file in the gist), you need to add a header (“GData-Version” => “3.0”) to the GET request so that the Etag is returned for each line.
2. Once you have a list of entries and find the one that you want to edit, you need to grab its Etag, Id, and the edit URL (see lines 1, 2, and 9 in the image above, and click on the image to see the whole entry).
3. Now you just need to issue a PUT request to the edit URL and include the XML with the Etag, Id, and updated sheet data.

Hope this helps a few people!

People who use my Insert and Delete Cell Add-on for Google Sheets have been reporting this error.  I was unable to recreate the error until someone shared a spreadsheet with me and I found the culprit: merged cells.

What’s going on?

When you insert cells and shift the existing contents down, my Add-on selects the range of cells from your selection to the bottom of the sheet and moves it down.  But if there are merged cells below your selection, the new range doesn’t make sense and an error is generated.  The same thing happens if you try to delete and shift up, or if you try to shift left or right with merged cells to the right of your selection.

Why not catch the error and generate a friendly error message?

I’ve been trying, but this error isn’t triggering the try – catch section of my code.  There also isn’t a way to detect merged cells without trying some strange tricks that would take a lot more time than I have.  I’ve opened a bug report with Google about this.

This error seems to be Google’s version of “We’re not sure what happened, and hope that if the customer tries again it might go away”.

Note on 8 February 2017: Google has released the Sheets API v4 and is now providing PHP sample code and improved PHP support for Sheets in the PHP API client.  My series of articles is now largely obsolete.  See https://developers.google.com/sheets/api/quickstart/php

==========================

Google has been sending out messages saying that several of their APIs are going to be discontinued (for example, the Document List API).  As a result, I tried to modify my custom Google Spreadsheet-populating PHP script to use the new Drive API.  After way too much wasted time, I discovered that the Drive API can create sheets and read their metadata, but can’t add rows — for that we can keep using the Spreadsheets API.

I was successful at converting the authentication portion of my script from the old ClientLogin to OAuth with a Service Account (the script runs behind the scenes of a web site and populates a Google spreadsheet that the web site user has no knowledge of).

It wasn’t as simple as I’d hoped because the Google-provided PHP client for the Drive API doesn’t know about the Spreadsheets API.  I had to dig around the PHP libraries to piece together authentication codes and Google_Http_Requests.  Then Google changed the PHP client to use Guzzle, so I recently made that change.

So, if anyone else out there is interested in the combination of PHP, the Spreadsheets API, and OAuth with Service Accounts, this could save you some time.

The code is available in a public Gist at https://gist.github.com/karlkranich/de225928665dc6b83667

Here are the step-by-step instructions to get this to work:

Continue reading →