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?
2 Answers
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:
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:
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.
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 ,
.