REGEX to find a specific formula in a google sheet cell

Question

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

Mihael Mihov · Answer

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)

TheMaster · Answer

MYFORMULA$([^,\n]+,?){0,2}$

MYFORMULA Literal MYFORMULA
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

/**/console.config({maximize:true,timeStamps:false,autoScroll:false});/**/ 
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)]));

user30hu · Answer

This also requires the input to contain an equals sign

function shouldRefreshFormula(input) {
  return input.match(/^=.*MYFORMULA$(\w+,?){0,2}$)/g) ? true:false;
}

REGEX to find a specific formula in a google sheet cell

3 Answers