Automatic "Transfer" Matcher with Clickable Links (Google Apps script version)

[alan.heatherley]

Overview

This solution provides a clickable link in your Transaction sheet that takes you to the other side of a transfer. This solution can usually automatically identify the 2 transactions that represent the origin and destination of a transfer, but functionality is provided to fill in the gaps if the solution can’t make the link automatically.

This an alternate, Google Apps script-based version of this template I posted a while ago - Automatic “Transfer” Matcher with Clickable Links (Google Sheets version) - Google Sheets / Show & Tell - Tiller Community. See the Background section below for why I rewrote that template using a script.

Installation

If you’ve never used a Google Apps script with your spreadsheet, installation can be a little daunting, but you only have to do it once then never think about it again.

1. Grab the Transfer Link Ext sheet from here: Transfer Link Script Version - Google Sheets
2. Copy the Transfer Link Ext sheet to your Tiller spreadsheet, and rename it back to “Transfer Link Ext” if the name is changed to “Copy of…”
3. Switch to the Transactions sheet, and create a new column called “Transfer Link”
4. From the Extensions menu, choose Apps Script
   
   
5. This will open a new window that looks like this:
   
   
6. Click on the “Untitled project” title and give it a name, if you want
7. Highlight the function myFunction() {} code snippet and delete it
8. Copy and paste this code into the window:

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Transfer Link')
    .addItem('Refresh Transfer Links', 'refreshTransferLinks')
    .addToUi();
}


