Google Sheets: current date when paste new row

Question

Sorry for my poor English.

I need the current date to automatically appear when I insert a new row by copy-paste from another sheet or table into column C. The TODAY() function is not suitable because A fixed date is required.

I created a table as a sample. I need that when copying a row from the “Just for example-copy row from here” sheet into the “Operational Sheet”, a fixed current date appears in column C.

Table link: https://docs.google.com/spreadsheets/d/18rF449WUy4DhoSypdt5ZBoJ3K0_k-7aKZozxWcFkkkY/edit?usp=sharing

I am a complete novice in writing scripts, I can only copy and slightly modify other scripts.

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

  if(col === 1 && e.source.getActiveSheet().getName() === "Operational Sheet"){
  e.source.getActiveSheet().getRange(row,3).setValue(Utilities.formatDate(new Date(), "GMT+9:00", "dd.MM.yyyy"));
}
}

I have this script, but it only works when changing a cell manually, not when copying and pasting.

Answer 1

Modification points:

• When I saw your provided Spreadsheet, the 1st sheet name was Operational sheet. That is not Operational Sheet. So, when your showing script is used, I'm worried that col === 1 && e.source.getActiveSheet().getName() === "Operational Sheet" always returns false. Please be careful about this.

• About I have this script, but it only works when changing a cell manually, not when copying and pasting., in this case, I guessed that you might have wanted to copy and paste multiple rows.

When these points are reflected in a sample script, how about the following script?

Modified script:

Before you test this script, please confirm your sheet name again.

function onEdit(e) {
  var sheetName = "Operational sheet"; // Please confirm your sheet name again.

  var { range } = e;
  var sheet = range.getSheet();
  if (range.columnStart != 1 || sheet.getSheetName() != sheetName) return;
  sheet.getRange(range.rowStart, 3, range.rowEnd - range.rowStart + 1).setValue(Utilities.formatDate(new Date(), "GMT+9:00", "dd.MM.yyyy"));
}

• In this sample script, when multiple rows are copied to "Operational sheet" sheet from column "A", the value of Utilities.formatDate(new Date(), "GMT+9:00", "dd.MM.yyyy") is put into column "C" of the copied rows.

Note:

• If you want to put the date to columns "C" only when columns "A" and "B" have the values in the copied rows, please test the following script.

  function onEdit(e) {
    var sheetName = "Operational sheet"; // Please confirm your sheet name again.
  
    var { range } = e;
    var sheet = range.getSheet();
    if (range.columnStart != 1 || sheet.getSheetName() != sheetName) return;
    var values = range.getDisplayValues();
    var rowStart = range.rowStart;
    var ranges = values.reduce((ar, r, i) => {
      if (r.every(e => e)) {
        ar.push(`C${i + rowStart}`);
      }
      return ar;
    }, []);
    if (ranges.length == 0) return;
    sheet.getRangeList(ranges).setValue(Utilities.formatDate(new Date(), "GMT+9:00", "dd.MM.yyyy"));
  }
  

Reference:

• Event Objects