Google Sheets – Count Columns in a Merge

google sheetsgoogle-apps-scriptgoogle-sheets-named-ranges

Is there a formula function in Google Sheets to count the number of columns in a merge? I could assign a named range to the merge to make it easier….

For instance: see the highlighted merged cells here, gives 13 columns:

enter image description here

Best Answer

Sure. Use the range object's getMergedRanges() method. Note that it returns an array of Ranges, so if testing a single cell, be sure to [0] -grab the first range returned. Demo with G3 as the cell to test, and getNumColumns() used to detect merge width, per your example. Run it and choose View→Logs to see what it found.

function getCellMergeRange() {
  var testSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(),
      testRange = testSheet.getRange("G3"),
      mergeRange = testRange.getMergedRanges()[0];
  Logger.log("Merged Range is "+mergeRange.getA1Notation()+", containing "+(mergeRange.getNumColumns()*mergeRange.getNumRows())+" cells.");
}
Related Topic