Google Sheets date query won't work on specific columns

0
votes

I have data that I'm importing from Salesforce, and I'm using query functions to find all rows where any of the columns has a date in a given range. Here's an example of the data:

enter image description here

The query that's not working is:

=query('Salesforce Data'!A2:C,"SELECT A,C WHERE C >= date '"&TEXT(DATEVALUE($A$1),"yyyy-mm-dd")&"' AND C < date '"&TEXT(DATEVALUE($B$1),"yyyy-mm-dd")&"'")

I'm using the same query except in one case, it's looking at dates in column B, and in the other, it's looking at the dates in column C. The column B version works, the column C version does not. I have verified that there is at least one date in column C that falls in the range, so it should not be an issue of no data, as the error suggests:

error

I've looked over data formatting, and there is no difference between columns B and C in that regard. These are the same types of field in Salesforce as well, so I would not expect a difference in formatting. I tried manually changing the first value in column C to a date (that was an obvious difference between the columns), but that also didn't work.

2
google-sheetsgoogle-sheets-formulaarray-formulasgoogle-sheets-querygoogle-query-language
share a copy of your sheet with example of desired output - player0

2 Answers

0
votes

After a lot of trial and error, I found the issue: it seems that Google Sheets classifies the column of data based on what the majority of the cells are. So, even though both columns B & C have some cells with valid dates and some with a - signifying null, column B has more dates than strings, but C has more strings than dates, so date compare queries won't work on column C at all.

My solution for now is to add a formula sheet to transform all of the null values, -, into a date that won't mess with my query, 1/1/1970: enter image description here

Example formula: =IF( OR('Salesforce Data'!C2="-",'Salesforce Data'!C2=""), date(1970,1,1), 'Salesforce Data'!C2)

Another solution would be to edit the data source, but this solution will work entirely within sheets.

Also note, I dragged this formula down far below where I needed, just in case, make sure that if you have a text column (like my column A), you replace empty values there with junk text of some sort. At first I replaced with 0 and then my text column wasn't picked up by the query.

0
votes

try:

=ARRAYFORMULA(QUERY(TO_TEXT('Salesforce Data'!A2:C), 
 "select Col1,Col3 
  where Col3 >= date '"&TEXT(A1, "yyyy-mm-dd")&"' 
    and Col3 <  date '"&TEXT(B1, "yyyy-mm-dd")&"'", 0))