CSV Spreadsheet Importer Multitool for Google

[randy]

Overview

CSV Spreadsheet Importer Multitool for Google is a set of sheets that make importing transaction data into your Tiller Transactions sheet quick, easy, customizable and reliable.

What the CSV Spreadsheet Importer Multitool for Google does:

• Remaps transaction-data fields to the column order of your personal Tiller Transactions sheet (for easy copying & pasting)
• Unlocks customizable composite/calculated fields during imports
• Preserves reusable rule sets for future imports for multiple data sources
• Flags transactions that may be duplicates prior to importing into your Transactions sheet
• Renders a robust Metadata field similar to the Tiller Community Solutions Import CSV Line Items workflow

It essentially prepares imported data for a quick copy-paste operation into the Transactions sheet. There is a little legwork getting it up and running, but quick and easy as a workflow enhancement once you get there.

Pre-built Import Rules

Currently the CSV Spreadsheet Importer Multitool is shared with CSV rules sets for:

• Amazon Items
• Amazon Refunds
• Amazon Business Purchases
• Apple Card
• Mint
• Paypal
• Personal Capital
• Venmo
• YNAB

These rule sets match the Tiller Community Solutions Import CSV Line Items workflow in both breadth and implementation.

Why Did I Build This?

People ask me in this community to add CSV Line Items workflow support for all kinds of data sources. Honestly, I feel like a bottleneck sometimes and I can’t build out all the great ideas all of you have.

When I shared the Simple Formula to Map Imported Data to Core Sheets solution recently, it occurred to me that the approach could be expanded into a powerful CSV-import engine.

I’m hoping those Quicken, Quickbooks and eBay workflows come to fruition now. Let’s do this!

Two Similar Tools

Comparison

This workflow and the CSV Line Items workflow are similar:

• Transaction sheet data will ultimately look the same (or very similar) from both tools
• Both tools currently support the same set of data sources

But the tools are different.

This workflow:

• Is mostly manual
• Allows you to customize import mapping and fields for existing data sources
• Allows you to create— and share!— import rules sets for new/unsupported data sources

The CSV Line Items workflow:

• Is mostly automatic
• Is run entirely in the sidebar via Tiller-managed scripts
• Implements the transaction offsets (not present in this workflow)
• Requires no additional support sheets
• Includes integrated date & account filters

Which Tool Should I Use?

Generally speaking, the CSV Line Items workflow is going to be easier if you are happy with the data mapping and the list of supported data sources.

This workflow is for people eager to import new data sources or who want more granular control of how data flows from your CSVs to your Tiller Transactions sheet.

How It Works

Required Sheets

To function, the CSV Spreadsheet Importer Multitool for Google requires at least 3 sheets:

CSV Spreadsheet Importer

Once the tool is configured, this will be your workspace for import operations. This is where your remapped transactions are rendered ready for copy and pasting into your Transactions sheet. The column order will match your personal Transactions sheet for direct copying and pasting.

CSV Spreadsheet Importer Rules

This is the setup sheet where you configure column mapping for all of your data sources. Once your go-to data sources are configured, you shouldn’t have to touch this sheet. In blue on the left, you see most typical Transaction sheet headers mapping to raw column names in the CSV exports from various data sources.

Under each data source header in the first row, enter the import worksheet (see ) sheet name in the second row.

Import worksheets

An import worksheet is required for each data source you want to use (e.g. “Paypal” or “Amazon Items”). This is where you import the raw CSV data each time you run an import. (If you use many data sources, you will need many import worksheets in your spreadsheet.)

The green cell in the header row is the target for your CSV import. Select this cell then, during the import operation, select “Replace data at selected cell” in the Import Location dropdown.

The columns in the middle and right are raw CSV data. The grayed columns on the left are customizable composite/calculated fields that render dynamically. You can update these fields using the formulas in the header row or add new composite/calculated fields (but don’t forget to link new columns using the CSV Spreadsheet Importer Rules sheet).

Installation

1. Open the CSV Spreadsheet Importer Multitool master
2. In the template master, copy the sheet named CSV Spreadsheet Importer Rules into your personal spreadsheet
3. In your personal spreadsheet, rename the new sheet from Copy of CSV Spreadsheet Importer Rules to CSV Spreadsheet Importer Rules
4. In the template master, copy the sheet named CSV Spreadsheet Importer into your personal spreadsheet
5. In your personal spreadsheet, rename the new sheet from Copy of CSV Spreadsheet Importer to CSV Spreadsheet Importer
6. Review the remaining import worksheets and copy any worksheets that support data sources you plan to use into your personal spreadsheet

