Add a timestamp when a row changes

1 · Prepare

Put these pieces in place

• Open the spreadsheet you want to stamp and create a tab named Tasks.
• Put a task name in column A, a status in column B, and leave column E free for the timestamp.
• Keep your headers in row 1. The script ignores edits in that row.

2 · Review before running

What the script can access and change

3 · Copy

Paste this into the Apps Script editor

Create or open a script file, replace the example values marked above, then save the project.

const SHEET_NAME = 'Tasks'; // The script reacts only to edits made on the tab with this name.
const WATCHED_COLUMN = 2; // Column B is column number 2. Change it to the column you want to watch.
const TIMESTAMP_COLUMN = 5; // Column E is column number 5. The date and time goes here, in the row you edited.
const DATE_FORMAT = 'yyyy-MM-dd HH:mm'; // This is how the stamp is written, for example 2024-03-08 14:05.

/** @param {Events.SheetsOnEdit} e */
function onEdit(e) {
  const range = e.range;
  const sheet = range.getSheet();
  if (sheet.getName() !== SHEET_NAME) return;
  if (range.getNumRows() > 1 || range.getNumColumns() > 1) return;
  if (range.getColumn() !== WATCHED_COLUMN) return;

  const editedRow = range.getRow();
  if (editedRow < 2) return;

  const stamp = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), DATE_FORMAT);
  sheet.getRange(editedRow, TIMESTAMP_COLUMN).setValue(stamp);
}

4 · Run

Follow these steps in order

1. Make one small test row

   On the Tasks tab, put headers in row 1 and one test task in row 2. Leave column E empty so you can watch it fill in.

2. Paste the function and save

   Choose Extensions, then Apps Script. Replace the starter function with this one and save. A function named onEdit is a simple trigger, so there is nothing else to set up and no need to click Run.

3. Test it by hand

   Go back to the spreadsheet and type a word in cell B2 yourself. Within a second or two, cell E2 shows the current date and time.

4. Point it at your own columns

   Change the tab name, the watched column number, and the timestamp column number to match your sheet. Save, then edit one cell again to confirm the stamp lands in the right place.

5 · Confirm

How to tell it worked

A second or two after you change a cell in column B, column E of that same row shows a stamp such as 2024-03-08 14:05.

If the result is different

Troubleshooting

Nothing appears in column E after I change a cell.

Check that the tab name matches Tasks exactly, including capital letters and spaces, and that you edited column B in row 2 or below. A simple trigger runs only for edits a person makes in the browser, so a value written by another script or by an import does not start it.

Pasting a block of cells adds no timestamps.

Expect this. The getNumRows and getNumColumns check skips any edit that covers more than one cell. Remove that line only if you also write a stamp for every row in the block.

The Execution log shows "You do not have permission to call GmailApp.sendEmail".

Move the email code out of onEdit. A simple trigger cannot use services that need approval, such as Gmail or UrlFetchApp. Put that work in a function with another name and run it yourself, or install it as an On edit trigger from Triggers in the left sidebar.

The stamp shows as a plain number such as 45359.

Select column E, then choose Format, Number, and then Date time or Plain text. The cell was formatted as a number.