function refreshTransferLinks() {

  /*
    This function:
      1. Reads in the Tiller Transactions sheet
      2. Filters out any transaction that isn't a transfer
      3. Attempts to identify the other side of the transfer
      4. Writes a transfer summary table to the Transfer Link sheet
      5. Writes hyperlinks to the Transactions sheet that links the two sides of a transfer
  */


  //Name of the Transfer Link sheet. Change this if you need to change the name of the sheet
  var transferLinkSheetName = 'Transfer Link Ext';




  Logger.log('Starting refresh transfer links');

  //read in basic information about the target spreadsheet
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var transactionsSheetName = spreadsheet.getRangeByName(transferLinkSheetName + '!transfer_link_transactions_sheet_name').getDisplayValue();
  var transferLinkSheet = spreadsheet.getSheetByName(transferLinkSheetName);
  var transactionsSheet = spreadsheet.getSheetByName(transactionsSheetName);

  //Set ID's for the Tiller spreadsheet, Transfer Link sheet, and Transactions sheet. These ID's are used in the hyperlink on the Transactions sheet
  spreadsheet.getRangeByName(transferLinkSheetName + '!transfer_link_sheet_id').setValue(spreadsheet.getId());
  spreadsheet.getRangeByName(transferLinkSheetName + '!transfer_link_transactions_gid').setValue(transactionsSheet.getSheetId());
  spreadsheet.getRangeByName(transferLinkSheetName + '!transfer_link_transfer_link_gid').setValue(transferLinkSheet.getSheetId());

  Logger.log('Loading transactions data');
  //These named ranges store A1 ranges for the data to be read in later
  var transactionsDateDataRange = spreadsheet.getRangeByName(transferLinkSheetName + '!transfer_link_transactions_date').getDisplayValue();
  var transactionsDescriptionsDataRange = spreadsheet.getRangeByName(transferLinkSheetName + '!transfer_link_transactions_description').getDisplayValue();
  var transactionsAmountDataRange = spreadsheet.getRangeByName(transferLinkSheetName + '!transfer_link_transactions_amount').getValue();
  var transactionsCategoryDataRange = spreadsheet.getRangeByName(transferLinkSheetName + '!transfer_link_transactions_category').getDisplayValue();
  var transactionsAccountDataRange = spreadsheet.getRangeByName(transferLinkSheetName + '!transfer_link_transactions_account').getDisplayValue();
  var transactionsTransactionIDDataRange = spreadsheet.getRangeByName(transferLinkSheetName + '!transfer_link_transactions_transaction_id').getDisplayValue();
  var transactionsTransferLinkDataRange = spreadsheet.getRangeByName(transferLinkSheetName + '!transfer_link_transactions_transfer_link').getDisplayValue();

  //Read in the Transactions sheet data
  var transactionsDateData = spreadsheet.getRange(transactionsDateDataRange).getDisplayValues();
  var transactionsDescriptionsData = spreadsheet.getRange(transactionsDescriptionsDataRange).getDisplayValues();
  var transactionsAmountData = spreadsheet.getRange(transactionsAmountDataRange).getValues();
  var transactionsCategoryData = spreadsheet.getRange(transactionsCategoryDataRange).getDisplayValues();
  var transactionsAccountData = spreadsheet.getRange(transactionsAccountDataRange).getDisplayValues();
  var transactionsTransactionIDData = spreadsheet.getRange(transactionsTransactionIDDataRange).getDisplayValues();
  var transactionsTransferLinkData = spreadsheet.getRange(transactionsTransferLinkDataRange);

  //Categories are assigned a Type - Income, Expense, or Transfer. This field defines the name of the Transfer Type so that only Transfer categories are kept
  var categoryTransferTypeFilter = spreadsheet.getRangeByName(transferLinkSheetName + '!transfer_link_transfer_category_type').getDisplayValue();

  //These named ranges store A1 ranges for the data to be read in later
  var categoriesCategoryRange = spreadsheet.getRangeByName(transferLinkSheetName + '!transfer_link_categories_category').getDisplayValue();
  var categoriesTypeDataRange = spreadsheet.getRangeByName(transferLinkSheetName + '!transfer_link_categories_type').getDisplayValue();

  //Read in the Categories and their Types
  var categoriesCategoryData = spreadsheet.getRange(categoriesCategoryRange).getDisplayValues();
  var categoriesTypeData = spreadsheet.getRange(categoriesTypeDataRange).getDisplayValues();

  Logger.log('Identifying transfer transactions');
  //Array to hold the transactions identified as Transfers
  var filteredResults = [];

  //Creates a table of Transfer Categories that will be used to filter out non-Transfer Transactions
  var categoryMap = {};
  for (var j = 0; j < categoriesCategoryData.length; j++) {
    if (categoriesTypeData[j][0] == categoryTransferTypeFilter) {
      categoryMap[categoriesCategoryData[j][0]] = true;
    }
  }

  //Array to hold the Transfer Hyperlinks
  var newTransferLinkData = new Array(transactionsCategoryData.length).fill(['']);

  //Read in the column letter for the TransactionID on the Transactions sheet. This will be used in the Hyperlink
  var transactionsTransactionIDColLetter = spreadsheet.getRangeByName(transferLinkSheetName + '!transfer_link_transactions_transaction_id_col_letter').getDisplayValue();

  //Loops through the entire Transactions data set looking for Transfers
  for (var i = 0; i < transactionsCategoryData.length; i++) {
    var category = transactionsCategoryData[i][0]; //The category of the current transaction
    if (categoryMap[category]) {  //if the current category is a Transfer...
      var dt = new Date(transactionsDateData[i][0]);
      //Generate an ID that can be used to manually match transfers on the Transfer Link sheet
      var transferID = YEAR(dt) + "-" + padNumber(MONTH(dt)) + "-" + padNumber(DAY(dt)) + " | " + transactionsAccountData[i][0] + " | " + category + " | " + (transactionsAmountData[i][0] < 0 ? `($${Math.abs(transactionsAmountData[i][0])})` : `$${transactionsAmountData[i][0]}`) + " | " + transactionsTransactionIDData[i][0].slice(-5);
      //Adds the current transaction to the filteredResults array 
      filteredResults.push([transactionsDateData[i][0], transactionsDescriptionsData[i][0], transactionsAmountData[i][0], category, transactionsAccountData[i][0], transferID, transactionsTransactionIDData[i][0]]);
      //Generates the Hyperlink formula that will be written to the Transactions sheet
      newTransferLinkData[i] = ["=IFNA(HYPERLINK(VLOOKUP(" + transactionsTransactionIDColLetter + (i + 2) + ", HSTACK(INDIRECT('" + transferLinkSheetName + "'!transfer_link_transaction_id_range),INDIRECT('" + transferLinkSheetName + "'!transfer_link_hyperlink_range)), 2, FALSE),VLOOKUP(" + transactionsTransactionIDColLetter + (i + 2) + ", HSTACK(INDIRECT('" + transferLinkSheetName + "'!transfer_link_transaction_id_range),INDIRECT('" + transferLinkSheetName + "'!transfer_link_hyperlink_display_range)), 2, FALSE)),\"ERROR\")"];
    }
  }
  //There now exists an array (filteredResults) that contains all of the identified Transfer transactions
  //There is also an array (newTransferLinkData) that contains all of the Hyperlinks that will be written to the Transactions sheet

  Logger.log('Finding auto matches');

  //Read in the user-configured before/after day ranges to look for possible matches to the transfer
  var autoMatchDaysAfter = spreadsheet.getRangeByName(transferLinkSheetName + '!transfer_link_days_after').getDisplayValue();
  var autoMatchDaysBefore = spreadsheet.getRangeByName(transferLinkSheetName + '!transfer_link_days_before').getDisplayValue();
  //Read in the user-configured option to indicate whether the auto-match function will use the account/category table
  //If the option is turned off, the auto-match function will only use the amount and date to look for matches
  var checkAccountCategories = spreadsheet.getRangeByName(transferLinkSheetName + '!transfer_link_use_acct_categories').getValue();
  //Read in the optional user-configured account/category look-up table. This table is used to assist the auto-match function in identifying the matching transfer
  var transferOutCategories = spreadsheet.getRangeByName(transferLinkSheetName + '!transfer_link_config_transfer_out_categories').getDisplayValues();
  var transferInCategories = spreadsheet.getRangeByName(transferLinkSheetName + '!transfer_link_config_transfer_in_categories').getDisplayValues();
  var transferAccounts = spreadsheet.getRangeByName(transferLinkSheetName + '!transfer_link_config_accounts').getDisplayValues();
  var result = [];
  var matches = [];

  for (var i = 0; i < filteredResults.length; i++) { //Loops through the filteredResults array to run the auto-match function on each transaction
    //Reads the date, amount, category and account of the current transfer transaction
    var currentDate = new Date(filteredResults[i][0]);
    var currentAmount = filteredResults[i][2];
    var currentCategory = filteredResults[i][3];
    var currentAccount = filteredResults[i][4];

    //Calculates the range of dates before and after the current transaction date to look for the matching transfer
    var currentDatePlusAutoMatchDaysAfter = new Date(currentDate.getTime() + (autoMatchDaysAfter * 24 * 60 * 60 * 1000));
    var currentDateMinusAutoMatchDaysBefore = new Date(currentDate.getTime() - (autoMatchDaysBefore * 24 * 60 * 60 * 1000));

    //The auto-match funtction
    for (var j = 0; j < filteredResults.length; j++) {
      if (i === j) continue;  // Skip same transaction
      //Reads the date, amount, category and account of the transfer transaction to compare against
      var compareDate = new Date(filteredResults[j][0]);
      var compareAmount = -filteredResults[j][2];
      var compareCategory = filteredResults[j][3];
      var compareAccount = filteredResults[j][4];

      /*
        The auto-match function uses the following criteria to find possible matches:
        1. dateCheck: The date of the transaction being compared (compareDate) must fall within the user-configured range (i.e. within +/- 5 days) of currentDate
        2. amtCheck: The amount of the transaction being compared (compareAmount) must equal -1*currentAmount

        If the user enabled the checkAccountCategories flag, then the auto-match function will also consider the following criteria:

        Sample account/category look-up table from the Transfer Link sheet:

        Account         Transfer Out      Transfer In
        Acct 1          To Acct 1         From Acct 1
        Acct 2          To Acct 2         From Acct 2
        Acct 3          To Acct 3         From Acct 3

        3. acctCheck: Look up the category of the transaction being compared (compareCategory) in the table. If currentAmount >0, look at the Transfer Out column
           for the category, otherwise look at Transfer In column. Return the Account configured for that category. Compare this value to currentAccount.
        4. catCheck: Look up the account of the transaction being compared (compareAccount) in the table. If currentAmount >0, return the Transfer In category,
           otherwise return the Transfer Out category. Compare this value to currentCategory.
        
        If all the checks = TRUE, then the transaction being compared is added to the matches array.
        
      */

      var dateCheck = (compareDate <= currentDatePlusAutoMatchDaysAfter && compareDate >= currentDateMinusAutoMatchDaysBefore);
      var amtCheck = (compareAmount === currentAmount);

      if (checkAccountCategories) {
        var acctMatch = findAcctMatch(compareCategory, currentAmount, transferAccounts, transferOutCategories, transferInCategories);
        var catMatch = findCatMatch(compareAccount, currentAmount, transferAccounts, transferOutCategories, transferInCategories);

        var acctCheck = (acctMatch === currentAccount);
        var catCheck = (catMatch === currentCategory);

        if (dateCheck && amtCheck && acctCheck && catCheck) {
          matches.push(filteredResults[j][6]);
        }
      } else if (dateCheck && amtCheck) {
        matches.push(filteredResults[j][6]);
      }

    }

    /*
      Check the length of the matches array. If there is more than 1 possible match, add a 0 to the filteredResults array.

      If there is exactly 1 possible match, add the transactionID of the match to filteredResults.

      If neither of those criteria are met, add a 0 to the filteredResults array. 

      Transfers with a 0 for the auto-match are presented to the user in the Transfer Link sheet to manually make the link between transfers.
    */

    if (matches.length > 1) {
      result.push([...filteredResults[i], 0]);  // Spread the array
    } else if (matches.length === 1) {
      result.push([...filteredResults[i], matches[0]]);
    } else {
      result.push([...filteredResults[i], 0]);
    }

    //Reset matches to prepare for the next run through
    matches = [];
  }

  Logger.log('Clearing old transfer summary table');
  //Clear the existing transfer summary table
  var transferLinkDataRange = spreadsheet.getRangeByName(transferLinkSheetName + '!transfer_link_data_range').getDisplayValue();
  var transferLinkData = spreadsheet.getRange(transferLinkDataRange);
  transferLinkData.clearContent();

  Logger.log('Writing new transfer summary table');
  //Write the new transfer summary table
  if (result.length > 0) {
    var outputRange = transferLinkSheet.getRange(transferLinkDataRange + (result.length + 1));
    outputRange.setValues(result);
    Logger.log('Transfers identified: ' + result.length);
  } else {
    Logger.log('No transfers identified');
  }

  Logger.log('Writing Transfer Link Hyperlinks to Transactions sheet');
  //Clear the Transfer Link column on the Transactions sheet
  transactionsTransferLinkData.clearContent();
  //Write the new Hyperlink formulas to the Transactions sheet
  transactionsTransferLinkData.setFormulas(newTransferLinkData);

  Logger.log('Finished refreshing transfer links');
}