Usage

Once the CSV Spreadsheet Importer Multitool for Google is configured, the steps to import a CSV file are:

1. Download a CSV from a data source (which has a supported rule set in your “CSV Spreadsheet Importer Rules” sheet)
2. Select the green target cell in the data-source’s import worksheet (for example 'Amazon Items'!G1)
3. Import the CSV in to the data-source’s import worksheet - in the Import Location dropdown, select “Replace data at selected cell”
4. Select the Rule Set in the CSV Spreadsheet Importer
5. Add empty rows to the bottom of your Transactions sheet
6. (Optionally) Filter the data on the Dupe column to avoid re-inserting transactions already present
7. Copy and paste the new transactions into the bottom of your Transactions sheet
8. (Optionally) Sort the Transactions sheet.

IMPORTANT: Don’t forget to set the Import Location to “Replace data at selected cell” in the import dialog.

Permissions

Please build on this foundation. Copy. Share. Improve. I’m excited to see where it leads you.

Notes

As some of you add support for new data sources, please share the data mapping in the community! Let’s work together to support more integrations.

I’m running out of steam tonight on these instructions. Let me know if more exposition is needed anywhere.

FAQ

• The Dupe column in the CSV Spreadsheet Importer sheet will show as checked if there is already a transaction in the Transactions sheet with the exact same Date, Amount and Description.

[dixonge]

This looks like it would help for my Apple Card transactions - I have 10 CSV files to import for 2022 and another one for 2023. Plus this is something I’ll need to do for every month going forward.

Thanks!

[randy]

Did you get it working, @dixonge?

[dixonge]

yeah, turned off some Chrome extensions and evidently one of them was preventing the spreadsheet from loading the screen where you select the existing spreadsheet to copy to. All good now.

[ScottC]

Thank you for this tool. I think it’s going to help a lot.

Can the mapping rules be modified?

The mapping rules for Applecard import the Description column from the Applecard .csv file into the Tiller Transactions tab twice: Full Description and Note columns.

I add a Notes column in my Applecard statement to describe the item. I’d like to map this Notes column from my Applecard .csv file to the Note column in the Tiller Transaction tab.

Will this cause problems of any kind?

[randy]

Can the mapping rules be modified?

Absolutely, @ScottC. That is the whole purpose of this tool: to allow you to customize and save data mappings without having to write code. Not only can you change mappings, you can create formula-driven concatenations (see cols A:D in the “Apple Card” sheet).

If you’re nervous about changes, consider making a backup copy of any sheets you are changing.

[ScottC]

Thanks. I hadn’t noticed the concatenations.

One more question. I’ve modified my Transactions tab by adding a Tags column and I moved the Note column.

I’m assuming I need to duplicate that column layout in each of the Multitool templates.

Correct?

Thanks again.

Scott

[randy]

I’m not sure I understand what is not working but… I don’t think so. For the most part, everything should be smart about column orders changing.

Is something not working the way it is setup now in your spreadsheet?

[dixonge]

@randy well, somehow I forgot to do this monthly so now I have 7 months to catch up on!
I also forgot how this works. I have successfully exported my CSV’s from my phone, then copied them up to Google Sheets. I go into the Apple Card sheet and import one month. And … um, that’s it, nothing else happens! Nothing is populated in the CSV Spreadsheet Importer sheet!
So what did I forget here?

[randy]

Have you stepped through the steps in the Usage section, @dixonge? Can you be more specific about what is supposed to happen per the instructions that isn’t happening?

[MarcC]

Hi @dixonge,

I used to do this all the time and just verified it still works. Not sure why it’s not working for you.

This is what I do.

1. Export the Apple Card transactions to .csv on my phone.
2. Once the csv is visible on my phone I share (Airdrop) it to my desktop.
3. I then go to the “Import CSV Line Items” tool → Apple Card Purchases
4. Select my file and select Upload CSV file button.

All the transaction are imported perfectly.

Those steps are basically what it says within the “Import CSV Line Items” tool → Apple Card Purchases

