Tag: Bash

In my company, we have been using Expensify to manage small receipts, travel expenses, etc. Recently, however, I decided to switch to another platform that is part of the SAAS platform that our accountant uses. Even though it lacks some of the functionality provided by Expensify, having all receipts in a single location reduces the amount of time I have to spend on administrative tasks.

Every quarter Dutch companies have to file a VAT report, which meant I exported the Expensify reports to CSV files (to send to my accountant) and in PDF form, as a more “visual” backup, which lists the reported expenses, sorted in categories, and, importantly, also includes the scans on the various receipts.

As we changed accountants a couple of years ago, I wasn’t sure whether I had actually downloaded both the CSV and the PDF file for each Expensify report. Keeping records is required by Dutch law, so I decided to make sure and download all PDF files and back them up somewhere.

Unfortunately, the Expensify website doesn’t offer an option for bulk downloading of the PDF files. They do offer a kind of REST API (they call it the Integration Server), that I had played with years ago, so I decided to try that. Luckily, the credentials I had saved in my password manager still worked.

The process for downloading the PDFs consists of two steps:

• Run a command to generate the reports, this returns the file names for the PDF files.
• Use those names to download the PDFs

The first step took a couple of minutes to run and then listed the filenames for the PDF on stdout:

curl -X POST 'https://integrations.expensify.com/Integration-Server/ExpensifyIntegrations' \
    -d 'requestJobDescription={
        "type":"file",
        "credentials":{
            "partnerUserID":"XXXXXXXXXX",
            "partnerUserSecret":"YYYYYYYYYY"
        },
        "onReceive":{
            "immediateResponse":["returnRandomFileName"]
        },
        "inputSettings":{
            "type":"combinedReportData",
            "filters":{
                "startDate":"2013-01-01"
            }
        },
        "outputSettings":{
            "fileExtension":"pdf",
            "includeFullPageReceiptsPdf":"true"
        }
    }' \
    --data-urlencode 'template@expensify_template.ftl'

I’m not sure what the expensify_template.ftl file does in this command, but it was necessary to create that file locally, otherwise the curl call would return an error. I simply copied the example from the sample provided in the documentation for the Expensify Integration Server. I made a copy of the long list of PDF filenames output by the above command. A typical filename would look like this: exportc992bd79-aa4a-4b04-a76a-1149194bac94-34589514.pdf. Not very descriptive… As expected (confirmed in the web UI), there were 191 file names.

Next, step two: actually downloading the files. The basic call for that is:

curl -X POST 'https://integrations.expensify.com/Integration-Server/ExpensifyIntegrations' \
    -d 'requestJobDescription={
        "type":"download",
        "credentials":{
            "partnerUserID":"XXXXXXXXXX",
            "partnerUserSecret":"YYYYYYYYYY"
        },
        "fileName":"exportc992bd79-aa4a-4b04-a76a-1149194bac94-5803035.pdf",
        "fileSystem":"integrationServer"}
    }' \
    --data-urlencode 'template@expensify_template.ftl' --output "my_output.pdf"

So, in order to download all PDFs, I saved all file names in the file pdflist. All PDF file names are unique:

$ wc -l pdflist
191 pdflist
$ sort pdflist| uniq | wc -l
191

Next, I used a loop to read each line from the pdflist file and fiddled a bit with the quotes so I could use the pdf variable in the Curl call and download each file:

cat pdflist | while read pdf; do
curl -X POST 'https://integrations.expensify.com/Integration-Server/ExpensifyIntegrations' \
    -d "requestJobDescription={
        'type':'download',
        'credentials':{
            'partnerUserID':'XXXXXXXXXX',
            'partnerUserSecret':'YYYYYYYYYY'
        },
        'fileName':${pdf},
        'fileSystem':'integrationServer'}
    }" \
    --data-urlencode 'template@expensify_template.ftl' --output ${pdf}
done

This indeed gave me 191 Expensify report PDFs, with very uninformative names 😐 . To fix that I resorted to some more shell “scripting”. Every report has a title (usually something like “Small expenses 2020 Q4”) and by using the pdftotext utility, it looked like this was always on the third line of the pdftotext output. So I moved the original PDFs to a separate “archive” directory OriginalExports and ran the following to make a copy of each PDF to a new name that was equal to its title. My first attempt failed somewhat, because the number of renamed PDF files as smaller than the number of original PDFs. I guessed this would happen when two reports have the same name, and indeed, adding -i to the cp command to warn me of this showed I was right. As this was only happening for four files, I manually converted those.

for pdf in OriginalExports/export*.pdf; do
echo ${pdf}
title=$(pdftotext ${pdf} - | head -3 | tail -1 | tr "/" "_")
cp -i ${pdf} "${title}.pdf"
done

So there I had my backup of all receipts since we started using Expensify. And if the tax office or the accountant ever want to see those receipts, I am now sure I can provide them.

Related Images: