Multiple IMPORTRANGE

Question

I have a column (A1:A) with URLs of the Sheets I want to IMPORTRANGE. The problem is it's open end and I can't just do ={IMPORTRANGE(A1,"Sheet1!A1:A");IMPORTRANGE(A2,"Sheet1!A1:A")} as the list of URLs is open end (indefinite). Since I cannot use ARRAYFORMULA with IMPORTRANGE, I was wondering if there is an alternative to it. I'd also like to have a URL next to IMPORTRANGE data, so I know what URL the data shown came from. Maybe someone has a clue.

https://docs.google.com/spreadsheets/d/1YhLJpy_p39pDvTKfjyOKQMN0W_TDz95E9XsmtOJV-GQ

But this one is working right? `=QUERY({IMPORTRANGE(A2; B2);IMPORTRANGE(A3; B3);IMPORTRANGE(A4; B4)}; "select * where Col1 is not null")` — Marios, Oct 30 '20 at 07:47
yes, it's working, but it's not open end. It works only as long as I hard code the URLs, but when I don't know how many URLs will be there. — Mara, Oct 30 '20 at 08:02
I see what you want to achieve. I am not sure if you can do that. However, keep in mind that the more importranges you have the more laggy the file will be. — Marios, Oct 30 '20 at 08:21

kishkin · Answer 1 · 2020-11-03T10:45:35.400

Maybe it would be better to use script for that. IMPORTRAGE not working inside ARRAYFORMULA is usually solved with importing using scripts.

In case of formulas with import failure tolerance and source markings here are a few solutions.

Solution 1 - many `QUERY`, same number of `IMPORTRANGE`

Try this:

=QUERY(
  ARRAYFORMULA(
    {
      IFERROR(QUERY(IMPORTRANGE(A2; B2); "SELECT '" & A2 & "', Col1 WHERE Col1 IS NOT NULL"); {""\ ""});
      IFERROR(QUERY(IMPORTRANGE(A3; B3); "SELECT '" & A3 & "', Col1 WHERE Col1 IS NOT NULL"); {""\ ""});
      IFERROR(QUERY(IMPORTRANGE(A4; B4); "SELECT '" & A4 & "', Col1 WHERE Col1 IS NOT NULL"); {""\ ""})
    }
  );
  "WHERE Col2 IS NOT NULL
   LABEL Col1 'Source',
         Col2 'Imported Data'"
)

Solution 2 - one `QUERY`, twice the `IMPORTRANGE`

You can use IFERROR in case import will go wrong.

Here is a possible solution for you (IFERROR and source URLs) (one formula in E1):

=QUERY(
  ARRAYFORMULA(
    {
      IFERROR({IF(SEQUENCE(ROWS(IMPORTRANGE(A2; B2))); A2)\ IMPORTRANGE(A2; B2)}; {""\ ""});
      IFERROR({IF(SEQUENCE(ROWS(IMPORTRANGE(A3; B3))); A3)\ IMPORTRANGE(A3; B3)}; {""\ ""});
      IFERROR({IF(SEQUENCE(ROWS(IMPORTRANGE(A4; B4))); A4)\ IMPORTRANGE(A4; B4)}; {""\ ""})
    }
  );
  "WHERE Col2 IS NOT NULL
   LABEL Col1 'Source',
         Col2 'Imported Data'"
)

Multiple IMPORTRANGE

1 Answers1

Solution 1 - many `QUERY`, same number of `IMPORTRANGE`

Solution 2 - one `QUERY`, twice the `IMPORTRANGE`

Linked

Multiple IMPORTRANGE

1 Answers1

Solution 1 - many QUERY, same number of IMPORTRANGE

Solution 2 - one QUERY, twice the IMPORTRANGE

Linked

Solution 1 - many `QUERY`, same number of `IMPORTRANGE`

Solution 2 - one `QUERY`, twice the `IMPORTRANGE`