Auto date insertion in Google Sheets when cells are modified

Other Questions Google Sheets
1

Need help with automatic date script

I want to create a script that automatically adds the current date to column A whenever any cell in the same row gets edited. This should only work for columns D through Y and rows 4 through 28.

I found some code online but I can’t figure out how to set the specific column and row ranges properly.

function onEdit(event){
 const worksheet = event.source.getActiveSheet();
 worksheet.getRange('A' + event.range.rowStart)
 .setValue(new Date())
 .setNumberFormat('dd/MM HH:MM');
}

Can someone explain how to modify this to work only within my specified range? Thanks!

2

Your script runs on every cell edit, but you only want it for a specific range. Add conditions to check if the edited cell is in rows 4-28 and columns D-Y. Here’s the fix:

function onEdit(event){
 const worksheet = event.source.getActiveSheet();
 const row = event.range.getRow();
 const col = event.range.getColumn();
 
 // Check if edit is in rows 4-28 and columns D-Y (4-25)
 if (row >= 4 && row <= 28 && col >= 4 && col <= 25) {
   worksheet.getRange('A' + row)
   .setValue(new Date())
   .setNumberFormat('dd/MM HH:MM');
 }
}

I added row and column checks using getRow() and getColumn(). Column D = 4, column Y = 25 in Google Sheets numbering. Now your timestamp only updates when you edit within that range.

3

Just tested this on my sheet - works perfectly! Quick tip: if you’re working with large datasets, the script can get sluggish. Add event.range.getNumRows() === 1 && event.range.getNumColumns() === 1 so it only triggers on single cell edits, not when pasting huge ranges. Saved me a ton of lag during data imports.

4

Pete_Magic’s solution is correct, but I learned something the hard way when I tried this. Set your script as an installable trigger, not a simple trigger - otherwise it won’t work consistently. Also, add a quick check to stop the script if someone edits column A itself. Without it, you’ll get an infinite loop. Just put if (col === 1) return; at the start of your conditional block. I’ve been running something similar for project updates and ran into timezone issues with new Date(). If that happens to you, try Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'dd/MM HH:mm') instead of setNumberFormat. Gives you way more control and keeps everything consistent across different user timezones.