Expensify / Report Exporter / Template

Overview

This file describes the template you can use to customize the export format of your data. Export templates are based on Freemarker.

Report level

Name Description
accountEmail The email address of the user who created the report
accountID The account ID of the user who created the report
approved The date the report was approved
approvers Array that contains the list of all approvers of the report. Each is represented by an object containing anemail, date, and employeeUserID and employeePayrollID if they are defined in the report’s policy.
created The date the report was created
currency The currency of the report, based on its policy
customField.Name_of_Report_Field Formula to export report field “Name of Report Field”. All non-alphanumerical characters must be replaced with an underscore.
isACHReimbursed Boolean, true if the report has been reimbursed via ACH
managerEmail The email addres of the last approver of the report
managerUserID The ID of the last approver of the report
managerPayrollID The payroll ID of the last approver of the report
policyName The name of the policy the report is under
policyID The ID of the policy the report is under
reimbursed The date the report was reimbursed
reportID The ID of the report
reportName The name of the report
status The current status of the report
submitted The date the report was submitted
submitterPayrollID The payroll ID of the user who submitted the report
submitterUserID The user ID of the user who submitted the report
total The total amount of the report, in cents
transactionList Array of objects representing the expenses of the report. See Expense Level below

Expense level

Name Description
amount The original amount of the expense, in cents
bank The bank the expense was pulled from
billable Boolean determining if the expense is billable
category The category of the expense
categoryGlCode The GL code associated to the category of the expense
categoryPayrollCode The payroll code associated to the category of the expense
comment The comment of the expense
convertedAmount The amount of the expense, converted in the currency of the report. Populated only if the original amount is in a different currency that the report.
created The date of the expense
currency The original currency of the expense
currencyConversionRate The conversion rate between the original currency of the expense and that of the report
hasTax Whether the expense has tax associated
inserted The datetime when the transaction was created in Expensify
mcc The MCC code associated to the category of the expense
merchant The original merchant name of the expense
modifiedAmount The amount of the expense, as updated by the user
modifiedCreated The date of the expense, as modified by the user
modifiedMCC The mcc of the expense, as modified by the user
modifiedMerchant The merchant name, as modified by the user
ntagX The tag at the X-th level. Only set when multi-tagging is enabled. Example: ntag1, ntag2, etc.
ntagXGlCode The GL Code of the tag at the X-th level. Only set when multi-tagging is enabled
receiptFilename The name of the receipt attached to the expense
receiptID The unique ID of the receipt attached to the expense
receiptObject.smallThumbnail
receiptObject.thumbnail
receiptObject.transactionID
receiptObject.type
receiptObject.url The URL to the receipt
reimbursable Boolean determining if the expense is reimbursable
reportID The ID of the report associated to the expense
tag The tag of the expense. If multi-tagging is enabled on the policy, this will be the full tag, with each level separated by a colon
tagGlCode The GL Code associated to the tag
taxAmount The tax amount, set only if hasTax is true
taxName The name of the tax, set only if hasTax is true
taxRate The tax rate of the expense, set only if hasTax is true
taxRateName The name of the selected tax rate, set only if hasTax is true
taxCode The code of the selected tax, set only if hasTax is true
transactionID Unique ID of the expense
type The type of the expense. One of expense, distance or time
units.count Set only if type is distance or time
units.rate Set only if type is distance or time
units.unit Set only if type is distance or time

Misc.

Name Description
requesterEmail Email address of the user who triggered the script

How to use templates

To access the report data, loop over the reports array.

<#list reports as report>
    <#-- Here, data at the report level is accessed using the "report" object -->
    ${report.reportName},<#t>
    ${report.reportID},<#t>
    ${report.accountEmail}<#lt>
</#list>

To access the expense level data, loop over the transactionList object, defined in each report

<#list reports as report>
    <#-- Report level -->
    <#list report.transactionList as expense>
        ${expense.merchant},<#t>
        ${expense.amount},<#t>
        ${expense.category}<#lt>
    </#list>
</#list>

When an expense is imported from a credit card, the imported amount, merchant name and transaction date are respectively stored in amount, merchant and created. If the user modifies these afterwards, the updated values will be stored in modifiedAmount, modifiedMerchant and modifiedCreated. Also, if the expense is in a different currency, convertedAmount will be populated with the amount in the currency of the policy.

Here is how to get the data as it would appear on the website:

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

Note on large exports

When exporting a large amount of reports, it is good practice to wrap the header line of your template with a <#if addHeader == true>...</#if> statement. This allows Expensify to generate your export report in multiple separate chunks, thus increasing the speed of the process.

For instance, the following template:

Report ID, Report name
<#list reports as report>
    ${report.reportID},${report.reportName}<#lt>
</#list>

should be replaced with:

<#if addHeader == true>
    Report ID, Report name<#lt>
</#if>
<#list reports as report>
    ${report.reportID},${report.reportName}<#lt>
</#list>