We're currently experiencing a site outage. Please visit our status page for more details and to subscribe to updates.

Expensify.org/hunger SNAP campaign status 


The number of families signing up for reimbursement has vastly outnumbered the limited funds available. Though we’re unable to provide a specific timeline on when your report will be reimbursed, we’re doing our best to drive donations and keep our fund moving forward. Rest assured, we’ll review every report that’s submitted per our instructions. 


Thanks for your patience! 

How To: Zero-to-hero in Postman with your first Report Retrieval

fpettinellafpettinella Expensify Customer Posts: 17 Expensify Newcomer
edited October 2019 in Integrations and API

Hi,

Please read on to find how to retrieve your first expenses from a report, using Postman API manager. I'm writing this because it took me around 90 minutes to get my first request to work successfully, and I'm hoping that the API and the documentation grows for future use. Specifically this use-case: https://community.expensify.com/discussion/4862/pull-mileage-from-all-employees

How to download the expenses from a report as a Comma Separated Values file (CSV)

Step 1: Generate your authentication information

Go to https://www.expensify.com/tools/integrations/ and generate your partnerUserID and partnerUserSecret. Save these for later, as you'll need them for your API calls. The partnerUserSecret, especially, will need to be saved as it won't be displayed again. If you've already generated the credentials, you can re-generate them to get a new partnerUserSecret.

Step 2: Find the ID of a report you want to export in Expensify

I could not find a way to list report IDs using the API. Hence, please open a report on Expensify.com and take note of the Report ID. In my case, I want to export report ID 50352738.

Step 3: Export (generate) a "Report" as a CSV file

For this you'll use the Documentation under "Report Exporter".

In postman, set the following:

The requestJobDescription key will have a value like below:

{
    "type": "file",
    "credentials": {
        "partnerUserID": "my_user_id",
        "partnerUserSecret": "my_user_secret"
    },
    "onReceive": {
        "immediateResponse": [
            "returnRandomFileName"
        ]
    },
    "inputSettings": {
        "type": "combinedReportData",
        "filters": {
            "reportIDList": "50352738"
        }
    },
    "outputSettings": {
        "fileExtension": "csv"
    }
} 

Take the above replace with your own partnerUserID, partnerUserSecret,and reportIDList. You can also pass in a comma-separated list as the reportIDList like "12345,45678,111111".

The template key will have the value like below:

<#if addHeader>
 Merchant,Amount,Transaction Date<#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>
 ${merchant},<#t>
 ${amount},<#t>
 ${created}<#lt>
 </#list>
</#list>

The template variable determines what information is saved in your CSV file. If you want more columns than merchant, amount, and transaction date, as defined above, you can follow the syntax as defined in the export template format documentation.

Here are some examples of the Export Report as it looks in my Postman:

Note the POST action on the URL. The URL ending (?requestJob...) is added automatically by the Params.

See the requestJobDescription here in the Params tab.

See the template here in the Body tab. Note the x-www-form-urlencoded.

Step 4: save your generated file name

As expensify only currently supports the "onReceive":{"immediateResponse":["returnRandomFileName"]} option in step 3, you should receive a random filename back from the API like "exportc111111d-a1a1-a1a1-a1a1-d1111111f.csv". This is required to run the download command, so take note of it.

Step 5: download your exported report

Now, set up another API call in almost the same way as you did before. You don't need the template key in the Body anymore, so delete that and set the Body type to "none". Now, modify your requestJobDescription to read like below, but with your own credentials and file name:

{
    "type": "download",
    "credentials": {
        "partnerUserID": "my_user_id",
        "partnerUserSecret": "my_user_secret"
    },
    "fileName": "exportc111111d-a1a1-a1a1-a1a1-d1111111f.csv",
    "fileSystem": "integrationServer"
}

The press go and you should see the CSV in the response body:

Best of luck!

Tagged:

