Docs: Using a Google Sheets macro to transform bank data for import

About

Tiller Community Solutions add-on offers a Basic Bank CSV import option via the Import CSV Line Items tool.

This workflow will help you more quickly transform the CSV you export from your bank for use with the Line Item Importer tool by recording a reusable macro for each institution or account that requires a manual import.

Video Demo

Watch a demo of the steps outlined below

How to

  1. Download a CSV from your bank for a single account
  2. Upload it to your Google Drive and then open it as a Google Sheet
  3. Open and make a copy (via the File menu) of the Basic Bank Import Template
  4. Copy the CSV from your bank into your copy of the Import template - steps here
  5. Record a macro (from the Tools menu > Macros) that transforms the CSV bank import tab to the correct format, with the correct headers, in the correct column order and then pastes it into the Transaction Prep sheet.
  6. Use the Config sheet to list out the Institutions, Accounts, and Account #s for accounts you want to use with this workflow.
  7. Select the Institution, Account, and Account # for the import you’re currently working on
  8. Export the Transactions Prep sheet as a CSV (File menu > Download as > Comma Separated Values)
  9. Launch (or Install) the Tiller Community Solutions add-on
  10. Navigate to Tools > Import CSV Line Items
  11. Browse to and upload the CSV you generated in step 8
  12. Confirm the settings for upload and then upload the transactions

Testing the Macro

Make sure you test the macro to ensure that it recorded all the steps correctly. I had an issue with deleting columns that caused an error when I ran the macro. I was able to fix this by directly editing the macro’s script. The video demo above goes over how to address this error - click here for exact start time for this error.

Ongoing use

You can continue to use this same copy of the Basic Bank Import Template every time you need to go through these steps.

You can (and probably should) record a separate macro for each account or institution for which you need to manually upload data. We recommend a separate macro for each if the column order, format, or headers are different between different institutions.

Be sure to clear out the Transaction Prep columns A - C each time you are getting ready to prep a new batch of transactions for import.