How do I know if spreadsheet cells are merged using google apps script
Yes, now you can use isPartOfMerge
to check.
And for anyone wants to get the value of merged cell:
var value = (cell.isPartOfMerge() ? cell.getMergedRanges()[0].getCell(1,1) : cell).getValue();
Hope it help.
range.isPartOfMerge()
It seems the .isPartOfMerge()
method was implemented (11 sept 2016).
So you can check if a range is merged as such:
function testMerge() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("Sheet1");
var range = sheet.getRange(1,1,2); // example for cells A1 and A2
Logger.log(range.isPartOfMerge());
}
Docs https://developers.google.com/apps-script/reference/spreadsheet/range#ispartofmerge
OLD APPROACH
Posting here another approach that I like more than the accepted answer.
If you try to use GAS to store a value in a cell of a merged range, except the first cell, it will not store it.
As such, we can exploit this and attempt to store the value and check if it was stored, using a function similar to this one:
function isMerged(range){
try {
range.getDataSourceUrl()
} catch (err) {
throw "isMerged function only works with valid GAS Range objects"
}
var sheet = range.getSheet();
var rangeData = range.getValues();
var testValue = "testing merge";
if (rangeData.length == 1 && rangeData[0].length == 1) return false; // we have a single cell. Can't possible be merged :)
if (rangeData.length == 1) {
// We have a single row. Which means we're testing a row with multiple columns
var mainCell=range.getA1Notation().split(":")[0];
var rowNumber= sheet.getRange(mainCell).getRow();
var nextColNumber = sheet.getRange(mainCell).getColumn()+1;
var row = rangeData[0];
var oldValue = row[1]; // for testing purposes, we're chosing the next possible column
sheet.getRange(rowNumber,nextColNumber).setValue(testValue);
if (sheet.getRange(rowNumber,nextColNumber).getValue() !== testValue) {
return true;
} else {
sheet.getRange(rowNumber,nextColNumber).setValue(oldValue);
return false;
};
} else if (rangeData[0].length == 1) {
// We have multiple rows and a single column.
var mainCell=range.getA1Notation().split(":")[0];
var nextRowNumber= sheet.getRange(mainCell).getRow()+1;
var colNumber = sheet.getRange(mainCell).getColumn();
var oldValue = rangeData[1][0]; // for testing purposes, we're chosing the next possible row
sheet.getRange(nextRowNumber,colNumber).setValue(testValue);
if (sheet.getRange(nextRowNumber,colNumber).getValue() !== testValue) {
return true;
} else {
sheet.getRange(nextRowNumber,colNumber).setValue(oldValue);
return false;
};
} else {
// We have multiple rows and multiple columns
var mainCell=range.getA1Notation().split(":")[0];
var nextRowNumber= sheet.getRange(mainCell).getRow()+1;
var nextColNumber = sheet.getRange(mainCell).getColumn()+1;
var oldValue = rangeData[1][1]; // for testing purposes, we're chosing the next possible row and next possible column
sheet.getRange(nextRowNumber,nextColNumber).setValue(testValue);
if (sheet.getRange(nextRowNumber,nextColNumber).getValue() !== testValue) {
return true;
} else {
sheet.getRange(nextRowNumber,nextColNumber).setValue(oldValue);
return false;
};
}
// if none of these checks worked, something's fishy. Either way, return false
return false
}
I've ran a series of quick tests and it returned true
/false
accordingly, but there is a limitation that I didn't have time to cover:
If you had a merged range like "A1:F1"
and you checked the range "A1:G1"
(so, one more column), it will return true
, even if G1 is not part of the merged range - because it checks only the next column using as reference the first bound row/column.
For those like me who didn't know the .breakapart()
method to unmerge cells:
https://developers.google.com/apps-script/class_range#breakApart
Thanks to Henrique for the tip !