Excel supports nested arrays in cards / rich data field (A1.myArray)
Excel allows for arrays and other compound data inside of cards:
Using excel API method valuesAsJson we can create custom cards.
To get the above, open a new blank sample in scriptlab and replace the script text with the following:
document.getElementById("run").addEventListener("click", () => tryCatch(run)); async function run() { await Excel.run(async (context) => { context.workbook.worksheets.getActiveWorksheet().getRange("B2").valuesAsJson = [[{type: "Entity", text: "MyPoint", properties: { values: {type: "Array", elements: [[ {type: "Double", basicValue: 1 }, {type: "Double", basicValue: 2 }, {type: "Entity", text: "Array", properties:{ values: {type: "Array", elements: [[ {type: "Double", basicValue: 4 }, {type: "Double", basicValue: 5 }, {type: "Double", basicValue: 6 }]]}}} ]]}}}]]; await context.sync(); }); } async function tryCatch(callback) { try { await callback(); } catch (error) { console.error(error); } } There are 3 types of cards / rich data:
- Linked entity, for example stocks, currencies & other options in Data tab.
- Entity (I assume these are the ones added to the worksheet from power query).
- Basic types, which is the example above.
Basic types behave as expected in functions, their properties / metadata does not influence functions.
All cards may have nested cards (entities), but with basic types I couldn't access nested cards, I didn't exhaust testing though.
The valuesAsJson method is not in office script, and VBA has no methods for creating cards.
Initially my goal was to return an array from power query entity, but it returns as a string "[List]", "[Record]" or "[Table]", and images are returned as "[Binary]", even though we know excel does support nesting an array and an image inside of a card.
It would be powerful if we can do it with power query.
One way around this is to turn nested list into text "{1,2,3}" then use EVALUATE or TEXTSPLIT.
To use Evaluate, you will have to add it to the name manager first: Lambda(x,Evaluate(x))
But this isn't optimal, since excel supports nesting arrays.
What if we can create these cards using worksheet functions? Gemini once hallucinated and suggested such functions:
Disclaimer, I am not familiar enough with office or excel typescript API, the info was gathered from the following documentation:
https://learn.microsoft.com/en-us/office/dev/add-ins/excel/excel-data-types-entity-card#card-properties
https://learn.microsoft.com/en-us/office/dev/add-ins/excel/excel-data-types-add-properties-to-basic-cell-values
https://learn.microsoft.com/en-us/javascript/api/excel/excel.cellvaluetype?view=excel-js-preview#excel-excel-cellvaluetype-linkedentity-member
https://learn.microsoft.com/en-us/javascript/api/excel/excel.linkedentitycellvalue?view=excel-js-preview
The documentation of cell type includes function, my basic attempt to nest it inside of a card failed:
https://learn.microsoft.com/en-us/javascript/api/excel/excel.functioncellvaluetype?view=excel-js-preview#excel-excel-functioncellvaluetype-javascriptreference-member
Microsoft has a video on how to create an entity card in power query:
https://support.microsoft.com/en-us/office/create-a-data-type-power-query-a465a3b7-3d37-4eb1-a59c-bd3163315308
[link] [comments]
Want to read more?
Check out the full article on the original site