Google Sheets: Adding date stamps to specific rows when editing

Other Questions Google Sheets
1

I’m working on a Google Sheets project and need some help. I want to add date stamps to certain rows when they’re edited, but not to others. My current script works fine, but it adds dates to all rows including headers.

Here’s what I’m trying to do:

  • Add date stamps only to rows 9-13, 15-18, and 20-26
  • Keep header rows (and others) without date stamps
  • Update the date in column C when any cell in that row is edited

My current script looks like this:

function updateDateOnEdit() {
  let sheet = SpreadsheetApp.getActiveSheet();
  let editedCell = sheet.getActiveCell();
  let editedRow = editedCell.getRow();

  if (editedCell.getColumn() !== 3) {
    let today = new Date();
    let formattedDate = Utilities.formatDate(today, 'GMT-07:00', 'MM/dd/yy');
    sheet.getRange('C' + editedRow).setValue(formattedDate);
  }
}

How can I modify this to work only on the specific rows I mentioned? Any help would be great!

2

hey there! i’ve dealt with similar stuff before. you could try something like this:

function isInRange(row) {
return (row >= 9 && row <= 13) || (row >= 15 && row <= 18) || (row >= 20 && row <= 26);
}

then use it in ur main function:

if (isInRange(editedRow) && editedCell.getColumn() != 3) {
// add date stamp
}

that should do the trick for ya!

3

Having worked extensively with Google Sheets, I can offer some insights on your date stamping issue. Your approach is on the right track, but you’ll need to modify the script to target specific row ranges.

I’d suggest creating an array of row ranges you want to date stamp, then use a function to check if the edited row falls within these ranges. Something like this:

const targetRanges = [[9, 13], [15, 18], [20, 26]];

function isInTargetRange(row) {
  return targetRanges.some(range => row >= range[0] && row <= range[1]);
}

Then in your main function, you can use this check before applying the date stamp:

if (isInTargetRange(editedRow) && editedCell.getColumn() !== 3) {
  // Apply date stamp
}

This should solve your problem while keeping the code clean and easy to maintain. You can easily add or remove ranges as needed in the future.

4

I’ve encountered a similar issue in my Google Sheets projects. Here’s a solution that should work for your specific row requirements:

Modify your script to include a function that checks if the edited row is within your specified ranges:

function isTargetRow(row) {
  return (row >= 9 && row <= 13) || (row >= 15 && row <= 18) || (row >= 20 && row <= 26);
}

Then, in your main function, use this check before applying the date stamp:

function updateDateOnEdit(e) {
  let sheet = e.source.getActiveSheet();
  let editedCell = e.range;
  let editedRow = editedCell.getRow();

  if (isTargetRow(editedRow) && editedCell.getColumn() !== 3) {
    let today = new Date();
    let formattedDate = Utilities.formatDate(today, Session.getScriptTimeZone(), 'MM/dd/yy');
    sheet.getRange(editedRow, 3).setValue(formattedDate);
  }
}

This approach ensures date stamps are only added to your specified rows, solving your initial problem efficiently.