I'm looking for script that will allow me to copy a specific row and paste it to the bottom of a list. The bottom of the list will not be the last row on the sheet. Using VBA I would have used an offset that triggers off a hidden column to know where the list ends.

In other words... I want to copy row 9 and insert it into row 10 pushing the total to town 11. If I run the script again it should copy row 9 and insert it into row 11 pushing the totals to row 12 and so on.

1 Answer 1


You need some marker of where the list ends, if the end is not the last row with the data. One approach, as you indicated, is to use a hidden row. For example, enter "end of list" in column A of a row, and hide that row. Then use this script, which copies the current row (where the cursor points) to the bottom of the list.

function copyToBottom() { 
  var sheet = SpreadsheetApp.getActiveSheet();
  var colA = sheet.getRange('A:A').getValues().map(function (row) {
    return row[0];
  var endOfList = colA.indexOf('end of list') + 1;
  if (endOfList > -1) {
    var currentRow = sheet.getActiveRange().getRow();
    var lastColumn = sheet.getLastColumn();
    sheet.getRange(currentRow, 1, 1, lastColumn).copyTo(sheet.getRange(endOfList, 1, 1, lastColumn));

To run this in a convenient way, use onOpen to add a menu item.

  • Another question... how would I modify this to delete rows from a set row to the end of the list? Commented Apr 30, 2016 at 0:37
  • Another question should be posted as another question.
    – user79865
    Commented Apr 30, 2016 at 0:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.