Using Macros Copy and Paste At Last Row

user_17454408 0
Viewed 97

I need to copy and paste rows data only in a spreadsheet using Google Macros. But I tring to recording Macro to keep my functions work. And it gives the result just paste a values to the next function(+1) . So However, I wish that paste a new row data at bottom row before would create a new row. And the way is First create a new row at the bottom row Second paste insert any row data that I selected.

function _3333() {
  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);
};

enter image description here

google-apps-script google-sheets
1 Answers

In your situation, how about the following modification?

From:

sheet.getRange(spreadsheet.getCurrentCell().getRow() - 1, 1, 1, sheet.getMaxColumns()).copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);

To:

sheet.getRange(spreadsheet.getCurrentCell().getRow() - 1, 1, 1, sheet.getMaxColumns()).copyTo(sheet.getRange(sheet.getLastRow() + 1, 1), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);

  • In this modification, your getCurrentCell() is copied to the next row of the last row.

  • If you want to copy getCurrentCell() to the last row, please modify it to as follows.

      sheet.getRange(spreadsheet.getCurrentCell().getRow() - 1, 1, 1, sheet.getMaxColumns()).copyTo(sheet.getRange(sheet.getLastRow(), 1), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);

Added:

From your following replying,

I wish that paste a new row data at bottom row before would create a new row. And the way is First create a new row at the bottom row Second paste insert any row data that I selected.

  1. Insert a new row to the bottom row.
  2. Copy and paste the selected row to the inserted new row.
  3. Those are the same active sheet.

In this case, how about the following sample script?

Sample script:

function myFunction() {
  var sheet =  SpreadsheetApp.getActiveSheet();
  var lastRow = sheet.getLastRow();
  sheet.insertRowAfter(lastRow);
  var selectedRow = sheet.getRange(sheet.getActiveRange().getRow(), 1, 1, sheet.getLastColumn());
  selectedRow.copyTo(sheet.getRange(lastRow + 1, 1), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
}
user_71086530
Related