Discussing how to generate Excel documents in Dynamics 365 CRM using a non-documented “RenderTemplate” request. We also explore its uses in the native CRM environment, while overcoming sandbox isolation mode limitations within HTTP web requests. We also provide JSON format to successfully execute the requests.
Recently, we encountered a unique requirement, we needed to generate an Excel document based on a fetchXML query and an Excel document template within Microsoft Dynamics 365 CRM.
During our exploration, we stumbled upon a particular request that is available in Dynamics 365 CRM, but isn’t officially documented by Microsoft.
Hence, we decided to share this blog post, to assist those who may face similar challenges! We personally invested a significant amount of time in unraveling this process and understood the difficulties it can present.
As we delved into this task, we successfully executed it using the “RenderTemplate” OrganizationRequest, as demonstrated below:
var request = new OrganizationRequest("RenderTemplate");
request.Parameters["Template"] = new EntityReference("documenttemplate", new Guid("E95B0CA6-4730-EE11-BDF4-6045BD47B0C7"));
request.Parameters["FetchXml"] = @"<fetch version='1.0' output-format='xml-platform' mapping='logical' no-lock='false' distinct='true'><entity name='account'><attribute name='entityimage_url'/><attribute name='parentaccountid'/><attribute name='name'/><attribute name='address1_city'/><order attribute='name' descending='false'/><attribute name='primarycontactid'/><attribute name='telephone1'/><attribute name='accountid'/><filter type='and'><condition attribute='ownerid' operator='eq-userid'/><condition attribute='statecode' operator='eq' value='0'/></filter><link-entity alias='accountprimarycontactidcontactcontactid' name='contact' from='contactid' to='primarycontactid' link-type='outer' visible='false'><attribute name='emailaddress1'/></link-entity></entity></fetch>";
request.Parameters["QueryApi"] = "";
request.Parameters["QueryParameters"] = new Microsoft.Crm.Sdk.Messages.InputArgumentCollection();
var response = service.Execute(request);
if (response.Results.Any())
{
File.WriteAllBytes("D:\\Roy_Harper\\Desktop Applications (For Testing)\\Console_App\\ConsoleApp\\ConsoleApp\\All Acts.xlsx", response.Results["ExcelFile"] as byte[]);
}
However, we discovered a limitation! This request could only be used when the assembly is registered in sandbox isolation mode. Given that all requests deployed in Microsoft Dynamics 365 CRM online deployments must adhere to sandbox isolation mode, we couldn’t use this request in such scenarios.
Consequently, we decided to explore an alternative approach using HTTP web requests.
Although we managed to obtain access tokens to execute the request via HTTP Web Request, we further encountered a roadblock related to the “QueryParameters” parameter. No matter how we formatted the value, we consistently encountered a “400 Bad Request” error!
After numerous trials and errors, we finally succeeded in executing the request using the following format:
"{\"Template\": {\"@odata.type\": \"Microsoft.Dynamics.CRM.documenttemplate\",\"documenttemplateid\": \"9f3a0c48-9a3e-ee11-bdf4-6045bd47b0c7\" },\"FetchXml\": \" <fetch version='1.0' mapping='logical'><entity name='new_invoice'><attribute name='new_invoiceid' /><attribute name='new_name' /><attribute name='createdon' /><order attribute='new_name' descending='false' /><filter type='and'><condition attribute='statecode' operator='eq' value='0' /><condition attribute='new_customer' operator='eq' value='df9727ca-3004-ee11-8f6e-6045bd47b0c7'/></filter></entity></fetch>\",\"QueryApi\": \"\",\"QueryParameters\":{\"@odata.type\":\"Microsoft.Dynamics.CRM.InputArgumentCollection\",\"Arguments\":{\"Count\":0}}}";
It’s worth noting that even though “QueryApi” and “QueryParameters” aren’t actively used in our request, they are required parameters for the “RenderTemplate” request.
Just for the reference, below is the information about the parameters:
- Template – Entity Reference of the Excel Template we want to be used.
- FetchXml – The fetchXML we want to use in string format.
- QueryApi – This can be an empty string. Need to provide to satisfy the request.
- QueryParameters – New instance of Input Argument Collection. Need to provide to satisfy the request.
In conclusion, this is how we could invoke the “RenderTemplate” method using HTTP Web Request, bypassing the limitations posed by sandbox isolation mode in D365 online deployments.
