Google-sheets – How to reference existing formulas from custom formulas in Google Spreadsheet Scripts

formulasgoogle sheetsgoogle-apps-scriptgoogle-sheets-custom-functionworksheet-function

I want something essentially like this:

function contains(arg1, arg2) {
  return NOT(ISERROR(search(arg1, arg2)));
}

But NOT and ISERROR are undefined even though using =ISERROR(…) works in the spreadsheet view.

How do I build custom formulas that can reference and combine existing formulas?

Best Answer

Google Sheets built-in functions can't be used on Google Apps Script but they could be used as arguments of custom functions and custom functions could be used as arguments of them.

It's worth to note that some Google Sheets built-in functions have direct JavaScript equivalents while others don't. By the other hand as Google Apps Script runs only on Google servers, using a built-in function usually is way faster than a custom function.

Knowing the above it doesn't make sense to use a custom function that does the same as a formula made only with built-in functions.

Related Solutions

Google-sheets – How to pass a range into a custom function in Google Spreadsheets

So I've searched long and hard for a good answer to this and here is what I have found:

an unmodified range parameter passes in the values of cells in the range, not the range itself (as Gergely explained) ex: when you do this =myFunction(a1:a2)
to use a range in your function you need to first pass the range in as a string (ex: =myFunction("a1:a2") ), then turn it into a range with the following code inside the function:
```
Function myFunction(pRange){
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var range = sheet.getRange(pRange);
}
```
Finally, if you want the benefits of passing in a range (like intelligently copying range references to other cells) AND you want to pass it in as a string, you have to use a default function that accepts a range as a parameter and outputs a string you can use. I detail both ways I have found below, but I prefer the 2nd.

For all Google spreadsheets:
=myFunction(ADDRESS(row(A1),COLUMN(A1),4)&":"&ADDRESS(row(A2),COLUMN(A2),4))

For the new Google Sheets:

=myFunction(CELL("address",A1)&":"&CELL("address",A2))

Google-sheets – In Google Spreadsheet, finding what formulas reference a given value

The following code will add a menu to the Spreadsheet:

Detective > Trace Dependents

Selecting this will add a note to the active cell with all dependent cell references.

(added search for static references as suggested by Graham below)

You could add a similar function to the traceDependents function to search for the text within the active cell for a Search in Formulas function. I'll leave that as an exercise for you.

function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var menuEntries = []
  menuEntries.push({name: "Trace Dependents", functionName: "traceDependents"});
  ss.addMenu("Detective", menuEntries);
}

function traceDependents(){
  var dependents = []
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var currentCell = ss.getActiveCell();
  var currentCellRef = currentCell.getA1Notation();
  var range = ss.getDataRange();

  var regex = new RegExp("\\b" + currentCellRef + "\\b");
  var formulas = range.getFormulas();

  for (var i = 0; i < formulas.length; i++){
    var row = formulas[i];

    for (var j = 0; j < row.length; j++){
      var cellFormula = row[j].replace(/\$/g, "");
      if (regex.test(cellFormula)){
        dependents.push([i,j]);
      }
    }
  }

  var dependentRefs = [];
  for (var k = 0; k < dependents.length; k ++){
    var rowNum = dependents[k][0] + 1;
    var colNum = dependents[k][1] + 1;
    var cell = range.getCell(rowNum, colNum);
    var cellRef = cell.getA1Notation();
    dependentRefs.push(cellRef);
  }
  var output = "Dependents: ";
  if(dependentRefs.length > 0){
    output += dependentRefs.join(", ");
  } else {
    output += " None";
  }
  currentCell.setNote(output);
}

Best Answer

Related Solutions

Google-sheets – How to pass a range into a custom function in Google Spreadsheets

Google-sheets – In Google Spreadsheet, finding what formulas reference a given value

Related Topic