Sheets API access with PHP part 5: Querying rows

query-rowsThe 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.

 

7 thoughts on “Sheets API access with PHP part 5: Querying rows

  1. Greg Turner

    Thanks Karl. I’ve worked with query parameters for ages, but have never come across structured query parameters before. I have been scouring the internet for more information on them, and specially in relation to the Guzzle Client, because now I am finding that Guzzle throws an exception when the value of the query is more than one word, as in ?sq=county=Marin+County. None of the examples I am finding on the net include something like this. I think I will need my client introduce an additional column into his spreadsheet containing either a single word or number that will serve as the unique key for the Guzzle Client to look up the row that it needs.

  2. Greg Turner

    Karl,
    Thanks for asking. Yes I tried that and still get 400 Bad Request` response: Invalid query parameter value for sq.
    Greg

  3. karl.kranich Post author

    Greg,
    I was able to get this going by taking the format of your original tries and using a combination of single and double quotes:
    $resp = $httpClient->request($method, $url, [‘query’ => [‘sq’ => ‘gear=”mifi device”‘]]);

  4. karl.kranich Post author

    Piter,
    Using my example code, where one of the columns is called “Color”, I could do a query like this: color=””

    It returns rows where the cells in column “Color” are empty.

    Karl

Leave a Reply

Your email address will not be published. Required fields are marked *