About the Tiller Migration Helper Add-on

The Migration Helper is a special case add-on intended to help customers transition from Feed Bot sheets into Tiller Money Feeds sheets and help clean up duplicated account data after upgrading their feeds from Legacy to YSL.

⚠️ Review the Installation Notes, which include disclaimers about the sensitive Google Drive permissions required by this add-on, before installing this add-on.

⚠️ This add-on makes changes to your Google Sheet. We recommend creating a named version from the File menu > Version History so you have a known restore point in the event that you do not like the changes.

Share feedback. Let us know how it went by completing this quick survey.

Clean up duplicates from Legacy to YSL Data Feeds Upgrade

Installing the Add-on

  1. Open the Add-on’s Google Marketplace listing

  2. Click “Install”

  3. Click “Continue”

  4. Choose the Google account you’d like to use to install the add-on

  5. Accept the add-ons scopes

⚠️ You should have completed these steps BEFORE completing any of the steps below this point.

ℹ️ You can re-run the workflows in this add-on as needed. For example, if you have many accounts to merge and repair you can do these in batches. We recommend that you use the Merge & Repair workflow before running the Transaction Duplicate Clean Up workflow so that the transaction duplicate flagging is most accurate.

Merge & Repair

  1. Create a named version from the File menu > Version History so you have a known restore point.

  2. Launch the Tiller Migration Helper from the Extensions menu

  3. Accept the acknowledgement of use & privacy disclaimer

  4. Select the Merge & repair accounts workflow radio option

  5. Follow the prompts for the Merge & Repair workflow

After choosing the “Merge & repair accounts” workflow the add-on will scan your Balance History sheet to determine if it can match any accounts automatically for the Merge & Repair part of the workflow.

Automatic merge matches

If the Legacy account has an exact match on Account name, Institution, and Account # with a YSL account a match will be recommended for you.

⚠️ Be sure to review automated matches carefully to make sure the older account is merged into the newer account.

Manually adding merges

If no merge suggestions are generated automatically you'll need to manually match the legacy accounts to the YSL accounts by adding account matches to the merge queue.

  1. Click the + button

  2. Select a Legacy account from the "Old account" dropdown list (it will be the one with the older data date range)

    1. If you don't see an account you expect to see in the "Old" dropdown list, see these troubleshooting notes.

  3. Select the matching YSL account from the “New Account” dropdown list (it will be the one with the newer data date range)

  4. You can confirm you have the correct matching order (Merge Legacy Into YSL) using the information below the drop downs.

  5. Click + near the top to queue additional merges and match other accounts or scroll down in the modal (if necessary).

  6. If you need to change any of your queued merges you can use the forward and back buttons near the top to review or click the X near the top to remove a merge from the queue.

  7. When you are ready to merge the accounts click “Merge Accounts” to continue and have the workflow Merge & Repair the queued matches. Read more about the changes this workflow makes here.

    ⚠️ Once you click "Merge Accounts" the only way to revert your changes it to restore from a prior version using the Version History under the File menu.

  8. The sidebar will indicate it's repairing your Balance History sheet. This may take up to 5 minutes, depending on the amount of data and amount of merges.

    1. If this takes more than 5 minutes, it's possible the operation crashed and you should close the add-on and re-launch it from the Extensions menu. If needed you can revert to your named version restore point. If the process crashed we recommend queuing only a couple accounts at a time in the Merge & repair workflow.

  9. Choose your next step. The add-on is designed to allow you pause at any point in the process and come back to where you left off and to merge & repair and clean up duplicates in batches if needed due to a high volume of accounts.

    1. Choose Clean up duplicates to flag duplicate transactions for removal for accounts you have Merged & Repaired.

    2. Choose re-run Merge & Repair to merge more accounts

    3. Choose Start Over if you want to start back at the main screen to choose between Merge & repair accounts or migrate data from another sheet

    4. Choose Close Tiller Migration Helper to close the add-on

  10. Share feedback. Let us know how it went by completing this quick survey.

About Merge & Repair

This part of the workflow modifies data in your Balance History and Transactions sheet to merge the Legacy account into the YSL account. This step merges the Legacy account (Old account) Balance History entries into the selected YSL account (New account) entries so that they have consistent Account ID, name, last four digits, and institution data and appear as a single account in the sheet.

This operation helps prevent duplicated instances of the account appearing on the Balances sheet and cleans up transactions so that the duplicate cleaning part of the workflow can find the duplicate transactions.

This merge is local to this sheet only and does not affect the legacy account data in the database.

The workflow adds a “Migration Notes” column to the far right of each sheet with notes about the changes that were made.

The process will make the following changes based on the matches you selected in the Merge & Repair dialog window. These changes are only made in the sheet where the workflow is run. It does not make changes to data in Tiller’s databases or other spreadsheets.

  • Overwrite the Account ID for the Legacy account entries in the Balance History and Transactions sheets to use the account ID for the YSL account.

  • Overwrite the name, Institution, and Account # data for the Legacy account entries in the Balance History and Transactions sheets to use the data for the YSL account.

The old account ID and data points will be noted in the Migration Notes column for historical reference.

