Search code examples
google-sheetsgoogle-sheets-formulagoogle-sheets-querygoogle-query-languageimportrange

Using Query & Importrange to filter between 2 dates and if condition to change the value of the dates based on condition

I am using query and importrange function to collect data from another Google sheet, I am filtering by the Publisher and the user-specified Start Date (F1) and End Date (G1). What I have right now works just for the start date, except that I haven't been able to filter it between the two dates. So I would like to keep all of the data that has fallen within the start date or the end date. Start date is Col9 and End Date is Col10 in the source data

The next step for this would be to change the start date and end date if it is less than or greater than the user-specified dates. For example, if the start date based on the importrange function is July 15th, but the user-specified date is August 1st then I want the cell with that date to be August 1st. The same logic applies to the end date, except any end date greater than the user-specified date should be changed to August 31st in this case. Is including this aspect in a single formula possible? Or would I have to create a separate query/script?

Below is a link to a copy of the formula I'm working with: https://docs.google.com/spreadsheets/d/1hdE2q8UDdStx98WH9de3PljiMZzasZzhBChUAxLPgGQ/edit#gid=28163102

Below is the source data: https://docs.google.com/spreadsheets/d/1N8m_YtO1Nc5AXSCAvT5Z1RiKs30bwjt6XDk9zA5QSOE/edit?usp=sharing

If I remove " and Col10 <= date '"&text(G1,"yyy-mm-dd")&"' " from the code, it works with the start date. 

=QUERY(importrange("https://docs.google.com/spreadsheets/d/1N8m_YtO1Nc5AXSCAvT5Z1RiKs30bwjt6XDk9zA5QSOE/edit#gid=0","Activity Tracker - Publisher Co!B10:K"),"Select Col6, Col1, Col2, Col4, Col9, Col10 where Col2 is not null and Col9 is not null and Col1 contains 'Publisher 1' and (Col9 >= date '"&text(F1,"yyy-mm-dd")&"' and Col10 <= date '"&text(G1,"yyy-mm-dd")&"')")
Solution

  • try:

    =QUERY(IMPORTRANGE(
 "1N8m_YtO1Nc5AXSCAvT5Z1RiKs30bwjt6XDk9zA5QSOE", 
 "Activity Tracker - Publisher Co!B10:K"),
 "select Col6,Col1,Col2,Col4,Col9,Col10 
  where Col2 is not null 
    and Col9 is not null 
    and Col1 contains 'Publisher 1' 
    and Col9  <= date '"&TEXT(G1, "yyyy-mm-dd")&"' 
    and Col10 >= date '"&TEXT(F1, "yyyy-mm-dd")&"'", 0)

    0