Powershell to update tags using API

MarkW2
MarkW2 Expensify Customer Posts: 3

I'm using Invoke-Webrequest to emulate the curl command in the API for updating tags using a file. I've used community posts to resolve most of my issues. I can successfully connect with the API and post the JSON portion. However, I've not been able to upload the file that contains the tags. The curl example shows this as 'file@tags.csv' after the JSON portion of the post. I've tried Invoke-RestMethod as well. No matter how I format it, I can't seem to emulate that portion of the curl and the response is always "Tag file is missing". Any help would be appreciated.

Answers

  • MarkW2
    MarkW2 Expensify Customer Posts: 3

    further examined the example by DMuncy and encoded the filename to get past the "Tag file is missing"

    Now I get "Could not split the file on multiple lines. The file probably does not have proper End Of Line characters"

    Confirmed csv has CR LF end of each line.

    Following is the current code


    $ptruserID = 

    $ptrusersec = 

    $PolicyID = 

    $dependency = $false

    $glCodes =$true

    $header = $true

    $setRequired=$false

    $json = [ordered]@{

        "type"="update";

        "credentials"=@{

          "partnerUserID"=$ptruserID;

          "partnerUserSecret"=$ptrusersec

        };

        "inputSettings"=@{

          "type"="policy";

          "policyIDList"=@($PolicyID)

        };

        "tags"=@{

          "action"="replace";

          "source"="file";

          "config"=@{

              "dependency"=$dependency;

              "glCodes"=$glCodes;

              "header"=$header;

              "setRequired"=$setRequired;

              "fileType"="csv"

              };

        };

      } | ConvertTo-Json

    $url = "https://integrations.expensify.com/Integration-Server/ExpensifyIntegrations"

    $request = "requestJobDescription="

    $file = Get-Item -Path C:\PM_to_Expensify.csv

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

    $body = $request + $json + "'&file='$encode"


    Invoke-Webrequest -uri $url -body $body -Method Post

  • Francois Laithier
    Francois Laithier Expensify Team Posts: 33 Expensify Team
    edited November 2019
    Hi there,

    Any chance you can check the value of [System.Web.HttpUtility]::UrlEncode($file) and see if the line breaks are preserved? The result shouldn't be on one single line.

    If the line breaks are still present as expected, can you try to convert them to just LF (instead of CRLF)?

    Cheers
  • MarkW2
    MarkW2 Expensify Customer Posts: 3
    I assumed that the API is looking for two inputs ("requestjobdescription" and "file"), so I did a slight rewrite so make those elements of an array in the Powershell.  It didn't seem to make a difference.  No matter what I try, I get the same 666 response code (should I take that as a sign?).  The API seems to accept the "requestjobdescription" component, but I have no idea what the API is expecting for the "file" input.  I've tried:
    • full path to the file
    • relative path to the file
    • encoded paths (using [System.Web.HttpUtility]::UrlEncode)
    • content of the file using Get-Content
    • encoded content of the file
    For the file itself, I've tried:
    • File with header information
    • File with just a single line and no header (header set to $false)
    • CRLF for EOL
    • LF for EOL
  • Francois Laithier
    Francois Laithier Expensify Team Posts: 33 Expensify Team
    The API does expect a second parameter called file if your requestJobDescription contains:
            "tags": {
                "action": "replace",
                "source":"file",
            "..."
    
    which is the case for you. The fact that you received a Could not split the file on multiple lines error means that the data in your CSV file was transmitted to our API, but the line breaks weren't correct, e.g. if your CSV file contained:
    a,b,c
    d,e,f
    
    then we received
    a,b,cd,e,f
    
    Since you only have one level of tags, you can also directly pass them as JSON objects in the requestJobDescription:
    {
        "type": "update",
        "credentials": {
            "...": "...",
        },
        "...": "...",
        "tags": {
            "data": [
                {
                    "name": "Tag Level 1",
                    "setRequired": true,
                    "tags": [
                        {
                            "name": "Tag 1",
                            "glCode": "Tag 1 GL Code"
                        },
                        {
                            "name": "Tag 2",
                            "glCode": "Tag 2 GL Code"
                        }
                    ]
                }
            ]
        }
    }
    
  • ComportTom
    ComportTom Expensify Customer Posts: 21 Expensify Newcomer

    Powershell rookie....

    Hoping to resurrect this issue. I am also trying to import tags using Powershell. In my case, the tags ARE dependent, so I need to import from a file. It looks to me like the import from file was never made to work, instead the user included the content in the json.

    I tried the same code in post 3, and I too get the:

    Could not split the file on multiple lines. The file probably does not have proper End Of Line characters.","responseCode":666

    Looks like the script is passing the file's directory info instead of its contents... See below.

    ------------------------- Here is the content of $file ------

    PS C:\Users\Repetto> $file

      Directory: C:\users\repetto

    Mode        LastWriteTime     Length Name                                                                                                       

    ----        -------------     ------                                                                                                       

    -a----    2/11/2020 10:10 AM     362446 ExpensifyTags.csv                                                                                                


    ------------------------ and the content of $encode ---------------

    PS C:\Users\Repetto> $encode

    C%3a%5cusers%5crepetto%5cExpensifyTags.csv

  • ComportTom
    ComportTom Expensify Customer Posts: 21 Expensify Newcomer
    edited February 2020

    So maybe I am looking at this wrong. When I UrlEncode, is it the filename or the file contents I am trying to encode?

    When I query the $body variable, I see this:

    PS C:\Users\Repetto> $body

    requestJobDescription={

       "type": "update",

       "credentials": {

                           "partnerUserID": "*********",

                           "partnerUserSecret": "********"

                       },

       "inputSettings": {

                             "type": "policy",

                             "policyIDList": [

                                                  "***********"

                                              ]

                         },

       "tags": {

                    "config": {

                                   "setRequired": true,

                                   "glCodes": false,

                                   "header": true,

                                   "dependency": true,

                                   "fileType": "csv"

                               },

                    "source": "file",

                    "action": "replace"

                }

    }'&file='C%3a%5cusers%5crepetto%5cExpensifyTags.csv

  • Francois Laithier
    Francois Laithier Expensify Team Posts: 33 Expensify Team

    Hi there,

    It looks like you're only passing the path to the CSV file in the request, instead of the file's content.

    Have you tried using Get-Item and [System.Web.HttpUtility]::UrlEncode($file) like in the posts above?

  • madslinden
    madslinden Expensify Customer Posts: 6

    I’m on a Mac using curl and also have this issue “could not split bla....”

    did anyone find the solution?

    Thanks

  • ComportTom
    ComportTom Expensify Customer Posts: 21 Expensify Newcomer

    Time to revisit this again so I can get this issue off my plate..... When we last left this problem, it was recommended that I revert to using Get-Item and [System.Web.HttpUtility]::UrlEncode($file) . Well, that didn't work either.

    Does ANYONE have sample code that uses a csv upload to update tags?


    My new code is at the bottom of this post. The response I get is.....

    StatusCode    : 200

    StatusDescription : OK

    Content      : {"responseMessage":"Could not split the file on multiple lines. The file probably does not have proper End Of Line characters.","responseCode":666}

    RawContent    : HTTP/1.1 200 OK

              Access-Control-Allow-Origin: *

              Access-Control-Allow-Methods: OPTIONS, GET, POST

              Strict-Transport-Security: max-age=31536000;

              X-XSS-Protection: 1; mode=block

              X-Content-Type-Options...

    Forms       : {}

    Headers      : {[Access-Control-Allow-Origin, *], [Access-Control-Allow-Methods, OPTIONS, GET, POST], [Strict-Transport-Security, max-age=31536000;], [X-XSS-Protection, 1; mode=block]...}

    Images      : {}

    InputFields    : {}

    Links       : {}

    ParsedHtml    : mshtml.HTMLDocumentClass

    RawContentLength : 147

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    Code below

    $ptruserID = '*******'

    $ptrusersec = '*****************.'

    $PolicyID = '********'

    $dependency = $true

    $glCodes =$false

    $header = $true

    $setRequired=$true

    $json = [ordered]@{

        "type"="update";

        "credentials"=@{

          "partnerUserID"=$ptruserID;

          "partnerUserSecret"=$ptrusersec

        };

        "inputSettings"=@{

          "type"="policy";

          "policyIDList"=@($PolicyID)

        };

        "tags"=@{

          "action"="replace";

          "source"="file";

          "config"=@{

              "dependency"=$dependency;

              "glCodes"=$glCodes;

              "header"=$header;

              "setRequired"=$setRequired;

              "fileType"="csv"

              };

        };

      } | ConvertTo-Json

    $url = "https://integrations.expensify.com/Integration-Server/ExpensifyIntegrations"

    $request = "requestJobDescription="

    $file = Get-Item -Path C:\Users\repetto\ExpensifyTags.csv

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

    $body = $request + $json + "'&file='$encode"

    Invoke-Webrequest -uri $url -body $body -Method Post

  • ComportTom
    ComportTom Expensify Customer Posts: 21 Expensify Newcomer

    I made some GOOD progress... The suggestion to use 'Get-Item' gets the 'Item' (the file listing). Replace the line with 'Get-Content', and the script grabs the tag list IN the file. The 'replace' line changes line breaks to 'return' and 'new line'.

    I have not yet uploaded the tags because I am having an issue getting an accurate export of the tags in the system (which I will use as a backup should things go wrong). But this is certainly a step in the right direction.


    $request = "requestJobDescription="

    $file = Get-Content -Path C:\Users\repetto\ExpensifyTags.csv

    $file -replace '<BR>,',"`r`n"

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

    $body = $request + $json + "'&file='$encode"

  • ComportTom
    ComportTom Expensify Customer Posts: 21 Expensify Newcomer

    Got it WORKING.....almost.

    Now it looks like the problem is on the Expensify side.

    I have the script running correctly (I think), but now I am getting the ambiguous error below. It says an engineer has been notified, but none has reached out to me since yesterday, when I got this far.

    I am SO CLOSE. Can anyone shed light on this?

    Thanks


    Begin ERROR MESSAGE ------------------------------------

    StatusCode    : 200

    StatusDescription : OK

    Content      : {"responseMessage":"An unexpected error occurred. An engineer has been notified and we will fix the problem promptly","responseCode":666}

    RawContent    : HTTP/1.1 200 OK

              Server: GlassFish

              Server: Server

              Server: Open

              Server: Source

              Server: Edition

              Server: 4.1.1

              X-Powered-By: Servlet/3.1 JSP/2.3 (GlassFish Server Open Source Edition 4.1.1 Java/P…

    Headers      : {[Server, System.String[]], [X-Powered-By, System.String[]], [Access-Control-Allow-Origin, System.String[]], [Access-Control-Allow-Methods, System.String[]]…}

    Images      : {}

    InputFields    : {}

    Links       : {}

    RawContentLength : 137

    RelationLink   : {}

    End ERROR MESSAGE -------------------------------------



    Begin CODE --------------------------------------------

    <#

    .SYNOPSIS 

        Export curent active projects from GP and import as Tags to Expensify 

    .DESCRIPTION 

    .NOTES 

        This script runs daily in order to keep the Expensify tag list in sync with active projects in Great Plains

        The export from SQL creates the file 'SQLTagsExport.csv'.  That file is read into an array, adding CRLF then url encoded

        Then the upload command is created by concatenating the URI, API options and the encoded array

    .COMPONENT 

        Requires Module SQLServer 

    #>


    #

    #Initialize variables

    #

    # SQL Variables

    $SQLUser = '*******'

    $SQLPassword = '*******'


    # File variables 

    $OutFilePath = 'C:\Users\Files\Desktop\Expensify\'

    $OutFileName = $OutFilePath + 'SQLTagsExport.csv'


    # Integration authentication and variables

    $APIuserID = '*************'

    $APIusersec = '************'

    $APIPolicyID = '************'

    $APIdependency = $true

    $APIglCodes =$false

    $APIheader = $true

    $APIsetRequired=$true

      

    # API endpoint URI and options

    $APIurl = "https://integrations.expensify.com/Integration-Server/ExpensifyIntegrations"

    $APIrequest = "requestJobDescription="

    $APIjson = [ordered]@{

      "type"="update";

      "credentials"=@{

        "partnerUserID"=$APIuserID;

        "partnerUserSecret"=$APIusersec

      };

      "inputSettings"=@{

        "type"="policy";

        "policyIDList"=@($APIPolicyID)

      };

      "tags"=@{

        "action"="replace";

        "source"="file";

        "config"=@{

            "dependency"=$APIdependency;

            "glCodes"=$APIglCodes;

            "header"=$APIheader;

            "setRequired"=$APIsetRequired;

            "fileType"="csv"

        };

      };

    } | ConvertTo-Json


    # SQL dataset of qualified Customers, Projects and CostCategories extracted and written to csv file

    Invoke-Sqlcmd -ServerInstance 'SQLServer' -Username $SQLUser -Password $SQLPassword -Query "SELECT

        RTRIM(Cust_Mast.CUSTNAME) [Customer]

        ,CASE

            WHEN Cust_Mast.CUSTNMBR = 'EVENT EXPENSES' 

            THEN RTRIM(Proj_Mast.PAprojname)

            ELSE RTRIM(Proj_Mast.PAPROJNUMBER)

        END [Project]

        ,RTRIM(Budget_Mast.PACOSTCATID) [CostCategory]  

        FROM COMPO..PA01301 Budget_Mast

        INNER JOIN COMPO..PA01201 Proj_Mast ON Budget_Mast.PAPROJNUMBER = Proj_Mast.PAPROJNUMBER

        INNER JOIN COMPO..RM00101 Cust_Mast ON Proj_Mast.CUSTNMBR = Cust_Mast.CUSTNMBR

        INNER JOIN COMPO..PA01001 Cat_Mast ON Budget_Mast.PACOSTCATID = Cat_Mast.PACOSTCATID

        INNER JOIN COMPO..PA01101 Cont_Mast ON Proj_Mast.PACONTNUMBER = Cont_Mast.PACONTNUMBER

        WHERE Proj_Mast.PASTAT = 1              -- Project is OPEN

            AND Cont_Mast.PASTAT = 1            -- Contract is OPEN

            AND Budget_Mast.PASTAT = 1          -- Budget item is OPEN

            AND Cat_Mast.PATU = 5               -- Transaction type is EMPLOYEE EXPENSE

            AND Cat_Mast.PAinactive = 0         -- Category is ACTIVE

            AND Proj_Mast.PAcloseProjcosts = 0  -- Project is NOT closed to posting of new costs

            AND Cust_Mast.Custname IS NOT NULL  -- Customer Card has a customer name

        ORDER BY Customer, Project, CostCategory" | Export-Csv -Path $OutFileName -NoTypeInformation


    $APIUpload = Get-Content -Path $OutFileName -Encoding UTF8 -Raw

    $APIUpload -replace '<BR>','`r`n'

    $APIencode = [System.Web.HttpUtility]::UrlEncode($APIUpload)

    $APIbody = $APIrequest + $APIjson + "'&file='$APIencode"

    Invoke-Webrequest -uri $APIurl -body $APIbody -Method Post

    End CODE --------------------------------------------------------

  • ComportTom
    ComportTom Expensify Customer Posts: 21 Expensify Newcomer

    FOLLOW-UP

    Found out that the error message is misleading. No engineer is notified. Nothing will be fixed promptly.

    So we are on our own. Can ANYONE chime in? Has ANYONE done something like this successfully?

  • Lauren Schurr
    Lauren Schurr Expensify Team, Expensify Student Ambassador Posts: 137 Expensify Team

    Thanks for your patience, @ComportTom! Our engineers took a look but couldn't find enough information in the error you experienced. They're adding some logs on our end to try to get more information when you do run into the error. This should be completed by the end of the business day today, so could you try again tomorrow and let me know if you get the error again?

  • ComportTom
    ComportTom Expensify Customer Posts: 21 Expensify Newcomer

    Thanks Lauren. I will rerun the script tomorrow morning and report back

  • ComportTom
    ComportTom Expensify Customer Posts: 21 Expensify Newcomer

    Interesting outcome this morning. I ran the script and got a 200 return code without any error. My import file did not contain any new tags, so the count after the test remained the same. I was expecting to generate an error so the expanded logs could be reviewed. Not sure if the update failed and left the old tags, or if the update succeeded and all the tags were replaces with identical tags.

    I am working on generating test data in our ERP to rerun the test to see if an update is actually taking place. If your log review can shed any light on the outcome, please let me know.

  • ComportTom
    ComportTom Expensify Customer Posts: 21 Expensify Newcomer

    OK, I just ran a test with an updated source file that should have added 1 Project and 20 Cost Categories. No records were added via the script. Had to upload the changes manually.

  • Francois Laithier
    Francois Laithier Expensify Team Posts: 33 Expensify Team

    Hi @ComportTom,

    Can you please make sure that the values of your headers (first line in the CSV file) don't start with a combination of single and double quotes please, for example '"Value"?

    Instead, please use only "Value", or simply Value.

    This should fix the error you're encountering.


    Cheers

  • ComportTom
    ComportTom Expensify Customer Posts: 21 Expensify Newcomer

    I just opened the csv file with a hex editor and the fist part of the first line is "Customer".


    What's next?

  • Francois Laithier
    Francois Laithier Expensify Team Posts: 33 Expensify Team

    Our logs indicate that we received '"Customer". Any chance that a part of your system is prepending the extra quote before making the request?

  • ComportTom
    ComportTom Expensify Customer Posts: 21 Expensify Newcomer

    Just back from vacation. Looking into this right now, but querying the dataset in powershell shows the first line as "Customer". There are 2 statements after this dataset is built and I am checking to see if anything is adding another single quote....

  • ComportTom
    ComportTom Expensify Customer Posts: 21 Expensify Newcomer

    Well, it's amazing how vacation can give you a new perspective on things..,...

    I found the line of code that prepended the single quote. After making the correction I have tested the integration and it works perfectly.


    Thanks for all the assistance