Custom Properties Truncated in Excel 16.85 Using Office-JS API

[ab-mwright]

Custom properties in Excel are being truncated when retrieved using the office-js API in newer versions of Excel (16.85). The same implementation works correctly in older versions (16.16.27), returning the full property values.

Environment

• Platform: Mac, PC desktop
• Host: Excel
• Office version number: 16.85 (problematic), 16.16.27 (works correctly)
• Operating System: macOS, Windows
• Browser (if using Office on the web): N/A

Expected behavior

Custom property values should be returned in full, consistent with the behavior observed in Excel 16.16.27, with each chunk up to 250 characters.

Current behavior

In Excel version 16.85, custom properties are being truncated to around 127 characters when retrieved using the office-js API, whereas in Excel version 16.16.27, the full property values (up to 250 characters) are returned correctly.

Steps to reproduce

1. Split a large custom property value into chunks of 250 characters and store each chunk as a separate custom property in Excel.
2. Retrieve the custom properties and reassemble the chunks into the original value.
3. Compare the results between Excel 16.85 and Excel 16.16.27.

Context

We need to store and retrieve large custom properties. To handle large values, we split the properties into smaller chunks (each with a maximum length of 250 characters) and reassemble them when retrieving.

Code Example

class WorkbookCustomPropertyService {
    context: Excel.RequestContext;
    private customPropertyCollection: Excel.CustomPropertyCollection;
    private customProperties: Excel.CustomProperty[] = [];
    private maxLength = 250;

    constructor(context: Excel.RequestContext) {
        this.context = context;
    }

    // Initialize the custom property collection
    async init() {
        this.customPropertyCollection = this.context.workbook.properties.custom;
        this.customPropertyCollection.load('items');
        await this.context.sync();

        for (const property of this.customPropertyCollection.items) {
            const loaded = this.customPropertyCollection.getItem(property.key);
            loaded.load('key,value');
            this.customProperties.push(loaded);
            try {
                await this.context.sync();
            } catch (err) {
                console.error(`Error syncing custom property ${property.key}:`, err);
            }
        }
    }

    // Add a custom property, splitting it into chunks if necessary
    addCustomProperty(key: string, value: string) {
        if (value.length > this.maxLength) {
            const chunks = value.match(/.{1,250}/g);
            chunks.forEach((chunk, index) => {
                const chunkKey = `${key}_${index + 1}`;
                this.customPropertyCollection.add(chunkKey, chunk);
            });
        } else {
            this.customPropertyCollection.add(key, value);
        }
    }

    // Reassemble the custom property from its chunks
    buildWorkbookCustomProperty(key: string): { key: string, value: string } | null {
        const components = this.findPropertyComponents(key);

        if (components.length > 1) {
            components.sort((a, b) => {
                const indexA = parseInt(a.key.split('_').pop());
                const indexB = parseInt(b.key.split('_').pop());
                return indexA - indexB;
            });

            const value = components.map(c => c.value).join('');
            return { key, value };
        } else if (components.length === 1) {
            return { key: components[0].key, value: components[0].value };
        } else {
            return null;
        }
    }
}

[shanshanzheng-dev]

Hi @ab-mwright We'll be looking into this problem, thanks for reporting! we will report back here if we have a suggestion for you.

[penglongzhaochina]

could you please give me the value you added into the customproperty?

[karthous]

I encountered the same issue with Version 2405 (Build 17628.20110). The values include "uuid", "len", and "list1". It works well in the previous versions and the Office 365 Web version.

OS: Windows 11,
Host: Excel

[ab-mwright]

@penglongzhaochina,

Full Value Before Splitting (610 characters total)

{"id":"e826ab7c18f567c37feff4844b74c903","linkStatus":false,"targetType":"xlsxsheets","targetHash":4518,"targetName":"Property - ABC - Defg and Hijklm Example Procedures - 2023 -","title":"Property - ABC - Defg and Hijklm Example Procedures - 2023 -","targetUrl":"https://example.com/dummy?xlsxsheet_id=4518&name=Property%20-%20ABC%20-%20Defg%20and%20Hijklm%20Example%20Procedure%20-%202023%20-ExtraPadding%20-","targetAddress":"Reference!B6","targetTextSelection":"Reference!B6","sameFile":false,"sourceAddress":"Example Procedures'!D4","sourceTextSelection":"","targetHashCode":"WP#4518","targetFileId":7469}

Custom Properties Stored:

Custom Property demolink_1 (250 characters):

{"id":"e826ab7c18f567c37feff4844b74c903","linkStatus":false,"targetType":"xlsxsheets","targetHash":4518,"targetName":"Property - ABC - Defg and Hijklm Example Procedures - 2023 -","title":"Property - ABC - Defg and Hijklm Example Procedures - 2023 -"

Custom Property demolink_2 (250 characters):

,"targetUrl":"https://example.com/dummy?xlsxsheet_id=4518&name=Property%20-%20ABC%20-%20Defg%20and%20Hijklm%20Example%20Procedure%20-%202023%20-ExtraPadding%20-","targetAddress":"Reference!B6","targetTextSelection":"Reference!B6","sameFile":false,"so

Custom Property demolink_3 (110 characters):

urceAddress":"Example Procedures'!D4","sourceTextSelection":"","targetHashCode":"WP#4518","targetFileId":7469}

Custom Properties Retrieved Using Office-JS

Excel.run(async (context) => {
    this.customPropertyCollection = this.context.workbook.properties.custom;
    // Retrieval logic...
});

Custom Property demolink_1 (truncated to 127 characters):

{"id":"e826ab7c18f567c37feff4844b74c903","linkStatus":false,"targetType":"xlsxsheets","targetHash":4518,"targetName":"Property

Custom Property demolink_2 (truncated to 126 characters):

,"targetUrl":"https://example.com/dummy?xlsxsheet_id=4518&name=Property%20-%20ABC%20-%20Defg%20and%20Hijklm%20Example%20Proced

Custom Property demolink_3 (returned in full, 110 characters):

urceAddress":"Example Procedures'!D4","sourceTextSelection":"","targetHashCode":"WP#4518","targetFileId":7469}

Reassembled Value (Broken JSON)

{"id":"e826ab7c18f567c37feff4844b74c903","linkStatus":false,"targetType":"xlsxsheets","targetHash":4518,"targetName":"Property","targetUrl":"https://example.com/dummy?xlsxsheet_id=4518&name=Property%20-%20ABC%20-%20Defg%20and%20Hijklm%20Example%20ProcedurceAddress":"Example Procedures'!D4","sourceTextSelection":"","targetHashCode":"WP#4518","targetFileId":7469}

[penglongzhaochina]

We are woking on it. Will let you know once we have any progress.

[kylenakano]

Here is a code snippet that should help:

const someString = Array(256).join('x');
const someProperty = 'blah';

console.log(someString.length); // 255

this.customPropertyCollection = this.context.workbook.properties.custom;
this.customPropertyCollection.add(someProperty, someString);
await this.context.sync();
this.context.load(this.customPropertyCollection);

const prop = this.customPropertyCollection.getItem(someProperty);
prop.load('key,value');
await this.context.sync();

console.log(prop.value.length); // 126

[penglongzhaochina]

Yes, we are actively working it, will mitigate this issue as soon as possible.

[penglongzhaochina]

Hi @karthous @kylenakano @ab-mwright We have fixed this issue, could you please have a try on your side? Thank you.

[karthous]

Hi @karthous @kylenakano @ab-mwright We have fixed this issue, could you please have a try on your side? Thank you.

It is functioning correctly now. Thank you.