Reliable way for getting ranges of visible view

Question

After filtering a worksheet, I would like to copy the visible cells to another worksheet.

      currentWorksheet.autoFilter.remove();

      currentWorksheet.autoFilter.apply(currentWorksheet.getUsedRange(), 0, {

        filterOn: Excel.FilterOn.custom,

        criterion1: "<50",

      });

      const visibleView = currentWorksheet.getUsedRange().getVisibleView();

      visibleView.load(["cellAddresses"]);

      await context.sync();

      const toBeCopied = currentWorksheet.getRanges(visibleView.cellAddresses.toString());

Unfortunately, I get the following error intermittently, depending on how many rows there are to filter.

RichApi.Error: The argument is invalid or missing or has an incorrect format.

The stacktrace does not include the actual offending line. It just points to the last await context.sync(), but I have narrowed it down to the call to const toBeCopied = currentWorkseet.getRanges(visibleView.cellAddresses.toString()).

The error occurs with varying frequency depending on the number of rows that are to be filtered:

• 100: 0 out of 10 runs
• 1000: 5 out of 10 runs
• 10000: 10 out of 10 runs

What could be the reason for this? Here is the full function:

export async function run() {
  try {
    await Excel.run(async (context) => {
      let currentWorksheet = context.workbook.worksheets.getItemOrNullObject("Worksheet");
      await context.sync();
      if (currentWorksheet.isNullObject) {
        let existingWorksheet = context.workbook.worksheets.getItem("Sheet1");
        existingWorksheet.name = "Worksheet";
        currentWorksheet = existingWorksheet;
      } else {
        currentWorksheet.name = "Delete Me";
        let newWorksheet = context.workbook.worksheets.add("Worksheet");
        currentWorksheet.delete();
        currentWorksheet = newWorksheet;
      }
      const targetRange = currentWorksheet.getRange("A2:Z10000");
      const headers = currentWorksheet.getRange("A1:Z1");
      const randomFormula = "=RandBetween(1, 100)";
      headers.load(["columnCount"]);
      targetRange.load(["columnCount", "rowCount"]);
      await context.sync();
      for (let h = 0; h < headers.columnCount; h++) {
        const headerCell = headers.getCell(0, h);
        headerCell.load(["address", "values"]);
        await context.sync();
        headerCell.values = [[headerCell.address]];
      }
      let data = [];
      for (let i = 0; i < targetRange.rowCount; i++) {
        let rowData = [];
        for (let j = 0; j < targetRange.columnCount; j++) {
          rowData.push(randomFormula);
        }
        data.push(rowData);
      }
      targetRange.formulas = data;
      currentWorksheet.autoFilter.remove();
      currentWorksheet.autoFilter.apply(currentWorksheet.getUsedRange(), 0, {
        filterOn: Excel.FilterOn.custom,
        criterion1: "<50",
      });
      const visibleView = currentWorksheet.getUsedRange().getVisibleView();
      visibleView.load(["cellAddresses"]);
      await context.sync();
      currentWorksheet.getRanges(visibleView.cellAddresses.toString());
      await context.sync();
    });
  } catch (error) {
    console.error(error);
  }
}

Also asked on GitHub Discussions: https://github.com/OfficeDev/office-js/discussions/4240