Google Sheets - App Script Code to Extend BD functions (Sensitivity Table) and (TraceDependents)

GoogleSheetsBDFunc.js

function run_sensitivity() {
   
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  
  // set up
  var xVar = sheet.getRangeByName("xVar").getValue();
  var xMin = sheet.getRangeByName("xMin").getValue();
  var xMax = sheet.getRangeByName("xMax").getValue();
  var xStep = sheet.getRangeByName("xStep").getValue();

  var yVar = sheet.getRangeByName("yVar").getValue();
  var yMin = sheet.getRangeByName("yMin").getValue();
  var yMax = sheet.getRangeByName("yMax").getValue();
  var yStep = sheet.getRangeByName("yStep").getValue();
  
  var outputVar = sheet.getRangeByName("outputVar").getValue();
  var tableOrigin = sheet.getRangeByName("tableOrigin"); //hold as index, not val
 
  var xCell = sheet.getRange(xVar);
  var yCell =sheet.getRange(yVar);
  var outputCell = sheet.getRange(outputVar);
  
  var old_x = xCell.getValue();
  var old_y = yCell.getValue();
  
  // fix infinite loop scenario 
  yStep = (yStep == 0 ? 1 : yStep);
  xStep = (xStep == 0 ? 1 : xStep);

  // wipe table
  sheet.getRangeByName("sensitivityTableRange").clear({contentsOnly: true});
  
  //format
  sheet.getRangeByName("sensitivityValuesRange").setNumberFormat( outputCell.getNumberFormat() );
  
  // build walls
  for (var cols=0, x=xMin; x <= xMax; cols+=1, x+=xStep){
    tableOrigin.offset(0,cols+1).setValue( x );
  }
  for (var rows=0, y=yMin; y <= yMax; rows+=1, y+=yStep){
    tableOrigin.offset(rows+1,0).setValue( y );
  }
  
  // build sensitivities
  var output;
  for (var cols=1, x=xMin; x <= xMax; cols+=1, x+=xStep) {
    for (var rows=1, y=yMin; y <= yMax; rows+=1, y+=yStep) {
     
      xCell.setValue( x );
      yCell.setValue( y );
      SpreadsheetApp.flush();  
      output = outputCell.getValue();
      tableOrigin.offset(rows,cols).setValue( output );
      
    }
  } 
  
  // Wind down
  xCell.setValue( old_x );
  yCell.setValue( old_y );
  SpreadsheetApp.flush();
  
  return;
}

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

}


function onOpen() {
  var ui = SpreadsheetApp.getUi();
  // Or DocumentApp or FormApp.
  ui.createMenu('Sensitivity Analysis')
      .addItem('Run sensitivity', 'run_sensitivity')
      .addToUi();
}