Merged cells look tidy, but a script reading them only sees the value in the top-left cell. Every other cell in the block comes back empty. unmergeAndFillDown(range) breaks up every merged block in the range and copies the block's value into each cell it covered, then writes the whole range back in one setValues call.
When to use
- Reports where a region or date is merged down several rows
- Exports from other tools that merge header blocks
- Before sorting, filtering, or reading a range into a script
How to use this snippet
/**
* Unmerge every merged block in a range and fill each block's value
* into every cell it covered. One batch write at the end.
*
* @param {GoogleAppsScript.Spreadsheet.Range} range
* @return {GoogleAppsScript.Spreadsheet.Range} the same range
*/
function unmergeAndFillDown(range) {
var r0 = range.getRow(), c0 = range.getColumn();
var values = range.getValues();
var rows = values.length, cols = values[0].length;
range.getMergedRanges().forEach(function (m) {
var top = m.getRow() - r0, left = m.getColumn() - c0;
var value = m.getCell(1, 1).getValue();
m.breakApart();
for (var i = Math.max(top, 0); i < Math.min(top + m.getNumRows(), rows); i++) {
for (var j = Math.max(left, 0); j < Math.min(left + m.getNumColumns(), cols); j++) {
values[i][j] = value;
}
}
});
range.setValues(values);
return range;
}
Example
function cleanReport() {
var sheet = SpreadsheetApp.getActive().getSheetByName('Report');
unmergeAndFillDown(sheet.getDataRange());
}
Notes
- Formatting stays; only the merges and the empty cells change.
- If a merged block sticks out past the range, the whole block is unmerged but only the cells inside the range get filled.
- Formulas in merged blocks are written back as values. Run it on a copy first if that matters.
Happy Coding!
