How to get data

RandomRandom Posts: 2

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.

Answers

  • Sheena TrepanierSheena Trepanier Posts: 1,657 admin

    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.

  • RandomRandom Posts: 2

    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 TrepanierSheena Trepanier Posts: 1,657 admin

    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.

  • BlouBlouBlouBlou Posts: 1

    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.

Sign In or Register to comment.