CSV Spreadsheet Importer Multitool for Google

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 :point_down:) 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.