I have a Google Apps Script pulling the names of each tab in my Google Sheet into one list. The tabs are labeled by month and year (i.e., February 2020) but I don't have a tab for every single month since February. I create a new tab for months when I have new data which is not always every consecutive month.
With these tabs, I want to query all unique values across all tabs, in column D on each of the tabs. I'm wondering if it's possible to do this in anticipation for potential new tabs, so I don't have to go and update the query whenever I add a new tab?
Currently I have the list of existing tabs and concatenated to include the range (so each tab/range is listed as January 2020!A:D, for example) in cells C2:C6. The query I have that is correctly pulling unique values is:
=UNIQUE(QUERY({INDIRECT(C2);INDIRECT(C3);INDIRECT(C4);INDIRECT(C5);INDIRECT(C6)},"Select Col4 where Col4 is not null",1))
Is there a way to possibly add new month/dates to the list of existing tabs in anticipation of that tab name possibly being added in the future, and adding those not-yet-existing tab names into the query, but telling it to ignore it if it can't find that tab in the sheet?
Thank you in advance!
CodePudding user response：
there are several ways how to solve this:
- with a script that will pick sheet names
- without script using only formula
- with logic if the creation of sheets can be predicted
non-existing sheets can be ignored like this:
=UNIQUE(QUERY({
IF(C2="", {"","","",""}, INDIRECT(C2));
IF(C3="", {"","","",""}, INDIRECT(C3));
IF(C4="", {"","","",""}, INDIRECT(C4));
IF(C5="", {"","","",""}, INDIRECT(C5));
IF(C6="", {"","","",""}, INDIRECT(C6))},
"select Col4 where Col4 is not null", 1))
note 4 columns in A:D = 4 empty slots {"","","",""}
see:
https://stackoverflow.com/a/69067831/5632629
https://stackoverflow.com/a/57452968/5632629
https://stackoverflow.com/a/58599649/5632629