How To Use The Tiller Community Solutions Migration Helper

Overview

The Migration Helper is built to help users migrate enriched personal-finance data— e.g. categories, splits, manual balances, etc.— from an old “source” Tiller Money spreadsheet to a new “destination” Tiller Money spreadsheet.

If you are considering migrating, review the recommended workflow.

The Migration Helper is a best-effort community built workflow for a very complex migration problem. We hope this tool helps you, but please be aware that this workflow will not work for everyone.

Migration Helper Functions

The Migration helper is designed to migrate enriched account data from the source to destination spreadsheet including:

Migration Helper Compatibility

Several sheets and columns are required for the migrator to run. A compatibility check will run before the Migration Helper makes any changes.

The Transaction ID column is required in both the source and destination spreadsheets to migrate Categories and Notes. Transaction ID uniquely identifies transactions in the Tiller database. This field is used by the Migration Helper to identify matches between the two spreadsheets.

Configuration + How The Migration Helper Works

The Migration Helper can move data from the source to destination spreadsheet for each of the following sheets:

  • Transactions
  • Balance History
  • Categories
  • AutoCat


Using the checkbox controls in the sidebar, the user can choose which sheets to review for data migration. All sheets are included by default. There are a number of nuanced settings— especially the Transactions sheet. If you aren’t sure, we recommend you stick with the defaults.

Source Spreadsheet

Identify the source spreadsheet by opening it in a separate tab then pasting its URL (or Spreadsheet ID) into the text box in the sidebar.

Transactions

:warning:The Migration Helper does not support the check box feature in the source Transactions sheet. If you have a columns with checkboxes in the source Transactions sheet you will need to delete that column before running the Migration Helper.

Missing Columns

Your source Transaction sheet may include columns that are not present in your destination Transactions sheet. When this option is set to “Migrate Columns”, missing columns will be added to the destination. Note that columns where the header is a formula will be skipped.

Set to “Ignore” if you’d prefer to not add columns to your destination Transactions sheet.

Matching Transaction ID Found

The Matching Transaction ID Found section determines how transactions are handled when there is a matching Transaction ID in both the source and destination Transactions sheet. (For split transactions, this is a many (source) to one (destination) relationship.)

Categories & Other Columns

When the Migration Helper finds a matching Transaction ID between the source and the destination spreadsheets, it will apply the Category and any Other Columns from the source to the destination row.

“Other Columns” can be standard Tiller columns (e.g. Note) or user created columns (e.g. Tags). The following column headers are protected and thereby will be ignored by this setting:

  • Date
  • Amount
  • Account
  • Account #
  • Institution
  • Account ID
  • Transaction ID
  • Date Added

For both Categories and Other Columns, a dropdown allows the user to choose to overwrite categories/other-columns in one of two scenarios: “Overwrite Always” or “Overwrite Blanks”. If “Overwrite Always” is selected, categories/other-notes will be overwritten with source data in the destination sheet whenever a match is found and the source cell contains data. If “Overwrite Blanks” is selected, categories and other columns will be overwritten with source data when a match is found AND the category/other-column field in the destination is blank.

Split Transactions

When the Migration Helper finds a Transaction ID with more than one instances in the source but only a single instance in the destination, those rows are flagged as a split transaction. If the sidebar is set to “Migrated Splits”, the existing (single) destination row is removed (any unique destination data is lost) and all source rows are inserted. In this way, splits are replicated into the destination spreadsheet.

Set Split Transactions to “Ignore” if you do not wish to migrate these types of records.

No Matching Transaction ID Found

The No Matching Transaction ID Found section determines how transactions are handled when either the Transaction ID is blank or there is not a matching Transaction ID in the destination Transactions sheet.

Manually Created

Manually-created transactions created by the Tiller Community Solutions add-on in the source spreadsheet (i.e. rows with Transactions IDs that are prefixed with manual:) will be moved to the destination spreadsheet if the “Manually Created” dropdown is set to “Migrate All”. (If the dropdown is set to “Ignore”, thes transactions will be discarded in the migration.)

No Transaction ID

Manually-added transactions in the source spreadsheet (i.e. rows with empty Transactions ID) will be moved to the destination spreadsheet this dropdown is set to “Migrate All”. (If the dropdown is set to “Ignore”, transactions with empty Transaction IDs will be discarded in the migration.)

Note that the Migration Helper is unable to check if rows without a Transaction ID have already been imported. They will be reimported each time this workflow is executed. To prevent duplicates, be careful not to run migrations with this option set multiple times.

Other Transactions

“Other Transactions” are transactions with Tiller-Money-Feeds-provided Transaction IDs in the source Transactions sheet that are not found in the destination Transactions sheet. These transactions are migrated when the option is set to “Migrate All” and ignored when the option is set to “Ignore”. “Other Transactions” with unrecognized Transaction IDs are most often from accounts not linked in your destination sheet. This option defaults to “Ignore” as importing transactions from unlinked accounts can create confusion.

Categories

image

The Migration Helper will move any categories that do not exist in destination from the source spreadsheet. Note that budget migration (from the Categories) sheet is not implemented at this time. Budgets must be migrated manually.