Are you using the “Import CSV Line Items” tool → Apple Card Purchases to upload the file?

[dixonge]

@MarcC

Are you using the “Import CSV Line Items” tool → Apple Card Purchases to upload the file?

This is what I get for not writing down these steps somewhere. I’ve been using the main Google Sheet menu File > Import File - which seems to match the Usage section above. (So yes, @randy, I have been following those steps)

But you (@MarcC) are describing this step as “Import CSV Line Items” tool. So after a few minutes of searching I find this in my Tiller Community Solutions extension. And that process seems to have worked just fine! No categories were assigned, so I guess I need to run AutoCat then clean up any remaining stragglers and move forward one month at a time.

And hopefully I can save this workflow somewhere so that I don’t completely forget the steps when I need to import October’s transactions LOL

End note: I think I was originally looking at the current spreadsheet-based solution presented here in relation to maybe importing Amazon transactions? But I’m just guessing here because my memory for such things seems to be quite spotty

[dixonge]

Having said all that, if the community solutions extension is working for me, do I actually need any of the Spreadsheet Importer Multitool sheets any more?

[randy]

The Import CSV workflow is not integrated with Autocat… so you will need to categorize afterward.

Personally, I prefer the CSV workflows over the “multitool”. The multitool is better if you want to customize the workflow output or build for datasources that are not currently supported with the CSV workflow.

[maglovato]

Using it. I liked it. I created another one, to import Ally Bank credit card. So far, so good. One quick comment on the DUPE … I use AutoCat to turn Full Description in better descriptions, and store that on Description. How could I tell which formula DUPE could use, so I could point to FullDesc instead of just Desc.

► Nevermind. I figured out how the DUPE function works. Problem solved!!!
Flip to “Full Description” instead of description, and from C to column N.
DUPE function lives on the import spreadsheet, cell D1.

[randy]

Glad to hear you’ve got this tool working for you, @maglovato. It’s not trivial to setup but it is powerful and flexible once you get there.

[libindaniel2000]

I have close to 25k transactions that I have imported with this tool. This tool was great to get the Mint transactions imported. I am running into a few issues with using Community tools to look up historical net worth, so I had a couple of questions:

1. How does name matching work between Tiller and Mint for various accounts? The names used by Mint were slightly different to those being used by Tiller. If this is a manual task that has to be done one transaction at a time - are there any tools to make this easier? Right now, my net worth is showing up as 0 from a year ago even after importing data for that time period.
2. Tiller has imported transactions to varying dates for varying accounts BEFORE using this tool. I used this tool to import starting a date that worked for most of the accounts. Can I just go line by line and delete the Mint transactions (by leveraging the metadata column) ONCE and not worry about duplicates?

[jpfieber]

In the Transactions sheet you should be able to see what the accounts are called now in Tiller, and then use Find/Replace to rename your mint accounts to match. That will help with any templates that look at transactions. I think anything that is looking at net worth likely will get it’s values from the Balance History sheet, which I don’t recall being something you can import from Mint, so that will be limited to data that Tiller pulled in directly from your accounts.
To avoid duplicates, I usually find a good overlap point in the data, pick a day, and delete anything new from the imported data after that date plus 5 days, and delete anything in tiller from before that date minus 10 days. This gives you a 10 day window to account for any differences in how transactions posted in each system. Then you just have these 10 days of duplicates to go through and clean up by hand. Once you’ve deleted Tiller transactions, they won’t be imported again.

[libindaniel2000]

Thank you, @jpfieber!
I should have also just read the instructions in the website: How to Rename an Account Connected to Tiller also. It seems like I can do the Find and Replace action in Balance History also. Do you see any issues with me directly editing that hidden page? I assume it is hidden for a reason…

One thing that I am struggling as a newbie is what sheets I should change and not change. And what sheets are the truth source for other tools.
For example, I did not know that transactions should be edited manually and line items removed manually. I just assumed that it is the automated feed from Tiller Console you don’t mess with. At the same time, the Community tools might leverage some assumptions into its creation. But, I guess I can always restore a community add-on page after a change is made?

Once you’ve deleted Tiller transactions, they won’t be imported again.

I should delete the Mint transactions over Tiller ones, right? Since those are static CSV imports and Tiller might repull the deleted Tiller transactions?