Dynamic array for queries in google sheets with imported ranges-CodePudding

I'm wondering if there's any way to build a dynamic range into a query, so it automatically adds new imported ranges if I add them onto a sheet. I saw an example on here for within sheets, but I can't see how it would translate to imported ones. I've done an example here:

Note: there is not similar to Excel EVALUATE() function in google sheets to evaluate text in cell to a formula.

CodePudding user response：

One cannot use the IMPORTRANGE function the way you wish (concatenate or join). But.

You have an alternative.

Use the following formula.

=QUERY(QUERY({IFERROR({IMPORTRANGE(C3,E1)},{"",""}); 
              IFERROR({IMPORTRANGE(C4,E1)},{"",""});
              IFERROR({IMPORTRANGE(C5,E1)},{"",""});
              IFERROR({IMPORTRANGE(C6,E1)},{"",""});
              IFERROR({IMPORTRANGE(C11,E1)},{"",""});
              IFERROR({IMPORTRANGE(C8,E1)},{"",""});
              IFERROR({IMPORTRANGE(C21,E1)},{"",""});
              IFERROR({IMPORTRANGE(C15,E1)},{"",""});
              IFERROR({IMPORTRANGE(C7,E1)},{"",""})}),
           "where Col1 <>'' ")

How the formula works

What you do is predict how many links you may have and pre-fill them within your query.

As you see the basic part is:

IFERROR({IMPORTRANGE(your_link_cell_here,E1)},{"",""})

You can pre-fill as many parts as you wish.

(You need to other formula or helper columns. Even if you later need more you can just add them.)