Google-sheets – Copy a row and add to the bottom of a list

google sheetsgoogle-apps-script

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.

Best Answer

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.

Related Solutions

Google Sheets – Automatically Copy and Paste Updated Cells from Forms

You need onFormSubmit, not onEdit. Open the documentation of classes Spreadsheet, Sheet, and Range, and look up each of the methods used below. To give you a general idea of what is done here:

ss = active spreadsheet, sheet = active sheet
values = an array of values in the active sheet. Note that arrays in JavaScript are 0-indexed.
row = the row added to sheet when the onFormSubmit event happened. This one is 1-based, so the adjustment row-1 is used when addressing arrays.
values[row-1][1] - the value in column B of this row. [Row number][Column Number] in 0-based indexing.

The script isn't perfect. For one thing, it assumes the target has rows available. You may want to use insertRowAfter() to be safe. Or just create a lot of rows. The logic for other things you mentioned is not here either, but it's essentially the same.

function onFormSubmit(event) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = SpreadsheetApp.getActiveSheet();
  var values = sheet.getDataRange().getValues();
  var row = event.range.getRow();
  if (values[row-1][1] == 'Luke') {   // column B in 0-based indexing 
    var targetSheet = ss.getSheetByName('for Luke');
    var lastRow = targetSheet.getLastRow();
    var targetRange = targetSheet.getRange(lastRow+1, 1, 1, values[0].length);
    targetRange.setValues([values[row-1]]);
  }   
}

Google-sheets – Script to delete a dynamic range of rows

This is how such a script can work. The variable startRow is fixed at 12 here - an alternative option is to take it from the current selection, which would be startRow = sheet.getActiveRange().getRow();

The script then searches column A for "end of list" text, and then deletes the required number rows.

function deleteRows() { 
  var startRow = 12;
  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.deleteRows(startRow, endOfList - startRow);
  }
}

Note that this is really deletion, not merely clearing out the rows. To clear the content but leave the rows, replace the line with sheet.deleteRows by

    sheet.getRange(startRow, 1, endOfList - startRow, sheet.getLastColumn()).clear();

Best Answer

Related Solutions

Google Sheets – Automatically Copy and Paste Updated Cells from Forms

Google-sheets – Script to delete a dynamic range of rows

Related Topic