function padNumber(number) {
  return number < 10 ? "0" + number : number;
}

function YEAR(date) {
  return date.getFullYear();
}

function MONTH(date) {
  return date.getMonth() + 1; // getMonth() returns 0-11
}

function DAY(date) {
  return date.getDate();
}

function findAcctMatch(cat, amt, account, transferOut, transferIn) {
  var match = null;
  var range = amt > 0 ? transferOut : transferIn;
  for (var i = 0; i < range.length; i++) {
    if (cat === range[i][0]) {
      match = account[i][0];
      break;
    }
  }
  return match;
}

function findCatMatch(acct, amt, account, transferOut, transferIn) {
  var match = null;
  var range = account;
  for (var i = 0; i < range.length; i++) {
    if (acct === range[i][0]) {
      match = amt < 0 ? transferOut[i][0] : transferIn[i][0];
      break;
    }
  }
  return match;
}

9. It should now look like this:
   
   
10. Click the save icon (to the left of the Run button)
11. If you’ve never used Google Apps scripts before, it may prompt you to give permission for the script to be saved and run
12. Close the Apps Script tab
13. Go back to your Tiller spreadsheet tab, and refresh the page
14. If all goes well, you should now have a new menu to the right of Help:
    
    

Setup

There are a few options to customize the functioning of the Transfer Link script:

