How do you merge two cells if another cell contains certain strings?

Tag: google-apps-script Author: duanhao123 Date: 2014-02-06

I want B1 and C1 to merge if A1 is either a,b,c but not if it is anything else.

I think the merging function is
sheet.getRange('B1:C1').merge();
but I am not sure how to check what the value of A is and if it is one of the strings that trigger the merging.

To clarify the merging bit, I mean two cells becoming one cell which spans over two columns.

Can you give a detailed example of what do you expect to happen if a merge is performed? Some might not even be sure what a merge is, so it would be good if you detail that, too.
I have edited the main post, but to clarify what I mean by merge, I mean the two cells become one cell which spans over two columns

Best Answer

Not sure I understood what you want exactly but the code below merges cells B1 & C1 if value in A1 meets one of the 3 conditions you choose.

function onEdit(e){
  var range = e.range;
  Logger.log(range.getA1Notation());
  if(range.getA1Notation()!='A1'){return};
  if(range.getValue()=='a' || range.getValue()=='b' || range.getValue()=='c'){
    SpreadsheetApp.getActiveSheet().getRange('B1:C1').merge();
  }
}

It runs automatically when a cell is edited and does nothing if the cell is not A1.


EDIT : since it appears from your comments that you wanted it to work for all the rows, here is a version that will merge cells in col B and C in any row if column A in this row meets the condition.

function onEdit(e){
  Logger.log(JSON.stringify(e));
  var range = e.range;
  if(range.getColumnIndex()!=1){return};
  if(range.getValue()=='a' || range.getValue()=='b' || range.getValue()=='c'){
    SpreadsheetApp.getActiveSheet().getRange(range.getRowIndex(),2,1,2).merge();
  }
}

comments:

When I try to run the code, this error comes up: TypeError: Cannot read property "range" from undefined. (line 2, file "Code")
You can not test it from the script editor since e is indeed undefined in this case, try it by editing the sheet cell directly
Ah, thank you. It works for A1, B1, C1, but when I change it to A2, B2, C2, it doesn't seem to work.
You didn't ask for a function that works for the whole column... is that what you need ?
If possible please. Also, the code works when I make a new sheet, but not in an existing sheet. Finally, is it possible to have it so when the cell is edited after the merge occurs, and does not have one of the strings, the cells are unmerged?