Automatically insert current date in Google Sheets when specific columns are edited

Other Questions Google Sheets
1

I’m working on a Google Sheets project and need help fine-tuning my script. I want today’s date to be inserted automatically in certain adjacent columns when an edit occurs in a specified column. For example, when a change is made in Column C, today’s date should appear in Column D. The same should happen for Column G with the date appearing in Column H, and Column I with the date appearing in Column J. The cells in Columns C, G, and I use data validation like dropdown menus or checkboxes. I tried implementing separate scripts and a combined script, but neither worked. Below is a revised version I came up with:

function updateDate(sheet, editedColumn) {
  var activeCell = sheet.getActiveCell();
  if (activeCell.getColumn() === editedColumn) {
    var dateCell = activeCell.offset(0, 1);
    if (dateCell.getValue() === '') {
      dateCell.setValue(new Date());
    }
  }
}

function onEdit(e) {
  var sheet = e.source.getActiveSheet();
  if (sheet.getName() === 'Log') {
    updateDate(sheet, 3);
    updateDate(sheet, 7);
    updateDate(sheet, 9);
  }
}

Any suggestions or improvements would be much appreciated. Thanks!

2

I’ve been using a similar setup in my work tracking sheet, and I found a neat trick to make it even more flexible. Instead of hardcoding the column numbers, you can use named ranges to define which columns should trigger the date update. This way, if you ever need to change the layout of your sheet, you won’t have to modify the script.

Here’s how I do it:

  1. Create named ranges for your trigger columns (e.g., ‘EditTriggers’)
  2. Modify the script to check if the edited cell is within these named ranges

function onEdit(e) {
var sheet = e.range.getSheet();
if (sheet.getName() !== ‘Log’) return;

var editedRange = e.range;
var triggers = sheet.getNamedRanges().filter(nr => nr.getName() === ‘EditTriggers’);

if (triggers.some(t => t.getRange().getColumn() === editedRange.getColumn())) {
var dateCell = editedRange.offset(0, 1);
if (dateCell.getValue() === ‘’) {
dateCell.setValue(new Date()).setNumberFormat(‘yyyy-MM-dd’);
}
}
}

This approach has saved me a lot of headaches when I’ve had to restructure my sheets. Give it a try!

3

hey excitedgamer85, ur script looks pretty good! i’d suggest adding a check for the row number to avoid updating header rows. maybe something like:

if (activeCell.getRow() > 1) {
// rest of ur code
}

also, consider using .setNumberFormat(‘M/d/yyyy’) on the date cell for consistent formatting. hope this helps!

4

Your script is on the right track, but there’s room for optimization. Consider using e.range instead of getActiveCell() for better performance. Also, you might want to format the date consistently across all cells. Here’s a suggestion:

function onEdit(e) {
var sheet = e.range.getSheet();
if (sheet.getName() !== ‘Log’) return;

var col = e.range.getColumn();
var row = e.range.getRow();

if ((col === 3 || col === 7 || col === 9) && row > 1) {
var dateCell = sheet.getRange(row, col + 1);
if (dateCell.getValue() === ‘’) {
dateCell.setValue(new Date()).setNumberFormat(‘yyyy-MM-dd’);
}
}
}

This approach is more efficient and handles all cases in a single function. Let me know if you need any clarification on the changes.