Split (break apart) merged table cells in google s

Question

When operating (copying/moving) ranges containing merged cells, I always get the error “Your paste overlaps with merged cells. Please unmerge the cells and try again”. But when trying to unmerge the cells in the range using Range#breakApart, I get another error: “The selected cells cannot be merged.”, which is even more confusing since I’m not trying to merge anything, I’m only trying to break the cells apart.

Answer 1

Turns out breakApart only works when the range it is called on encompasses all merged cells, not just parts of the merge area.

This would not be too bad. The problem starts once you realize that there is no way in the API to get the ranges of merged cells. So the best way to solve this (that I’ve found so far) is just to gently extend your range until no error occurs:

var breakRange = myRange;
for(;;) {
  try {
    breakRange.breakApart();
    break;
  } catch(e) {
    breakRange = mySheet.getRange(
      breakRange.getRowIndex(),
      breakRange.getColumnIndex(),
      Math.min(
        breakRange.getHeight()+5,
        mySheet.getMaxRows()-breakRange.getRowIndex()+1
      ),
      Math.min(
        breakRange.getWidth()+5,
        mySheet.getMaxColumns()-breakRange.getColumnIndex()+1
      )
    );
  }
}

(I’ve added five rows/cols instead of one since getting ranges seems to be a rather expensive process). This works quite well so long as you don’t have additional merged cells being added as you expand your search area.

When it comes to merged cells in spreadsheets, the Google Apps Scripts API is broken fundamentally in at least the following ways:

1. There is no way to get an array of ranges of merged cells in a sheet/range.
2. There should be a way to see which cells have been split (breakApart only returns the original range “for chaining”). This makes it impossible to split merges and then have each cell contain the content of the original merge cell.
3. Trying to use breakApart in a range that encompasses only a part of a merge cell should still break apart the entire merge instead of throwing an exception.

All in all, the API as it currently stands only works for spreadsheets whose layout and usage the script author herself has complete control over.