1. Name of the Transactions sheet
2. Days Before/After - this will define the range of dates the auto-match function will look for matches. For example, if the date of a transfer is January 10, then the auto-match function will only consider other transactions that occur between January 5 and January 15
3. Transfer Type Name - on the Categories sheet, there are three types of category: Income, Expense, or Transfer. Make sure this value matches what’s on your Categories sheet.
4. Hyperlink friendly name - When a matching transfer is identified for a transaction, this is the text displayed on the hyperlink in the Transfer Link column on the Transactions sheet
5. Need help message - If a matching transfer is not identified, this text will alert you that you need to manually set the matching transfer
6. Hyperlink Start/End Column - when you click a hyperlink, a portion of the target row will be selected. Set these to be as narrow or wide as you choose.
7. Use Account-Specific Categories - this enables the Account/Category look-up table to enhance the auto-match function. See the “How the Auto-Match Works” section for why you might want to enable this.

Usage

To run the Transfer Link script, select Refresh Transfer Links from the new menu

The script will run, and once it’s complete (it takes ~20 seconds for me), the Transfer Link column on the Transactions sheet will populate with hyperlinks:

Clicking on a link will take you to the matching transaction.

If a link says “Need manual override”, clicking the link will take you to the Transfer Link Ext sheet to the Manual Matches table. Pick the incoming and outgoing transactions to link them, and the hyperlink will update automatically.

