I have this table (A3:H16) in google sheets like in the photo above and I want to filter this whole table by 2 dates (from and to) and these 2 dates should be entered by user in F2 and G2 (in yellow background). Whenever "from" and "to" dates in F2 and G2 are changed, the table should be filtered accordingly. Can you please explain how I can do it using Apps Script?
The google sheet file is here: https://docs.google.com/spreadsheets/d/17NsFH90yQCeXy6K5YAKAErHxUZBzROXAGeV_-KBJYCU/edit?usp=sharing It has dummy data
Thank you in advance!
CodePudding user response：
In a separate sheet, where you inform the date, you use it, adapting to your reality:
=iferror(QUERY(Sheet1!A3:H,"select * where todate(F) >= date '"&TEXT(F2,"yyyy-mm-dd")&"' and todate(F) <= date '"&TEXT(G2,"yyyy-mm-dd")&"'",0),"No record found")
QUERY is a very powerful GSheets function.
In it, you can convert columns' data to Date Format, so it can be compared e.g todate()
Then, you make sure that the cell referenced is in the date format as well, by doing the following:
date and forcing the cell format with '"&TEXT(A1,"yyyy-mm-dd")&"'
Reference: