Getting ranges of visible view is unreliable

[geoffreyvanwyk]

Provide required information needed to triage your issue

After filtering a worksheet, I would like to copy the visible cells to another worksheet as shown in the code below.

      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());

Your Environment

• Platform [PC desktop, Mac, iOS, Office on the web]: PC Desktop
• Host [Excel, Word, PowerPoint, etc.]: Excel
• Office version number: Microsoft 365
• Operating System: Windows 10
• Browser (if using Office on the web): ______

Expected behavior

The ranges of the visible view should be gotten without any error, no matter how many cells there are.

Current behavior

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

Steps to reproduce

1. Create a new Office Task Pane add-in using the Yeoman generator yo office.
2. Update the run() function with the one listed below this list in the Provide additional details section.
3. Execute npm run start:desktop.
4. In Excel, in the taskpane, click "Run".
5. Open the development tools by right-clicking in the task pane, then clicking "Inspect".
6. In the development tools window, open the "Console" tab.
7. You will see the error there.

Provide additional details

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

This issue was closed Duplicate

[penglongzhaochina]

Thanks for reporting this issue.
I can reproduce your issue.
It has been put on our backlog<Bug#8791170> for internal track. We will keep track of this issue and let you know if there are any updates.

[penglongzhaochina]

Hi @geoffreyvanwyk ,

the parameter's length of the getRanges exceed the limitation, It's too long to handle it for excel Api.

[geoffreyvanwyk]

Hi @geoffreyvanwyk ,

the parameter's length of the getRanges exceed the limitation, It's too long to handle it for excel Api.

Thanks @penglongzhaochina.

What is the limit on the size?

I wonder why it would only fail 5 times out of 10 for a 1000 rows.

[penglongzhaochina]

Hi, @geoffreyvanwyk
your cell address is too long, which exceed the string buffer Limitation which is 65535. Please divide your cell address into small piece to use.

Due to the number of line are different every time after you run the filter, so sometimes work, sometimes fail.