Expense Download Through API

gterio
gterio Expensify Customer Posts: 2

Is it possible to download expenses for selected date ranges through the API without having to access each report? The objective is to import these data into our data warehouse but not really concerned with the resulting output as I am sure we can get the data into CSV. Not really concerned with the actual reports but the more so the content within those reports. Thanks.

JT

Answers

  • John Schuster
    John Schuster Expensify Team Posts: 127 Expensify Team

    Hi @gterio! Great question!

    Yes, you can definitely export expenses from a select date range via the API, but you will need to utilize reports to do this. As you alluded to before, expenses will need to be contained within reports so you can export them, but once that's true, you will have complete control over which data points you would like from the expenses contained within.

    I'd recommend using the Report Exporter API job in combination with your own custom export template.

    Much like the API, writing your own export template is considered self-serve, but there are quite a few of us on the team that enjoy writing these templates, so we'd be happy to steer you in the right direction if you want to buddy check your template with us!

  • gterio
    gterio Expensify Customer Posts: 2

    Thank you. I will turn this over to the developer for further research.

  • John Schuster
    John Schuster Expensify Team Posts: 127 Expensify Team

    You're welcome! If your developer has any questions, they can reach out to us here or at concierge@expensify.com!

  • mmourton
    mmourton Expensify Customer Posts: 13 Expensify Newcomer

    Hello John -

    We are trying to download all of our expenses into a single report. We understand that expenses must be added to a report and we have configured the expensify settings to automatically submit reports daily.

    I am using the scripts below. For some reason, the report is not showing all of the transactions. Can you help me understand why this may be hapening?


    https://integrations.expensify.com/Integration-Server/ExpensifyIntegrations?requestJobDescription={

    "type": "file",

    "credentials": {

    "partnerUserID": "XXXX",

    "partnerUserSecret": "XXXX"

    },

    "onReceive": {

    "immediateResponse": [

    "returnRandomFileName"

    ]

    },

    "inputSettings":{

    "type":"combinedReportData",

    "reportState":"OPEN,SUBMITTED,APPROVED,REIMBURSED,ARCHIVED",

    "filters":{

    "startDate":"2022-01-01",

    "endDate":"2050-09-01",

    }

    },

    "outputSettings": {

    "fileExtension": "csv",

    "fileBasename": "export"

    }

    }

    }



    //////////////////// DOWNLOADER ////////////////////////


    https://integrations.expensify.com/Integration-Server/ExpensifyIntegrations?requestJobDescription={

    "type": "download",

    "credentials": {

    "partnerUserID": "xxxx",

    "partnerUserSecret": "xxxx"

    },

    "fileName": "exportxxxxxxxxxxxxxxxxxxxx.csv",

    "fileSystem": "integrationServer"

    }


    /////////////////// TEMPLATE /////////////////////////

    <#if addHeader == true>

     ExpensifyID,Date,Merchant,Amount,Card,User,Description,GL Code,Job Code,Cost Code,Last Updated,Receipt,Expensify Status,ExternalID<#lt>

    </#if>

    <#assign expenseNumber = 1>

    <#list reports as report>

     <#list report.transactionList as expense>

      <#if expense.cardNumber?has_content>

       <#assign created = expense.created>

       <#assign amount = expense.amount/100>

      <#else>

       <#if expense.modifiedCreated?has_content>

        <#assign created = expense.modifiedCreated>

       <#else>

        <#assign created = expense.created>

       </#if>

       <#if expense.modifiedAmount?has_content>

        <#assign amount = expense.modifiedAmount/100>

       <#elseif expense.amount?has_content>

        <#assign amount = expense.amount/100>

       <#else>

        <#assign amount = "">

       </#if>

      </#if>

      ${expense.transactionID},<#t>

      ${created},<#t>

      ${quoteCsv(unescapeHtml(expense.merchant))},<#t>

      ${amount},<#t>

      ${expense.cardNumber?keep_after_last("X")},<#t>

      ${report.submitter.fullName},<#t>

      ${quoteCsv(unescapeHtml(expense.comment))},<#t>

      ${expense.ntag1GlCode},<#t>

      ${expense.ntag2GlCode},<#t>

      ${expense.ntag3GlCode},<#t>

      ${expense.inserted},<#t>

      ${expense.receiptFilename},<#t>

      ${report.status},<#t>

      ${expense.externalID}<#lt>

      <#assign expenseNumber = expenseNumber + 1>

     </#list>

    </#list>

  • mmourton
    mmourton Expensify Customer Posts: 13 Expensify Newcomer

    This is resolved. We accidentally had the incorrect partner user id and secret (from another one of our companies)