Answers

  • sbismilsbismil Expensify Customer Posts: 20 Expensify Newcomer

    @fpettinella That was a good post I only wish if you could have posted earlier. Can you post on how to upload that exported .csv file to SFTP? I'm having issue where I can get the file using Email option but with SFTP I get following message.

    ExpenseReport956f2274-b28c-4405-9318-2c8e2.csv{"responseMessage":"Could not establish connection to SFTP host","responseCode":401


    I can access SFTP using WINSCP/Putty utility to upload file manually. BTW i am using AWS sftp. Appreciate you response in advance. Thanks

  • Sheena TrepanierSheena Trepanier Expensify Success Coach - Admin Posts: 1,395 Expensify Team

    @fpettinella, what can I say but WOW and thank you. This is a fantastic guide, one my team could benefit from just as much as other Expensify users. I've already shared it internally and I'm going to pin this post for others to find as well.

  • fpettinellafpettinella Expensify Customer Posts: 17 Expensify Newcomer
    edited October 2019

    @sbismil unfortunately I don't have the capacity to test the SFTP option currently. Mind sharing the sftpUpload JSON object that you're sending to the API?

    @Sheena Trepanier no problem, I'm glad I can help make the process more accessible. Writing this also helped me understand the process. Hope people share their examples as well :)

  • sbismilsbismil Expensify Customer Posts: 20 Expensify Newcomer

    @fpettinella Waiting for the message approval before it can be posted. The JSON is correct. I had senior software engineer looked at it. He thinks its issue with Expensify site as i can export file but cant upload. Just wanted to know worked on it. Expensify support is very slow to respond, not sure if they have the know how.

  • fpettinellafpettinella Expensify Customer Posts: 17 Expensify Newcomer

    If I had to guess, it's either that the port you're sending is a String instead of a number, or perhaps the Expensify server is unable to send SFTP requests. It seems the rest of your request seems to be sound (with email working) so I'm quite happy to see the sftpUpload object if you change the host, login, and password to "www.example.com", "username", and "p4ssw0rd" so it's anonymous.

  • fpettinellafpettinella Expensify Customer Posts: 17 Expensify Newcomer

    @sbismil If I had to guess, it's either that the port you're sending is a String instead of a number, or perhaps the Expensify server is unable to send SFTP requests. It seems the rest of your request seems to be sound (with email working) so I'm quite happy to see the sftpUpload object if you change the host, login, and password to "www.example.com", "username", and "p4ssw0rd" so it's anonymous.

  • sbismilsbismil Expensify Customer Posts: 20 Expensify Newcomer
    edited October 2019

    @fpettinella I'm using port=22. actionName":"sftpUpload

    sftpData": {"host": "s-d8"

    login=login

    password=password

    It looks like issue is with Expensify server as same credentials and host can be accessed manually.

    Please see attached code.

  • fpettinellafpettinella Expensify Customer Posts: 17 Expensify Newcomer

    @sbismil I see what you mean. Perhaps the DNS requires an update on the Expensify API-execution server, or perhaps the DNS change on AWS hasn't propogated to Expensify's DNS server. This will require an expensify admin to answer, I think the code you shared should be enough for them to verify if your structure is correct (seems sound to me).

  • sbismilsbismil Expensify Customer Posts: 20 Expensify Newcomer

    @fpettinella Thanks for your feedback. Unfortunately we are not getting help from Expensify. They have scripted response that it's a self-serve system and we should use documentation. After 6 days of opening a ticket on this issue this is the response (below) I received. I think they know the issue is on their side are not willing to fix it.


    Expensify's API is generally considered a self-serve tool, but we have a customer community that should be able to help! Please feel free to share your question and API call on our community here. Remember, it's important to make sure you're not sharing your partner credentials when sharing the API call you're having trouble with! You can also find more information and answers specific to Expensify's API (i.e., not general questions regarding coding) on our API documentation page here.


    Thanks Again.

  • sbismilsbismil Expensify Customer Posts: 20 Expensify Newcomer

    @fpettinella Thanks for responding. Yo are definitely faster than Exp. support.


    "outputSettings":{

          "fileExtension":"csv",

          "fileBasename":"ExpenseReport"

        },

        "onFinish":[

          {"actionName":"markAsExported","label":"Expensify Export"},

          {"actionName":"sftpUpload", "sftpData": {"host": "s-d8d6fce742cd434a8.server.transfer.us-west-2.amazonaws.com", "port":22, "login":"xxxxxxx", "password":"xxxxxxx"}}

  • sbismilsbismil Expensify Customer Posts: 20 Expensify Newcomer

    @fpettinella I tried to send code but its waiting for approval before it can be displayed here. Basically

    "actionName": markAsExported

    "ActionName": "stpUpload"

    "sftpData": {"host": "s-d8d6fce742cd434a8.server.transfer.us-west-2.amazonaws.com"

    {"actionName":"sftpUpload", , "port":22, "login":"xxxxxxx", "password":"xxxxxxx"}

    It's fairly straight forward.

    Thanks for fast response


       

  • sbismilsbismil Expensify Customer Posts: 20 Expensify Newcomer

    @fpettinella I hope this goes thru.

    {"actionName":"sftpUpload", {"actionName":"sftpUpload", "sftpData": {"host": "s-d8d6fce742cd434a8.server.transfer.us-west-2.amazonaws.com, "port":22, "login":"xxxxxxx", "password":"xxxxxxx"}

  • sbismilsbismil Expensify Customer Posts: 20 Expensify Newcomer
    edited October 2019

    Above comments posted by Expensify after review.

Sign In or Register to comment.