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

MarcusG
MarcusG Expensify Customer Posts: 6 Expensify Newcomer
edited January 2019 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

  • MarcusG
    MarcusG Expensify Customer Posts: 6 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 =
    '
    template@expensify_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
    
  • MarcusG
    MarcusG Expensify Customer Posts: 6 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
  • vimven
    vimven Expensify Customer Posts: 8 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.

  • MarcusG
    MarcusG Expensify Customer Posts: 6 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
    
  • AlexHeddell
    AlexHeddell Expensify Customer Posts: 1 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

  • MarcusG
    MarcusG Expensify Customer Posts: 6 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_126
    jweber_126 Expensify Customer Posts: 7 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_126
    jweber_126 Expensify Customer Posts: 7 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 Trepanier
    Sheena Trepanier Expensify Team, Approved! Accountant, Expensify Student Ambassador Posts: 1,362 Expensify Team
    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!!

  • DMuncy
    DMuncy Expensify Customer Posts: 3 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"=@("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"
    
  • derekritchison
    derekritchison Expensify Customer Posts: 6 Expensify Newcomer

    Hey all, not to resurrect an old thread but this conversation has been incredibly useful for me as I am working on automating new user creation with my onboarding script, which is written in PowerShell. I've pieced together quite a bit just from reading this thread, but I'm now stuck trying to figure out how to properly translate the "send the CSV" portion of the curl. Here's how I'm writing the JSON, but if anyone has any experience with the "data" portion that would be terrific.


    $json = [ordered]@{

        "requestJobDescription" = @{

                "type" = "update";

               "credentials" = @{

                        "partnerUserID" = $expensify_id;

                        "partnerUserSecret" = $expensify_secret;

                }

                "inputSettings" = @{

                        "type" = "employees";

                        "policyID" = "$policy_ID";

                        "fileType" = "csv";

                }

            };

        "data" = "C:\expensify.csv";

        } | ConvertTo-Json -Depth 5