Reliable way for getting ranges of visible view

Geoffrey van Wyk 6 Reputation points
2024-03-12T04:10:15.13+00:00

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

Excel
Excel
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
1,438 questions
Office Development
Office Development
Office: A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.Development: The process of researching, productizing, and refining new or existing technologies.
3,457 questions
{count} votes