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

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:

• HTTP Action: POST
• URL: https://integrations.expensify.com/Integration-Server/ExpensifyIntegrations
• Your only Parameter ("Params") will be "requestJobDescription", described below
• In the Body you'll need to set the body to "x-www-form-encoded", with a key "template", described below

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!