Adding automatic date stamps in spreadsheet

Other Questions Google Sheets
1

Hey everyone! I need some help with a spreadsheet issue. I want to create a script that puts the current date in column A whenever someone changes a cell in the same row. This should work for columns D through Y and rows 4 to 28.

I found a bit of code online but I’m not sure how to set the right range. Here’s what I’ve got so far:

function updateTimestamp(event) {
  const sheet = event.source.getActiveSheet();
  const row = event.range.getRow();
  const col = event.range.getColumn();
  
  if (row >= 4 && row <= 28 && col >= 4 && col <= 25) {
    sheet.getRange(row, 1).setValue(new Date()).setNumberFormat('MM/dd HH:mm');
  }
}

Can anyone help me adjust this to work for the specific range I need? Thanks in advance!

2

Your script is on the right track, Claire. To cover columns D through Y, adjust the column range in the if statement. Change ‘col >= 4 && col <= 25’ to ‘col >= 4 && col <= 25’. This ensures it captures all columns up to Y. Also, consider adding a check to prevent overwriting existing timestamps. Here’s a modified version:

function updateTimestamp(event) {
  const sheet = event.source.getActiveSheet();
  const row = event.range.getRow();
  const col = event.range.getColumn();
  
  if (row >= 4 && row <= 28 && col >= 4 && col <= 25) {
    const timestampCell = sheet.getRange(row, 1);
    if (timestampCell.getValue() === '') {
      timestampCell.setValue(new Date()).setNumberFormat('MM/dd HH:mm');
    }
  }
}

Remember to set up the onEdit trigger in the script editor to make it work automatically.

3

hey claire, ur script mostly works. but double-check the range settings so it properly covers col d thru y. also, rember to enable the trigger in the script editor.

4

I’ve been using a similar setup in my work spreadsheets for a while now. One thing I’d suggest is adding a bit more flexibility to your script. Instead of hardcoding the row and column ranges, you could define them as variables at the top of your function. This way, if you need to adjust the range later, you only need to change it in one place. Here’s how I’ve implemented it:

function updateTimestamp(event) {
  const startRow = 4;
  const endRow = 28;
  const startCol = 4; // Column D
  const endCol = 25; // Column Y

  const sheet = event.source.getActiveSheet();
  const row = event.range.getRow();
  const col = event.range.getColumn();
  
  if (row >= startRow && row <= endRow && col >= startCol && col <= endCol) {
    const timestampCell = sheet.getRange(row, 1);
    if (timestampCell.isBlank()) {
      timestampCell.setValue(new Date()).setNumberFormat('MM/dd HH:mm');
    }
  }
}

This approach has saved me a lot of time when I’ve needed to adjust the script for different sheets or projects. Just remember to set up the onEdit trigger in the script editor to make it run automatically.