google-sheetsgoogle-apps-scripttimestamp

How to make an auto-timestamped row in Google Sheets


I found a guide for a timestamp column that works great. But I'd like it as a header row, and can't get it to work. I'm attempting to get a horizontal auto-timestamp header.

Here is my code:

function onEdit(e){
  const sh = e.source.getActiveSheet();
  sh.getRange ('M' + e.range.rowStart)
    .setValue (new Date())
    .setNumberFormat ('MM/dd/yyyy HH:MMam/pm');
}

And here is my edited version:

function onEdit(e) {
  const sh = e.source.getActiveSheet();
  sh.getRange ('1' + e.range.columnStart)
    .setValue (new Date())
    .setNumberFormat ('MM/dd/yyyy');
}

The goal is to populate row 1 when a cell in the same column is edited.


Solution

  • Use getRange(row,column) Instead

    Instead of using A1Notation, why don't you use getRange().

    I made some minor adjustments to your code:

    From:

    sh.getRange ('1' + e.range.columnStart)
    

    To:

    sh.getRange (1 , e.range.getColumn())
    

    I opted for the getColumn() function instead of columnStart(). This ensures that the header will automatically update with the current timestamp whenever you make edits below.

    Here's the full code:

    function onEdit(e){
    const sh = e.source.getActiveSheet();
    sh.getRange (1 , e.range.getColumn()) // I modified this line
    .setValue (new Date())
    .setNumberFormat ('MM/dd/yyyy');
    }
    

    Sample Output:

    Output

    Note: I assumed that your Spreadsheet looked like this.

    References: