Range references stored in worksheet.names are corrupted on first use of add-in upon install

[rgallagherab]

While performing internal testing of a recently published Excel add-in we discovered a case where ranges stored in worksheet.names.items will be corrupted once worksheet.name is set. This issue specifically occurs on the first usage of the add-in after installation.

When installing a new add-in from the AppSource Marketplace an XLSX document is opened from office.com. For example, when installing Script Lab there is a button that links to ms-excel:https://api.addins.store.office.com/addinstemplate/en-US/608681f0-c758-4481-81ce-306f9dd4f3f1/WA104380862/none/Script-Lab--a-Microsoft-Garage-project.xlsx?omexsrctype=1. The issue described here only occurs when this initial document is opened. Closing the document or doing "Save As" before performing these actions works as expected.

Your Environment

• Platform [PC desktop, Mac, iOS, Office on the web]: Issue is confirmed both on Mac and Windows desktop
• Host [Excel, Word, PowerPoint, etc.]: Excel
• Office version number: 16.84 (24041420)
• Operating System: MacOS Sonoma and Windows 11
• Browser (if using Office on the web): N/A

Expected behavior

Ranges remain valid when references are stored in worksheet.names on first usage of add-in when installing from AppSource Marketplace.

Current behavior

As noted above, this issue only happens on first usage of a newly installed add-in. That means that the following sequence of events is required as a precursor:

1. Start with the add-in not installed in the Excel desktop application.
2. Visit the page for the add-in in the AppSource Marketplace.
3. Click the "Get it now" button.
4. On the next page click the "Open in Excel" button.
5. An XLSX file will be downloaded from Microsoft's servers and opened in Excel with a right task tray alerting you about the new add-in to be installed.
6. Click the button to allow or trust the add-in to complete the installation.
7. Setting the worksheet name through the JS API now causes the worksheet to be corrupted in some way so that range references stored in worksheet.names are bad.

If the document is closed and a new one opened, the issue will no longer be present. It only occurs in this initial load that the installation is bootstrapped to.

Steps to reproduce

The issue has been confirmed on installation of several different add-ins, but repro steps will use the Script Lab add-in since it will be used to run the code that demonstrates the issue.

1. Ensure Script Lab is not currently installed in Excel.
2. Trigger installation by clicking the "Open in Excel" button here: https://pages.store.office.com/addinsinstallpage.aspx?assetid=WA104380862&rs=en-US&correlationId=31115a0f-1e7b-f119-676a-65d5765ab49d
3. When the downloaded XLSX file opens in Excel, click the "Allow and Continue" button in the taskpane to complete the installation. To demonstrate the issue the remaining steps must be completed in this initial document that was loaded from the internet.
4. Open the Script Lab taskpane, click the menu icon, go to "Import", and import the following gist: https://gist.github.com/rgallagherab/20440e358fb25034c5c77f94f350ff80
5. Run the snippet.
6. Click the "Configure Sheet" button. This will set the worksheet name to "Worksheet 1", add text to three cells at the top of the document, and save those ranges (A1, B1, C1) in worksheet.names.
7. Click the "Display Worksheet Names" button to log out the details about the stored ranges.

After completing the above steps the following will occur:
An error will be thrown when attempting to freeze the header row of the sheet. This appears to in some way be caused by the bad references stored on worksheet.names:

{
  "name": "RichApi.Error",
  "code": "GeneralException",
  "traceMessages": [],
  "innerError": null,
  "debugInfo": {
    "code": "GeneralException",
    "message": "There was an internal error while processing the request.",
    "errorLocation": "WorksheetFreezePanes.freezeRows",
    "statement": "freezePanes.freezeRows(...);",
    "surroundingStatements": [
      "var workbook = context.workbook;",
      "var worksheets = workbook.worksheets;",
      "var worksheet = worksheets.getItem(...) /* originally getActiveWorksheet() */;",
      "var freezePanes = worksheet.freezePanes;",
      "// >>>>>",
      "freezePanes.freezeRows(...);",
      "// <<<<<"
    ],
    "fullStatements": [
      "var workbook = context.workbook;",
      "var worksheets = workbook.worksheets;",
      "var worksheet = worksheets.getItem(\"{19D9D9D4-2EFA-D346-AD67-1327B5923F46}\") /* originally getActiveWorksheet() */;",
      "var freezePanes = worksheet.freezePanes;",
      "freezePanes.freezeRows(1);"
    ]
  },
  "httpStatusCode": 500
}

After clicking "Display Worksheet Names" another console.log will output the bad data I believe is the source of the problem here:

worksheet.names:
{
  "items": [
    {
      "comment": "",
      "formula": "='Worksheet 1'!$A$1",
      "name": "named.range",
      "scope": "Worksheet",
      "type": "Range",
      "value": "'Worksheet 1'!$A$1",
      "visible": true
    },
    {
      "comment": "",
      "formula": "='Worksheet 1'!$B$1",
      "name": "named.range2",
      "scope": "Worksheet",
      "type": "Error",
      "value": "#REF!",
      "visible": true
    },
    {
      "comment": "",
      "formula": "='Worksheet 1'!$C$1",
      "name": "named.range3",
      "scope": "Worksheet",
      "type": "Error",
      "value": "#REF!",
      "visible": true
    }
  ]
}

Note here that the first item shows "type": "Range" and "value": "'Worksheet 1'!$A$1"(a valid range in the worksheet). The second and third items both show "type": "Error" and "value": "#REF!" since they were added after worksheet.name is set.

The sequence executed when clicking "Configure Sheet" in the included Script Lab script is as follows:

1. Configure A Column Header - Set header values and add on worksheet.names for A1 (this range is stored correctly)
2. Set Worksheet Name - Set worksheet.name to "Worksheet 1"
3. Configure A Column Header - Set header values and add on worksheet.names for B1
4. Configure A Column Header - Set header values and add on worksheet.names for C1
5. Freeze Header Row - Use freezeRows to freeze row 1

Link to live example(s)

Live example can not be provided as this only occurs on installation of a new add-in.

Context

Our add-in uses these named ranges to open a dialog when cells in certain columns are selected. This allows users to interact with our application's API to populate cells with data from the product's database. Since these are associated with bad range references this makes the add-in unusable immediately upon installation.

At this time our only idea is to insert a warning into the workbook that informs the user they must close this document and create a new one in order to start using the add-in which is a poor first impression.

Additional Information

Issues can also be observed when attempting to call worksheet.activate. Calling worksheet.activate before the name is set works as expected, but doing so after setting worksheet.name results in a very nondescript "GeneralException: There was an internal error while processing the request." As with the other issues this only occurs on the initial document load from the "Open in Excel" link on the AppSource Marketplace.

Useful logs

Log information is found above in Steps to Reproduce.

[rgallagherab]

After looking more closely at this repo, I understand that this won't fix my use case since this is separate from the MS CDN hosted office.js. I'm really not sure how best to get the issue brought to their attention, but I guess I'll need to dig deeper on that.

[rgallagherab]

One other thing of note is that when we extracted the contents of the XLSX file we noticed that the definedNames entries are stored with node contents like [1]Controls!$A$1 instead of the normal value Controls!$A$1 (where the sheet name is Controls).

[shanshanzheng-dev]

Hi @XuanZhouMSFT @SiruiSun-MSFT Could you help take a look this issue? Thanks.

[SiruiSun-MSFT]

@rgallagherab, Thanks for your reporting! We could repro the issue with the steps you provided. And the root cause is under investigation. Will share more information when we get any. Thanks for your patience!