When this option is set to “Merge”, for categories that exist in both the source and destination spreadsheets, the Migration Helper will overwrite Group, Type and Hide From Reports configuration to match the source spreadsheet. Categories that exist only in the destination will be preserved.

When this option is set to “Overwrite”, destination categories will be wiped away before source categories are migrated to the destination sheet, providing a fresh start. This approach is preferred when migrating into a spreadsheet with default categories as unused destination categories are removed rather than orphaned.

Note that the Migration Helper is not currently able to migrate budgets from the source’s Categories sheet.

Balance History

If a Balance History entry has 9999 assigned in the legacy Index column, the Migration helper will assign all balance entries with a matching Account name a new Account ID. Having a valid Account ID is required for many Tiller Community Solutions templates. (The Index column is not supported in Tiller Community Solutions templates.) Balance entries that previously had index 9999 and are assigned an Account ID will be migrated to the destination spreadsheet.

Balance History entries with Account ID prefix manual: (i.e. current manual balance entries) will be moved to the destination spreadsheet.

AutoCat

If the destination spreadsheet does not already contain an AutoCat sheet, the Migration Helper can copy the AutoCat rules sheet from the source spreadsheet into the destination spreadsheet.

How To Use The Migration Helper

This workflow works best when the destination spreadsheet is a recently-created/new Tiller spreadsheet with linked accounts and an initial feeds update.

The following steps are recommended:

  • Create a new Tiller spreadsheet using either the Foundation Template in the Tiller Console or a new Google Sheet
  • Link accounts to the new spreadsheet using the Tiller Money Feeds add-on
  • Run an initial fill in the new spreadsheet using the Tiller Money Feeds add-on
  • Open the Tiller Community Solutions add-on in the new spreadsheet
  • Navigate to the Tools option in the Community Solutions add-on main menu
  • Choose the Migration Helper in the Tools submenu
  • Copy the URL from your source spreadsheet— not the open destination spreadsheet— and paste it into the textbox below Source Spreadsheet
  • Set your migration configuration options per the instructions above
  • Click Migrate

Once the process completes, carefully review your data to ensure you are satisfied.

Troubleshooting

If you have a question or need help first search the community to see if someone has already asked and if not click here to quickly post a question about this tool in the Get Help > Workflows & Tools category.

Be sure to customize the title of your post with keywords about the issue or question so others can easily find the Q&A in search.

Tiller Budget (w/Rollovers) vs. Tiller Foundation Template Budget
Envelope/Zero Sum Budgeting
Adding a Community-shared Template to Your Personal Spreadsheet
Free Webinar: Using the Tiller Foundation Template for 2020 Budgeting
Single file or multiple files?
Upgrading your Feed Bot sheets to use Tiller Money Feeds
Free Weekly Webinar: Tiller Money Foundations
Envelope Budget (via Tiller Add-on) Comprehensive Guide
Moving transactions between tiller templates
Add the Savings Budget sheet
Savings Budget Shows as "Incompatible" in Tiller Money Labs
Rollover Adjustment Warning Wrongly Displayed on Envelope Budget Dashboard
Rollover Adjustment Warning Wrongly Displayed on Envelope Budget Dashboard
Migration Helper Crashed in Balance History
Starting Over in 2021 with No Data Carried Forward
Closed Account: Retain History But Keep from Showing Up Going Forward
Sync Categories Between Spreadsheets Using IMPORTRANGE()
Envelope Budget Adjust Cell Not Working
How to use the Tiller Community Solutions Add-on
Question about Monthly Budgets
Monthly Budget Dashboard Issues (Envelope System)
Recomendations for Someone New, Foundation Template w/ Savings & Debt Budget, OR Envelope Template
Weekly budget errors
Adding a single rollover to the new budget template
Discussion and Docs for the Foundation Template
Envelope Budget (via Tiller Add-on) Comprehensive Guide
Envelope Budget Scripts Not Working - adjust cell and dropdowns stuck
"Balances" tab is borked - how do I restore functionality?
Balance History sheet not updating
Tiller Labs Roundup 5/5/20
Tiller Money Live: Community webinar + Q&A

I’ve followed the instructions to migrate data from an old spreadsheet to a new one and get the following error message:

Unable to Open

Source Spreadsheet

The add-on could not open the [spreadsheet with the provided ID]

Confirm the ID is valid and that you have access to the spreadsheet, then try again.

I pasted the URL from the Transactions tab and the Help tab which are different, thinking maybe that would help…

That is strange, @dbcarlberg. I haven’t seen an issue with this before. You should be able to either paste the entire URL from the browser tab of the source sheet or just the spreadsheet ID which appears here:
https://docs.google.com/spreadsheets/d/[THIS PART]/edit#gid=618601935

It should look something like this:
1-eErvVvn0X3bBNcK4m7ZapdvDDzt-TBqnDCDQYkli88

Could you try again just using the spreadsheet ID?

Randy

It might be that the Google account you’re trying to use to do the migration does not have access to the source spreadsheet? You may need to share it with the account or use that Google Account to access the destination sheet.