When to run the script

You only need to run the script when:

• You add a new transfer transaction.
• You change the setup values in B1:B9
• You modify the Account/Category look-up table

If you filled transactions from Tiller, but none of them are transfers, you don’t need to run this script.

How the Auto-Match function works

Let’s say you have the following transactions:

Date          Amount       Account        Category
1/10/2025     $200         Acct 1         From Acct 2
1/10/2025    ($200)        Acct 2         To Acct 1
1/12/2025    ($200)        Acct 1         To Acct 3
1/12/2025     $200         Acct 3         From Acct 1

By default the Auto-Match function will only look at the date and amount to find a match. For example, if the Auto-Match function is looking for a match for the first transaction listed:

1. Dates - the dates must fall within the range specified in the Setup section. In this example, all the transactions fall within that range.
2. Amount - the amount of the matching transfer must equal (-1*Amount of Transaction 1). In this example, only transactions 2 and 3 meet this criteria.

Here, the Auto-Match function finds both transaction 2 and 3 to be possible matches, so it leaves the match blank, and the user must manually set the match.

However, if you enable the Account-Specific Category table, and configure it like so:

The Auto-Match function will also consider the following criteria:
3. Account - transaction 1 has category “From Acct 2” and the amount is >0, so only transactions for Acct 2 with an amount <0 will be considered. In this example, only transaction 2 meets the criteria.
4. Category - transaction 1 is for Acct 1 and the amount is >0, so only transactions with category “To Acct 1” and amount <0 will be considered. In this example, only transaction 2 meets the criteria.

In this case, enabling the Account/Category table enabled the Auto-Match function to identify the matching transfer when before it couldn’t.

In my data, I have ~800 transfers. Without the Account/Category table, the Auto-Match would identify ~600 matches, leaving the rest for me to manually set. With the Account/Category table, the match rate went to 750+, leaving me with far fewer to manually match.

Background

The primary issue I had with the original version was that it was a resource hog. Since it was formula-based, every time a change was made to the Transactions sheet, the entire Transfer Link look-up table and the Transfer hyperlink column on the Transactions sheet had to recalculate. That made it painful to have in place.

Some testing with a static look-up table completely eliminated the performance hit during day-to-day usage but still provided the same functionality. I also eliminated the MAP function on the Transfer Link column so only transfer transactions have the hyperlink. (The current version of the look-up table is completely static except for calculating the row of the transactions. The hyperlink requires the current row of the target transaction, which can change anytime new transactions are added, so that piece is still dynamic.)

The trade-off is that the look-up table no longer updates automatically when adding or removing a transfer transaction. Google Sheets provides events that can trigger a script to run, but they don’t work when an extension like Tiller fills in data, so the script has to be triggered manually. In my case I only add new transfers a few times a month, so it’s not something I have to do daily.

Performance of the script is pretty good: I have ~6700 transactions with ~800 of them being transfers, and the script takes ~20 seconds to run on my data. You can use that to gauge how long it will take on yours if you have more or less than I do.

Permissions

Is it ok for others to copy, use, and modify your workflow?

Yes!