Tiller Labs Utilities Add-on Comprehensive Guide

What is the Tiller Labs Utilities Add-on?

Our Tiller Utilities add-on by Tiller Labs is a free add-on that provides four new, experimental workflows to improve transaction & balance history integrity and performance in your Tiller-powered Google Sheets:

  • Trim Balance History. Removes extraneous balance history entries by thinning to a single balance entry for each day, week, or month. Select either All Accounts or individual accounts for trimming. The Trimmer can be scheduled to run automatically each night.
  • Repair Balance History. Finds and fixes account data inconsistencies in Balance History. Can merge duplicate account entries by index number and unify inconsistent account name, institution, and number data.
  • Manage Duplicates. Flags possible duplicated transactions (that have slipped into the Transactions sheet) then assists in removal.

Use at your own risk:

Tiller Labs releases early experimental tools & workflows for Tiller users. These workflows and tools have been lightly tested, but are not officially maintained or fully supported.

If you find issues or possible bugs, please let us know by emailing support@tillerhq.com, but use with caution as we are not responsible for data corruption due to the use of these tools.

PLEASE create a named version of your sheet before running a utility to ensure you have a restore point in case something goes wrong. We WILL NOT support fixing sheets that are corrupted by this experimental tool. (File > Version History > Name current version).

Installing the Tiller Utilities add-on

To get started with the Tiller Utilities workflows, install the Tiller Utilities add-on.

  1. Click here to access the add-on from the gSuite add-on store.
  2. Click “Free” in the upper right corner of the listing.
  3. This will open a blank Google sheet.
  4. Authorize the add-on to run by accepting the permissions.
  5. Open one of your Tiller-powered Google Sheets.
  6. Start using Tiller Utilities.

Trim Balance History

Tiller’s data feed adds new balance records to the Balance History sheet sometimes more often than once per day. As time passes, the balances dataset can become very large, causing decreased spreadsheet performance.

The Trim Balance History utility removes extraneous balance history entries by trimming them down to a single balance entry for each day, week, or month (for each account). The frequency you’d like to see is configurable. You can choose all accounts or a single account for trimming. You can schedule the Trimmer to run nightly.

How to use Trim Balance History

  1. Open the Add-ons menu at the top of your Tiller-powered Google Sheet, choose “Tiller Utilities, and click “Start”
  2. Click “Trim Balance History” in the sidebar. The workflow will check for compatibility issues and make the Balance History sheet is your actively selected tab.
  3. Leave “All accounts” selected if you want to trim all accounts or choose an Account from the Account(s) dropdown list.
  4. Choose your frequency from the Balance History Frequency dropdown list. You can choose to keep one balance history entry per day, week, or month for all or a single account. If you’re trimming one account at a time, you can choose a different frequency option per account.
  5. Click the Trim button.
  6. Before deleting the Balance History entries, you will get a confirmation dialog— e.g. “Ready to trim 174 Balance History rows. Are you sure you want to proceed?”
  7. Click Yes.

Be aware that modifications from the Balance History Pruner can only be undone using Sheet’s Version History tools.

How Trim Balance History works

  • The trimmer will always preserve the most recent balance history entry for all accounts.
  • When scheduled to trim on a monthly basis, the final balance history record of each month will also be preserved.
  • When scheduled to trim on a weekly basis, balance history records on a Sunday will be preserved. If no record exists on a Sunday, the trim will preserve the soonest record thereafter.
  • Further, when scheduled to trim on a weekly basis, the final balance history record of each month will be preserved (in case you wish to trim to a monthly basis later).

How to Schedule the Trimmer to Run Nightly

  1. Configure the trimmer per the instructions above.
  2. Before clicking the Trim button, check the “Schedule to run nightly” checkbox.
  3. Click the Schedule & Trim button to run the trimmer and set the schedule for the selected account or all accounts.

The trim balance history entries as configured on a nightly basis. The exact time of automated trimming may vary depending on your time zone.

Notes about the automated nightly trimming feature

  • Whenever the Trim Balance History sidebar is open, below the scheduler checkbox, the sidebar will display the configuration for your trimming schedules— e.g. “Currently configured to trim Bank of America Checking (xxxx0001) nightly to a daily basis”.
  • Only one trimmer configuration can be scheduled at a time. Scheduling a different trimming configuration will overwrite any existing automation.

Repair Balance History

If you or your bank has updated any of your account information, your Balance History sheet may have varying information for a unique account such as multiple index numbers or account names.

