Accessing a nested 2d array for a custom datatype within the same cell as the custom datatype only returns the first cell rather than the whole array.

[evanherbert]

We've created a custom function which returns a custom data type. Some of the fields that are returned are 2 dimensional arrays. These are fine when accessing the field from a separate cell that links back to a cell that just has the custom datatype. However when accessing the fields using dot notation in the same cell as the custom data type only the first element in the array is returned.

Your Environment

• Platform [PC desktop, Mac, iOS, Office on the web]: Office on the web.
• Host [Excel, Word, PowerPoint, etc.]: Excel
• Office version number: Build number 16.0.19512.42301
• Operating System: Windows 10
• Browser (if using Office on the web): Edge (142.0.3595.80)

Note the above are are the current settings used in our development environment but the issue is also confirmed to exist in production which uses both office on the web and pc desktop.

Expected behavior

When accessing a field of a custom datatype using the syntax

=CUSTOMNAMESPACE.CUSTOMDATATYPE().[2-dimensional-array-field]

I would expect the result to be the 2 dimensional array of data if the field itself returns a 2 dimensional array of data.

Current behavior

At the moment only the first element in the range is getting returned.

Note if I have =CUSTOMNAMESPACE.CUSTOMDATATYPE() in cell A1 for example and do
A1.[2-dimensional-array-field] I correctly get the 2 dimensional array returned.

Steps to reproduce

1. Define a custom function which returns a custom datatype where some of the fields are 2 dimensional arrays
2. In a single cell, try to call the custom function and access one of these 2 dimensional array fields with the syntax
   =CUSTOMNAMESPACE.CUSTOMDATATYPE().[2-dimensional-array-field]

Link to live example(s)

Note I was having issues getting scriptlab working even with the builtin samples so code snippet used to generate the results in the screenshots shared below (The actual custom datatype includes a lot more fields)

/**

• Get the custom data type
• @customfunction DATA_TYPE
• @param {CustomFunctions.Invocation} invocation Invocation object.
• @returns {any[][]}
  */
  export async function dataTypeFunction() {
  const exampleData = {
  Id: "Example1",
  companyId: "1",
  companyName: "Example Company",
  };
  return formatDataType(exampleData);
  }

function formatDataType(exampleData) {
const entity = {
type: Excel.CellValueType.entity,
text: exampleData["Id"],
properties: {
"Id": {
type: Excel.CellValueType.string,
basicValue: exampleData["Id"],
},
"Nested Table": getNestedTable(exampleData),
},
};

return [[entity]];

}
//returns a 2d array where the first column is the labels
//and the second column is the values (or a single string if the Id isn't yet set)
function getNestedTable(exampleData) {
if (exampleData["companyId"] == "Id not set") {
return {
type: Excel.CellValueType.string,
basicValue: "Company Id not yet set",
};
//arbitrary string to indicate that the data in the workbook has not been set
} else {
return [
["CompanyId", exampleData["companyId"]],
["Company Name", exampleData["companyName"]],
];
}
}

Provide additional details

Nb. haven't included custom metadata and custom function associating logic above but can add if needed.

Context

At present our app provides a number of custom formulas with various arguments that users can insert directly into their excel workbook from a taskpane.
One of our custom formulas is a custom datatype. Most of the fields for this datatype are either single values or single cell custom datatypes themselves, however a handful of the fields are 2-dimensional arrays.
When a user tries to insert this function it is inserted in the form

=CUSTOMNAMESPACE.CUSTOMDATATYPE().[fieldname]

They reasonably expect that this should return all the data for each of the available fields but for fields that return 2-d arrays it is only returning the first element, which is causing friction.

Note I'm not sure if it's related to #5044

Useful logs

• Console errors
  N/A No console errors

• Screenshots

Screenshot taken from a simplified version of the

• Test file (if only happens on a particular file)
  N/A happens on all files

Thank you for taking the time to report an issue. Our triage team will respond to you in less than 72 hours. Normally, response time is <10 hours Monday through Friday. We do not triage on weekends.

[microsoft-github-policy-service]

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

[github-actions]

Regression Analysis Needs Your Input

Our automated system analyzed this issue but couldn't determine with confidence if this is a regression bug.

A regression bug is when functionality that previously worked properly no longer works after a recent change.

If you believe this is a regression issue, please confirm below:

• Yes, confirm this is a regression issue

Note: Once confirmed, the issue will be labeled as regression.
After selecting the option, your choice will be automatically processed.

---

[github-actions]

Here are some similar issues that might help you. Please check if they can solve your problem.

• array formulas as parameter for custom function not working as expected in Excel Online #580

---

Possible solution (Extracted from existing issue, might be incorrect; please verify carefully)

The parameters in the custom function should be defined as matrices to handle dynamic arrays properly. Update the function definition to use any[][] for parameters that return dynamic arrays. For example:

function data(connection: string, cube: string, el0: any[][], el1: any[][], el2: any[][], el3: any[][], el4: any[][], el5: any[][])

This approach ensures that the function works correctly with dynamic arrays in Excel Online.

Reference:

• array formulas as parameter for custom function not working as expected in Excel Online #580 (comment)
• array formulas as parameter for custom function not working as expected in Excel Online #580 (comment)

Powered by issue-sentinel

[AmandaHan123]

Hi @evanherbert , I am able to repro the issue in GitHub with a little change on your code. I filed a MS internal bug #10827078 to track. We will update you here when there is any progress.

/**

• Get the custom data type
• @customfunction DATA_TYPE
• @returns {any[][]}
  */
  async function dataTypeFunction() {
  const exampleData = {
  Id: "Example1",
  companyId: "1",
  companyName: "Example Company",
  };
  return formatDataType(exampleData);
  }

function formatDataType(exampleData) {
const entity = {
type: "Entity",
text: exampleData["Id"],
properties: {
Id: {
type: "String",
basicValue: exampleData["Id"],
},
"Nested Table": getNestedTable(exampleData),
},
};

return [[entity]];
}
//returns a 2d array where the first column is the labels
//and the second column is the values (or a single string if the Id isn't yet set)
function getNestedTable(exampleData) {
if (exampleData["companyId"] == "Id not set") {
return {
type: "String",
basicValue: "Company Id not yet set",
};
//arbitrary string to indicate that the data in the workbook has not been set
} else {
return [
["CompanyId", exampleData["companyId"]],
["Company Name", exampleData["companyName"]],
];
}
}