CODEWITHSUNDEEP
functiongoogle-sheetsgoogle-sheets-apigoogle-query-language
Andrew Gonzales
Andrew Gonzales

Reputation: 15

How to query a range from a date value in Google Sheets

I'm trying to query the full range of Assignment Names that correspond to a particular "Proffer Date", in this case 4/17/2019 in this sheet:

enter image description here

and import them into this sheet:

enter image description here

so that ideally I get the Assignment names in E2:E5 (Big Switch - Chris, Big Switch - Patrick, Verafin, etc.)

To do this I've been trying an API query: 

=query(importrange("https://docs.google.com/spreadsheets/d/1JUF_ptZQ- 
Xi01jCUpCtQPJECydJlwF8ixHAcrWbFyZo/edit#gid=1823646744","Assignment 
Structure (by client)!H:N"),"select Col1 where Col3 = '4/17/2019'" ,1)

in cell F2, but it's only returning the first row, i.e. "Assignment Name".

How can I adjust this query to return the full range of Assignment names that have proffer dates of 4/17/2019?

Thanks in advance! Apologies for the imgur links but this is my first question and I don't have any reputation ;)

Upvotes: 0

Views: 599

Answers (1)

Tedinoz
Tedinoz

Reputation: 7959

When using query and a date is used for comparison "Use the keyword date followed by a string literal in the format yyyy-MM-dd." Per Query Language documentation.

Your query should look like this:

`=query(importrange("https://docs.google.com/spreadsheets/d/1JUF_ptZQ-Xi01jCUpCtQPJECydJlwF8ixHAcrWbFyZo/edit#gid=1823646744","Assignment Structure (by client)!H:N"),"Select Col1 where Col7 = date '2019-4-17'" ,1)`

NB: Ensure that the entries in Column N (Proffer Date) are formatted as dates (check with isdate()) and are formatted as dates.

Upvotes: 1

Related Questions