How To: Auto-Export your Approved Expenses to Google Sheets with Zapier

Ted Harris

The Expensify API is incredibly powerful, and more and more tools allow you access to your Expensify data if you can wield it well. Below is just one of the ways you can use it to export your data to a Google Sheet.

Prerequisites:

1. You must have a paid Zapier account in order to accomplish this as it uses multiple steps and the Premium Expensify App.
2. You must have your partnerUserID and partnerUserSecret, which can be gathered from https://www.expensify.com/tools/integrations/

Set up the Zap:

The Zap is fairly simple. In a nutshell, we'll get the report ID from the Expensify App. Then we'll call the Expensify API to get all the data associated with that report. Then we'll get the CSV file for that report. Then we'll parse that data into the linked Google Sheet. Let's go!

• Choose the trigger of "Expensify" and "New Report" - this is the only option right now. You can trigger on any report state, but you likely only want to export "final" data, so this would be Approved or Closed.

• Next choose Webhooks by Zapier, and set it to POST:

Within the "Action" settings, you'll populate the following variables as so:

URL: https://integrations.expensify.com/Integration-Server/ExpensifyIntegrations

Payload Type: Custom and then input: requestJobDescription

Data: There are two variable names. These will be: requestJobDescription and template

• The payload for requestJobDescription is below. Note, you can add one other inputSettings if you like - the policyIDList filter, in case you only want to export the report if it's on a certain policy.

{
    "type": "file",
    "dry-run" : false,
    "credentials": {
      "partnerUserID": "ENTER YOUR PARTNER USER ID HERE",
      "partnerUserSecret": "ENTER YOUR PARTNER USER SECRET HERE"
    },
    "onReceive":{
      "immediateResponse":["returnRandomFileName"]
    },
    "inputSettings":{
      "type":"combinedReportData",
      "reportState":"APPROVED,ARCHIVED",
      "limit":"1",
      "filters":{
        "reportIDList":"CHOOSE THE REPORT ID FROM THE ZAPIER DROPDOWN HERE",
      }
    },
    "outputSettings":{
      "fileExtension":"csv",
      "fileBasename":"myExport"
    },
    "onFinish":[
      {"actionName":"markAsExported","label":"Expensify Export"},
      {"actionName":"email","recipients":"YOUR EMAIL ADDRESS", "message":"New expenses have been approved. They have been added here: THE GOOGLE SHEET URL YOU'LL FIND THESE ON and can be found in the download below:"}
    ]
}

• The payload for template is your export template. You can make this whatever you like, and it should match the Google Sheet headings you want. You can see the default one from the API documentation here.

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

Wrap Request In Array: No

File: Leave blank

Unflatten: Yes

Basic Auth: Leave blank

Headers: Leave blank

• Add another Webhooks by Zapier step. Again, choose POST. The data will all be the same, except there is only one Payload, the requestJobDescrition, which this time is:

{
    "type": "download",
    "dry-run" : false,
    "credentials": {
		   "partnerUserID": "ENTER YOUR PARTNER USER ID HERE",
			 "partnerUserSecret": "ENTER YOUR PARTNER USER SECRET HERE"
    },
    "fileName":"THE CSV FILENAME YOU GOT WHEN TESTING YOUR PREVIOUS STEP",
    "fileSystem":"integrationServer"
}

• Next, choose "Formatter by Zapier" and opt for "Utilities". From the next dropdown, choose "Import CSV File". The choose the CSV file you downloaded with your second POST request.

• Next, you want to use the Zapier Google Sheets integration to "Create multiple spreadsheet rows". In your Action setup, simply map each row from the CSV to the header you've created in Google Sheets.

Would you like to see this functionality added to the native Expensify Zapier app? Let us know!