Copy a row and add to the bottom of a list

Question

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.

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) {
    sheet.insertRowBefore(endOfList);
    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.