If you’re still using my “Insert and Delete Cells” Add-on for Google Sheets, you’re now seeing: “401. That’s an error”, “Error: deleted_client”, and “The OAuth client was deleted”.

I shut down the “Insert and Delete Cells” Google Sheets Add-on yesterday because the functionality has been built into Google Sheets for a few years now.

You will find the Delete functions in the Edit menu and the Insert functions in the Insert menu.

Sorry for the abrupt shut-down of the Add-on.  I was trying to figure out a graceful way to remove the Add-on from the store, and instead made an irrevocable shut down choice.

I’m glad the Add-on was useful to people while it lasted!

The Google Spreadsheets API allows you to send simple queries to retrieve subsets of the rows in your sheet - it’s as easy as appending the query to the end of the URL.

For example, if I have a column called “Quantity” and want rows with Quantity greater than 9, I would append ?sq=quantity>9 to the end of the URL and issue the GET request (don’t forget that column names are always converted to lowercase and spaces are removed).

The other method is to add the sq query parameter to the Guzzle $httpClient.  I’ve illustrated this in my example code.

See this page for more info.

Here’s how to get up to speed with the Google Sheets cell-based feed and PHP.

My previous posts have illustrated the use of Google’s PHP API client to interact with Google Sheets using list-based feeds.  This method is ideal for reading from and writing to spreadsheets that have a single header row, but isn’t useful for more complicated sheets.

The cell-based feed allows individual cells to be read and written.  I’ve written some example PHP code that implements the cell-based feed methods to help get you up to speed a little quicker.  Google documentation is here.

To start, follow the steps in this post to set up your service account, share a spreadsheet, and install the API client.  Then you should be able to adapt my example code to interact with your spreadsheet.

Please let me know if you have any comments or questions!

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.