Google表格IMPORTRANGE与多个工作表 [英] Google Sheets IMPORTRANGE with multiples Sheets

问题描述

我想知道如何处理QUERY + IMPORTRANGE函数，以便合并来自几(40)个Google表格的数据，这些表格的链接都在表格中.

I would like to know how to handle a QUERY + IMPORTRANGE functions in order to combine data from several (40) google sheets, the links of which are in a table.

我不能像这样将它们全部放在一个公式中，键入...可能太长了...

I cannot put them all in a single formula like so, it would be too long to type...

=QUERY({IMPORTRANGE(Link1,"A1:E500");IMPORTRANGE(Link2,"A1:E500");IMPORTRANGE(Link3,"A1:E500");...},"Select * where Col1 is not null")

我没有通过INDIRECT函数来做到这一点

I did not manage to do it with an INDIRECT function

如果有更简单的解决方案，我也有兴趣！

If there is a simpler solution, I am also interested !

谢谢！

推荐答案

我知道您想阅读两个不同的图纸，然后使用公式将它们全部转储到第三张图纸中.如果那是正确的，那么您将非常接近解决这个目标.您需要略微修改公式以使其符合以下条件:

I get that you want to read two different Sheets and dump them all in a third Sheet using a formula. If that is correct, you are very close to solving this goal. You need to slightly modify your formula to match this:

=QUERY({IMPORTRANGE("{FIRST SPREADSHEET ID}","{SHEET RANGE}");IMPORTRANGE("{SECOND SPREADSHEET ID}","{SHEET RANGE}")},"SELECT * WHERE Col1 <> ''")

在使用该公式之前，您需要批准与两个工作表的连接.为此，您需要分别复制每个IMPORTRANGE并在运行它时接受该对话框.如果您仍有疑问，请随时问我.

Before using that formula you need to approve the connection to both Sheets. To do it, you need to copy each IMPORTRANGE separately and accept the dialog box when running it. If you still have questions, feel free to ask me.

这篇关于Google表格IMPORTRANGE与多个工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！