🔥 URGENT: Excel corrupts files with Pivot tables based on the output of custom functions which include formatted number values

[wh1t3cAt1k]

Your Environment

• Platform [PC desktop, Mac, iOS, Office on the web]: PC desktop (may also affect Mac on Preview channel!)
• Host [Excel, Word, PowerPoint, etc.]: Excel
• Office version number: Version 2408 (build 17928.20156)
• Operating System: Windows

Steps to reproduce

1. Create a new Excel file
2. Open script lab, create a new snippet and paste the formula:

/**
 * Outputs some data.
 * @customfunction
 * @returns Some data.
 */
function outputData(): any[][] {
  return [
    ["Name", "Date"],
    ["Mike", "2024-01-01"],
    [
      "John",
      {
        type: "FormattedNumber",
        basicValue: 42000,
        numberFormat: "yyyy-mm-dd hh:mm:ss"
      }
    ]
  ];
}

3. Click on Register
4. Type the =outputData() formula into any cell

5. Create a Pivot Table with the output of the previous step. Ensure the header row is selected. Add some fields to the pivot table.

6. Save and close the file.
7. Re-open the file.

Actual result:

Recovery process removes the pivot table (!):

If you replace the FNV in the code snippet with any primitive value, repeat the process and see how the issue does not reproduce.

Expected result:

Pivot tables should work just fine with FNVs...

Context

This issue is a support volume driver for Velixo and does not have any recovery. This started happening relatively recently.

We currently had to disable all FNV support in our apps to prevent being overwhelmed with support requests. Please fix this soon!

Sample of the corrupted file

Repro (1).xlsx

[shanshanzheng-dev]

Hi @wh1t3cAt1k Thanks for reporting this issue.
I'm trying to repro this issue, when I register this function and insert pivot Table like this. And then I save and close this file and re-open. The issue is not repro from my side. (I can see the error when I open the document that provided by you)

My Excel version is:

Not sure if you can try to update a new version. And meanwhile, we'll actively investigate and report back if we have a suggestion for you. Thanks for your patience.

[wh1t3cAt1k]

@shanshanzheng-dev perhaps you're not selecting the header row when creating the pivot table.
On our side, if we don't select it, the issue does not repro either. But we I do, it does.

I will update the issue description.

[shanshanzheng-dev]

Thanks @wh1t3cAt1k we're actively investigating the issue.

[jgamboavx]

The issue is reproducible to me on windows on the current channel:

And in MacOs on the preview channel:

[XuanZhouMSFT]

@wh1t3cAt1k @jgamboavx Thanks for your information.
Unfortunately, after confirming selecting the header row and sync to the same office version as you provided, we still can't reproduce in our place.
We did trying both in MAC version 16.89.1 (24091630) and Windows version 2408 17928.20156, and try it in several different machines and accounts, but still not. Please see the attached video to see if there's still some action is wrong.
And, is it possible to provide the video from your side for us to see if there're some action different? Thanks in advance.

MACNotRepro.mov

cf-issue.mp4

[jgamboavx]

@XuanZhouMSFT I just retraced the same exact steps as in your Windows recording, and the problem is present:

The error log:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
    <logFileName>error135000_01.xml</logFileName>
    <summary>Errors were detected in file 'C:\Users\djorn\Desktop\Book1.xlsx'</summary>
    <removedFeatures>
        <removedFeature>Removed Feature: PivotTable report from
            /xl/pivotCache/pivotCacheDefinition1.xml part (PivotTable cache)</removedFeature>
        <removedFeature>Removed Feature: PivotTable report from /xl/pivotTables/pivotTable1.xml part
            (PivotTable view)</removedFeature>
    </removedFeatures>
    <removedRecords>
        <removedRecord>Removed Records: Workbook properties from /xl/workbook.xml part (Workbook)</removedRecord>
    </removedRecords>
</recoveryLog>

As a sanity check, I also cleared the addin cache, with the same result.

---

The only other difference I can see is the product version.
You're using Microsoft 365 Apps for enterprise, whereas I'm on Microsoft 365 Apps for business.

Given that there are differences in Office.js's behavior when used with regular O356 licenses versus a Office 2021 perpetual license, could the difference in versions here explain why the issue does not reproduce for you?

Here's a video recording of me retracing your steps:

repro.mp4

[XuanZhouMSFT]

Thanks @jgamboavx for the quick response.
I also didn't see any action difference between you and me. I will try to get a Microsoft 365 Apps for business account to have a test.

Meanwhile, could you help to double check from your place with some other steps to see if the issue still repro.

1. Typing =outputData() in any cell to get the dynamic data.
2. Copy the output data and paste only with values in other cells.
3. Insert the pivot table from the only copied value cells
4. save, close and re-open the file to see if it still fail..

We want to know if the issue is only happens with formula or also repros for other FormattedNumber. Thanks in advance.

[wh1t3cAt1k]

@XuanZhouMSFT do you guys also use a different (compliant) CDN for the Office.js bundle? If yes, maybe it's important to use exactly the same Office.js library as us (from the usual CDN).

[jgamboavx]

Meanwhile, could you help to double check from your place with some other steps to see if the issue still repro.

1. Typing =outputData() in any cell to get the dynamic data.
2. Copy the output data and paste only with values in other cells.
3. Insert the pivot table from the only copied value cells
4. save, close and re-open the file to see if it still fail..

We want to know if the issue is only happens with formula or also repros for other FormattedNumber. Thanks in advance.

@XuanZhouMSFT ,
The issue also reproduces after creating the pivot table directly from values.

repro2.mp4

[XuanZhouMSFT]

@jgamboavx Thanks for the testing. So it seems doesn't related to CF rich data but related to pivot table and maybe FormattedNumber. We will help to re-path the issue internally to further investigation.

@wh1t3cAt1k We already confirmed that it doesn't caused by CF side, so the CDN should not be a problem. Thanks also for the remaindering. :)

[wh1t3cAt1k]

@XuanZhouMSFT could it also depend on the regional settings of the OS? @jgamboavx please let us know what the settings are on your machine (digits separator, date format etc.)

[jgamboavx]

My regional settings are as follows:

[XuanZhouMSFT]

I synced my setting to be same as @jgamboavx but still fail to reproduce.

To double check for these settings, are we still same?

[jgamboavx]

@XuanZhouMSFT not the same in this menu. Mine are:

However, the issue still reproduces after changing my settings to match yours. So it's likely not related to regional settings

[XuanZhouMSFT]

Agree with you @jgamboavx , after sync to your version, it also couldn't reproduce.

[wh1t3cAt1k]

@XuanZhouMSFT is there any other team member on your side who can try to replicate?

[XuanZhouMSFT]

@wh1t3cAt1k I already asked several members for some help on this case. And we event created several test account to test. But until now, none of us could able to reproduce. :(

[wh1t3cAt1k]

@XuanZhouMSFT how can we help? We can do a joint call with @jgamboavx if you are able to remotely collect data for troubleshooting? It's not just us, it's our customers complaining too, so clearly it's not just an issue with a developer's machine...

[XuanZhouMSFT]

@wh1t3cAt1k How about we have a debug session after tomorrow sync meeting? It is already mid-night for us. Perhaps not all of us could attend the meeting now.

[wh1t3cAt1k]

Yes please shoot us an invite, you know my email!