How to import data from multiple ranges defined by two columns in google sheets

1
votes

I need help with importing multiple ranges into one block from two defining columns - one column defines the links, the second one defines the sheets.

Table 1 looks something like this:

  | Link to spreadsheet   | Sheet Name |
1 | docs.google/blahblah  | Team 1     |
2 | docs.google/blahblah2 | Team 2     |
3 | docs.google/blahblah3 | Team 3     |

I need to import data from columns C:E from these sheets, from those sheet names, so it'd look something like this:

  | C                               | D     | E     |
1 | some value from blahblah Team 1 | ditto | ditto |
1 | some value from blahblah Team 1 | ditto | ditto |
1 | some value from blahblah Team 1 | ditto | ditto |
1 | some value from blahblah Team 2 | ditto | ditto |
1 | some value from blahblah Team 2 | ditto | ditto |
1 | some value from blahblah Team 3 | ditto | ditto |

I already know how to import one range using the cells(I do it like this): QUERY(IMPORTRANGE(A2:A, "'"&A2:A&"'"&"!C:E"), "select *") but I don't know how to expand this approach into import from entire column.

1
google-sheets

1 Answers

2
votes

it would go like this:

=QUERY({IMPORTRANGE(A1; B1&"!C:E");
        IMPORTRANGE(A2; B2&"!C:E");
        IMPORTRANGE(A3; B3&"!C:E")}; "where Col1 is not null"; 0)

note that all imported sheets need to be connected separately beforehand