Integrate between Expensify to Oracle

Sharad_Jain

Hi,

I have read the API document (https://integrations.expensify.com/Integration-Server/doc/#report-exporter) & trying to access the report data but getting the Template error.

My requirement is integrate the daily Expensify data to Oracle, I thing Expensify return the data with JSON format so return JSON data i will store in our database table & interface to ORACLE. Can you please help me to run correct CURL API?

I am trying to access report id data with below curl command as it is from document

curl -X POST 'https://integrations.expensify.com/Integration-Server/ExpensifyIntegrations'
    -d 'requestJobDescription={
        "type":"file",         
        "credentials":{
            "partnerUserID":"_ID_",
            "partnerUserSecret":"_CODE_"
        },
        "onReceive":{
            "immediateResponse":["returnRandomFileName"]
        },
        "inputSettings":{
            "type":"combinedReportData",
         "filters":{
                "reportIDList":"63459549,59422577"
            }
        },
        "outputSettings":{
            "fileExtension":"csv"
        },
    }' \
    --data-urlencode 'template=expensify_template.ftl'

Ted Harris

Hi @Sharad_Jain - thanks for querying this! I've taken the time to format your question correctly, please always use the code options from the formatting when you're querying this on the Expensify Community!

Could you elaborate on what the error you're seeing when you use this is?

The only difference I see from our documentation is your decision to use:

template=expensify_template.ftl

rather than:

template@expensify_template.ftl

That's possibly it!

Sharad_Jain

Hi Ted Harris,

Thanks for your response , I am not getting your point for code options , but anyway I will explain the requirement again.

Requirement is interface daily expensify data to oracle, Can you please explain how many ways available in Expensify to export the data in Oracle.

For the above code I am just checking the return data format from expensify and i am getting this error {"responseMessage":"No Template Submitted","responseCode":410}

But main concerns is automatic integrations from expensify data to oracle, Please explain this thing.

Thanks in advance for your all help & support.

Sasha Kluger

Hi @Sharad_Jain - I've provided responses to your questions below.

Requirement is interface daily expensify data to oracle, Can you please explain how many ways available in Expensify to export the data in Oracle.

In order to export Expensify data to Oracle daily, you will need to set up a recurring job that calls the Expensify API daily. The API allows you to set parameters and filters so that you only export data that hasn't been exported previously.

For the above code I am just checking the return data format from expensify and i am getting this error {"responseMessage":"No Template Submitted","responseCode":410}

Thank you for providing the error you received. When you call the Report Exporter API, you need to include a template so that we know how to format the output. The API documentation contains a sample template, and you can find more documnentation around creating templates here.

Sharad_Jain

Hi Sasha Kluger,

Thank you so much, Its very useful information for me.

In order to export Expensify data to Oracle daily, you will need to set up a recurring job that calls the Expensify API daily. The API allows you to set parameters and filters so that you only export data that hasn't been exported previously.--> Can you please share some sample api or steps to run this API? Is very helpful to me if you give complete details. Thanks

Sasha Kluger

Hi @Sharad_Jain - Our API is a self-service tool and the API Docs lay out several ways to call the API along the right side of the page. Additionally, other Expensify Community members may be able to provide you with more details on steps to run the API.

Sharad_Jain

Hi Sasha Kluger,

Can you please transfer this communication to other Expensify Community members or give me details to contact?

Please take this request as higher priority, on immediate basic we have to interface Expensify data to Oracle.

Thanks in advance for your support.

Cheryl Walsh

Hi @Sharad_Jain

Our API is a self-serve tool. You might want to have a look at other Oracle related questions and answers made by our other community members here. Thank you!

Sharad_Jain

Hi Cheryl Walsh,

I have checked the other community members answer , maximum are my post only and other also not relevant. If you have any confusion on my requirement then I am ready to come on Call.

As previous post also i have mention its urgent so please provide some solutions.

Cheryl Walsh

@Sharad_Jain We make our API available for users to access; however, it is a self-service tool which means you need to create the request yourself. If you are unable to complete this and cannot find the answers you need from other Community members, previous posts or the API docs- then we will not be able to assist you.

Sharad_Jain

Hi,

Now i am going through API docs, using reportid list filter but getting below error

curl: (6) Could not resolve host: 'https

curl: (6) Could not resolve host: \

curl: (3) URL using bad/illegal format or missing URL

curl: (3) unmatched brace in URL position 13:

Now can you please assist me?

I am using below script to run my command prompt

curl -X POST 'https://integrations.expensify.com/Integration-Server/ExpensifyIntegrations' \
    -d 'requestJobDescription={
        "type":"file",         
        "credentials":{
            "partnerUserID":"__",
            "partnerUserSecret":"__"
        },
        "onReceive":{
            "immediateResponse":["returnRandomFileName"]
        },
        "inputSettings":{
            "type":"combinedReportData",
         "filters":{
                "reportIDList":"51313295,51317515"
            }
        },
        "outputSettings":{
            "fileExtension":"csv"
        },
    }' \
    --data-urlencode 'template@expensify_template.ftl'

Ted Harris

Hi @Sharad_Jain - do you not get these options when you use the Community?

For Code Blocks:

For Code Lines:

I reformatted your code with that. These seem to be errors with an API request itself, not limited to just the Expensify API. It's possibly Google may be a better place to search than our Community here though. I'm afraid I'm not having any luck finding an answer for you myself.

Sharad_Jain

Hi,

Now I am going through API docs and getting below error

curl: (6) Couldn't resolve host ' --data-urlencode'

curl: (6) Couldn't resolve host 'expensify_template.ftl'

{"responseMessage":"No Template Submitted","responseCode":410}

Script ran in Linux , Can you please assist me for this error ?

Ted Harris

Could you please share the template you're using as well as this call?

Sharad_Jain

Hi Ted,

Thanks for code formatting now i have attached script in Code formatting.

I am using below API in Linux server ,please check the attached screenshots.

curl -X POST 'https://integrations.expensify.com/Integration-Server/ExpensifyIntegrations' -d ""'requestJobDescription={ "type":"file", "credentials":{ "partnerUserID":"__", "partnerUserSecret":"__" }, "onReceive":{ "immediateResponse":["returnRandomFileName"] }, "inputSettings":{ "type":"combinedReportData", "filters":{ "reportIDList":"51313295,51317515" } }, "outputSettings":{ "fileExtension":"csv" } }' \ --data-urlencode 'template@expensify_template.ftl'

Ted Harris

@Sharad_Jain:

1. I've removed your partner user ID and secret from that post.
2. This is just the call. We will also need to see the template in order to help resolve the "No Template Submitted" message.

Please include the template as well in your next response.

Sharad_Jain

Hi Ted,

Thank you so much actually i remove form screenshots but forget to remove from code.

How to add template to API ?

If any document to add template in Curl Script then please provide me it would be very helpful to me.

Thanks.

Ted Harris

You can see an example here and a list of the some of the most common export strings here.

Sharad_Jain

Hi Ted,

Now I am able to see data through POSTMAN or CURL script on Linux server but i have to run 2 times from 1 getting template name and that template name have to pass in 2 any options to used in single script. or

How to do this process automatic ? or

Any way to export today's approved all expense report data come into our server.

Any possibility to setup some scheduling in Expensify to interfaced data into our server as csv format.

Using this CURL script.

1

curl -X POST 'https://integrations.expensify.com/Integration-Server/ExpensifyIntegrations' -d ""'requestJobDescription={ "type": "file", "credentials": { "partnerUserID": "__", "partnerUserSecret": "__" }, "onReceive": { "immediateResponse": [ "returnRandomFileName" ] }, "inputSettings": { "type": "combinedReportData", "filters": { "startDate":"2019-01-01", "endDate":"2020-08-13" } }, "outputSettings": { "fileExtension": "csv" } }' -d ""'template=<#if addHeader == true> Report ID, Report name<#lt> </#if> <#list reports as report> ${report.reportID},${report.reportName}<#lt> </#list>'

2

curl -X POST 'https://integrations.expensify.com/Integration-Server/ExpensifyIntegrations' -d ""'requestJobDescription={"type": "download", "credentials": { "partnerUserID": "__", "partnerUserSecret": "__" }, "fileName": "export34575ee2-6c54-44ff-8672-ba8423bfbd68.csv", "fileSystem": "integrationServer" }'

I am writing this all thing from BCG account & this all requirement is on high priority.

Please provide the best solutions for this.

Ted Harris

How to do this process automatic?

That is entirely a process on your own engineering and infrastructure team. Expensify doesn't facilitate any recurring job for you - you must set something up to run this at a recurring interval you specify.

Any way to export today's approved all expense report data come into our server.

Yes, you would simply build the logic to search for "today's approved all expense report data" into the template.

Any possibility to setup some scheduling in Expensify to interfaced data into our server as csv format.

Again, this is entirely something you must build and maintain. Our API is there to return data when you request it and in the format you request it. You are responsible for owning and building the script and job to request that data on a schedule and store the returned data in a system or format you need.

Sharad_Jain

Hi Ted Harris,

Thank you for your response.

I am able to download the data but data is not coming in proper format.

Can you please suggest?

I am using below CURL script

curl -X POST 'https://integrations.expensify.com/Integration-Server/ExpensifyIntegrations' -d ""'requestJobDescription={ "type": "file", "credentials": { "partnerUserID": "_Replace_", "partnerUserSecret": "_Replace_" }, "onReceive": { "immediateResponse": [ "returnRandomFileName" ] }, "inputSettings": { "type": "combinedReportData", "filters": { "startDate":"2019-01-01", "endDate":"2020-08-13" } }, "outputSettings": { "fileExtension": "xls" } }' -d ""'template=<#if addHeader == true> Report ID, Report name<#lt> </#if> <#list reports as report> ${report.reportID},${report.reportName}<#lt> </#list>'

