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:
