How to get data

Random
Random Expensify Customer Posts: 10 Expensify Newcomer

Hi,
I have a google sheet where I maintain all my data. I need it to pull expenses I enter in expensify. The API documentation is bit confusing. It talks about reports, templates and files. All I need to be able to do through API is.. given few filters (date of expense and a given tag) return all expenses matching that filter in json format.
Is that possible ? Would really appreciate some help.

Best Answer

  • BlouBlou
    BlouBlou Expensify Customer Posts: 1 Expensify Newcomer
    Answer ✓

    Hello,

    I ve similar requirements: getting all data from expensify to integrate in our accounting system.
    I end up wrapping expensify "report" api with the following steps:

    1. generate a report
    {
      "url": "https://integrations.expensify.com/Integration-Server/ExpensifyIntegrations",
      "method": "POST",
      "form": {
        "requestJobDescription": "{\"type\":\"file\",\"onReceive\":{\"immediateResponse\":[\"returnRandomFileName\"]},\"outputSettings\":{\"fileExtension\":\"json\"},\"inputSettings\":{\"type\":\"combinedReportData\",\"reportState\":\"APPROVED\",\"limit\":10000,\"filters\":{\"startDate\":\"2018-07-30\"}},\"credentials\":{\"partnerUserID\":\"xxxxxxxxxxxxxxxxxxx\",\"partnerUserSecret\":\"xxxxxxxxxxxxxxxxxxx\"}}",
        "template": "\n<#assign black_list = [] />\n<#--\n  -- The main macro.\n  -->\n\n<#macro stringify data escape=\"none\"><@compress single_line=true>\n  <#assign generated_json><@rawStringify data /></#assign>\n  <#if escape == \"html\">\n    ${generated_json?html}\n  <#elseif escape == \"js_string\">\n    ${generated_json?js_string}\n  <#else>\n    ${generated_json}\n  </#if>\n</@compress></#macro>\n\n<#-- private helper macros. it's not recommended to use these macros from\n  -- outside the macro library.\n  -->\n\n<#macro rawStringify data>\n  <#if data?is_enumerable>\n    <@printList data,[] />\n  <#elseif data?is_hash_ex>\n    <@printHashEx data,[] />\n  </#if>\n</#macro>\n\n<#macro printList list has_next_array>\n  <#local counter=0 />\n  <#local highestIndex = list?size-1 />\n  <#t>[<#list list as item><@printListItem item?if_exists,has_next_array+[item_has_next], counter /><#if counter < highestIndex>,</#if><#local counter = counter + 1/></#list>]\n</#macro>\n\n<#macro printHashEx hash has_next_array>\n  <#local isFirst=true />\n  <#t>{<#list hash?keys as key><#if !isFirst>,</#if><@printItem hash[key]?if_exists,has_next_array+[key_has_next], key /><#local isFirst = false /></#list>}\n</#macro>\n\n<#macro printItem item has_next_array key>\n<#if item?is_enumerable>\n<#t>\"${key?js_string}\":<@printList item, has_next_array />\n<#elseif item?is_hash_ex && omit(key?string)><#-- omit bean-wrapped java.lang.Class objects -->\n<#elseif item?is_hash_ex>\n<#t>\"${key?js_string}\":<@printHashEx item, has_next_array />\n<#elseif item?is_number>\n<#t>\"${key?js_string}\":${item?string.computer}\n<#elseif item?is_string>\n<#t>\"${key?js_string}\":\"${item?js_string}\"\n<#elseif item?is_boolean>\n<#t>\"${key?js_string}\":${item?string}\n<#elseif item?is_date>\n<#t>\"${key?js_string}\":\"${item?string(\"yyyy-MM-dd'T'HH:mm:sszzzz\")}\"\n</#if>\n</#macro>\n\n<#macro printListItem item has_next_array key>\n<#if item?is_enumerable>\n<#t><@printList item, has_next_array />\n<#elseif item?is_hash_ex && omit(key?string)><#-- omit bean-wrapped java.lang.Class objects -->\n<#elseif item?is_hash_ex>\n<#t><@printHashEx item, has_next_array />\n<#elseif item?is_number>\n<#t>${item?string.computer}\n<#elseif item?is_string>\n<#t>\"${item?js_string}\"\n<#elseif item?is_boolean>\n<#t>${item?string}\n<#elseif item?is_date>\n<#t>\"${item?string(\"yyyy-MM-dd'T'HH:mm:sszzzz\")}\"\n</#if>\n</#macro>\n\n<#function omit key>\n    <#local what = key?lower_case>\n    <#list black_list as item>\n        <#if what?index_of(item) gte 0>\n            <#return true>\n        </#if>\n    </#list>\n    <#return false>\n</#function>\n{ \"reports\":<@stringify data=reports />}"
      },
      "encoding": "utf8"
    }
    

    in details:

    • requestJobDescription: change filters based on your needs
    {
      "type": "file",
      "onReceive": {
        "immediateResponse": [
          "returnRandomFileName"
        ]
      },
      "outputSettings": {
        "fileExtension": "json"
      },
      "inputSettings": {
        "type": "combinedReportData",
        "reportState": "APPROVED",
        "limit": 10000,
        "filters": {
          "startDate": "2018-07-30"
        }
      },
      "credentials": {
        "partnerUserID": "xxxxxxxxxxxxxxxxxxxx",
        "partnerUserSecret": "xxxxxxxxxxxxxxxxxxxx"
      }
    }
    
    1. download file
    {
      "type": "download",
      "fileName": "RANDOM_FILE_NAME_PROVIDED_BY_PREVIOUS_CALL",
      "fileSystem": "integrationServer",
      "credentials": {
        "partnerUserID": "xxxxxxxxxxxxxxxxxxxxxxxx",
        "partnerUserSecret": "xxxxxxxxxxxxxxxxxxxxxxxx"
      }
    }
    
    1. you can use report json to push any data to another system.

    btw: why in 2018 expensify do not have a basic JSON rest api?
    report exporter with full customization is great feature, but a pain if we just want to integrate data to another system.

