In Google Sheets, how can I copy a row based on a specific column value into a new tab, and then app-CodePudding

I'm trying to write a script which would allow me to copy a row into a new tab based on a specific cell value. In this case, it is a checkbox of being set to TRUE.

I have no issue with copying the row to a new sheet based on the cell value, but now I'm unsure as to how I can use insertCheckboxes to append 5 checkboxes to the copied row in the new sheet.

This is the code I have at the moment:

function onEdit(event) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = event.source.getActiveSheet();
  var r = event.source.getActiveRange();

  if(s.getName() == "Client Database" && r.getColumn() == 9 && r.getValue() == true) {
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var targetSheet = ss.getSheetByName("Referrals");
    var target = targetSheet.getRange(targetSheet.getLastRow()   1, 1);
    s.getRange(row, 1, 1, numColumns).copyTo(target);
}
}

Column 9 contains checkboxes which when set to true, copies the row from "Client Database" into my new tab ("Referrals").

I'm scratching my head over two things:

What do I need to change in the code to only copy columns 1 and 4 from the original sheet? (as it is currently copying all columns from the row).
Unless there is an easier workaround, how can I append checkboxes into the adjacent columns within the new sheet using insertCheckboxes? (Illustration below). This is simply to avoid having to manually insert the boxes each time (as I will not be the one using the sheet)

Any advice is greatly appreciated.

CodePudding user response：

Checkboxes

Add to your script

var checkbox = SpreadsheetApp.newDataValidation().requireCheckbox().build();
targetSheet.getRange(targetRow,3,1,4).setDataValidation(checkbox).setValue("FALSE");

where targetRow is the row and targetSheet the sheet where you copy

code

function onEdit(event) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = event.source.getActiveSheet();
  var r = event.source.getActiveRange();

  if (s.getName() == "Client Database" && r.getColumn() == 9 && r.getValue() == true) {
    var row = r.getRow();
    var targetSheet = ss.getSheetByName("Referrals");
    var targetRow = targetSheet.getLastRow()   1
    targetSheet.getRange(targetRow,1).setValue(s.getRange(row,1).getValue())
    targetSheet.getRange(targetRow,2).setValue(s.getRange(row,4).getValue())
    var checkbox = SpreadsheetApp.newDataValidation().requireCheckbox().build();
    targetSheet.getRange(targetRow,3,1,4).setDataValidation(checkbox).setValue("FALSE");
  }
}