The problem and initial research
So the task – I was working on the complex PCF control of Dataset type for my customer that included a bunch of different operations and one of the requirements was to perform the export to Excel of the data shown in the grid. I didn’t want to reinvent the wheel and create something complex when Model Driven Apps can do it natively. So I went to ChatGPT and Search engines for help. ChatGPT was hallucinating (that was not a surprise for me), The Search engine provided a few resources:
- quite an old post from brilliant Natraj (subscribe to his channels if you haven’t done it yet) – https://dreamingincrm.com/2016/11/10/export-to-excel-using-dynamics-365-sdk/ where he shares the .Net version of the code and states that there is still no WebApi option. But since the post was written back in 2016 I assumed that there should be a way to make it work WebApi way nowadays
- post from Ludovic – https://ludovicperrichon.com/export-to-excel-dataverse-table-with-power-automate/ where he described a way to use ExportToExcel unbound action from PowerAutomate
So I took it as a fact and jumped to the VS Code to make it happen
Implementation
In order to identify the payload as it looks at the moment I went to the MDA I was working in, opened DevTools, and performed the export to see the request/response:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 |
function ExportToExcel() { const fetchXml = "Provide a valid FetchXML query here"; const layoutXml = "Provide a valid LayoutXML here"; const exportToExcelRequest = { View: { "@odata.type": "Microsoft.Dynamics.CRM.savedquery", //Replace this Id with the valid Guid from your system "savedqueryid": "00000000-0000-0000-0000-000000000000" }, FetchXml: fetchXml, LayoutXml: layoutXml, QueryApi: "", QueryParameters: { Arguments: { Count: 0, IsReadOnly: true, Keys: [], Values: [] } }, getMetadata: () => { return { boundParameter: null, parameterTypes: { View: { typeName: "mscrm.savedquery", structuralProperty: 5 }, FetchXml: { typeName: "Edm.String", structuralProperty: 1 }, LayoutXml: { typeName: "Edm.String", structuralProperty: 1 }, QueryApi: { typeName: "Edm.String", structuralProperty: 1 }, QueryParameters: { typeName: "mscrm.QueryParameters", structuralProperty: 5 } }, operationType: 0, operationName: "ExportToExcel" }; } }; const exportToExcelResult = await Xrm.WebApi.online.execute(exportToExcelRequest); const exportToExcelData = await exportToExcelResult.json(); const excelData = exportToExcelData.ExcelFile; const link = document.createElement("a"); link.href = `data:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;base64,${excelData}`; link.download = "Exported File Name.xlsx"; document.body.appendChild(link); link.click(); document.body.removeChild(link); } |
Conclusions
With the provided code I was able to meet the requirement of my customer relying on API provided by a system. Is it fully supported considering the usage of undocumented API and small dom hack allowing a user to download the file – that’s arguable so consider using this approach at your own risk.