These issues fall into two categories:

  • Index Mismatch are accounts with matching “Account #” but with index numbers, which Tiller uses to uniquely identify and enumerate accounts in your spreadsheet, that are different. Index numbers are often re-assigned for a single account when you remove the account from the Tiller Console (deleting the data from our databases) and then reconnect the account later and link it to the same sheet. Read more about the account index number here.
  • Inconsistent Account Data: accounts with inconsistent account names, institutions, types, and classes through Balance History

The Repair Balance History workflow finds inconsistencies in account data in the Balance History sheet and offers suggestions on how to repair them. Inconsistent Account Data is repaired by unifying account data to the latest balance history record.

For best results be sure that you have your desired Account names set on the Tiller Console under Account Summary for all accounts before repairing Balance History.

How to use Repair Balance History

  1. Open the Add-ons menu at the top of your Tiller-powered Google Sheet, choose “Tiller Utilities, and click “Start”
  2. Click on “Repair Balance History”. The workflow will check for compatibility issues and make sure the Balance History sheet is your actively selected tab.
  3. The tool will analyze your Balance History sheet for inconsistent data.
  4. If there are any issues, the sidebar will report them in two groups Index Mismatch & Inconsistent Account Data.
  5. Review the found issues carefully.
  6. To correct an issue, click the checkbox to the left of it, then click the Repair Checked Accounts button.
  7. New Balance History inconsistencies may appear after correcting one or more issues. Continue addressing issues until no new issues appear.

Be aware that modifications from the Repair Balance History can only be undone using Sheet’s Version History tools.

How the Repair Balance History works:

  • Index Mismatch issues are flagged when one or more account index numbers are used for accounts with the same “Account #”. Be aware that empty/unpopulated account numbers may result in false positives.
  • When Index Mismatch issues are corrected, they may result in new Inconsistent Account Data issues. Be sure to
  • This workflow ignores manually tracked accounts with index numbers of 9999.
  • Inconsistent account data issues arise when the account number, account name, institution, and class of accounts vary across a single index number. Be sure to correct Index Mismatch issues before correcting Inconsistent Account Data issues.

Manage Duplicates

Tiller’s bank data feed may occasionally insert duplicate transactions into your Transactions sheet. Often times these are addressable by user action or by having the support team contact Tiller’s data provider. In rare cases, duplication issues cannot be resolved and will persist. Review this help article on why you might be seeing duplicates to help prevent them.

If duplicate transactions happen occasionally or regularly without resolution, use the Manage Duplicates workflow to address the issue.

The workflow identifies transactions that have a date of within 3 days of each with the same amount and other data including description, institution, and account number. The Manage Duplicates workflow highlights a suspected duplicate row with an orange fill. You’ll have an opportunity to flag additional transactions that should have been highlighted and also to remove the flag for transactions that are not actually duplicates. Once you’re ready, the workflow can delete all flagged duplicate transactions.

Recommended Duplicate Cleaning Workflow

  1. Open the Add-ons menu at the top of your Tiller-powered Google Sheet, choose “Tiller Utilities, and click “Start”

  2. Click on “Manage Duplicates”. The workflow will check for compatibility issues and make sure the Transactions sheet is your actively selected tab.

  3. Click the Flag Duplicates button (in Step 1). The workflow will analyze your transactions and highlight rows with suspected duplicate transactions in orange.

  4. Review the highlighted transactions.

  5. If you see a transaction that should have been flagged as a duplicate, select a cell in the row and then click the orange Flag Selected button. (Hold shift and click multiple rows to select multiple transactions before clicking the “Flag Selected” button.)

  6. If you see a transaction flagged as a duplicate that isn’t actually a duplicate (i.e. you do not want it deleted), select a cell in that transaction’s row and then click the Unflag Selected button. (Hold shift and click multiple rows to select multiple transactions before clicking the “Unflag Selected” button.)

  7. Once you’re satisfied with the flagged transactions, click the “Delete Flagged Transactions” to delete the duplicate transactions. All rows that have been highlighted in orange will be deleted.

Be aware that deletions can only be undone using Sheet’s Version History tools.

How Manage Duplicates works

  • The deduplication analysis will usually preserve the oldest/original transaction from a set of duplicate transactions.
  • If only one transaction within a set of duplicates has been categorized, the deduplication analysis will preserve the categorized transaction (even if it is the newest).
  • To identify a match, the deduplication analysis requires an exact match for all of the following fields (if present) across the set of duplicates:
    • Amount
    • Description
    • Full Description
    • Account
    • Account #
    • Institution