API with Template Parameter based on Freemarker

[Anonymous]

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".

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):

curl --location --request POST 'https://integrations.expensify.com/Integration-Server/ExpensifyIntegrations?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"},}' \

--header 'Content-Type: application/x-www-form-urlencoded' \

--data-urlencode 'template=

<#if addHeader == true>

   Merchant,Original Amount,Category,Report number,Expense number<#lt>

</#if>

<#assign reportNumber = 1>

<#assign expenseNumber = 1>

<#list reports as report>

   <#list report.transactionList as expense>

       ${expense.merchant},<#t>

       <#-- note: expense.amount prints the original amount only -->

       ${expense.amount},<#t>

       ${expense.category},<#t>

       ${reportNumber},<#t>

       ${expenseNumber}<#lt>

       <#assign expenseNumber = expenseNumber + 1>

   </#list>

   <#assign reportNumber = reportNumber + 1>

</#list>'

Here is the Power Query Advanced Editor I am having issues with:

let

URLRequest = "https://integrations.expensify.com/Integration-Server/ExpensifyIntegrations?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 = "<#if addHeader == true>

   Merchant,Original Amount,Category,Report number,Expense number<#lt>

</#if>

<#assign reportNumber = 1>

<#assign expenseNumber = 1>

<#list reports as report>

   <#list report.transactionList as expense>

       ${expense.merchant},<#t>

       <#-- note: expense.amount prints the original amount only -->

       ${expense.amount},<#t>

       ${expense.category},<#t>

       ${reportNumber},<#t>

       ${expenseNumber}<#lt>

       <#assign expenseNumber = expenseNumber + 1>

   </#list>

   <#assign reportNumber = reportNumber + 1>

</#list>",

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

in

   Request

Any assistance or guidance would be greatly appreciated!!

Thanks!

Natalie

[v-lid-msft]

Hi @Anonymous ,

We can try to use the Uri.EscapeDataString function to encode the body request:

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=
<#if addHeader == true>
   Merchant,Original Amount,Category,Report number,Expense number<#lt>
</#if>
<#assign reportNumber = 1>
<#assign expenseNumber = 1>
<#list reports as report>
   <#list report.transactionList as expense>
       ${expense.merchant},<#t>
       <#-- note: expense.amount prints the original amount only -->
       ${expense.amount},<#t>
       ${expense.category},<#t>
       ${reportNumber},<#t>
       ${expenseNumber}<#lt>
       <#assign expenseNumber = expenseNumber + 1>
   </#list>
   <#assign reportNumber = reportNumber + 1>
</#list>
"
),


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 & BodyRequest)]))

in
    Request

Best regards,

[Anonymous]

Thanks for the response @v-lid-msft ,

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.

Any other ideas?

Seems silly that this template parameter is being such a pain!

[nutzschneider]

Hello, 

I've been tackeling the exact same problem, any chance you eventually found a solution? 

Thanks,