Best practice for Google Application Scripting in Tiller

[BryanS]

Update: I set up a test environment by copying over a subset of my transactions tab. I have the following substring matching custom function working. Heavy lifting around array processing and clever caching to speed things up was done by ChatGPT.

const LOOKUP_CACHE = {};

function SUB_LOOKUP(searchTerms, numbers) {
  const cacheKey = 'lookupData';
  if (!LOOKUP_CACHE[cacheKey]) {
    const lookupSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("URL Lookup");
    const range = lookupSheet.getRange("A:C");
    const lookupData = range.getValues();
    const richTextValues = range.getRichTextValues();

    LOOKUP_CACHE[cacheKey] = lookupData.map((row, i) => {
      const richText = richTextValues[i][2];
      const linkUrl = richText ? richText.getLinkUrl() : null;
      const visibleText = row[2] || "";
      return [row[0], row[1], visibleText, linkUrl];
    }).filter(row => row[0] !== "" && !isNaN(row[1]));
  }

  const lookupArray = LOOKUP_CACHE[cacheKey];

  if (Array.isArray(searchTerms) && Array.isArray(numbers)) {
    return searchTerms.map((row, i) => {
      const term = row[0];
      const num = parseFloat(numbers[i][0]);
      return [getApproximateMatchUrl(term, num, lookupArray)];
    });
  } else {
    return getApproximateMatchUrl(searchTerms, parseFloat(numbers), lookupArray);
  }
}


function getApproximateMatchUrl(text, num, lookupArray) {
  try {
    if (!text || isNaN(num)) return "No Input";

    const tolerance = 0.01;
    const cleanText = text.toString().toLowerCase().trim();

    for (const [substring, lookupNum, visibleText, linkUrl] of lookupArray) {
      const cleanSubstring = substring.toString().toLowerCase().trim();
      if (
        cleanText.includes(cleanSubstring) &&
        Math.abs(parseFloat(lookupNum) - num) <= tolerance
      ) {
        return linkUrl || visibleText || "No Link";
      }
    }
    return "";
  } catch (err) {
    Logger.log("Error: " + err);
    return "ERROR";
  }
}

This matches on amount and a substring match of the Description against the store name in the lookup table. With a properly setup lookup table, you can then use the following formula in the header row:

=ARRAYFORMULA(IF(isblank($C:$C),"",if($C:$C<>"Description", hyperlink(SUB_LOOKUP($C:$C, $F:$F)), "Receipt")))

This yields the following sample output:

The URLs are hoverable, to show a preview of the receipt, and with the right browser extensions should show the full file. Have not tested that bit yet.

For reference: the lookup table looks like this: