W arkuszu kalkulacyjnym Google znajdowanie formuł odwołuje się do danej wartości

Chciałbym dowiedzieć się, które komórki mają zależności formuł w dużym arkuszu kalkulacyjnym. Szukam sposobu na zrobienie czegoś takiego jak OpenOffice

Narzędzia> Detektyw> Zależne od śledzenia

i

Edycja> Znajdź i zamień> Szukaj w formułach

lub sposób utworzenia wyzwalacza w GAS, który jest wywoływany, gdy odwołuje się do danej wartości komórki i może identyfikować źródło odniesienia.

Poniższy kod doda menu do arkusza kalkulacyjnego:

Detektyw> Zależności śledzenia

Zaznaczenie tego spowoduje dodanie notatki do aktywnej komórki ze wszystkimi zależnymi odniesieniami do komórki.

(dodano wyszukiwanie statycznych odniesień, jak sugeruje Graham poniżej)

Możesz dodać podobną funkcję do funkcji traceDependents, aby wyszukać tekst w aktywnej komórce dla funkcji Wyszukaj w formułach. Zostawię to dla ciebie jako ćwiczenie.

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);
}

To jest super i pozwoliło mi zaoszczędzić dużo pracy - dzięki.
Jednak w powyższej odpowiedzi nie znaleziono żadnych odniesień korzystających z narzędzia do naprawy wierszy lub kolumn $.
Następująca niewielka zmiana w kodzie powoduje, że:

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);
}