google script: copy paste values only range with offset

Question

I would like to implement a script that checks the values in column "D" of sheet "Review&Trigger". If the value is yes, then it copies the content only of the cells in the same row between columns E and P. Afterwards, it pastes the content to the same range.

For example: if the value in D5 (Important, this applies to any row of column D) is "Yes", then the script should copy the range E5:P5 and paste the values only to the same range E5:P5.

I am pretty new to google script with basic knowledge of it.

I have implemented this code but it does not work:

function onEdit() {
 var s = SpreadsheetApp.getActiveSheet();
 if( s.getName() == "Review&Trigger" ) {
  var r = s.getActiveCell();
  var y = s.getActiveRange();
  if( r.getColumn() == 4 && y.getValue() == "Yes") {
  var nextCell = r.offset(0, 12);
  var range = s.getRange(r,nextCell);
  range.copyTo (range, {contentsOnly: true});
source.clear();
  }
 }
}

Any idea about how to fix the code? Many thanks in advance!

I doubt var range = s.getRange(r,nextCell); is a right way to use getRange() — Max Makhrov
Thanks Max! I guess that you are right.... Can you help me and shed some light on how I should address it correctly? — Stefano

Max Makhrov Max Makhrov · Accepted Answer · 2018-06-23T06:54:04

You have options to use getRange():

getRange(row, column). Row and column are numbers.
getRange(row, column, numRows). Numbers.
getRange(row, column, numRows, numColumns). Numbers.
getRange(a1Notation). string like "A1:C15"

After range is defined, use offset to get other range on the same sheet.

Before using onEdit script, test it with usual function. Use Logger.Log('hello world'); or Browser.msgBox('hello world'); to get the info about execution.

See more:

https://developers.google.com/apps-script/reference/spreadsheet/sheet https://developers.google.com/apps-script/reference/spreadsheet/range

google script: copy paste values only range with offset

1 Answers