Forum Discussion
Power Query API with BodyRequest
- 3 years ago
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"
- 3 years ago
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
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?
Yes, that is the relevant link to the API documentation. I pasted the cURL command below that I am using in Postman without any issues.
curl --location -g --request POST 'https://integrations.expensify.com/Integration-Server/ExpensifyIntegrations?requestJobDescription={
"type": "file",
"credentials": {
"partnerUserID": "XXXXXXXXXXXXXXX",
"partnerUserSecret": "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX"
},
"onReceive": {
"immediateResponse": ["returnRandomFileName"]
},
"inputSettings": {
"type": "combinedReportData",
"filters": {
"startDate": "2022-01-01"
}
},
"outputSettings": {
"fileExtension": "csv"
}}' \
--header 'Content-Type: application/x-www-form-urlencoded' \
--data-urlencode '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>
<#if expense.tag?contains("Retail Products")>
<#assign Service = "Retail Products">
<#elseif expense.tag?contains("Injectables")>
<#assign Service = "Injectables">
<#elseif expense.tag?contains("Energy Devices")>
<#assign Service = "Energy Devices">
<#elseif expense.tag?contains("Surgical")>
<#assign Service = "Surgical">
<#elseif expense.tag?contains("Dermatology")>
<#assign Service = "Dermatology">
<#elseif expense.tag?contains("Other Clinical")>
<#assign Service = "Other Clinical">
<#elseif expense.tag?contains("Other Non-Clinical")>
<#assign Service = "Other Non-Clinical">
<#else>
<#assign Service = "No Value">
</#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>'
- ams13 years agoResponsive Resident
Hi,
IF you have that curl working (I think you're missing a -d), the first thing to try is to add the --trace-ascii parameter and thus see what is the actual body curl sends to your api:
curl --trace-ascii my2.log ...Your PowerQuery body should be ~identical with the curl body.
Looking at the curl generated my2.log, we see that the body looks like this:
Notice there is an ampersand (&) between the end of requestJobDescription parameter and the template parameter -> I think you're missing that ampersand from your PowerQuery when you concatenate Description and BodyRequest.
So I think the solution would be to ADD this ampersand to your PQ, something like:
... Request = Csv.Document( Web.Contents( URLRequest, [ RelativePath = RelativePathString, Headers = [#"Content-Type" = "application/x-www-form-urlencoded"], Content = Text.ToBinary(Description & "&" & BodyRequest) // ^^^^^^^ added ampersand above! ] ) ) ...Let me know if it worked and mark this as answer.
- msellner_10253 years agoHelper I
Hi ams1 ,
Thank you for your time. Unfortunately, I am still receiving the exact same error after adding the ampersand you suggested. I've seen a few other posts regarding this same issue and it looks like it has never been solved. I'm not sure how to proceed at this point. Do you have any other suggestions?
Thanks,
Mikail- ams13 years agoResponsive Resident
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"