Home > database >  Using ischecked function to activate a macro
Using ischecked function to activate a macro

Time:12-14

I have a column with some checkboxes. I've tried to get a macro to activate when ANY of the checkboxes is checked. I thought that this could be achieved with checking if ischecked returns null, since true is returned when they're all checked and false when none are. Null should then be the result if it is a combination of the two, right? Instead the macro now activates with any edit, all the time, even if I uncheck the box.

Here's the code and macro below it:

function onEdit(e) {

if(SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange("B4:B15").ischecked == null) 
{
copy();
}
};

function copy() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('E2').activate();
  spreadsheet.getRange('C2').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
};

What am I missing? Of course getting the same result with a different function is fine as well.

Here's a link to the test sheet I created for demo: https://docs.google.com/spreadsheets/d/1k728vBAe4F4zi1M53NwHK6L5RwWG8x6gd71Wnx6u9PE/edit?usp=sharing

Thanks!

CodePudding user response:

Try it this way:

You should avoid using function calls inside of onEdit triggered functions because they need to finish with 30 seconds and all function calls take time. You should also try to take advantage of the data in the event object . I know it's easier to troubleshoot the functions if you don't use the event object but the problem is that everything runs much slower.

function onEdit(e) {
  const sh = e.range.getSheet();
  if (sh.getName() == 'Your Sheet Name' && e.range.columnStart == 2 && e.range.rowStart > 3 && e.range.rowStart < 16 && e.value == "TRUE")
    sh.getRange('C2').copyTo(sh.getRange('E2'), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
}
  • Related