Powershell Invoke-RestMethod for Report Exports

DMuncyDMuncy Posts: 2 Expensify Newcomer
edited February 4 in Integrations and API

Good Evening Everyone,
I noticed most of the resources on here related to Powershell didn't have many successful results. Below is my successful working code to generate PDF reports, then export an XML document for each of those reports following a format for DocStar.

I noticed a post before by @MarcusG having "Error while parsing job description" issues and discovered it was due to the JSON being incorrect. It's case sensitive and the "onReceive" is an array.

The templates can be an absolute nightmare. I finally found I just had to start requesting fields and seeing what happens. Trying to mix and match data from the Reports and Expense level really messed with me, and found it was almost easier to just get the data as generically as possible then use powershell to manipulate it.

# Bypasses the Fails to Could not create SSL/TLS secure channel error
[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12

# Adds System.web assembly for the HttpUtility to work for the encoding of the URL to work
Add-Type -AssemblyName System.Web

$startDate = 2019-01-01
$endDate = 2019-01-01

# Credentials, prompts the user for their api credentials.
$authentication = Get-Credential -Message "Expensify PartnerID and PartnerSecret"
$UserID = $authentication.UserName
$UserSecret = $authentication.GetNetworkCredential().Password

# Endpoint to connect
$url = "https://integrations.expensify.com/Integration-Server/ExpensifyIntegrations?requestJobDescription="

# User to pull reports from (email used in expensify)
$useraccountemail = "[email protected]"

<# Creates the job for the approved reports
Ordered is not required but helps make it easier to read.
immediateResponse is an array
#>
$json = [ordered]@{
        "type"="file";
        "credentials"= @{
            "partnerUserID" = "$userID";
            "partnerUserSecret" = "$userSecret"
        };
        "onReceive"[email protected]{
            "immediateResponse"[email protected]("returnRandomFileName")
        };

        "inputSettings"= @{
            "type" = "combinedReportData";
            "reportState" = "APPROVED";
            "employeeEmail" = "$useraccountemail";
            "filters" = @{
                "startDate" = "$startDate";
                "endDate" = "$endDate"
                }

        };

        "outputSettings" = @{
            "fileExtension" = "pdf";
            "includeFullPageReceiptsPdf" = "true";
            "fileBasename"="myExport"
        }
} | ConvertTo-Json

#template can be empty for the PDF report
$template = ''

$encode = [System.Web.HttpUtility]::UrlEncode($template)

# Generates the URI with Json for authentication
$urlson = $url + $json

$result = Invoke-RestMethod -Method post -uri ($urlson + "'&template='$encode") -ContentType 'application/json'
# If the query fails, script stops with the error message.
if ($result.responseMessage) {
$result.responseMessage
Break
} Else {$expense = $result -split ","}

# Gets all the report ID numbers to feed into the XML report generator.
$reportid = foreach ($item in $expense){($item.Substring($item.LastIndexOf('-')+1)).replace(".pdf","")}

# Downloads the report (file name needs to be the name of the file returned from the above

foreach ($item in $expense) {

$down = [ordered]@{
       "type"="download";
        "credentials"= @{
            "partnerUserID" = $UserID;
            "partnerUserSecret" = $UserSecret;
        };
        "fileName"="$item";
        "fileSystem"="integrationServer"
      } | ConvertTo-json
$urlson = $url + $down
# The output is setup to strip the random string and leave just the reportID
Invoke-RestMethod -Method post -uri ($urlson + "'&template='$encode") -ContentType 'application/json' -OutFile (".\" + $item.Substring($item.LastIndexOf('-')+1))
}

#End of PDF report generation

#BEGIN generates the XML data

foreach ($report in $reportID) {
$docstar = [ordered]@{
        "type"="file";
        "credentials"= @{
            "partnerUserID" = $UserID;
            "partnerUserSecret" = $UserSecret;
        };
        "onReceive"[email protected]{
            "immediateResponse"[email protected]("returnRandomFileName")
        };

        "inputSettings"= @{
            "type" = "combinedReportData";
            "filters"[email protected]{
                "reportIDList" = "$report" 
                }
        };
        "outputSettings" = @{
            "fileExtension" = "xml"
        }
    }
}

<# Defines the layout of the report itself and what fields are provided 
My use case is a template for DocStar import.
I did find that there are some fields unreported in the API reference that can be used from
https://docs.expensify.com/setup-for-admins-and-accountants/using-your-policies/custom-formulas
I use a double quotes as there are parts I removed that needed to reference variables.
Because of this, each $ will have to be escaped with a back tick, as do any other double quotes
The template is just setting the text format, change to CSV fields if you like ans set the extension.

#>
$template = "
<#list reports as report>
<#setting number_format`=`"#0.00`"`><#-- Defines number should be treated with 2 decimal places. There won't be any comma separators. So that pricing, has the correct format -->
<#setting datetime_format`=`"yyyy-MM-dd HH:mm:ss`"><#-- Defines the ?datetime format to match what the report.created outputs to recognize it as a date -->
<#setting date_format`=`"yyyy-MM-dd`"><#-- might be redundant, but sets the ?date format to convert dates to -->
<?xml version`=`"1.0`"?>
 <DOCUMENT>
   <DATA>
     <Document_Type>Invoice</Document_Type>
     <User_ID>`${report.accountEmail}</User_ID>
     <Invoice_Number>`${report.reportID?c}</Invoice_Number><#-- the ?c formats numbers as a computer would, up to 16 characters. No separators. -->
     <Invoice_Date>`${report.created?date}</Invoice_Date>
     <Invoice_Amount>`${report.total/100}</Invoice_Amount>
     <Facility_ID>glfacilityid</Facility_ID><#-- String is later searched to be replaced with the tagGlCode -->
     <#list report.transactionList as expense>
     <#if expense.modifiedAmount?has_content>
     <#assign amount = expense.modifiedAmount/100>
     <#else>
     <#assign amount = expense.amount/100>
     </#if>
     <GL_Distributions>
       <GL_Facility_ID>`${expense.tagGlCode}</GL_Facility_ID>
       <GL_Account>`${expense.tagGlCode}-`${expense.categoryGlCode}</GL_Account>
       <DistType>Purch</DistType>
       <Debit>`${amount}</Debit>
       <Credit>`${expense.creditamount}</Credit>
       <DistRef>`${expense.comment}</DistRef>
     </GL_Distributions>
     </#list>
   </DATA>
 </DOCUMENT>
</#list>
"
$encode = [System.Web.HttpUtility]::UrlEncode($template)

$urlstar = $url + $docstar

# need to add check for $docstarxml.result and display the message as it indicates  a failure
$docstarxml = Invoke-RestMethod -Method post -uri ($urlstar + "'&template='$encode") -ContentType 'application/json'

#This code downloads the report (file name needs to be the name of the file returned from the above

$down = @{
        "type"+"download";
        "credentials"= @{
            "partnerUserID" = $UserID;
            "partnerUserSecret" = $UserSecret;
        },
        "fileName"="$docstarxml";
        "fileSystem"="integrationServer"
    }

$urldown = $url + $down
$xmlout = (".\" + $report + ".xml")

#loads the contents of the xml document into memory
$xmlcontent = Invoke-RestMethod -Method post -uri ($urldown + "'&template='$encode") -ContentType 'application/json'

$reportxml = $null

#imports the xml data as a powershell object allowing the data to be manipulated.
#However the file downloads with something (Byte Order Marker?) at the beginning the corrups it, this removes the first line
[xml]$reportxml = $xmlcontent -split "`n" | Select-Object -skip 1

#sets the FacilityID field used above the GL GL_Distributions
$reportxml.DOCUMENT.DATA.Facility_ID = ($reportxml.DOCUMENT.DATA.GL_Distributions.GL_Facility_ID | Select-Object -First 1).tostring()
#any credits without a value are set to "0.00"
foreach ($item in $reportxml.DOCUMENT.DATA.GL_Distributions | Where-Object {$_.credit -eq ""}) {$item.credit = "0.00"}
$reportxml.Save($xmlout)
}

Hopefully this helps anyone else trying to do some exports with Powershell.

Cheers,
Dustin

Answers

Sign In or Register to comment.