Update timestamp in one column of Google Sheets doc when another column is changed

google-sheets-update-timestamp.gs

function onEdit(event)
{
  Logger.log("onEdit() triggered with: %s", event);
  
  //
  // User params, edit to suit your needs
  //
  var timezone = "PST"; // You can use something like "GMT-7" as well.
  var timestampFormat = "yyyy-MM-dd HH:mm:ss";
  var headerRowNum = 1; // The row containing data headers.
  var triggerColName = "Amount"; // This column name has to be in the header row. Changes to that column trigger the script.
  var updatedColName = "Date"; // This column will be updated with timestamp.
  var sheetName = "Payments"; // Name of the sheet where to run the script.
  
  // spreadsheet
  var spreadsheet = event.source;
  var sheet = spreadsheet.getSheetByName(sheetName);
  
  // range acted upon
  var range = event.source.getActiveRange();
  var col0 = range.getColumn();
  var colL = range.getNumColumns();
  var row0 = range.getRow();
  var rowL = range.getNumRows();
  Logger.log("Edited range was: rows[%s-%s], cols[%s-%s]", row0, row0+rowL-1, col0, col0+colL-1);
  
  // header info
  var headers = sheet.getRange(headerRowNum, 1, 1, sheet.getLastColumn()).getValues();
  Logger.log("Headers: %s", headers);
  var updatedColNum = headers[0].indexOf(updatedColName) + 1; // + 1 because row/col indexing is 1 based
  var triggerColNum = headers[0].indexOf(triggerColName) + 1; // + 1 because row/col indexing is 1 based
  Logger.log("updatedColNum[%s], triggerColNum[%s], activated columns [%s-%s]", updatedColNum, triggerColNum, col0, col0+colL-1);
  
  // the actual update
  if (triggerColNum >= col0 && triggerColNum < col0 + colL) { // if our trigger column is within the modified cellTarget range
    // only operate below the Header Row
    if (row0 <= headerRowNum) {
      var upToRow = row0 + rowL;
      row0 = headerRowNum + 1;
      rowL = upToRow - row0;
    }
    // update each row
    for (var i=row0; i < row0 + rowL; i++) {
      var cellOrigin = sheet.getRange(i, triggerColNum, 1);
      var cellTarget = sheet.getRange(i, updatedColNum, 1);
      if (cellOrigin.getValue() == "") {
        Logger.log("Origin was empty");
        cellTarget.clearContent();
      } else {
        Logger.log("Origin was NOT empty");
        var date = Utilities.formatDate(new Date(), timezone, timestampFormat);
        cellTarget.setValue(date);
      }
    }
  }
}