Excel Script keeps giving error Range merge You cannot perform the requested operation

Question

Ï'm writing an Excel Script. Given a selected cell, it should:

• Insert a row below the selected cell (this works)
• For all cells of the inserted row which are in a column left of the selected cell, merge it with the cell above it (which is on the selected row). This fails.

The code is:

function main(workbook: ExcelScript.Workbook) {
  const selectedSheet = workbook.getActiveWorksheet();
  const selectedRange = workbook.getSelectedRange();
  if (selectedRange == null) {
    console.log(`No cells in worksheet selected.`);
    return;
  }
  if ( selectedRange.getCellCount() != 1 ) {
    console.log(`More than one cell selected`);
    return;
  }
  // console.log(`Selected range for the worksheet: " ${selectedRange.getAddress()}`);
  // Insert a rwo below the selected cell
  const targetRow = selectedRange.getOffsetRange(1,0).getEntireRow();
  targetRow.insert(ExcelScript.InsertShiftDirection.down)

  // now merge each inserted row's cell left of the selected column with the cell on the selected row (i.e. the one above it)
  const sColNr = selectedRange.getColumnIndex();
  const sRowNr = selectedRange.getRowIndex();
  console.log(`col,row = ${sColNr}, ${sRowNr}`);
  for ( let c = 0; c < sColNr ; c++ ) {
    let r = selectedSheet.getRangeByIndexes(sRowNr, c, 2, 1);
    r.merge(false);
  }
}

Problem: I can set values in those cells, so I know I have the correct cells in the for loop.

No matter which cell I choose on a blank page, the script always fails with the message "Line 23: Range merge: You can not perform the desired action."

It works when I manually pick the cells and use the Excel Interface to combine them.

Answer 1

Range.merge() Operation is not possible in ExcelScript on desktop Excel... It is though on office.com/excel.

Code had another tricky functional bug. solved that too. Final code:

    function main(workbook: ExcelScript.Workbook) {
      const selectedSheet = workbook.getActiveWorksheet();
      const selectedRange = workbook.getSelectedRange();
      if (selectedRange == null) {
        console.log(`No cells in worksheet selected.`);
        return;
      }
      // Insert a rwo below the selected cell
      console.log(`Selected range for the worksheet: " ${selectedRange.getAddress()}`);
       //get 1 based indexes of lower left cell of selection
      const sColNr = selectedRange.getColumnIndex();
      const sRowNr = selectedRange.getRowIndex() + selectedRange.getRowCount() - 1;
      console.log(`reference cell = ${sColNr}, ${sRowNr}`)
      const targetRow = selectedSheet.getRangeByIndexes(sRowNr+1,0,1,1).getEntireRow();
      targetRow.insert(ExcelScript.InsertShiftDirection.down)
    
      // now merge each inserted row's cell left of the selected column with the cell on the selected row (i.e. the one above it)
      console.log(`col,row = ${sColNr}, ${sRowNr}`);
      for ( let c = 0; c < sColNr ; c++ ) {
        r = selectedSheet.getRangeByIndexes(sRowNr, c, 2, 1);
        r.merge(false);
      }
    }