CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Borg
Borg

Reputation: 29

Sheets Scripts- Copy cells from same sheet when check box is "True"

Objective:

Background: I currently have a script copyTo function working when I click on a custom menu item I have created. I have tried to create an onEdit code to do the same thing when a check box in A61 is checked (Marked true). It executes correctly without any errors but it does not copy anything to the target cells. I have tried other onEdit codes which have copied the respective cells to the target cells but when I try to edit the target cells, the onEdit function kicks in and overwrites my correction. This is the reason for the onEdit to only be triggered by the check box.

Below is my script. I have kept within comment characters (/* */) the function that I was using to trigger the copying of cells from the custom menu that does work. Thank you

// https://stackoverflow.com/questions/68229155/google-sheets-apps-script-use-checkbox-to-copy-cell-content-to-another-cell

function onEdit() { // Copies cells from bottom of sheet to individual selected cells
  var ss3 = SpreadsheetApp.getActiveSpreadsheet();
  var s3 = ss3.getSheetByName("CQF");
  var selection = s3.getActiveCell().getA1Notation().split(""); //Gets the Location where user cliks on the sheet
  var checkBoxValue = s3.getActiveCell().getValue().toString(); //Gets the checkbox value
     if(selection[0] != "A61") return; //Makes sure to run only when user selects checkbox on A61

  switch(checkBoxValue){
    case checkBoxValue = "true":
      

/*
//Triggered when "Insert CQF Data" is clicked in custom menu
function CopyCellsCQF() { // Copies cells from bottom of sheet to individual selected cells
  var ss3 = SpreadsheetApp.getActiveSpreadsheet();
  var s3 = ss3.getSheetByName("CQF"); 
*/
    
    s3.getRange('CQF!D61').copyTo(s3.getRange('CQF!H11:K11'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);  //Amount
    s3.getRange('CQF!G61').copyTo(s3.getRange('CQF!C6:J6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);    //Name #1
    s3.getRange('CQF!I61').copyTo(s3.getRange('CQF!L6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);       //Sex #1
    s3.getRange('CQF!R61').copyTo(s3.getRange('CQF!L6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);       //Sex #1
    s3.getRange('CQF!H61').copyTo(s3.getRange('CQF!D7:H7'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);    //DOB #1
    s3.getRange('CQF!B61').copyTo(s3.getRange('CQF!J7'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);       //Age #1 
    s3.getRange('CQF!F61').copyTo(s3.getRange('CQF!E8:L8'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);    //Email #1
    s3.getRange('CQF!L61').copyTo(s3.getRange('CQF!D15:I15'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);  //Occupation #1
    s3.getRange('CQF!S61').copyTo(s3.getRange('CQF!D24:G24'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);  //Tobacco #1
    s3.getRange('CQF!J61').copyTo(s3.getRange('CQF!J24'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);      //Height #1
    s3.getRange('CQF!K61').copyTo(s3.getRange('CQF!L24'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);      //Weight #1
    s3.getRange('CQF!M61').copyTo(s3.getRange('CQF!P6:W6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);    //Name #2
    s3.getRange('CQF!O61').copyTo(s3.getRange('CQF!Y6'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);       //Sex #2
    s3.getRange('CQF!N61').copyTo(s3.getRange('CQF!Q7:U7'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);    //DOB  #2
    s3.getRange('CQF!C61').copyTo(s3.getRange('CQF!W7'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);       //Age #2 
    s3.getRange('CQF!P61').copyTo(s3.getRange('CQF!W24'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);      //Height #2
    s3.getRange('CQF!Q61').copyTo(s3.getRange('CQF!Y24'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);      //Weight #2
    
    s3.getRange('CQF!T61').copyTo(s3.getRange('CQF!C59:G59'), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, true);   //PDF Link

  var sex1 = s3.getRange('CQF!I61').getValue();
  var sex2 = s3.getRange('CQF!R61').getValue();

    if (sex1.trim().length > 0){ //if sex1 is not empty, copy sex1 to target cell
    s3.getRange('CQF!L6').setValue(sex1);
    };
    if (sex2.trim().length > 0){ //if sex2 is not empty, copy sex2 to target cell ()
    s3.getRange('CQF!L6').setValue(sex2);
    };

    break;
  }
}

Upvotes: 0

Views: 304

Answers (1)

TheWizEd
TheWizEd

Reputation: 8598

If everything is done on CQF then use the event object instead. Wrap everything in the test of event. If you only need values and not formatting etc. I would suggest you use getValues()/setValues() instead of repeated copyTo.

function onEdit(evt) {
  if( evt.range.getSheet().getName() === "CQF" ) {
    if( evt.range.getA1Notation() === "A61" ) {
      if( evt.value ) {
         // put everyting here
      }
    }
  }
}

Upvotes: 2

Related Questions