CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
svetlinsem
svetlinsem

Reputation: 37

Copy in new row every time run app script in google sheets

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});
};

Upvotes: 0

Views: 1253

Answers (2)

Hal Hanlin
Hal Hanlin

Reputation: 11

I copy/pasted and modified just the names of my tabs, but got exactly the same result. Each time I run it, it pastes over the previous entry. I only am getting one line overwritten each time I run it. Most likely I broke something.

function progressiveCopy2() {
  var spreadsheet = SpreadsheetApp.getActive();
  var originSheet = spreadsheet.getSheetByName("Digest");
  var destSheet = spreadsheet.getSheetByName("Jira CSV Generator");
  var rowContents = originSheet.getRange('A2:I2').getValues().flat();
  destSheet.appendRow(rowContents);
};

Upvotes: 1

ziganotschka
ziganotschka

Reputation: 26796

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:

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

Upvotes: 1

Related Questions