I need assistance with a Google Sheets task. I’m dealing with a workbook that contains several sheets, and I aim to gather certain data from each sheet. My objective is to locate rows that start with the date 9/1/2022 across all my worksheets. When I identify this date, I wish to collect all the values in the second column that follow it until the next instance of 9/1/2022 appears. Each sheet corresponds to a different user, displaying dates in the first column and related values in the second column.
Ultimately, I want to merge all these values from every user into a single master sheet that updates automatically. Is there a built-in function available that can facilitate this type of data extraction across multiple sheets? I’ve been having difficulty determining the best method.
FILTER + INDIRECT works better than QUERY here. I pulled monthly data from 15 project sheets and FILTER with date ranges was way more straightforward. Try something like =FILTER(INDIRECT("Sheet1!B:B"), (INDIRECT("Sheet1!A:A") >= DATE(2022,9,1)) * (INDIRECT("Sheet1!A:A") < DATE(2022,9,2))) then use VSTACK to combine everything. You’ll have to reference each sheet separately, but once it’s set up, it updates automatically when your source data changes. Just heads up - it gets slow with huge datasets, so use specific ranges instead of entire columns. Way simpler than scripting and still gives you the auto-updates.
Google Apps Script is perfect for this instead of built-in functions. I dealt with something similar pulling quarterly data from multiple department sheets - native functions like QUERY get messy fast when you’re working across sheets. Apps Script lets you loop through all sheets, find your 9/1/2022 date markers, and grab the values until the next date. You can set it to run automatically so your master sheet updates without you touching it. The script would cycle through each sheet, use findNext() to spot the dates, then collect column B values between them. There’s a learning curve with Apps Script, but it’s way more reliable than trying to stack QUERY or FILTER functions across tons of sheets. The automation alone makes it worth it for regular data pulls.
i’d start with QUERY and IMPORTRANGE before diving into Apps Script. try something like =QUERY(IMPORTRANGE("your-sheet-id","Sheet1!A:B"),"SELECT Col2 WHERE Col1 >= date '2022-09-01' AND Col1 < date '2022-09-02'") for each sheet, then combine the results. yeah, it’s tedious setting up multiple importranges, but way easier than coding if you’re not comfortable with scripts.