3

My goal is to retrieve a range of rows from a Google Worksheet using the sq parameter in a standard cURL call--the API's protocol method. I know that the Google Sheets documentation explains the sq parameter by way of example. In that documentation example, it uses column header names with comparison operators, but is there a way to retrieve a range of rows specified by a start and end row index using the sq parameter?

I am aware of the fact that one could use Google's Visualization API to do something like this, but it seems like the Google Sheet's URL I am using for retrieving the data would need to be "reconstructed" to match that needed by the Visualization API. I would need to extract the spreadsheet "key" and then rebuild the URL as per documentation (scroll to the bottom). As a developer, I'm a bit weary of doing any string manipulation to formulate a URL so I can simply pull a range of rows from a Google Sheet.

What is the best practice for retrieving a range of rows without having to resort to cell-based API feeds?

EDIT: I checked out Google's Query Language reference (version 0.7) and found that using offset together with limit could grab a range of rows with an SQL statement. However, when I tried to use a query with these, it does not work with the sq parameter in the url. I tried URL encoding the value of the sq parameter as well to be sure, but no luck. It seems like the sq parameter value can only reference the column headers from a list based feed. Does anybody know if that is the case? In my cURL call, here are some URLs I tried (the cURL requests are authenticated):

https://spreadsheets.google.com/feeds/list/{key}/od6/private/full?sq=limit+3+offset+3 //returns Parse error: null

https://spreadsheets.google.com/feeds/list/{key}/od6/private/full?sq=limit%203%20offset%203 //URL encoded value for sq returns Parse error: null

https://spreadsheets.google.com/feeds/list/{key}/od6/private/full?sq="limit 3 offset 3 //Returns HTML marked up error with Error 400 (Bad Request)!! as <title>

What can we use with the sq parameter? Is it documented anywhere, Google?

Cœur
  • 37,241
  • 25
  • 195
  • 267
ariestav
  • 2,799
  • 4
  • 28
  • 56

1 Answers1

3

Alright, so after some more exploration, and having read David Bullock's answer on this SO thread, I headed over to Google's old Protocol Reference documentation--for API vesion 2.0. I took a read through the Queries section and am happy to report that the start-index and max-results parameters still work for the list-based feed URLs even for the newest API version.

So, assuming you have an href to a specific worksheet that has 100 rows, for example, you can use something like this to retrieve row 50 through 67:

https://spreadsheets.google.com/feeds/list/{key}/od6/private/full?start-index=50&max-results=17

And while that is satisfactory for now, it would be good to know from Google if those params will ever be deprecated. I assume they will be deprecated since there is no reference to them from the current API documentation. Would be great to hear directly from Google, but for now I'll just use these classic params.

Community
  • 1
  • 1
ariestav
  • 2,799
  • 4
  • 28
  • 56
  • Given how pushy Google are about telling you to use the newer docs, it's really frustrating when they are incomplete. I spent ages looking for how to paginate sheets feeds, so your answer solved my problem, thanks! – Andrew Jan 03 '16 at 17:08