Macro / Google Sheets - How to duplicate/copy a row to the end of a list within the same sheet?-CodePudding

Hey,

I am trying to create a macro that will duplicate/copy a selected row to the end of a list (which is NOT the last row) within the same sheet. But so far, the only thing I got was to create a row underneath the selected row and duplicate it there.

Attached an screenshot as an example, I need to copy a selected row to the bottom of the list (row10 in this example) and not to the bottom of the sheet (row15 in this example).

Can anyone let me know where I'm going wrong and how I can fix it? This is driving me mad :(

This is what I came up with so far:

function Reschedule() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getActiveSheet().insertRowsAfter(spreadsheet.getActiveRange().getLastRow(), 1);
  spreadsheet.getActiveRange().offset(spreadsheet.getActiveRange().getNumRows(), 0, 1, spreadsheet.getActiveRange().getNumColumns()).activate();
  spreadsheet.getCurrentCell().activate();
  var sheet = spreadsheet.getActiveSheet();
  sheet.getRange(spreadsheet.getCurrentCell().getRow() - 1, 1, 1, sheet.getMaxColumns()).copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
};

Thank you so much in advance!

Also, any alternatives that would help me to get the same result is very welcome too!

CodePudding user response：

In your situation, how about the following sample script?

Sample script:

function Reschedule() {
  var activeRange = SpreadsheetApp.getActiveRange();
  var sheet = activeRange.getSheet();
  var row = sheet.getRange("A1").getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow()   1;
  var dst = sheet.getRange(row, 1);
  activeRange.offset(0, 0, 1, sheet.getLastColumn()).copyTo(dst, SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
  // dst.activate(); // From your showing script, if you want to activate the appended row, please use this.
}

When this script is run after a cell is selected, the row of the selected cell is copied to the next row of the last row of the sheet.

Reference:

getNextDataCell(direction)

CodePudding user response：

So looking at the insertRowsAfter function, the first argument should be set to the highest empty row.

How do we find the highest empty row? Taken from Tanaike's answer, getNextDataCell.

I think your two .activate() calls are best left out--or do you need to change which cells are focused?

Finally, to copy the data, getting the source and target ranges and using getValues and setValues is probably the simplest way.

  var sheet = SpreadsheetApp.getActiveSheet();
  
  var firstColumn = 1;
  var lastColumn = sheet.getRange("A1").getNextDataCell(SpreadsheetApp.Direction.NEXT).getColumn();
  var numColumns = lastColumn - firstColumn   1;
  
  var sourceRow = sheet.getActiveRange().getRow();
  var targetRow = sheet.getRange("A1").getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow()   1;
  sheet.insertRowsAfter(targetRow, 1);

  var sourceRange = sheet.getRange(sourceRow,firstColumn,1,numColumns);
  var targetRange = sheet.getRange(targetRow,firstColumn,1,numColumns);
  targetRange.setValues(sourceRange.getValues());

Example: https://docs.google.com/spreadsheets/d/1GxZdTBRsI20nTphqJIsEPONfMGY3E-BE1-zhgWCgnzw/edit