I'm writing a script for my Google Sheet and tried to do something like this:

function test() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Cabins");
  const range = sheet.getRange(4, 27);
  const value = range.getValue();
  Logger.log(value);
}

which gives me a result of "#ERROR!" even though the spreadsheet shows a value of "0" in that cell (R4C27).

Puzzled, I started playing around and copied the exact same formula into a different cell (R1C13) then did this:

function test() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Cabins");
  const range1 = sheet.getRange(4, 27);
  const range2 = sheet.getRange(1, 13);
  const value1 = range1.getValue();
  const value2 = range2.getValue();
  Logger.log(`value1: ${value1}`);
  Logger.log(`value2: ${value2}`);
}

value1 still gives "#ERROR!" but value2 gives the correct response of "0".

Here are the two cells and their formula. First is the problematic R4C27:

And the working R1C13:

What could possibly cause the first one to return a range Value of "#ERROR!"?

UPDATE 1: I think I'm getting closer to figuring out the cause, but not the solution. So, my COST custom function will calculate a value, but the later calls to COST lower in the spreadsheet may actually refer to a cell which itself needs to call COST, so it's a recursive function. In this image, the errored cell actually refers to the highlighted cell which also uses COST.

Since all formulas are executed simultaneously when the sheet is loaded, the ones that are recursive will point to cells whose formulae haven't finished yet.

I'm not sure what the solution is but I'm leaning towards converting the cell-based custom formula into a menu item which I can trigger on demand, which will call all the formula in the correct order and ensure that later calls have access to data.