I am writing a small script to reconcile bank transactions against recorded budget entries in a sheet. The budget entries also include some formulas, for things such as Supermarket shopping which has many entries for a month. Problem is when I use textFinder, it gets a hit on a partial match when it is in a formula.
function extractFormulas(amount) {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("June");
const lrow = sheet.getLastRow();
const receiptsRange = sheet.getRange("C4:C"+lrow);
var result = null;
if (amount <= 0) { //payments are negative in the bank statement
if (result = receiptsRange.createTextFinder(-amount).matchFormulaText(true).findNext()){
return(result.offset(0,-1).getValue()); //I'm interested in the adjacent cell contents
} else {
return("not found");
}
} else {
return("deposit");
}
sample data from bank statement after function has executed
Supermarket -160
deposit 2000
Supermarket -45.5
Petrol car & Bike & Rideon -12
The supermarket match (160) is the 160.76 element
=26.24+39.74+15.37+4.7+41.92+45.5+48.49+133.39+10.19+34.81+26.09+7.7+11.03+19.17+160.76+10.4+298.96+4.2+26.99+335.05
The Petrol car & Bike & Rideon match (12) is the 112.92 element
=36.89+112.92
If I add the matchEntireCell(true) then I dont get hits at all in the formulas
I was expecting a full match not a partial match for each entry in a formula
ie:
=36.89+112.92
a match if and only if either of the 2 entries in the formula was matched (36.89, 112.92)
not a partial match
tikitour is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.