sensitivityTable.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;
}

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