How to Add a Last Modified Date to a Google Sheets Cell

When multiple people are collaborating on a large Google Sheets inventory tracker or project management dashboard, it is crucial to know exactly when a specific row of data was last updated. While Google Sheets has a comprehensive “Version History” feature for the entire document, digging through it to see when one specific cell changed is incredibly tedious. The most efficient solution is to create a dedicated “Last Modified” column that automatically stamps the current date and time whenever data in that row is edited.

Why You Need Google Apps Script

Standard formulas like =NOW() or =TODAY() are volatile; they constantly recalculate every time the sheet is opened or refreshed. If you use them, every row will simply show today’s date, defeating the purpose of a timestamp. To freeze the date at the exact moment of the edit, you must use a short Google Apps Script.

Step 1: Access the Script Editor

1. Open your Google Sheets document.
2. Click on the Extensions menu in the top toolbar.
3. Select Apps Script. A new browser tab will open displaying a blank coding environment.

Step 2: Add the Timestamp Script

1. Delete any default code (like function myFunction()) in the editor window.
2. Copy and paste the following script exactly as written:

function onEdit(e) {
  // Define which sheet to monitor
  var sheetName = "Sheet1"; 
  // Define which column you are editing (e.g., Column A is 1, Column B is 2)
  var columnToMonitor = 2; 
  // Define which column gets the timestamp (e.g., Column C is 3)
  var timestampColumn = 3; 

  var range = e.range;
  var sheet = range.getSheet();
  
  if (sheet.getName() === sheetName && range.getColumn() === columnToMonitor) {
    var timestampCell = sheet.getRange(range.getRow(), timestampColumn);
    timestampCell.setValue(new Date());
  }
}

Step 3: Customize the Variables

Before saving, you must customize the first three variables in the script to match your specific spreadsheet layout.

• sheetName: Change `”Sheet1″` to the actual name of your tab (located at the bottom of your screen).
• columnToMonitor: Change `2` to the numerical value of the column you want the script to watch for edits. (A=1, B=2, C=3, etc.)
• timestampColumn: Change `3` to the numerical value of your dedicated “Last Modified” column where the date should appear.

Click the Save icon (a floppy disk) at the top of the Apps Script editor. You can now close the Apps Script tab.

Step 4: Test the Script

Return to your Google Sheet. Type a new value into the column you designated as the ‘Monitor’ column and press Enter. A static timestamp showing the exact date and time will instantly appear in the ‘Timestamp’ column of that same row. Because it is written by a script, this date will not recalculate tomorrow; it will remain permanently frozen until the row is edited again.