search

OfficeDev / office-js

A repo and NPM package for Office.js, corresponding to a copy of what gets published to the official "evergreen" Office.js CDN, at https://appsforoffice.microsoft.com/lib/1/hosted/office.js.
https://learn.microsoft.com/javascript/api/overview
Other
687 stars 94 forks source link

getDirectPrecedents return unexpected result in Excel Online #3380

Open FullStackEngN opened 1 year ago

FullStackEngN commented 1 year ago

I had a formula(AVERAGE(Sheet2!A1:Sheet2!F2)) in Sheet1, then the getDirectPrecedents return "Sheet1!A1:F2, Sheet2!A1 and Sheet2!F2" for the cell, the "Sheet1!A1:F2" is not right response. But the issue only happen in Excel Online, it returns right response in Excel Desktop.

Expected Behavior

It should not return "Sheet1!A1:F2".

Current Behavior

The result contains "Sheet1!A1:F2".

Steps to Reproduce, or Live Example

  1. Type some data in Sheet2!A1:F2.
  2. In Sheet1 cell A2, enter formula: =AVERAGE(Sheet2!A1:Sheet2!F2)

  3. Use code get the A2 direct precedents. ` var range = sheet.getRange("A2"); var precedents = range.getPrecedents(); var directPrecedents = range.getDirectPrecedents();

    range.load("address"); precedents.areas.load("address"); directPrecedents.areas.load("address");

    await context.sync();

    console.log(All precedent cell of ${range.address}:);

    // Use the precedents API to loop through all precedents of the active cell. for (let i = 0; i < precedents.areas.items.length; i++) { // Highlight and print out the address of all precedent cells. precedents.areas.items[i].format.fill.color = "Orange"; console.log(${precedents.areas.items[i].address}); }

    console.log(Direct precedent cells of ${range.address}:);

    // Use the direct precedents API to loop through direct precedents of the active cell. for (let i = 0; i < directPrecedents.areas.items.length; i++) { // Highlight and print out the address of each direct precedent cell. directPrecedents.areas.items[i].format.fill.color = "Yellow"; console.log(${directPrecedents.areas.items[i].address}); }`

  4. Run the code in Excel Online, I use "Script Lab" test my code, and found it returns "Sheet1!A1:F1" which is not right in the Excel Online.
  5. I found if I change the formula to "=AVERAGE(Sheet2!A1:F2)", then the response seems like OK.
  6. The issue happened on getPrecedents() and getDirectPrecedents().

Context

Your Environment

Useful logs

ghost commented 1 year ago

Thank you for letting us know about this issue. We will take a look shortly. Thanks.

honxmsft commented 1 year ago

Hi @FullStackEngN we created a work item internally to track this bug. Currently, writing the fomula as =AVERAGE(Sheet2!A1:F2) should be a workaround for this case.

FullStackEngN commented 1 year ago

Thanks @honxmsft , I have already provided the customer with the workaround. However, they mentioned that the formula is defined by a user, which is beyond their control. The user who utilizes 'getDirectPrecedents' and the user responsible for the formula belong to different groups, making the workaround challenging for them. The customer is eagerly awaiting a fix for this issue.