I have a google sheet addon, with custom formulas that fetch data from my API, I built a refresh mechanism to force the formulas to execute again to get updated data, but I want only specific formulas to refresh and not not every cell in the spreadsheet.
let's suppose my formula is called MYFORMULA(), I should only refresh if the cell contains this formula and it has 2 or less parameters, because the third one is always a date, which means it fetches historical data and doesn't need to be updated.
I need a function that given the cell content returns whether it should be refreshed or not:
some sample inputs and expected outputs:
=MYFORMULA(A1) --> true
=MYFORMULA(A1,B1) --> true
=MYFORMULA(A1,B1,C1) --> false
=TODAY() --> false
=ifs(C1=TODAY(),MYFORMULA(A1),A1<>"", MYFORMULA(A1,B1,C1),C1="","") --> true
=MYFORMULA(A1,B1,C1) - MYFORMULA(A2,B2) --> true
=if(D20="cc",if(B20<=MYFORMULA(A20),"Over","ok"),"-") --> true
So to sum up the input can be any kind of complex google sheet formula, but I only care if inside there is a call to MYFORMULA with less than 3 parameters
EDIT: this is what I have so far:
const MY_FORMULAS = ['MYFORMULA1', 'MYFORULA2'];
function shouldRefreshFormula(input) {
if(input[0] != '=')
return false;
var matches= input.split(/[\(,\)]/g).slice(0, -1);
const splittedArray = [];
while (matches.length > 0) {
var index = -1;
for (let formula of MY_FORMULAS) {
var formulaIndex = matches.indexOf(matches.find(e => e.includes(formula)), 1);
if (formulaIndex > -1) {
if (index == -1)
index = formulaIndex;
else if (formulaIndex < index) {
index = formulaIndex;
}
}
}
if(index > 0)
splittedArray.push(matches.splice(0,index));
else
splittedArray.push(matches.splice(0,matches.length));
}
for (let formula of splittedArray) {
if (formula.indexOf('') != -1)
formula.splice(formula.indexOf(''), formula.length);
if(input && formula.length > 0 && (MY_FORMULAS.some(x => formula[0].includes(x)))) {
if (formula.length < 4) {
return true;
}
}
}
return false;
}
what this function tries to do is to generate an array of arrays which contains the formula name plus its parameters so if I find a call to my formula I can validate the amount of parameters. This is not efficient plus I'm not sure I'm contemplating all combinations of inputs
CodePudding user response:
Check out this one: MYFORMULA\(\w ?(,\w )?\)|MYFORMULA\(\)
I think it will work provided you don't have nested functions as arguments
The regex above captures only MYFORMULA() | MYFORMULA(A1) | MYFORMULA(A1,B1)
CodePudding user response:
MYFORMULA\(([^,\n] ,?){0,2}\)
MYFORMULA
LiteralMYFORMULA
- Borders
\(
and\)
- Group 1:
([^,\n] ,?)
[^,\n]
One or more of: Not a comma or a newline,?
followed by a optional,
{0,2}
Group 1 repeated between 0 and 2 times
/*<ignore>*/console.config({maximize:true,timeStamps:false,autoScroll:false});/*</ignore>*/
const inputs = [
'=MYFORMULA(A1) ',
'=MYFORMULA(A1,B1)',
'=MYFORMULA(A1,B1,C1)',
'=TODAY()',
'=ifs(C1=TODAY(),MYFORMULA(A1),A1<>"", MYFORMULA(A1,B1,C1),C1="","")',
'=MYFORMULA(A1,B1,C1) - MYFORMULA(A2,B2)',
'=if(D20="cc",if(B20<=MYFORMULA(A20),"Over","ok"),"-")',
];
console.table(inputs.map((s) => [s,/MYFORMULA\(([^,\n] ,?){0,2}\)/.test(s)]));
<!-- https://meta.stackoverflow.com/a/375985/ --> <script src="https://gh-canon.github.io/stack-snippet-console/console.min.js"></script>
CodePudding user response:
This also requires the input to contain an equals sign
function shouldRefreshFormula(input) {
return input.match(/^=.*MYFORMULA\((\w ,?){0,2}\))/g) ? true:false;
}