Excel Office Script - Does ".getVisibleView" return a valid range?

Question

IIn excel, using an office script, I'm trying to copy a value from one cell in what I assume is a valid range to another. Simplified version of my code:

  let selectedCell = workbook.getActiveCell();
  let selectedSheet = workbook.getActiveWorksheet();
  let range = selectedSheet.getUsedRange();
  let visibleRange = range.getVisibleView();
  let rangeValues = visibleRange.getValues();  // This doesn't fail, so I assume it's a valid range.

  let rowCount = visibleRange.getRowCount();

  for (let idx = 1; idx<rowCount; idx++) {
    visibleRange("L"+idx).copyFrom(visibleRange("J"+idx));
  }
}

I get this error:

visibleRange is not a function. (In 'visibleRange("L" + idx)', 'visibleRange' is an instance of r)

... and I am not sure why. I started with addressing the visibleRange numerically but I got the same error. Any suggestions would be helpful!

Thanks in advance.

Scott.

Answer 1

• getVisibleView() returns an Excel.RangeView object, it is different with Range object.
• Use getSpecialCells(ExcelScript.SpecialCellType.visible) to get the visible range. Please note that it's often a non-continuous range.

function main(workbook: ExcelScript.Workbook) {
  let selectedCell = workbook.getActiveCell();
  let selectedSheet = workbook.getActiveWorksheet();
  let range = selectedSheet.getUsedRange();
  let visibleRange = range.getSpecialCells(ExcelScript.SpecialCellType.visible);
  visibleRange.getAreas().forEach(areaRange => {
    let rowCount = areaRange.getRowCount();
    // console.log(areaRange.getAddress());
    let startRow = areaRange.getCell(0, 0).getRowIndex() + 1;
    for (let idx = 1; idx < rowCount; idx++) {
      selectedSheet.getRange("L" + (idx + startRow)).copyFrom(selectedSheet.getRange("J" + (idx + startRow)))
    }
  })
}