How to Merge rows in Google Sheets

Question

I tried digging this stuff and have no solution so I'm hoping someone can assist. I have a sheet with the following:

Data

123|456|789        
111|222|333

etc...

Result Needed

|123 456 789|        
|111 222 333|

etc...

I'm trying to avoid formulas (=Concat) and (=A2&" "&B2&" "&C2) etc...

I tried sheet.getRange(2,1,1,2).mergeAcross(); and it merged the cells and kept he left-most value. Google searches point to the formula solution.

TheMaster · Accepted Answer · 2018-12-17 22:02:27Z

1

You can try Array.join() for each row:

Snippet:

var jointRowsArray = sheet
  .getRange(2, 1, 2, 3) //A2:C4
  .getValues()
  .map(function(row) { 
    return [row.join(' ')];//join Each row
  });
sheet.getRange(2, 4, jointRowsArray.length, 1).setValues(jointRowsArray);

To Read:

answered Dec 17, 2018 at 22:02

TheMaster

51.6k7 gold badges76 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

James Over a year ago

Thanks TheMaster my Logger is showing that the data merged however when the last line executes I'm receiving an error "Cannot convert Array to Object[][]" This looks positive, when I change the range to 2,1,1,3 and change setValues to setValue it works.

Tom · Accepted Answer · 2018-12-17 22:15:23Z

0

var range  = sheet.getRange(2,1,1,2)  
var values = range.getValues()

range.clearContent()

sheet.getRange(2,1).setValue(values.join(' '));

You can pull the values into JS and then join and insert them into a single cell. You can also place this inside an iterator and do something akin to getRange(i,1,1,2). This can be triggered manually or by an edit hook.

However, this seems like the perfect fit for a single formula.

=TRANSPOSE(QUERY(TRANSPOSE(A:C),,COLUMNS(A:C)))

The formula would go on the first row in perhaps column D. JOIN functions cannot usually be arrayed in google sheets, and you would normally have to put a formula in for every row. However, here we are tricking the sheet into thinking our data is the header and displaying it accordingly.

edited Dec 17, 2018 at 22:15

answered Dec 17, 2018 at 21:45

Tom

1,32010 silver badges18 bronze badges

3 Comments

James Over a year ago

Hi Tom, i appreciate your response. The reason why I'm avoiding formulas because when I choose to write the information on a new column or sheet I get errors because it's including the formula cells so its throwing off the row length. so when I log the data it shows [123, 456, 789], [],[],[],[] <-- blanks represent my formulas. I tried the formula and I have an error of: "Result was not automatically expanded, please insert more rows (1)." Js code snippet shows error : TypeError: Cannot find function flat in object 123,456,789. (file Code.gs, line 41)

Tom Over a year ago

To avoid that error the formula must be on the top row of the data. If you are doing a full column A:C it needs to be on row 1. A2:C can go on row 2 and so on.

Tom Over a year ago

re: TypeError I removed the call to flat(). It was causing the error and is not needed. Sorry about that.

Collectives™ on Stack Overflow

How to Merge rows in Google Sheets

2 Answers 2

Snippet:

To Read:

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Snippet:

To Read:

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related