Custom app script goal seek function (not using Google Goal Seek)

Question

I'd like to create my own goal-seek-like functionality in App Scripts for a Google Sheet. I want to be able to pass the function the target cell and target value and have the function iterate over different values until that target is hit.

Unfortunately, the setValue function does not appear to be available, even if I am trying to set the active cell's value. This means I can't set the current cell value and test to see if the target cell has hit the target.

This is the functional I attempted to write:

function computePO(MonthsOnHandCell,targetValue) {  
  rangeInv = SpreadsheetApp.getActive().getActiveCell()
  rangeMOH = SpreadsheetApp.getActive().getRange(MonthsOnHandCell);

  for (var counter = 0; counter <= 50000; counter = counter + 1) {
    rangeInv.setValue(counter)

    if (rangeMOH.getValue() >= targetValue) {
      break;
    }
  }
  
  return rangeInv.getValue();
}

But I get the permission error. Does anyone have any idea on how to accomplish this? I can't use goal seek as this function needs to be run on hundreds of cells when we make other updates automatically.

Answer 1

Your question refers to a custom function that is called from a spreadsheet formula. Custom functions run in a restricted context and cannot modify cells directly — they can only return a single value, or an array of values to fill several cells.

You need to call the function through some other means, such as a button, a custom menu item, a sidebar or a trigger.

As noted in the comments, the performance of the function will be terrible. It may not even give the result you want unless you add a call to SpreadsheetApp.flush().