Got it, let's sort out this timestamp problem! The root issue with your current code is that offset(0,8) calculates the target cell relative to the edited cell—so if you edit column 1, it jumps 8 columns over to column 9, which isn't what you intended. We need to directly target column 8 on the exact same row as the edited cell, no matter which of columns 1-7 was changed.

Here's the revised code that does exactly that: #

function onEdit(e) { 
  AddTimestampToMetricsList(e) 
} 

function AddTimestampToMetricsList(e) { 
  const rangeModified = e.range 
  const editedSheet = rangeModified.getSheet()
  const editedRow = rangeModified.getRow()
  const editedCol = rangeModified.getColumn()

  // Debug logs (feel free to remove these once it's working)
  console.log(rangeModified.getA1Notation()) 
  console.log(editedSheet.getSheetName()) 
  console.log(editedCol) 
  console.log(editedRow) 

  // Exit early if the edit doesn't meet our criteria
  if(editedCol > 7) return 
  if(editedSheet.getSheetName() !== "Metrics List") return 
  if(editedRow < 2) return 

  // Directly target column 8 (H) on the edited row
  editedSheet.getRange(editedRow, 8).setValue(new Date()) 
}

Key changes explained: #

• Instead of using offset() (which relies on relative position), we use getRange(editedRow, 8): this method lets us specify the exact row and column we want to update. editedRow pulls the row number of the cell that was changed, and 8 fixes the target column to the 8th column every time.
• I added variables for editedSheet, editedRow, and editedCol to make the code easier to read and maintain—no need to call getSheet() or getRow() multiple times.
• All your original guard clauses (the if statements that exit early) are kept intact, so the script only runs when edits happen on the "Metrics List" sheet, rows 2 and above, and columns 1-7.

Optional: Format the timestamp nicely #

If you want the timestamp to show in a human-readable format (like 2024-05-20 14:30:00), you can add a number format to the cell right after setting the value:

editedSheet.getRange(editedRow, 8)
  .setValue(new Date())
  .setNumberFormat("yyyy-MM-dd HH:mm:ss");

内容的提问来源于stack exchange，提问作者Ivan