For Download

curl -X POST 'https://integrations.expensify.com/Integration-Server/ExpensifyIntegrations' -d ""'requestJobDescription={"type": "download", "credentials": { "partnerUserID": "_Replace_", "partnerUserSecret": "_Replace_" }, "fileName": "exporta567156c-f63a-4193-ba96-2b032f9b8c3c.xls", "fileSystem": "integrationServer" }' -o 13aug20_1.xls

Attaching the file which are received both format same issue xls & csv.

13aug20_1.xls

Please let me know where i have to correct in script.

Ariel Green

@Sharad_Jain what format are you wanting?

Sharad_Jain

Hi Ariel,

Thank you for your response.

All expense report data is coming in single line.

Can you please check attached sheet in above post "13aug20_1.xls".

It should be in this format but data is coming in single line.

Ted Harris

The template you're currently using appears to be:

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

You don't appear to be using <#t> after your ${report.reportID}, when including the template as Freemarker requires. Could you try formatting the template with this as stipulated in the example on the Integrations page.

Sharad_Jain

Hi Ted Harris,

I am not getting your point can you please give me new line tag ?

Which tag i have to used for new line .

Now i am using below code for template

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

Maddy Lewis

@Sharad_Jain - Sure! Let me clarify.

You'll want your template to look like this:

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

The <#t> was missing after ${report.reportID},

Sharad_Jain

Hi Maddy Lewis,

Thanks for your response.

Still facing same problem, all data is coming in single line.

output file screenshots

Main Script

curl -X POST 'https://integrations.expensify.com/Integration-Server/ExpensifyIntegrations' -d ""'requestJobDescription={ "type": "file", "credentials": { "partnerUserID": "_Replace_", "partnerUserSecret": "_Replace_" }, "onReceive": { "immediateResponse": [ "returnRandomFileName" ] }, "inputSettings": { "type": "combinedReportData", "filters": { "startDate":"2019-01-01", "endDate":"2020-08-13" } }, "outputSettings": { "fileExtension": "csv" } }' -d ""'template=<#if addHeader == true> Report ID, Report name<#lt> </#if> <#list reports as report> ${report.reportID},<#t> ${report.reportName}<#lt> </#list>'

Download Script 

curl -X POST 'https://integrations.expensify.com/Integration-Server/ExpensifyIntegrations' -d ""'requestJobDescription={"type": "download", "credentials": { "partnerUserID": "_Replace_", "partnerUserSecret": "_Replace_" }, "fileName": "export29c893ab-61a6-41e9-9db0-0324ac3db8d7.csv", "fileSystem": "integrationServer" }' -o 13aug20_9.csv

above script using for this.

output should be in this format but is coming in above screenshots format

Please suggest to correct template tag.

Sharad_Jain

Hi,

Can you please reply on above query ?

Ted Harris

Hi @Sharad_Jain - just wanted to re-confirm that this is the Expensify Community and whilst the employees of Expensify will always try to hop in to help when we know the answer, if we don't, we will leave it open for the rest of the Community to weigh in on if they can.

I've reached out internally to see if anybody else knows what your problem is. If we have an answer for you, we'll reach back out here.

In my tests for these reports for your account, the template for:

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

does seem to work just fine.

Francois Laithier

Hi @Sharad_Jain,

The template parameter is the only thing that has an effect on the output format. If you want line breaks in the output, there must be line breaks in the template as well.

To prevent issues with special characters, you'll also want to use --data-urlencode instead of -d/--data :

curl -X POST 'https://integrations.expensify.com/Integration-Server/ExpensifyIntegrations' -d 'requestJobDescription={ "type": "file", "credentials": { "partnerUserID": "_Replace_", "partnerUserSecret": "_Replace_" }, "onReceive": { "immediateResponse": [ "returnRandomFileName" ] }, "inputSettings": { "type": "combinedReportData", "filters": { "startDate":"2019-01-01", "endDate":"2020-08-13" } }, "outputSettings": { "fileExtension": "csv" } }' --data-urlencode 'template=<#if addHeader == true>Report ID,Report name<#lt> 
</#if><#list reports as report>
${report.reportID},${report.reportName}<#lt>
</#list>'

Incidentally, this is why we always recommend storing the template data in its own file, and then using the @ operator to make curl load the content of the file, instead of dealing with line breaks in the request line.

Sharad_Jain

Hi Francois Laithier,

Thank you for your response.

I have handle all the tag in template, Now its working fine.

I will post all consolidate point what i face & what is the solutions for this it will help to new person.

Thank you so much to Expensify Team for all support.