1

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

enter image description here

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.

1

1 Answer 1

0

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().

2
  • 1
    The script uses .getValue(), so it's only checking the top-left cell of the nextCell range. It might require further enhancement (or better to use another script). Sep 18, 2023 at 16:50
  • Thank you for taking a look and for your response! Understand now why it wasn't working - I suspected it was to do with the range. I'll dig deeper into the range function to understand it further too.
    – Jan
    Sep 18, 2023 at 23:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.