onEdit function where action in specific column sets value for corresponding row in the next column over

0
votes

I'm trying to work out a google scripts onEdit function that is only triggered by one specific column, and will set the value in the same row on the next column.

The script should set the value (new Date()) in column D when a cell in column C is edited, and should be limited to rows after row 7.

Currently I have one that kinda works, but it works when you edit any cell in the sheet, so it's not ideal. I've been googling and reading for a while to no avail. Any help would be greatly appreciated.

Here's the code I currently have:

function onEdit() { //Function to add a time stamp to Complete Time Column D
  
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var range= sheet.getActiveCell();
  var row = range.getRow();
  if(row>7){
    sheet.getRange(row, 4).setValue(new Date());
    
  }
}
1
google-apps-scriptgoogle-sheets

1 Answers

2
votes

Explanation:

  • You should take advantage of the event object and use it to find the active sheet and active range.

  • The following script will set a new date in column D when a cell in column C is edited, but after row 7.

  • You are very close. In the same way you use var row = range.getRow(); you can also use var row = range.getColumn(); and put that as a constraint to be 3 (column C).

  • The script will work for the active sheet. You might want to restrict this functionality to a particular sheet (e.g. Sheet1). If yes, then change the if condition to something like this:

    if(as.getName() == 'Sheet1' && row>7 && col==3)

Solution:

function onEdit(e) { 
    
  const row = e.range.getRow();
  const col = e.range.getColumn();
  const as = e.source.getActiveSheet();
  
  if(row>7 && col==3){
    as.getRange(row, 4).setValue(new Date());
  }
}