You can skip this part of the process by choosing the “Skip this step” in the Merge & Repair dialog window.

If you skip this step, you will end up with duplicate accounts in your Balance History, Accounts, and visibly on your Balances sheet. The Remove Duplicates process will also not be able to find all potential duplicate transactions without exact matches on Account name, Institution, and Account # data for the Legacy and YSL accounts.

Remove Duplicates

After you’ve merged accounts or skipped that step the Remove Duplicates part of the workflow will scan your Transactions sheet for suspected duplicates.

The remove duplicates process will add a temporary column to your Transactions sheet for suspected duplicate flagging.

ℹ️ We recommend sorting the Transactions sheet by the amount column and then Z>A by the date column to sort a suspected duplicate next to its match.

Review & remove duplicates

⚠️ If you notice that the add-on did not flag obvious duplicates review this troubleshooting information.

  1. Carefully review the transactions with a check mark in the temporary “Delete Duplicate” column

  2. If any transaction with a check mark is NOT a duplicate, click the checkbox to remove the check mark.

  3. After you have carefully reviewed all transactions marked for deletion, click the “Delete Checked Transactions” button in the sidebar to delete the transaction.

About Remove Duplicates

The Remove Duplicates workflow will flag suspected duplicate transactions based on exact matches against Account name, Institution, Amount, and Account # for transactions that occur within 3 days of each other.

When the add-on is reviewing two transactions it will flag the one that is NOT categorized as the duplicate so if those that have notes or tags are also categorized (most likely they are) then it will not get flagged for removal.

If neither is categorized, it will flag the one that is older for removal based on the Date Added date.

If there is no Date Added date/column, it will try to flag the 2nd one in the list.

Reassign Groups to Accounts

If you customized your accounts by applying groups to them on the Accounts sheet you’ll need to re-assign them.

Since the Merge & Repair overwrote the Account ID for the Legacy accounts the selections in the Account column on the Accounts sheet will have validation errors (red triangles in the upper right of the cell).

  1. Navigate to the Accounts sheet

  2. Re-select accounts in the Account column

Review AutoCat Rules and Settings

Be sure to review your AutoCat rules sheet and make adjustments to any rules that use Account name, last four, or institution that might be different now.

Turn your AutoCat Auto Run on Update back on under the AutoCat Run Settings in the AutoCat section of the Tiller Money Feeds add-on.

Installation Notes

Ideally, you should use the Google account associated with your Tiller subscription to install the Tiller Migration Helper add-on. However, because this add-on requires sensitive scopes you will not be able to install it with a Google account that has Advanced Protection turned on. You can install it with any Google account that doesn’t have Advanced Protection turned on because it does not need to authenticate with the Tiller service.

Disclaimer & explanation of scopes

This add-on requires several sensitive scopes, as depicted in the screenshot below. These scopes are required because they are bundled with the capability to use a batchUpdate() function that is only available in the v4 API from Google and to be able to select a file from your Google Drive, using a file picker. The batchUpdate() function, which requires the "https://www.googleapis.com/auth/spreadsheets" scope, allows the add-on to more quickly, accurately, and responsively merge & repair balance history entries and clean up duplicate transactions. The file picker, which requires the "https://www.googleapis.com/auth/drive.readonly" scope, is for ease of use when selecting a “source” sheet in the scenario where you are migrating data from one sheet to another.

The use of this add-on is completely optional. If you prefer not to grant access to the sensitive scopes required you can manually perform the same workflows using the steps in this guide.

You can also uninstall this add-on after completing these migration workflows, at which point, the add-on’s access to the contents of your Google Drive will be completely revoked.

Tiller uses the privileged access only to complete the selected workflows in the specified spreadsheets as part of the workflow’s process (i.e. the source and destination sheets that you choose as part of the workflow’s configuration).

Screen Shot 2021-12-21 at 9.20.05 AM

Troubleshooting

Account missing from "Old account" dropdown

This generally happens when the last balance pulled for the account occurred before the account was archived but after the Account Status column was introduced in the Balance History sheet. At the time the last balance was filled, the account was still "active" because your upgrade had not happened yet.

To fix:

  1. Either Merge & Repair accounts you've got merges queued for already or close the Merge & Repair modal.

  2. In the Balance History sheet, find the most recent entry for the old account and enter ARCHIVED into the Account Status column even if it says "Active."

    1. You can identify an Old/Legacy Account by looking at previous entries for the Account ID (the column might be hidden). The old account will have entries where the Account Status column is blank.

  3. Click "Start over" in the Migration Helper add-on sidebar if it's still open or re-launch the add-on and re-start the Merge & repair workflow.

Transactions sheet data not cleaned up

The Merge & Repair process cleans up the transaction data associated with merged accounts as well by modifying the account name, institution, account #, and account ID (if the Account ID column is present).

If you notice that the Transactions sheet does not have the updated data from the Merge & Repair it is likely due to a missing Account ID column and there is no Account # associated with the account (e.g. PayPal or Venmo do not have Account #s). You will need to manually repair data in the Transactions sheet to use the new account name (Account column), and Institution in order for the remove duplicates workflow to flag duplicates for you.

Did this answer your question?