CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
eirever
eirever

Reputation: 143

return QUERY or FILTER results from multiple sources with a single formula

I am using a Google Sheet to track data that uses Apps Script to automatically move rows from the "current" sheet to the "past" sheet after seven days from the date of the row.

I create individual sheets for team members that populate their specific data. As the data is split between two sheets (i.e., current & past), is there a way to have one formula (QUERY) or a set of formulas (multiple FILTER) that will populate the results from one sheet and then the other?

This is a simplified version. It includes the Apps Script for moving rows 7 days old. Eventually, the movePast function will be set to a daily trigger.

https://docs.google.com/spreadsheets/d/1lyCK5eIEQYjGFOxh5T_4BxeuDS-1zvMLq80IYn0dc38/edit?usp=sharing

The "me" sheet is where I am seeking a solution.

The "goal" sheet shows the desired outcome. Currently is uses two QUERY functions: in A2:

=QUERY(past!A2:E, "select A,B,C,D,E where B contains 'me'")

in A6:

=QUERY(current!A2:E, "select A,B,C,D,E where B contains 'me'")

Since this will not work once additional data is added because the first QUERY will not be able to overwrite the second formula, the hope is that a single formula can be used in A2... or ...an Apps Script solution is possible that will clear and move the formula automatically (possibly when A2 returns an error). One consideration is that in the actual sheet, I have already assigned the onEdit function to serve a different task.

Upvotes: 0

Views: 268

Answers (1)

JPV
JPV

Reputation: 27302

Replace the formula in A2 (in the tab 'me') with

=QUERY({past!A2:E; current!A2:E}, "select * where Col2 contains 'me'")

and see if that works for you?

Upvotes: 2

Related Questions