onEdit updates correctly, but then overwrites following columns after pasting (editing) multiple rows

Question

I'm using Google Spreadsheets and found a script here:

onEdit only updates top row after pasting (editing) multiple rows

I manipulated the script to fit my needs, and I need it to only look at column 5 when it is updated and then populate the date in column 2.

However when I paste in bulk (which contains an edit in columns > 5), the script puts the date into every column from 2 onwards until it gets to the final offset of -3. This overwrites data

The script I have so far is as follows:

function addtimestampTodata(e) { 
var s = SpreadsheetApp.getActiveSheet();
if( s.getName() == "Data") { //checks that we're on the correct sheet
var r = e.range;

if(r.getColumn() == 5) { //checks the column
if(r.getValue() === '') {//don't update the timestamp if the cell is edited to a blank vaue
}
 else
 {
  var nextCell = r.offset(0, -3);
  if(nextCell.getValue() === '') // only update the timestamp if it has not already been updated
  
  {

  var dateTime = new Date();
  // Set the cell
  nextCell.setValue(dateTime);}
   }
  }
 }
}

Is there a way to correct this? I think it's the ranges I'm setting out but can't work out why I set col 5 as the first point of reference, but the script keeps going when other columns are updated too.

Answer 1

The Range.offset() function returns a range that is the same size as the original range, rather than a single cell or column, unless you specify optional parameters. You can specify those optional parameters like this:

  let nextCell = r.offset(0, -3, r.getHeight(), 1);

Note that your code only works correctly when the trigger column E is the first column in the range where you paste data.

See Range.offset().