3

I'd like to unmerge all the cells of my google spreadsheet using script. I believe that VBA has this option (cells.unmerge) but I can't find a similar operation in GAS. I've tried this script but it didn't seem to work.

function MyFunction() {
var Sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var Range = Sheet.getDataRange().activate();
Range.clearFormat();
}
beaver
  • 523
  • 1
  • 9
  • 20
Frank Montemorano
  • 321
  • 4
  • 6
  • 12
  • 1
    See **[this SO post](http://stackoverflow.com/questions/15441604/split-break-apart-merged-table-cells-in-google-spreadsheet-with-an-apps-script)**. – chuff Jun 28 '13 at 00:42
  • Unmerge all cells in sheet: `SpreadsheetApp.getActive().getActiveSheet().getDataRange().breakApart();` – Juuso Nykänen Dec 05 '21 at 15:05

2 Answers2

1

The correct word is "Range.breakApart" not "unmerge". Note that this only works when the range it is called on encompasses all merged cells.

Frank Montemorano
  • 321
  • 4
  • 6
  • 12
1

Try this code:

function unmerge() {
   var app = SpreadsheetApp;

  // get current active sheet use single line coding
  var activeSheet =app.getActiveSpreadsheet().getActiveSheet();

  // get last row
  var lstrow= activeSheet.getLastRow();

  // see below description **
  var mergerange = activeSheet.getRange(13,4,lstrow).getMergedRanges();

  for (var i = 0; i < mergerange.length; i++) {
        Logger.log(mergerange[i].getA1Notation());
        Logger.log(mergerange[i].getDisplayValue());
        mergerange[i].breakApart();
   }
}

** 13= start row number. 4 = column number of merge cells.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
TipVisor
  • 1,022
  • 10
  • 21
  • Great. Adapted it to loop across all columns as well in the sheet. Changed the following: let lastCol = activeSheet.getLastColumn(); for (let column = 1; column < lastCol; column++){ let mergerange = activeSheet.getRange(1,column,lastrow).getMergedRanges(); mergerange.map(cells => cells.breakApart()) } – Dave Sottimano Feb 14 '22 at 00:43