17

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.

12

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);
}
  • 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
  • 2
    How to use the code? – Ferrybig Jan 10 '18 at 15:56
  • 1
    why does the line menuEntries.push({name: "Trace Dependents", functionName: "traceDependents"}); appear twice in the code? – ThomasMcLeod Dec 25 '18 at 0:39
4

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);
}
  • Thanks; this helped somewhat. How would one make it work across worksheets? – wizonesolutions Oct 20 '15 at 10:25
  • Thanks Graham - I've incorporated your changes. As to working across worksheets, the code would need a bit of adjusting to incorporate the sheet name and then loop through each of the sheets – Tom Horwood Jul 3 '17 at 5:24

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