Forum Discussion
Power Query API with BodyRequest
Hello!
I am trying to move a functioning API from Postman into Power Query. The issue is I do not know how to convert the x-www-form-urlencoded Body value to a Power Query accepted BodyRequest. The API requires a template be submitted in the request which allows you to customize the export format of your data. Export templates are based on Freemarker language. I understand how to write the template and I have already successfully done so in Postman, but it doesn't seem like there is a way to submit a template in Power Query. Below is my current code that returns a response code of 410 and response message "No Template Submitted."
let
Description = "requestJobDescription={
""type"":""file"",
""credentials"":{
""partnerUserID"":""XXXXXXXXXXXXXXXX"",
""partnerUserSecret"":""XXXXXXXXXXXXXXXXXXXXXXXXXXXXX""
},
""onReceive"":{
""immediateResponse"":[""returnRandomFileName""]
},
""inputSettings"":{
""type"":""combinedReportData"",
""filters"":{
""startDate"":""2023-01-01"",
""endDate"":""2023-01-31""
}
},
""outputSettings"":{
""fileExtension"":""csv""
},
}",
BodyRequest = Uri.EscapeDataString("template=
<#if addHeader == true>
Policy ID,Policy Name,Employee Name,Email,Employee,Employee Clinic,Report ID,Report Ref,Report Name,Date Approved,Reimbursment ID,Date Reimbursed,Status,ReportLink,Merchant,Amount,Transaction Amount,Txn Date,Posted Date,Original Currency,BankRef,MCC Code,GL Account,Entity,Associated Clinic,Service,Require Service,Tag1,Tag2,CustomFieldDept,Expense Description,Transaction ID,Receipt Type,Receipt Link,<#lt>
</#if>
<#list reports as report>
<#list report.transactionList as expense>
<#if expense.modifiedMerchant?has_content>
<#assign merchant = expense.modifiedMerchant>
<#else>
<#assign merchant = expense.merchant>
</#if>
<#if expense.convertedAmount?has_content>
<#assign amount = expense.convertedAmount/100>
<#elseif expense.modifiedAmount?has_content>
<#assign amount = expense.modifiedAmount/100>
<#else>
<#assign amount = expense.amount/100>
</#if>
<#if expense.modifiedCreated?has_content>
<#assign created = expense.modifiedCreated>
<#else>
<#assign created = expense.created>
</#if>
<#if report.employeeCustomField1?has_content>
<#assign Employee = report.employeeCustomField1>
<#else>
<#assign Employee = report.accountEmail>
</#if>
<#if expense.details.posted?has_content && expense.managedCard>
<#assign postedDate = expense.details.posted?date(""yyyyMMdd"")?string(""yyyy-MM-dd"")>
<#else>
<#assign postedDate = "">
</#if>
<#if report.customField.Departments?has_content>
<#assign associatedClinic = report.customField.Departments>
<#else>
<#assign associatedClinic = expense.ntag1>
</#if>
<#if expense.category == ""Inventory"" || expense.category == ""Supplies & Materials"">
<#assign requireService = ""YES"">
<#else>
<#assign requireService = ""NO"">
</#if>
<#assign reportURL = ""https://www.expensify.com/report?param={%22pageReportID%22:%22"" + report.reportID + ""%22}"">
${report.policyID},<#t>
${report.policyName},<#t>
${report.employeeCustomField1},<#t>
${report.accountEmail},<#t>
${Employee},<#t>
${report.employeeCustomField2},<#t>
${report.reportID},<#t>
${report.oldReportID},<#t>
${report.reportName},<#t>
${report.approved},<#t>
${report.entryID},<#t>
${report.reimbursed},<#t>
${report.status},<#t>
${reportURL},<#t>
${merchant},<#t>
${(expense.amount/100)?string(""0.00"")},<#t>
${amount},<#t>
${expense.created},<#t>
${postedDate},<#t>
${expense.currency},<#t>
${expense.bank},<#t>
${expense.mcc},<#t>
${expense.category},<#t>
${report.customField.Locations},<#t>
${associatedClinic},<#t>
${Service},<#t>
${requireService},<#t>
${expense.ntag1},<#t>
${expense.ntag2},<#t>
${report.customField.Departments},<#t>
${expense.comment},<#t>
${expense.transactionID},<#t>
${expense.receiptObject.type},<#t>
${expense.receiptObject.url}<#lt>
</#list>
</#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
I would really REALLY appreciate some guidance on this as I have been stuck for a long time.
Thank you!
Mikail
Ah, yes, the problem is that if we put "template=" inside the BodyRequest step it will go into Uri.EscapeDataString and so it will get escaped aka be converted to "template%3D" -> we need the "=" to remain as it is in the POST body so that expensify api can detect the "template" parameter.
Long story short, below worked on my machine:
let Description = "requestJobDescription={ ""type"":""file"", ""credentials"":{ ""partnerUserID"":""..."", ""partnerUserSecret"":""..."" }, ""onReceive"":{ ""immediateResponse"":[""returnRandomFileName""] }, ""inputSettings"":{ ""type"":""combinedReportData"", ""filters"":{ ""startDate"":""2023-01-01"" } }, ""outputSettings"":{ ""fileExtension"":""csv"" }, }", BodyRequest = Uri.EscapeDataString( // NOTE there is NO more "template=" at the beginning of below string! ------- (!) "<#list reports as report> ${report.reportName},<#t> ${report.reportID},<#t> ${report.accountEmail}<#lt> </#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 & "&template=" & BodyRequest) // ^^^^^^^^^^^^ adding ampersand and template= here so it doesn't get escaped ] ) ) in RequestTadaaa 😊
Please mark this as answer if it works for you.
P.S.: I solved it using the strategy I mentioned before: PowerQuery request body has to be ~identical with curl request body - you see below they weren't as "=" got escaped to "%3D"
Hi ams1 ,
I believe I am starting to make progress on this, but unfortunately this suggestion did not work. It does seem to accept the template now so that's great news! However, the template cannot be processed (see error message below). The PowerQuery request body is identical with the curl request body so I don't understand what the issue could be. I am still running this out of Postman multiple times a day so I know that it works outside of PowerQuery. Even if I copy the exact request body from an example in the Expensify API reference, I still get the same error.
Is there anything else that could be causing this not to work in PowerQuery?
Thank you,
Mikail
13 Replies
- ams1Responsive Resident
Hi,
I see this has had no activity for a while - do you still have the problem?
- msellner_1025Helper I
Yes, I still cannot figure this out! Any suggestions would be greatly appreciated!
Thanks!
Mikail- ams1Responsive Resident
Is this the relevant link with the API documentation?
https://integrations.expensify.com/Integration-Server/doc/#report-exporter
Also, can you share a curl (or equivalent) command that is working for you?
- msellner_1025Helper I
Yes, I still cannot figure this out! Any suggestions would be greatly appreciated!
Thanks!
Mikail