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.

The error is giving you everything. Using () brackets is typescript notation for calling a function. It’s not a function, it’s an object. You’ll need to use a method from that object type … learn.microsoft.com/en-us/javascript/api/office-scripts/… — Skin
– Skin, Commented Jan 5, 2024 at 0:07

taller · Accepted Answer · 2024-01-05 02:17:50Z

0

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)))
    }
  })
}

edited Jan 5, 2024 at 2:17

answered Jan 5, 2024 at 0:41

taller

19.7k2 gold badges9 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related