How to concatenate ranges in Google spreadsheets

.how_to_concatenate_ranges_in_google_spreadsheets.md

How to concatenate ranges in Google spreadsheets

Sometimes it is necessary to concat ranges in Google Spreadsheet. Eg, Data 1 and Data 2

Sheet Data 1

Name	Date	Sum
Ethan	3/4/2017	31
Logan	3/6/2017	62
Brian	3/26/2017	61
...	...	...

Sheet Data 2

Name	Date	Sum
Nathan	3/30/2017	53
Alyssa	3/13/2017	72
John	3/24/2017	79
Megan	3/16/2017	10
...	...	...

Concatenation

Vertical concatenation

={'Data 1'!A1:C20;'Data 2'!A2:C20}

Name	Date	Sum
Ethan	3/4/2017	31
Logan	3/6/2017	62
Brian	3/26/2017	61
...	...	...
Nathan	3/30/2017	53
Alyssa	3/13/2017	72
John	3/24/2017	79
...	...	...

Horizontal concatenation

={TRANSPOSE('Data 1'!A1:C20),TRANSPOSE('Data 2'!A2:C20)}

Name	Ethan	Logan	Brian	...	Nathan	Alyssa	John
Date	3/4/2017	3/6/2017	3/26/2017	...	3/30/2017	3/13/2017	3/24/2017
Sum	31	62	61	...	53	72	79

Live examples

• How to concatenate ranges in Google spreadsheets

Tips

;'s not work

Perhaps your delimiter argument is a semicolon ;, rather than a comma ,, then you must use other concatenation characters

Vertical concatenation

Use semicolon ;

={'Data 1'!A1:C20;'Data 2'!A2:C20}

Horizontal concatenation

Use backslash \ without spaces!

={TRANSPOSE('Data 1'!A1:C20)\TRANSPOSE('Data 2'!A2:C20)}

Google Apps Script

function unionRanges(e) {
  var result = [];
  var length = 0;
  var i = 0;
  try {
    for (i = 0; i < arguments.length; i++)
      length += arguments[i].length;
    if (length > 3000) return '#BIGRANGE';
    for (var i = 0; i < arguments.length; i++)
      result = result.concat(arguments[i].filter(function (el) {
        return el.join('').length > 0
      }));
    return result;
  } catch (err) {
    return JSON.stringify(err);
  }
}

if the ranges to concatenate are produced by functions then its possible that such a function returns nothing and shows #N/A.
When concatenating such ranges with ; you get an error: In ARRAY_LITERAL, an Array Literal was missing values for one or more rows.

Do you know how to solve this?

@thehpi

You need to check #N/A and returns an empty array like that {"";"";""}