Sticky cells in Google Sheets

Code.gs

// This code takes values put in to column A, and applies a formula to calculate column B.
// The result of the formula, not the formula, is added to column B.

function onEdit(e) {

  var range = e.range;
  var sheet = range.getSheet();
  var row = range.getRow();
  var col = range.getColumn();
  
  // Check if the edit is in the right sheet and location.
  if (row > 1 && col == 1 && sheet.getName() == 'Sheet1') {

    var cell = sheet.getRange(row, col);
    var formulaCell = sheet.getRange(row, 2);

    // Check if the input cell is empty.
    if (cell.getValue() == "") {
      formulaCell.setValue("");
      return;
    }

    // Temporarily set the formula.
    formulaCell.setFormula('=GPT(A' + row + ')');

    // Ensure that the setFormula calculation is completed.
    SpreadsheetApp.flush();
    
    // Now set the value directly, removing the formula
    var calculatedValue = formulaCell.getValue();
    formulaCell.setValue(calculatedValue);
  }
}