3

In Excel we can trace precedents and dependents. Can some one help me with something similar for Google Sheets?

I have got the code for dependents from https://webapps.stackexchange.com/a/50149/88163.

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);
}
  • Also note this works for formulae in one sheet - can we make it work for other sheets in same file – Shirshendu Bhattacharya May 11 '15 at 6:05
  • 1
    Question title is about precedents, but all discussion is about dependents. What gives. No solution for precedents? – joe Apr 16 '16 at 13:15
2

Finally I have it working for different sheets in the same file:

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

function traceDependents(){
  var dependentRefs = [];
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var currentCell = ss.getActiveCell();
  var currentCellRef = currentCell.getA1Notation();;
  Logger.log(currentCellRef);
  var sheets = ss.getSheets();
  var actSheet = SpreadsheetApp.getActiveSheet();
  var actIndex = actSheet.getIndex();
  for (var i = 0; i < sheets.length; i++){
    var range = sheets[i].getDataRange();
    var currentSheetRef;
    if (i == actIndex - 1){
      currentSheetRef='';
    } else {
      currentSheetRef = 'Sheet' + actIndex + '!';
    }
    Logger.log(currentSheetRef + currentCellRef);
    var regex = new RegExp("\\b" + currentSheetRef + currentCellRef + "\\b")
    var formulas = range.getFormulas();
    var dependents = [];
    Logger.log("Range");
    Logger.log(range.getA1Notation());
    Logger.log(formulas);
    for (var j = 0; j < formulas.length; j++){
      var row = formulas[j];

      for (var k = 0; k < row.length; k++){
        var cellFormula = row[k];
        if (regex.test(cellFormula)){
          dependents.push([j,k]);
        }
      }
    }
    Logger.log("Dependents");
    Logger.log(dependents);
    for (var l = 0; l < dependents.length; l++){
      var rowNum = dependents[l][0] + 1;
      var colNum = dependents[l][1] + 1;
      Logger.log(rowNum+' '+colNum);
      Logger.log(range.getA1Notation());
      var cell = range.getCell(rowNum, colNum);
      var cellRef = cell.getA1Notation();
      var sheetRef = 'Sheet' + (i+1) + '!';
      dependentRefs.push(sheetRef + cellRef);
    }
  }

  var output = "Dependents: ";
  if(dependentRefs.length > 0){
    output += dependentRefs.join(", ");
  } else {
    output += " None";
  }
  currentCell.setNote(output);
}
2

Worked with the above (works with all sheets), but made the following changes:

  1. Can handle absolute references
  2. Now prints to a draggable prompt so it doesn't write over notes
  3. Handles variability in sheet names (besides "Sheet1" Sheet2" etc)

--

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

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

  var cellRightLength = currentCell.getRow().toString().length
  var cellLength = currentCell.getA1Notation().length
  var columnText = currentCell.getA1Notation().substring(0,cellLength-cellRightLength)
  var rowText = currentCell.getRow().toString()


  Logger.log(currentCellRef);
  var sheets = ss.getSheets();
  var actSheet = SpreadsheetApp.getActiveSheet();
  var actSheetGetName = SpreadsheetApp.getActiveSheet().getName();
  var actIndex = actSheet.getIndex();

  //Need to search and see if there is a space in the sheet name.  If there   is, in other cells in other sheets it will be surronded by: ' 
  //Otherwise, it won't have   '   characters around it

  spaceRegex = new RegExp(" ")
  if (spaceRegex.test(actSheetGetName) == true) {
     actSheetName = "'" + actSheetGetName + "'" + "!"
  } else {
     actSheetName = actSheetGetName + "!"
  }

  for (var i = 0; i < sheets.length; i++){
    var range = sheets[i].getDataRange();
    var currentSheetRef;
    if (i == actIndex - 1){
      currentSheetRef='';
    } else {
      currentSheetRef = actSheetName
    }
    Logger.log(currentSheetRef + currentCellRef);

    var regex1 = new RegExp(currentSheetRef + currentCellRef)
    var regex2 = new RegExp(currentSheetRef + "\\$" + currentCellRef)
    var regex3 = new RegExp(currentSheetRef + columnText + "\\$" + rowText)
    var regex4 = new RegExp(currentSheetRef + "\\$" + columnText + "\\$" + rowText)

    var formulas = range.getFormulas();
    var dependents = [];
    Logger.log("Range");
    Logger.log(range.getA1Notation());
    Logger.log(formulas);
    for (var j = 0; j < formulas.length; j++){
      var row = formulas[j];

      for (var k = 0; k < row.length; k++){
        var cellFormula = row[k];
        if ((regex1.test(cellFormula)) || (regex2.test(cellFormula)) || (regex3.test(cellFormula)) || (regex4.test(cellFormula))) {
          dependents.push([j,k]);
        }
      }
    }
    Logger.log("Dependents");
    Logger.log(dependents);
    for (var l = 0; l < dependents.length; l++){
      var rowNum = dependents[l][0] + 1;
      var colNum = dependents[l][1] + 1;
      Logger.log(rowNum+' '+colNum);
      Logger.log(range.getA1Notation());
      var cell = range.getCell(rowNum, colNum);
      var cellRef = cell.getA1Notation();
      var sheetRef = sheets[i].getName()
      dependentRefs.push(sheetRef + "!" + cellRef);
    }
  }

  var output = "";
  if(dependentRefs.length > 0){
    output += dependentRefs.join("\n");
  } else {
    output += " None";
  }

  ui = SpreadsheetApp.getUi()   
  ui.alert('Dependents:',output,ui.ButtonSet.OK);

}
0

There is an add-on for this now, you can install "Find Precedents and Dependents" from the add-ons menu in Google Sheets.

find precedents and dependents google sheets add-on

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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