Does Expensify integrate with Microsoft Power Query?

ncanderson · October 2019

Microsoft Power Query (querying tool in Excel and Power BI) has the ability to make connections to data sources with API. I have been able to make connections to other API sources with a JSON output, however have been having issues getting it to work with Expensify. Have you had any customers successfully do this, do you know if this is possible?

Thanks!

Natalie

Nicole Trepanier · October 2019

Hi @ncanderson! I haven't used the program but I haven't heard of any issues with it. Can you tell me a bit more about the issues you are running into?

ncanderson · January 2020

So I have spent quite a bit of time on this and have made some good progress. I can get successful responses when using Postman, however still having some issues in Power Query. I can get a successful response in Power Query when: requestJobDescription={"type":"reconciliation", and can successfully complete the downloader process to get the data.

The issue I am having is with the template parameter, when submitting a "reconciliation" it doesn't seem to be required as there must be a default set-up. But when submitting a "file" there isn't a default, so the template parameter is required and I get a "{responseMessage:No Template Submitted response Code:410}".

Below is the Power Query M formula language that I am using for the "File" request that is producing the "No Template Submitted". Is there any way to see why it is producing that response? Is it formatting? Encoding? ect….? Any help is appreciated!!

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 = "",

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

in

Request

Kirk Barrett · January 2020

Unfortunately, Power Query isn't something that we can provide support for from our end. Sorry about that! I don't know if this will help, but when researching this I did come across this Power Query guide that I thought might be useful for you.

If you have any other questions regarding Expensify please let us know!

Does Expensify integrate with Microsoft Power Query?

Answers