google-sheetsgoogle-apps-script

How can I use Google Sheets Apps Script to Auto Generate today's date when an edit is made to a given column?

I am attempting to write an apps script for a google sheet that will return today's date in Column D when an edit is made to Column C (note: ideally, column C will have a data validation dropdown). I want to be able to do the same for Column G (where today's date will auto-populate in Column H), and for Column I (where today's date will auto-populate in Column J). For Columns G and I, there will be a data validation checkbox. I've attempted to create separate app scripts for each separate column (which did not work), and I've tried combining them into one script (as it appears below), which is also not working.

function onEdit() {
  var s = SpreadsheetApp.getActiveSheet();
  if (s.getName() == "Log") { //checks that we're on Log or not
    var r = s.getActiveCell();
    if (r.getColumn() == 3) { //checks that the cell being edited is in column C
      var nextCell = r.offset(0, 1);
      if (nextCell.getValue() === '') //checks if the adjacent cell is empty or not?
      nextCell.setValue(new Date());
    }
  }
}
function onEdit() {
  var s = SpreadsheetApp.getActiveSheet();
  if (s.getName() == "Log") { //checks that we're on Log or not
    var r = s.getActiveCell();
    if (r.getColumn() == 6) { //checks that the cell being edited is in column F
      var nextCell = r.offset(0, 1);
      if (nextCell.getValue() === '') //checks if the adjacent cell is empty or not?
      nextCell.setValue(new Date());
    }
  }
}
function onEdit() {
  var s = SpreadsheetApp.getActiveSheet();
  if (s.getName() == "Log") { //checks that we're on Log or not
    var r = s.getActiveCell();
    if (r.getColumn() == 8) { //checks that the cell being edited is in column H
      var nextCell = r.offset(0, 1);
      if (nextCell.getValue() === '') //checks if the adjacent cell is empty or not?
      nextCell.setValue(new Date());
    }
  }
}

Spreadsheet structure enter image description here

Solution

  • Based on your question, I understand your expected result is as follows.

    1. When column "C" is edited, you want to put a date object into column "D".
    2. When column "G" is edited, you want to put a date object into column "H".
    3. When column "I" is edited, you want to put a date object into column "J".

    Modification points:

    When these points are reflected in your script, how about the following modification?

    Modified scripts:

    First, please remove or rename your 3 onEdit functions.

    In this modification, no event object is used. This is from your showing script.

    function onEdit() {
  var s = SpreadsheetApp.getActiveSheet();
  if (s.getName() == "Log") {
    var r = s.getActiveCell();
    if ([3, 7, 9].includes(r.getColumn())) {
      var nextCell = r.offset(0, 1);
      if (nextCell.getValue() === '')
        nextCell.setValue(new Date());
    }
  }
}

    In this modification, the event object is used. When this script is run, please edit columns "C", "G", and "I" in the "Log" sheet. By this, the script is automatically run. Also, when the event object is used, the process cost can be reduced a little. Ref (Author: me)

    function onEdit(e) {
  const { range } = e;
  const sheet = range.getSheet();
  if (sheet.getSheetName() != "Log" || range.rowStart == 1 || ![3, 7, 9].includes(range.columnStart)) return;
  const nextCell = range.offset(0, 1);
  if (nextCell.isBlank()) {
    nextCell.setValue(new Date());
  }
}