CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Meg
Meg

Reputation: 37

Dynamic array for queries in google sheets with imported ranges

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:https://docs.google.com/spreadsheets/d/1fPoi4x5H7uowBFyo1kbxTY_us6snW7juJVPxZHfi7SM/edit#gid=0

I've got 3 data sheets that are already there that would have been built into the recipient sheet to start with using an apps script generator, which concatenates the original query, but this was built by someone else, I'm still pretty new to apps script. Whenever I add new people to a recipient sheet I need to go and stick the URL of their sheet in there, which is not an issue and probably something someone else can do.

However I then need to go to another sheet and manually edit the query every time, which is something that other people in my team probably can't do without ruining things. I'm wondering if the textjoin output in cell B9, based on the joins in column A when pulled down could in some way serve as the data argument in the query function, similar to sticking a separate query statement in cell A1, as then they'd just have to pull the formula down when adding the sheet. I could probably concatenate up an entire query function for someone to copy and paste into the query cell, but that's getting a bit too close to letting someone into the protected cells, haha.

Any ideas? I'm not very hot on indirect which I thought could be an option. If it's not possible it's not possible, but as ever, would be nice if it could. Cheers, Meg

Edit: I forgot to mention the original data is from transposed importranges if that makes a difference, but when I started making the dummy sheet I lost track of how I'd done that in the first place...

Upvotes: 0

Views: 867

Answers (3)

capup
capup

Reputation: 1

You can use named func:

=IFERROR(
    UNIQUE(IMPORTRANGE(VLOOKUP(number;Links!$A$1:$D;4;FALSE);"Sheet!A2:T"));
    {"🛑🛑🛑 ERROR 🛑🛑🛑 "& VLOOKUP(number;Links!$A$1:$D;2;FALSE) &" 🛑🛑🛑"\""\""\""\""\""\""\""\""\""\""\""\""\""\""\""\""\""\""\number})

And use array:

=UNIQUE(SORT({
  GET_DATA(1)
  ;GET_DATA(2)
  ;GET_DATA(3)
  ;GET_DATA(4)
  ;GET_DATA(5)
  ;GET_DATA(6)
  ;GET_DATA(7)
  ;GET_DATA(8)
  ;GET_DATA(9)
  ;GET_DATA(10)
};20;TRUE))

Upvotes: 0

marikamitsos
marikamitsos

Reputation: 10573

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 <>'' ")

enter image description here

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

Upvotes: 1

Osm
Osm

Reputation: 2881

The Concatenation Formula

Paste this formula in E3 to Create and Concatenate the formula, No need to drag down and paste ID's instead of url's in column B take a look at this Sheet..

=CONCATENATE("= { ", TEXTJOIN("; ",1,ArrayFormula(SUBSTITUTE(CONCATENATE("IMPORTRANGE( ", """#"""," ,",$E$2, " )"),"#" ,FILTER(B3:B,B3:B<>"")))), " } ")

Now you can copy and paste the formula to the D6.

enter image description here

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

Upvotes: 0

Related Questions