Google Sheets - How can I copy 11 columns of data after enabling a checkbox on column A and sending that data to another sheet?

Question

Hello and thank you for any help in advance.

I have data on a sheet titled "AutoFlip" that is in the range B3:K53. Upon activating a checkbox in column A (so that I can operate this on the iOS version of Google Sheets) for a given row I want to copy the values of cells B:K of that row and paste them into another sheet titled "ActiveFlips" starting in cell A2, then each additional enabled checkbox would paste that row of data onto the next empty row of sheet "ActiveFlips" (cell A3 in this case).

Here's what I have right now:

function onEdit(e) {
  //IF the cell that was edited was in column 1 and therefore a checkbox AND if the cell edited was checked (not unchecked):
  if (e.range.columnStart === 1 && e.range.getValue() === true) {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = SpreadsheetApp.getActiveSheet();
    var srcsheet = ss.getSheetByName("AutoFlip");
    var dstsheet = ss.getSheetByName("ActiveFlips");
    var row = sheet.getActiveCell().getRow();
    var rangeToCopy = sheet.getRange(row,1,1,11);
    var dstrow = dstsheet.getRange(dstSheet.getLastRow()+1,1);
    dstsheet.insertRowAfter(dstrow);
    rangeToCopy.copyTo(dstsheet.getRange(dstrow + 1, 1));
    //Reset checked boxes
    srcsheet.getRange(row,1,2,1).setValue(false);
  }
}

Sources: I've been using the following questions as a source but can't quite get it right.

Google Sheets - How to run a script from iOS app?

copy and paste to next available row in a certain column google sheets

Copying and Paste multiple rows into next empty Row on another sheet

What is the behavior of your script right now and what is wrong with it? When you say "its not quite right" what do you mean exactly? — iansedano

Cooper Cooper · Accepted Answer · 2021-06-08T06:17:58

function onEdit(e) {
  //e.source.toast('entry');
  const sh = e.range.getSheet();
  if (sh.getName()== "AutoFlip" && e.range.columnStart == 1 && e.value == "TRUE") {
    //e.source.toast('cond');
    const tsh = e.source.getSheetByName('ActiveFlips');
    sh.getRange(e.range.rowStart,2,1,10).copyTo(tsh.getRange(tsh.getLastRow()+1,2));
    e.range.setValue("FALSE");
  }
}

Note: You cannot run this function from the script editor without supplying it an appropriate event object.

Google Sheets - How can I copy 11 columns of data after enabling a checkbox on column A and sending that data to another sheet?

Hello and thank you for any help in advance.

1 Answers