Answers

  • Sheena Trepanier
    Sheena Trepanier Expensify Team, Approved! Accountant, Expensify Student Ambassador Posts: 1,362 Expensify Team

    Hello @Random, thanks for joining the Community! In order to export information from Expensify, you'll need to use a template in your call. We have a guide to help you build your template here.

  • Random
    Random Expensify Customer Posts: 10 Expensify Newcomer

    Thanks Sheena. Unfortunately, it is still not clear how I will achieve my objective. (BTW I am a software engineer who work on web api's). There is no overview of how API works (what are different objects which can be queried and how) and it is confusing (it directly jumps into reports generation instead of plain reads in json format). The API documentation seems incomplete. In several places, possible values of expected field are not provided. For e.g. requestJobDescription has a field called 'type'. There is no enumeration of what values I can provide.
    Please don't take it as criticism. I love expensify would love for it to be wildly popular. If you can make api and it's documentation more user friendly, lot of people will be able to integrate it with other services like IFTTT, google sheets etc.

  • Sheena Trepanier
    Sheena Trepanier Expensify Team, Approved! Accountant, Expensify Student Ambassador Posts: 1,362 Expensify Team

    Hi @Random - sorry things aren't so clear still. I just want to confirm you're using both API resources we offer correct? You can find documentation here as well as here.

    Typically the API is pretty self-serve but I really want to be able to help you out as much as I can. If you can share some specific questions you have, I can get to work getting answers and instructions for you.

    Regarding the "requestJobDescription" that you mentioned previously, the value for type is listed here. If you're exporting reports you'll want to input "File" for the type so that the job results in an exported file.

  • caglarcem
    caglarcem Expensify Customer Posts: 4 Expensify Newcomer
    edited September 2019

    Hi,

    Seems like it's been a while since this is posted. I just started exploring api integration and have a similar issue.

    @Sheena Trepanier I followed the steps explained above. First generated a report, then attempted to download by the given random filename (*.json in my case) .

    I expect it to return a json response with all the expense information (is that right?)

    But all I'm getting is a template filename as the response- "template@expensify_template.ftl"

    So, is my expectation correct? Why could I be getting just a string instead?

    Cheers

  • Random
    Random Expensify Customer Posts: 10 Expensify Newcomer

    @caglarcem .. i think the extension is just for generating filename. it doesn't impact the content. Actual content/format of file are decided by template. I ended up generating a comma separated file using template, which i then prase.

  • caglarcem
    caglarcem Expensify Customer Posts: 4 Expensify Newcomer

    hi @Random thanks for clarifying that. Is generating a template done by the UI expensify providing? I need to automate the process, just like you needed. So, can you please clarify a bit further, what steps do I need to take for getting all the expense records based on a filter?

  • Random
    Random Expensify Customer Posts: 10 Expensify Newcomer

    I know it is very confusing. Getting data from expensify is two step process -

    1) Send a report request (with template and query). Output of report request is a file name.

    2) fetch the file name and parse it to get relevent data out.

    You can make template using info from this page - https://integrations.expensify.com/Integration-Server/doc/export_report_template.html


    will try to see if can remove my personal code from my program and post it some on pastebin somewhere..