Get a range of rows from Google Spreadsheet using list APIs and "sq" parameter
My goal is to get a series of strings from Google Worksheet using the sq parameter in a standard cURL call - API protocol method. I know the Google Sheets documentation explains the parameter sq
as an example . In this documentation example, it uses column header names with comparison operators, but is there a way to get a series of rows specified by the starting and ending row index using the sq parameter?
I am aware that it is possible to use the Google Visualization API to do something like this, but it looks like the Google Sheet URL that I am using to retrieve the data needs to be "rebuilt" to match the one the visualization API requires ... I would need to extract the "table" of the spreadsheet and then rebuild the URL as per the documentation (scroll down) . As a developer, I'm a little tired of any string manipulation to formulate a URL, so I can just output a series of strings from a Google Sheet.
What is the best practice for fetching a series of rows without resorting to cellular API channels?
EDIT: I checked the google query language link (version 0.7) and found that using offset
together with limit
can grab a range of rows using a SQL statement . However, when I tried to use query with them, it fails with the parameter sq
in the url. I tried url-encoding the parameter value sq
just to be sure, but no luck. It looks like the parameter value sq
can only refer to column headings from a list-based feed. Does anyone know if this is the case? In my cURL call, here are a few urls I tried (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 parameter sq
? Is it documented somewhere, Google?
source to share
Ok, so after doing some research and reading David Bulcock's answer on this SO thread , I went to Google the old protocol reference documentation - for API vesion 2.0. I've read the Requests section and I'm happy to report that parameters start-index
and max-results
still work for a list of URLs to URLs even for the newest version of the API.
So, if you have an href for a specific sheet that has 100 rows, for example, you can use something like this to extract rows 50 through 67:
https://spreadsheets.google.com/feeds/list/{key}/od6/private/full?start-index=50&max-results=17
While that is satisfactory, it would be helpful to ask Google if these options will never be out of date. I assume they will be deprecated as there are no references to them from the current API documentation. It would be great to hear directly from Google, but for now I'm just using these classic options.
source to share