Excel: valuesAsJson are truncated at 255 characters

[wh1t3cAt1k]

valuesAsJson's basicValue length is truncated to 255 characters, making it inconsistent with range.values. As a result, we need to load both, leading to performance impact.

Your Environment

• Platform [PC desktop, Mac, iOS, Office on the web]: PC Desktop
• Host [Excel, Word, PowerPoint, etc.]: Excel
• Office version number: Microsoft® Excel® for Microsoft 365 MSO (Version 2411 Build 16.0.18217.20000) 64-bit
• Operating System: Windows 11

Current behavior

Longer strings are truncated:

Expected behavior

There should be the same limit as for values.

Steps to reproduce

Download the file:

Book 218.xlsx

Select cell A1 to make it the active cell.

Execute the ScriptLab snippet at the bottom and click the Run button:

You can see clear difference that valuesAsJson are truncated.

Context

Our client uses Office.js API to upload long product descriptions from Excel back to their ERP system. We relied on valuesAsJson to handle errors and custom data types, however, it turns out this API is unreliable because it truncates the strings at 255 symbols.

ScriptLab snippet

name: Blank snippet (1)
description: Create a new snippet from a blank template.
host: EXCEL
api_set: {}
script:
  content: |
    $("#run").on("click", () => tryCatch(run));

    async function run() {
      await Excel.run(async (context) => {
        const cell = context.workbook.getSelectedRange();

        cell.load({ valuesAsJson: true, values: true });

        await context.sync();

        const a1 = cell.valuesAsJson[0][0].basicValue as string;
        const a2 = cell.values[0][0] as string;

        console.log(a1.length, a2.length);
      });
    }

    /** Default helper for invoking an action and handling errors. */
    async function tryCatch(callback) {
      try {
        await callback();
      } catch (error) {
        // Note: In a production add-in, you'd want to notify the user through your add-in's UI.
        console.error(error);
      }
    }
  language: typescript
template:
  content: |
    <button id="run" class="ms-Button">
        <span class="ms-Button-label">Run</span>
    </button>
  language: html
style:
  content: |-
    section.samples {
        margin-top: 20px;
    }

    section.samples .ms-Button, section.setup .ms-Button {
        display: block;
        margin-bottom: 5px;
        margin-left: 20px;
        min-width: 80px;
    }
  language: css
libraries: |
  https://appsforoffice.microsoft.com/lib/1/hosted/office.js
  @types/office-js

  office-ui-fabric-js@1.4.0/dist/css/fabric.min.css
  office-ui-fabric-js@1.4.0/dist/css/fabric.components.min.css

  core-js@2.4.1/client/core.min.js
  @types/core-js

  jquery@3.1.1
  @types/jquery@3.3.1

Internal Velixo item

[microsoft-github-policy-service[bot]]

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

[RuizhiSunMS]

Thank you for reporting this issue, I can repro it. It has been put on our backlog #9491621, the team will investigate and we will reply to you as soon as there is any progress. Thank you for your patience.