Extracting Report using API.
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
-
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
