Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
How to concatenate ranges in Google spreadsheets

How to concatenate ranges in Google spreadsheets

Unsplash

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

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);
  }
}
@thehpi

This comment has been minimized.

Copy link

thehpi commented Apr 4, 2019

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?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.