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, because the script ignores that row on purpose.

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 safely

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

   Open Extensions, choose 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 press 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 happens when 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.

That is on purpose. The getNumRows and getNumColumns check skips any edit that covers more than one cell. Remove those two lines 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".

Simple triggers cannot use services that need approval, such as Gmail or UrlFetchApp. Move that work into a normal function you run yourself, or create an installable trigger from the Triggers panel in the left sidebar.

The stamp shows as a plain number such as 45359.

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