Search code examples
regexgoogle-sheetsgoogle-sheets-formulagoogle-sheets-queryimportrange

How to do an importrange query with a time duration condition (all rows under 1 minute)

I'm trying to make a query(importrange) of this google-sheet file

I want to filter my data based on 3 conditions:

  1. Col5='GC' OR
  2. Col5='CL' AND(this is the problem I can not solve)
  3. In Col4 the time must be under 60 seconds.

I've tried different solutions (time, seconds, timevalue) but none of them works.

I tried this but it's WITHOUT the last, crucial passage: 

=query(IMPORTRANGE("18OOzibH9rmuzNxOPo_EbZ1rhF32qESuvPa4x4pB1BmA/edit#gid=0",
"data!A1:Q"),
"select Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10, Col11, Col12, Col13, Col14, Col15, Col16, Col17, WHERE (Col5='GC') OR (Col5='CL')"
)

The result I am expecting to see is to have only the rows with GC and CL in Col5 and a duration <= 60 seconds.

Solution 
  • =QUERY(IMPORTRANGE("18OOzibH9rmuzNxOPo_EbZ1rhF32qESuvPa4x4pB1BmA", "data!A1:Q"), 
 "where Col5 matches 'CL|GC' 
    and minute(Col4) < 1", 1)

    0