Automatically Timestamp Data Entries with Google Apps Script

by

📊 Automatically Timestamp Data Entries with Google Apps Script

When working with Google Sheets, it’s often helpful to automatically add timestamps whenever data is entered or updated. Whether you’re tracking form submissions, logging status updates, or managing tasks, having an automatic “last modified” or “created on” column can boost clarity and accountability.

In this blog post, you’ll learn how to set up a simple Google Apps Script that inserts a timestamp in a designated column whenever a user edits a row.

🛠️ The Problem

You want to track when data is added or changed in a specific range of your sheet, but you don’t want to manually enter the time each time someone edits a cell.

✅ The Solution

Use the onEdit() trigger to automatically record the current date and time in a chosen column.

✍️ Sample Code

function onEdit(e) {
  const sheet = e.source.getActiveSheet();
  const editedCell = e.range;

  // Optional: Only apply to a specific sheet
  if (sheet.getName() !== "Tasks") return;

  const timestampColumn = 5; // Column E, adjust as needed
  const triggerColumn = 2;   // Column B (e.g., task description)

  // Only set timestamp if something was added/edited in column B
  if (editedCell.getColumn() === triggerColumn) {
    const row = editedCell.getRow();
    const timeCell = sheet.getRange(row, timestampColumn);
    timeCell.setValue(new Date());
  }
}

🔍 How It Works

  • The onEdit trigger runs automatically every time you edit the sheet.
  • It checks if the change happened in a specific column (in this example, Column B).
  • If so, it writes the current timestamp into another column (Column E).

⚙️ Customization Tips

  • Track edits in multiple columns: Adjust the if condition.
  • Use different timestamp formats: Use .setNumberFormat("MM/dd/yyyy HH:mm:ss") on the timestamp cell.
  • Make it sheet-specific: Helps avoid conflicts if you’re using this script in a multi-sheet spreadsheet.

📌 Use Cases

  • Tracking task updates in project management tools
  • Logging when new rows are added
  • Monitoring form responses or CRM notes

💡 Pro Tip

If you want to prevent overwriting existing timestamps, add a condition like this:

if (!timeCell.getValue()) {
  timeCell.setValue(new Date());
}

This way, the timestamp is only added once—when the row is first filled in.