Extracting Report using API.

Pradeep_R
Pradeep_R Expensify Customer Posts: 1

Hello all,

I am trying to extract the report using Expensify APIs by following the steps below.

API References:

//File Export:

{    "type":"file",    

 "credentials":{      "partnerUserID":"//Valid UserID",

"partnerUserSecret":"//ValidUserSecret"    },     

 "onReceive":{       

 "immediateResponse":["returnRandomFileName"]    },     

 "inputSettings":{      "type":"combinedReportData",       

"reportState":"REIMBURSED,APPROVED",      

"limit":"10",       

"filters":{        

"startDate":"2023-07-01",        

"endDate":"2023-09-30",         

"markedAsExported":"Expensify Export"      }    },    

"outputSettings":{      

"fileExtension":"csv",       

"fileBasename":"myExport"    }

}

//Template

<#if addHeader == true>Submitted,AccountID,Merchant,Amount,Category,currency,reportID,mcc,convertedAmount,reimbursable,tag,ManagerUserID,PolicyID,ApproverUserID</#if>

<#list reports as report>

<#-- Report level -->

<#list report.transactionList as expense>

${report.submitter.fullName},<#t>

${report.accountID},<#t>

${expense.merchant},<#t>

${expense.amount/100},<#t>

${expense.category},<#t>

${expense.currency},<#t>

${expense.reportID},<#t>

${expense.mcc},<#t>

${expense.convertedAmount},<#t>

${expense.reimbursable},<#t>

${expense.tag},<#t>

${report.managerUserID},<#t> // Required Help

${report.policyID},<#t>

${report.approvers}<#lt> // Required Help

</#list>

</#list>


//Downloader

{

  "type": "download",

  "credentials": {

     "partnerUserID":"//ValiduserId",

"partnerUserSecret":"//ValidUserSecret",

  },

  "fileName": "Output from above export API",

  "fileSystem": "integrationServer"

}


This API call gives me the below output CSV file but I am not getting the required value for the field approvers / ManagerUserID.

I created a template by referring to this https://integrations.expensify.com/Integration-Server/doc/export_report_template.html.

* Approvers is in array! I just wanted to confirm whether the template formula that I have used to extract this approver is valid or not.

Even though I tried to define the approver in the report's policy, the formula I was using was not a valid one.

Not sure if this is mandatory to extract the approver information from the report.

* ManagerUserID- I used this field instead of approvers to get the approver but still, it has not given me any value.


Note:

My report is in approved status and I believe they should return a value of the approver.


Can anyone tell me where I am making a mistake in pulling the approver value? If the template formula that I am using is wrong, can anyone suggest how to write a formula to extract the approver value?

Any suggestion is appreciated.

Thanks

Pradeep

Answers

  • adobos
    adobos Expensify Customer Posts: 2 Expensify Newcomer

    Hi Pradeep,

    If you only have one approver you can use ${report.approvers[0].fullName} instead of ${report.approvers} to get the full name of the first approver.

    For the ManagerUserID, I would try managerEmail. I am not sure why this is blank.

    If you have multiple approvers and want the whole list you can add a function similar to:

    <#function concatApprovers apps><#local applist = \"\"><#list apps as app><#local applist = applist + \"{email:\" + app.email + \"|date:\" + app.date  + \"}|\"></#list><#local applist = \"["+ applist?remove_ending(\"|\") +\"]\"><#return applist></#function>

    Then instead of ${report.approvers}<#lt> call the function "${concatApprovers(report.approvers)}." This does use a pipe "|" instead of comma because the commas cause issues when trying to use the csv.

    Best of luck,

    Alan