I want To create One object in which I want to store Cell address and there column and row index.
I have achieve that using this code but in mereged cell I’m facing issue. If A1 and B1 Cell is merged than I want only A1 Cell but currently I get both A1 and B1 cell.
Is there way I can find merged cell from given range(namedRange).
HERE is my Image
My Excel Image
According to this table I want only A1 and G1 cell from given range because other cells are merge.
Here
- sheetName is my activeSheetName
- Context is just my current context of office.
- SheetRange is my Address Like A1:I14
- createRangeName is my unique named for NameItem as example S_361a26feb074409584f04deef2a9e354
async function setDefineNameInExcel(context, sheetName, SheetRange, createRangeName) {
var uniqueNamedRange = createRangeName;
let sheet = context.workbook.worksheets.getItem(sheetName);
let range = sheet.getRange(SheetRange);
sheet.names.add(uniqueNamedRange, range);
range.load("address,rowCount,columnCount,cellCount,values");
sheet.load("names");
await context.sync();
const propertiesToGet = range.getCellProperties({ address: true });
await context.sync();
for (let iRow = 0; iRow < range.rowCount; iRow++) {
for (let iCol = 0; iCol < range.columnCount; iCol++) {
const cellAddress = propertiesToGet.value[iRow][iCol];
var address = cellAddress.address.slice(cellAddress.address.lastIndexOf("!") + 1)
excelTableObj = {};
excelTableObj.Range = uniqueNamedRange;
excelTableObj.Address = address;
excelTableObj.RowIndex = iRow;
excelTableObj.ColIndex = iCol;
lstexcelTableObj.push(excelTableObj);
}
}
}