Using Powershell's Invoke-RestMethod cmdlet to export report data from Expensify

MarcusGMarcusG Posts: 6Expensify Customer Expensify Newcomer
edited January 21 in Integrations and API

Has anyone had luck using Powershell's Invoke-RestMethod cmdlet to export report data from Expensify? So far, everything I try returns 'requestJobDescription' is missing. I've been using https://integrations.expensify.com as my reference. I'm trying to automate report exports to a csv file using the Expensify API.

Answers

  • MarcusGMarcusG Posts: 6Expensify Customer Expensify Newcomer

    I have made progress. My next challenge is to figure out what the Expensify API is looking for exactly in regards to a template. I'm getting a "No Template Submitted" error now.

    This is the code I have so far in Powershell:

    $url = "https://integrations.expensify.com/Integration-Server/ExpensifyIntegrations?requestJobDescription="
    
    $json = ConvertTo-Json @{
    
            type = "file";
            credentials = @{
                partnerUserID = "_replace_";
                partnerUserSecret = "_replace_";
            };
        
            inputSettings = @{
                type = "combinedReportData";
                reportState = "APPROVED,REIMBURSED"
            };
    
            outputSettings = @{
                fileExtension = "csv"
            }
    
    }
    
    $template =
    '
    [email protected]_template.ftl=
    <#if addHeader == true>
        Merchant,Amount,Category,Report number,Expense number<#lt>
    </#if>
    <#assign reportNumber = 1>
    <#assign expenseNumber = 1>
    <#list reports as report>
        <#list report.transactionList as expense>
            ${expense.merchant},<#t>
            ${expense.amount},<#t>
            ${expense.category},<#t>
            ${reportNumber},<#t>
            ${expenseNumber}<#lt>
            <#assign expenseNumber = expenseNumber + 1>
        </#list>
        <#assign reportNumber = reportNumber + 1>
    </#list>
    '
    
    $encode = [System.Web.HttpUtility]::UrlEncode($template)
    
    Invoke-RestMethod -Method Post -Uri $url$json$encode
    
  • MarcusGMarcusG Posts: 6Expensify Customer Expensify Newcomer
    edited January 2018

    I think I have successfully passed a template through the API. Now I have the generic error of "Error while parsing job description". Any advice would be appreciated.

    Current Code:

    $url = "https://integrations.expensify.com/Integration-Server/ExpensifyIntegrations?requestJobDescription="
    
    $json = ConvertTo-Json @{
            type = "file";
            credentials = @{
                partnerUserID = "_replace_";
                partnerUserSecret = "_replace_"
            };
            onReceive = @{
                immediateResponse = "returnRandomFileName"
            };
    
            inputSettings = @{
                type = "combinedReportData"
            };
    
            outputSettings = @{
                fileExtension = "xlsx";
                fileBasename = "myExport"
            }
    
    }
    
    $template = '
    <#if addHeader == true>
        Merchant,Amount,Category,Report number,Expense number<#lt>
    </#if>
    <#assign reportNumber = 1>
    <#assign expenseNumber = 1>
    <#list reports as report>
        <#list report.transactionList as expense>
            ${expense.merchant},<#t>
            ${expense.amount},<#t>
            ${expense.category},<#t>
            ${reportNumber},<#t>
            ${expenseNumber}<#lt>
            <#assign expenseNumber = expenseNumber + 1>
        </#list>
        <#assign reportNumber = reportNumber + 1>
    </#list>
    '
    
    $encode = [System.Web.HttpUtility]::UrlEncode($template)
    
    Invoke-RestMethod -Method Post -Uri $url$json'&template='$encode
  • vimvenvimven Posts: 11Expensify Customer Expensify Newcomer

    Hi Marcus, I'm able to pass the job description fine by using the API code and replacing the necessary parameters. However i'm not sure how to get the output file.

  • MarcusGMarcusG Posts: 6Expensify Customer Expensify Newcomer

    I'm still getting the error: "Error while parsing job description". Once I figure out what's causing that for me the API should send back a filename. I think I would then use that filename in the following second call.

    $down = ConvertTo-Json @{
            type = "download";
            credentials = @{
                partnerUserID = "_REPLACE_";
                partnerUserSecret = "_REPLACE_"
            };
            
            fileName = "myFile.csv"
        }
    
    Invoke-RestMethod -Method Post -Uri $url$down
    
  • AlexHeddellAlexHeddell Posts: 1Expensify Customer Expensify Newcomer
    edited February 2018

    Sorry if this is obvious but you are missing the "reportJobDescription=" from before the JSON clause. Should look something more like:

    requestJobDescription={
    "type":"file",
    "credentials":{
    "partnerUserID":"",
    "partnerUserSecret":""
    },
    "onReceive":{
    "immediateResponse":["returnRandomFileName"]
    },
    "inputSettings":{
    "type":"combinedReportData",
    "reportState":"APPROVED,REIMBURSED",
    "limit":"1",
    "filters":{
    "startDate":"2017-01-01",
    "endDate":"2050-05-01",
    "markedAsExported":"Expensify Report"
    }
    },
    "outputSettings":{
    "fileExtension":"xml"
    }
    }&template=....

    Also, I think you are trying to include the JSON in your URI. It should be in the Body of the message - use the --Body argument for Invoke-RestMethod

  • MarcusGMarcusG Posts: 6Expensify Customer Expensify Newcomer
    edited February 2018

    Hi Alex. I have tried sending the requestJobDescription parameter in several ways. In the code above this parameter is attached to the URI.

    I have switched the JSON to the -Body parameter. That seems OK but now I'm back to the 'requestJobDescription' is missing error. I've tried wrapping type and credentials with requestJobDescription in the body. I've tried sending it in the -Headers parameter. I've tried sending it directly in the URI. I just can't seem to find where the Expensify API is expecting it. The best luck I've had to where it doesn't say it's missing is when I appended it to the URI as ?requestJobDescription=.

  • jweber_126jweber_126 Posts: 9Expensify Customer Expensify Newcomer
    edited November 2018

    Marcus,

    This code worked for me (please note requestJobDescription in the $url and the double quotations around the fields)

    [Net.ServicePointManager]::SecurityProtocol = "tls12, tls11, tls"

    $json = ConvertTo-Json @{
    type = "file";
    credentials = @{
    partnerUserID = "aa_jweber126_outlook_com";
    partnerUserSecret = "REPLACE"
    };
    onReceive = @{
    immediateResponse = "returnRandomFileName"
    };

                    inputSettings = @{
                        type = "combinedReportData"
                    };
    
                    outputSettings = @{
                        fileExtension = "csv";
                    }
            }
    
            $url = "https://integrations.expensify.com/Integration-Server/ExpensifyIntegrations?requestJobDescription={""type"":""file"",""credentials"":{""partnerUserID"":""aa_jweber126_outlook_com"",""partnerUserSecret"":""_REPLACE""},""onReceive"":{""immediateResponse"":[""returnRandomFileName""]},""inputSettings"":{""type"":""combinedReportData"",""filters"":{""startDate"":""2018-01-01""}},""outputSettings"":{""fileExtension"":""csv""}}"
    
            $template = '
            <#if addHeader == true>
                Merchant,Amount,Category,Report number,Expense number<#lt>
            </#if>
            <#assign reportNumber = 1>
            <#assign expenseNumber = 1>
            <#list reports as report>
                <#list
    

    report.transactionList as expense>
    ${expense.merchant},<#t>
    ${expense.amount},<#t>
    ${expense.category},<#t>
    ${reportNumber},<#t>
    ${expenseNumber}<#lt>
    <#assign expenseNumber = expenseNumber + 1>
    </#list>
    <#assign reportNumber = reportNumber + 1>
    </#list>
    '
    $encode = [System.Web.HttpUtility]::UrlEncode($template)

            Invoke-RestMethod -ContentType 'application/json' -Method Post -Uri $url'&template='$encode
    
  • jweber_126jweber_126 Posts: 9Expensify Customer Expensify Newcomer

    Marcus,

    I am able to get this to work. You need to include requestJobDescription in the $url and put double quotations around the JSON fields. Please see below:

    [Net.ServicePointManager]::SecurityProtocol = "tls12, tls11, tls"

    $json = ConvertTo-Json @{
    type = "file";
    credentials = @{
    partnerUserID = "REPLACE";
    partnerUserSecret = "REPLACE"
    };
    onReceive = @{
    immediateResponse = "returnRandomFileName"
    };

        inputSettings = @{
            type = "combinedReportData"
        };
    
        outputSettings = @{
            fileExtension = "csv";
        }
    

    }

    $url = "https://integrations.expensify.com/Integration-Server/ExpensifyIntegrations?requestJobDescription={""type"":""file"",""credentials"":{""partnerUserID"":""REPLACE"",""partnerUserSecret"":""REPLACE""},""onReceive"":{""immediateResponse"":[""returnRandomFileName""]},""inputSettings"":{""type"":""combinedReportData"",""filters"":{""startDate"":""2018-01-01""}},""outputSettings"":{""fileExtension"":""csv""}}"

    $template = '
    <#if addHeader == true>
    Merchant,Amount,Category,Report number,Expense number<#lt>
    </#if>
    <#assign reportNumber = 1>
    <#assign expenseNumber = 1>
    <#list reports as report>
    <#list report.transactionList as expense>
    ${expense.merchant},<#t>
    ${expense.amount},<#t>
    ${expense.category},<#t>
    ${reportNumber},<#t>
    ${expenseNumber}<#lt>
    <#assign expenseNumber = expenseNumber + 1>
    </#list>
    <#assign reportNumber = reportNumber + 1>
    </#list>
    '
    $encode = [System.Web.HttpUtility]::UrlEncode($template)

    Invoke-RestMethod -ContentType 'application/json' -Method Post -Uri $url'&template='$encode

  • Sheena TrepanierSheena Trepanier Posts: 1,914Expensify Success Coach - Admin Expensify Success Coach
    edited August 2018

    @MarcusG, @vimven, @jweber_126, @AlexHeddell - We're doing a summer cleanup of the Community and when I came across this thread I just had to give you all a big round of applause. You've helped each other and generated great back and forth on a tricky subject.

    Bravo!!

  • DMuncyDMuncy Posts: 2Expensify Customer Expensify Newcomer

    So I have to say, thank you to all that responded on this as it really helped me get going on this. However I've had a lot of success and figured out some of the issues.

    @MarcusG the issues was this bit in the $json field:

    onReceive = @{
        immediateResponse = "returnRandomFileName"
    
    

    The line is supposed to be an array

    "onReceive"={
        "immediateResponse"[email protected]("returnRandomFileName")
    

    The code above just gets Expensify to run a job to generate the report. If successful, it'll spit out an file name to then download. You can use the following code to download it:

    I added "[ordered]" to cast the data to JSON in the order entered. Although it expensify doesn't seem to care.

    [ordered]$down = @{
                "type"="download";
                "credentials"= @{
                    "partnerUserID" = $UserID;
                    "partnerUserSecret" = $UserSecret;
                },
                "fileName"="$nameofoutputfrompreviousscript";
                "fileSystem"="integrationServer"
            } | convertto-json
    
        $urldown = $url + $down
    
    Invoke-RestMethod -Method post -uri ($urldown + "'&template='$encode") -ContentType 'application/json' -outfile ".\myreport.csv"
    

    However my skillset is limited and I find the download has some junk on the first line that causes the output to be useless so I load the report into an object and strip off the line then save it

    $csvout = Invoke-RestMethod -Method post -uri ($urldown + "'&template='$encode") -ContentType 'application/json'
    
    $csvout -split "`n" | Select-Object -skip 1 | out-file ".\myreport.csv"
    
Sign In or Register to comment.