Power Query API with BodyRequest

[msellner_1025]

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

[ams1 · Accepted solution]

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
    Request

Tadaaa 😊

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"

[msellner_1025]

Yes, I still cannot figure this out! Any suggestions would be greatly appreciated!

Thanks!
Mikail

[ams1]

Hi,

I see this has had no activity for a while - do you still have the problem?

[msellner_1025]

Yes, I still cannot figure this out! Any suggestions would be greatly appreciated!

Thanks!
Mikail

[ams1]

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_1025]

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>'

[ams1]

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_1025]

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

[ams1 · Accepted solution]

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
    Request

Tadaaa 😊

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"

[msellner_1025 · Accepted solution]

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

[ams1]

Glad to hear we are making progress 😊.

Can you please try with the template I provided and tell us if using my simple template works?

The template I used to test should work also with your account (I'm not a big expensify expert).

If my template works with your account (or a really simple template), then we can isolate the problem to your the template.

If using a simple template doesn't work, then please post again the final powerquery you're using.

Hopefully we put this issue to rest.

[msellner_1025]

Hi @ams1 ,

I ran a simple version of the template and it finally worked! I went through my original template and isolated the issue. I had an else statement set to null like postedDate = "" and it worked as expected when I updated it to postedDate = """".

You have been so helpful! I really appreciate it. I have accepted your great solution.

Thank you!
Mikail

[ams1]

@msellner_1025 -> great news!

P.S.: I don't think you marked as answer the correct reply 😀 -> please double check so that this rests as "answered".

P.P.S: I really like expensify 😂

[rehvi1108]

Hi all

I got this one to work -> if additional fields needs to added -> you can add then once

this flow creates a cvs document and the later on, extracts it from the same template

you need to enter the username and password 2 places in the code

I used many different inputs other places and combines the inputs into this one 

let

Description_F = "requestJobDescription={
""type"":""file"",
""credentials"":{
""partnerUserID"":""XXXXXX"",
""partnerUserSecret"":""XXXXXX""
},
""onReceive"":{
""immediateResponse"":[""returnRandomFileName""]
},
""inputSettings"":{
""type"":""combinedReportData"",
""reportState"":""OPEN,SUBMITTED,APPROVED,REIMBURSED,ARCHIVED"",
""filters"":{
""startDate"":""2022-01-01""
}
},
""outputSettings"":{
""fileExtension"":""csv""
},
}",
BodyRequest_F = Uri.EscapeDataString(
// NOTE there is NO more "template=" at the beginning of below string! ------- (!)

"
<#assign expenseNumber = 1>
<#list reports as report>
<#list report.transactionList as expense>
${report.reportName}, <#t>
${report.reportID}, <#t>
${report.submitted},<#t>
${report.status},<#t>
${report.policyName},<#t>
${report.manager.fullName},<#t>
${report.approved},<#t>
${report.created},<#t>
${report.submitter.fullName},<#t>
${report.approvers},<#t>
${report.employeeCustomField1},<#t>
${report.employeeCustomField2},<#t>
${report.accountEmail}, <#t>
${expense.transactionID},<#t>
${expense.amount}, <#t>
${expense.created},<#t>
${expense.comment},<#t>
${expense.currency},<#t>
${expense.type},<#t>
${expense.taxName},<#t>
${expense.taxRate},<#t>
${expense.taxRateName},<#t>
${expense.taxCode},<#t>
${expense.tag},<#t>
${expense.convertedAmount},<#t>
${expense.currencyConversionRate},<#t>
${expense.inserted},<#t>
${expense.receiptObject.url},<#t>
${expense.modifiedAmount},<#t>
${expense.modifiedCreated},<#t>
${expense.modifiedMCC},<#t>
${expense.modifiedMerchant},<#t>
${expense.merchant},<#t>
${expense.category} <#t>

</#list>
</#list>
"
),
RelativePathString_F = "/Integration-Server/ExpensifyIntegrations",
URLRequest_F = "https://integrations.expensify.com",
Request_F = Csv.Document(
Web.Contents(
URLRequest_F,
[
RelativePath = RelativePathString_F,
Headers = [#"Content-Type" = "application/x-www-form-urlencoded"],
Content = Text.ToBinary(Description_F & "&template=" & BodyRequest_F)
// ^^^^^^^^^^^^ adding ampersand and template= here so it doesn't get escaped
]
)
),
Column2_F = Request_F{0}[Column1] ,

Description = "requestJobDescription={
""type"":""download"",
""credentials"":{
""partnerUserID"":""XXXXXXX"",
""partnerUserSecret"":""XXXXXXX""
},
""fileName"": "&Column2_F&",
""fileSystem"": ""integrationServer""
}",

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_F)
// ^^^^^^^^^^^^ adding ampersand and template= here so it doesn't get escaped
]
)
),
#"Renamed Columns" = Table.RenameColumns(Request,{{"Column1", "report.reportName"}, {"Column2", "report.reportID"}, {"Column3", "report.submitted"}, {"Column4", "report.status"}, {"Column5", "report.policyName"}, {"Column6", "report.manager.fullName"}, {"Column7", "report.approved"}, {"Column8", "report.created"}, {"Column9", "report.submitter.fullName"}, {"Column10", "report.approvers"}, {"Column11", "report.employeeCustomField1"}, {"Column12", "report.employeeCustomField2"}, {"Column13", "report.accountEmail"}, {"Column14", "expense.transactionID"}, {"Column15", "expense.amount"}, {"Column16", "expense.created"}, {"Column17", "expense.comment"}, {"Column18", "expense.currency"}, {"Column19", "expense.type"}, {"Column20", "expense.taxName"}, {"Column21", "expense.taxRate"}, {"Column22", "expense.taxRateName"}, {"Column23", "expense.taxCode"}, {"Column24", "expense.tag"}, {"Column25", "expense.convertedAmount"}, {"Column26", "expense.currencyConversionRate"}, {"Column27", "expense.inserted"}, {"Column28", "expense.receiptObject.url"}, {"Column29", "expense.modifiedAmount"}, {"Column30", "expense.modifiedCreated"}, {"Column31", "expense.modifiedMCC"}, {"Column32", "expense.modifiedMerchant"}, {"Column33", "expense.merchant"},{"Column34", "expense.category"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"report.submitted", type datetime}, {"report.approved", type datetime}, {"report.created", type datetime}, {"expense.created", type datetime}, {"expense.inserted", type datetime}, {"expense.modifiedCreated", type date}, {"expense.amount", type number}}),
#"Divided Column" = Table.TransformColumns(#"Changed Type", {{"expense.amount", each _ / 100, type number}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Divided Column",{{"expense.convertedAmount", type number}}),
#"Divided Column1" = Table.TransformColumns(#"Changed Type1", {{"expense.convertedAmount", each _ / 100, type number}}),
#"Changed Type2" = Table.TransformColumnTypes(#"Divided Column1",{{"expense.modifiedAmount", type number}}),
#"Divided Column2" = Table.TransformColumns(#"Changed Type2", {{"expense.modifiedAmount", each _ / 100, type number}})
in
#"Divided Column2"