Search code examples
google-sheetstimestampstring-formattinggoogle-sheets-queryimportrange

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?

Solution

  • 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