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.


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? – Justin Guerra Apr 30 '16 at 0:37
  • Another question should be posted as another question. – user79865 Apr 30 '16 at 0:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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