Google Sheets QUERY 从选项卡名称列表中提取，这将在添加新选项卡时提取新选项卡数据

发布于 2025-01-11 05:07:24 字数 571 浏览 0 评论 0原文

我有一个 Google Apps 脚本，将 Google 表格中每个选项卡的名称拉入一个列表。这些选项卡按月份和年份标记（即 2020 年 2 月），但自 2 月以来我没有每个月的选项卡。当我有新数据（并不总是每个连续月份）时，我会创建一个新选项卡几个月。

通过这些选项卡，我想查询所有选项卡中每个选项卡上 D 列中的所有唯一值。我想知道是否可以在预期潜在的新选项卡时执行此操作，这样我就不必在添加新选项卡时去更新查询？

目前，我有现有选项卡的列表，并连接到单元格 C2:C6 中的范围（因此每个选项卡/范围都列为 2020 年 1 月！A:D）。我正确提取唯一值的查询是：

=UNIQUE(QUERY({INDIRECT(C2);INDIRECT(C3);INDIRECT(C4);INDIRECT(C5);INDIRECT(C6)},"Select Col4 where Col4 is not null",1))

是否有一种方法可以将新的月份/日期添加到现有选项卡列表中，以预期将来可能添加该选项卡名称，并添加那些尚未存在的选项卡将选项卡名称添加到查询中，但如果在工作表中找不到该选项卡，则告诉它忽略它？

先感谢您！

原文

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!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

如梦亦如幻 2025-01-18 05:07:24

有几种方法可以解决这个问题：

使用脚本来选择工作表名称，
而无需脚本，仅使用
带有逻辑的公式（如果可以预测工作表的创建），

可以像这样忽略不存在的工作表：

=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))

注意 A:D = 中的 4 列4 个空槽{"","","",""}

请参阅：

https://stackoverflow.com/a/69067831/5632629

https://stackoverflow.com/a/57452968/5632629

https://stackoverflow.com/a/58599649/5632629

https://stackoverflow.com/a/68447014/5632629

https://stackoverflow.com/a/61819704/5632629

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

https://stackoverflow.com/a/68447014/5632629

https://stackoverflow.com/a/61819704/5632629

回复收藏 0 原文

~没有更多了~