I try to create script to copy one specific row from one sheet to another sheet in new row. But the script allways copy replace the last one.

I use this code:

function Test() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('A9:O9').activate();
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Logs'), true);
  spreadsheet.getRange('A24').activate();
  spreadsheet.getRange('Log!A9:O9').copyTo(spreadsheet.getActiveRange(), 
  spreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Log'), true);
  spreadsheet.getRange('D9:O9').activate();
  spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});
};

CodePudding user response：

Your script always copies the data to the activated range - which is always A24

Rewrite your funciton by specifying to where you want to copy your row. If you do not want to overwrite, probably you want to append your row at the bottom of the destination sheet.

You can do it as following:

function Test() {
  var spreadsheet = SpreadsheetApp.getActive();
  var originSheet = spreadsheet.getSheetByName("Log");
  var destSheet = spreadsheet.getSheetByName("Logs");
  var rowContents = originSheet.getRange('A9:O9').getValues().flat();
  destSheet.appendRow(rowContents);
};

Alternatively, you can copy the data to the first empty row:

function Test() {
 var spreadsheet = SpreadsheetApp.getActive();
 var originSheet = spreadsheet.getSheetByName("Log");
 var destSheet = spreadsheet.getSheetByName("Logs");
 var firstEmtyRow = destSheet.getLastRow()   1;
 var destRange = destSheet.getRange('A'   firstEmtyRow   ':O'   firstEmtyRow);
 originSheet.getRange('A9:O9').copyTo(destRange, 
 spreadsheetApp.CopyPasteType.PASTE_VALUES, false);
};

References:

• getSheetByName()
• appendRow()
• flat()
• getLastRow()

As pointed out by @Mike Steelson, activating sheets and ranges is not good practice. It is performed in the background if you record a macro, but it is prone to errors, so if you write a script yourself - avoid activate().