API with Template Parameter based on Freemarker

Question

I need help converting a Rest API to be useable in Power Query. The issue I am having is with the --data-urlencode Template as I get a "Response Message: No Template Submitted".&nbsp;Code request is copied from Postman which completes the request as expected by producing a "filename.csv", which then is downloadable (I have a Downloader API set-up in Power Query that works great at retrieving the files):&nbsp;curl --location --request POST 'https://integrations.expensify.com/Integration-Server/ExpensifyIntegrations?requestJobDescription={"type":"file",&nbsp;&nbsp; "credentials":{&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "partnerUserID":"username",&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "partnerUserSecret":"password"&nbsp;&nbsp; },"onReceive":{&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "immediateResponse":["returnRandomFileName"]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; },&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "inputSettings":{&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "type":"combinedReportData",&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "reportState":"APPROVED",&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "limit":"10",&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "filters":{&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "startDate":"2019-01-01",&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "endDate":"2019-10-01"&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; }&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; },&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "outputSettings":{&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "fileExtension":"csv"},}' \&nbsp;--header 'Content-Type: application/x-www-form-urlencoded' \&nbsp;--data-urlencode 'template=&lt;#if addHeader == true&gt;&nbsp;&nbsp; Merchant,Original Amount,Category,Report number,Expense number&lt;#lt&gt;&lt;/#if&gt;&lt;#assign reportNumber = 1&gt;&lt;#assign expenseNumber = 1&gt;&lt;#list reports as report&gt;&nbsp;&nbsp; &lt;#list report.transactionList as expense&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ${expense.merchant},&lt;#t&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;#-- note: expense.amount prints the original amount only --&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ${expense.amount},&lt;#t&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ${expense.category},&lt;#t&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ${reportNumber},&lt;#t&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ${expenseNumber}&lt;#lt&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;#assign expenseNumber = expenseNumber + 1&gt;&nbsp;&nbsp; &lt;/#list&gt;&nbsp;&nbsp; &lt;#assign reportNumber = reportNumber + 1&gt;&lt;/#list&gt;'&nbsp;Here is the Power Query Advanced Editor I am having issues with:&nbsp;let&nbsp;URLRequest = "https://integrations.expensify.com/Integration-Server/ExpensifyIntegrations?requestJobDescription={&nbsp;&nbsp; ""type"":""file"",&nbsp;&nbsp; ""credentials"":{&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ""partnerUserID"":""username"",&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ""partnerUserSecret"":""password""&nbsp;&nbsp; },""onReceive"":{&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ""immediateResponse"":[""returnRandomFileName""]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; },&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ""inputSettings"":{&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ""type"":""combinedReportData"",&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ""reportState"":""APPROVED"",&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ""limit"":""10"",&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ""filters"":{&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ""startDate"":""2019-01-01"",&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ""endDate"":""2019-10-01""&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; }&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; },&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ""outputSettings"":{&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ""fileExtension"":""csv""&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; },}",&nbsp;BodyRequest = "&lt;#if addHeader == true&gt;&nbsp;&nbsp; Merchant,Original Amount,Category,Report number,Expense number&lt;#lt&gt;&lt;/#if&gt;&lt;#assign reportNumber = 1&gt;&lt;#assign expenseNumber = 1&gt;&lt;#list reports as report&gt;&nbsp;&nbsp; &lt;#list report.transactionList as expense&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ${expense.merchant},&lt;#t&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;#-- note: expense.amount prints the original amount only --&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ${expense.amount},&lt;#t&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ${expense.category},&lt;#t&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ${reportNumber},&lt;#t&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ${expenseNumber}&lt;#lt&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt;#assign expenseNumber = expenseNumber + 1&gt;&nbsp;&nbsp; &lt;/#list&gt;&nbsp;&nbsp; &lt;#assign reportNumber = reportNumber + 1&gt;&lt;/#list&gt;",&nbsp;Request = Csv.Document(Web.Contents(URLRequest, [Headers=[#"Content-Type"="application/x-www-form-urlencoded"], Content = Text.ToBinary(BodyRequest)]))&nbsp;in&nbsp;&nbsp;&nbsp; Request&nbsp;&nbsp;Any assistance or guidance would be greatly appreciated!!&nbsp;Thanks!&nbsp;Natalie

v-lid-msft · Answer

Hi&nbsp;Anonymous&nbsp;,
&nbsp;
We can try to use the&nbsp;Uri.EscapeDataString function to encode the body request:
&nbsp;
&nbsp;
let
Description = "requestJobDescription={
   ""type"":""file"",
   ""credentials"":{
       ""partnerUserID"":""username"",
       ""partnerUserSecret"":""password""
   },
""onReceive"":{
           ""immediateResponse"":[""returnRandomFileName""]
       },
       ""inputSettings"":{
           ""type"":""combinedReportData"",
           ""reportState"":""APPROVED"",
           ""limit"":""10"",
           ""filters"":{
               ""startDate"":""2019-01-01"",
               ""endDate"":""2019-10-01""
           }
       },
       ""outputSettings"":{
           ""fileExtension"":""csv""
       },
}",

BodyRequest = Uri.EscapeDataString("template=
&lt;#if addHeader == true&gt;
   Merchant,Original Amount,Category,Report number,Expense number&lt;#lt&gt;
&lt;/#if&gt;
&lt;#assign reportNumber = 1&gt;
&lt;#assign expenseNumber = 1&gt;
&lt;#list reports as report&gt;
   &lt;#list report.transactionList as expense&gt;
       ${expense.merchant},&lt;#t&gt;
       &lt;#-- note: expense.amount prints the original amount only --&gt;
       ${expense.amount},&lt;#t&gt;
       ${expense.category},&lt;#t&gt;
       ${reportNumber},&lt;#t&gt;
       ${expenseNumber}&lt;#lt&gt;
       &lt;#assign expenseNumber = expenseNumber + 1&gt;
   &lt;/#list&gt;
   &lt;#assign reportNumber = reportNumber + 1&gt;
&lt;/#list&gt;
"
),

RelativePathString = "/Integration-Server/ExpensifyIntegrations",

URLRequest = "https://integrations.expensify.com",

Request = Csv.Document(Web.Contents(URLRequest, [RelativePath=RelativePathString, Headers=[#"Content-Type"="application/x-www-form-urlencoded"], Content = Text.ToBinary(Description &amp; BodyRequest)]))

in
    Request
&nbsp;
Best regards,
&nbsp;

anonymous · Answer

Thanks for the response v-lid-msft&nbsp;,&nbsp;Unfortunately it still results in the "Response Message: No Template Submitted". I have updated my Advanced Editor to be the text for reference as this progresses.&nbsp;Any other ideas?&nbsp;Seems silly that this template parameter is being such a pain!

nutzschneider · Answer

Hello,&nbsp;&nbsp;I've been tackeling the exact same problem, any chance you eventually found a solution?&nbsp;&nbsp;Thanks,&nbsp;

Forum Discussion

API with Template Parameter based on Freemarker

3 Replies

Related content

PBI Template

Business Requirement Document Template

Parameter in summarize

notebook parameter

BRD template