CODEWITHSUNDEEP
google-sheetstimestampstring-formattinggoogle-sheets-queryimportrange
Pat Ryder
Pat Ryder

Reputation: 15

filtering an importrange with a query in google sheets (timestamp troubles)

I have a dashboard that is pulling audit data from another sheet that has entries coming in continuously over the week. This gets to be 15k+ entries and is really slowing things down. I want to import the audit data to my dashboard, but only the audits from today. Here is what I have so far 

=QUERY(IMPORTRANGE(URL, "audit_data!A1:f"), "Select Col1, Col2, Col3, Col4, Col5, Col6 where Col1 = date '"&TEXT(G1,"yyyy-mm-dd")&"'",0)

the cell G1 that is referenced is 

=text(today(), "yyyy-mm-dd")

which I realize may be redundant. This keeps returning with an empty output and I think it's because the audit data I am importing has the timestamp in 2/25/2020 18:37:50. any way around that?

Upvotes: 1

Views: 657

Answers (1)

player0
player0

Reputation: 1

try:

=ARRAYFORMULA(QUERY({IMPORTRANGE(A1, "audit_data!A1:F"), 
      IFNA(DATEVALUE(IMPORTRANGE(A1, "audit_data!A1:A")))}, 
 "select Col1,Col2,Col3,Col4,Col5,Col6 
  where Col7 = "&DATEVALUE(G1), 0))

0

or:

=QUERY(IMPORTRANGE(A1, "audit_data!A1:F"), 
 "select Col1,Col2,Col3,Col4,Col5,Col6 
  where Col1 >= date '"&TEXT(G1,   "yyyy-mm-dd")&"'
    and Col1 <= date '"&TEXT(G1+1, "yyyy-mm-dd")&"'", 0)

0

Upvotes: 3

Related Questions