0

I am having a bit of an issue using IMPORTRANGE in Google Sheets to import multiple sheets into a single column on a master sheet and whenever one of the sheets is updated, it automatically creates the new row on the master. Would there be a better way to do it other than IMPORTRANGE?

Rubén
  • 34,714
  • 9
  • 70
  • 166

2 Answers2

4

You can attach arrays onto each other by using embedded arrays. See more: Using arrays in Google Sheets

This is a basic array ={1,2,3,4} and has an output that looks like this:enter image description here

To append an array on-top of another array you would use a semicolon ;; instead of a comma ,. Like so: ={1;2;3;4} which will look like this:

enter image description here

To append multiple arrays together you would take one array {1;2;3;4} and append it to another like: {1;2;3;4};{5;6;7;8} and encase both with the embedded array syntax like so: 

{{1;2;3;4};{5;6;7;8}}

However, one strict limitation is that both arrays need to be the same width, so if you have {{1,2};{1,2};{1,2,3}} you will receive an error becuase not all the array elements have the same number of columns.

How to import multiple ranges on top of each other?

Using the above concepts, assuming both IMPORTRANGE()'s are the same width:

={IMPORTRANGE(sheet, range);IMPORTRANGE(sheet, range)}

Will import two ranges one on top of the other.

Douglas Gaskell
  • 9,017
  • 9
  • 71
  • 128
  • did not pull the second importrange. – Guilherme Henrique Mendes Jan 27 '19 at 11:55
  • Using more than one importrange in a single formula will cause issues due to the lack of permission to link other sheets. I found that Google Sheets ask for permission the very first time using only one importrange in formula. So my suggestion is to change the formula to include only one importrange at a time and give permission to access the linked Sheet. Once the access to every needed external sheet is given you can use multiple importrange in the same formula. – Alessandro Alessandra Feb 10 '21 at 19:00
1

I'd suggest QUERY (that does not require authorisation). For example for a single row (Row2) from each of three sheets (1, 2 and 3), with the headers from '1'!A1:C1 :

=query({'1'!A1:C2;'2'!A2:C2;'3'!A2:C2})

The ; is for vertical stacking where the locale's general separator is ,.

pnuts
  • 58,317
  • 11
  • 87
  • 139