samedi 22 janvier 2022

In Google Sheets, how can I copy a row based on a specific column value into a new tab, and then append checkboxes to the row?

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.

Illustration




Aucun commentaire:

Enregistrer un commentaire