Web Applications Stack Exchange is a question and answer site for power users of web applications. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I would like to find out which cells have formula dependencies in a large spreadsheet. I am looking for a way to do something like OpenOffice

Tools>Detective>Trace dependents

and

Edit>Find & Replace>Search in formulas

or a way to create a trigger in GAS that gets called when a given cell value is referenced and can identify the source of the reference.

share|improve this question

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.

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];
      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);
}
share|improve this answer
    
This is terrific. I think you can remove the extra line: var output = "Dependents: "; – jaredcohe Nov 9 '13 at 22:23
    
Well caught. I have removed it. Thanks for the eagle eyes. – Tom Horwood Nov 10 '13 at 13:04
    
I like this very much! Do I see it correctly that it doesn't support Named Ranges, though? And if that is the case, would it be simple/complicated to add support? – Wizek Apr 6 '15 at 12:50

This is super and has saved me much work - thanks.
However the answer above does not find any references that use the row- or column-fixer $.
The following slight change to code accomplishes that:

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);
}
share|improve this answer
    
Thanks; this helped somewhat. How would one make it work across worksheets? – wizonesolutions Oct 20 '15 at 10:25

protected by Community Jul 4 at 20:34

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.