Using an If statement to check if a checkbox in a column of checkboxes is checked, and then run the remaining script

Question

I cannot get the below to work. Any help would be appreciated. I posted a similar post which I had some great assistance with, however when I apply the same concepts here I cannot get it to work. Thank you.

/** @OnlyCurrentDoc */

function onEdit(e) {

  //This IF statement ensures that this onEdit macro only runs when cells A1:A2 are edited
  if (
    e.source.getSheetName() == "Finances 2020" &&
    e.range.getColumn() == 1 &&
    1<=e.range.getRow()<=2
  ) { 

    //Cells A1:A2 are checkboxes. This section ensures the following script only runs when the checkbox is checked (and not when unchecked).
    var checkboxtest = e.range.getValue()    
    if (checkboxtest == true) {

      //Some script to test if the above works (by grabbing some text from a cell near by and pasting it into another):
      var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
      var test = spreadsheet.getRange(3, 3).getValues(); 
      spreadsheet.getRange('A3').setValues(test);
    }
  }
}
;

Answer 1

You haven't defined a sheet for getRange(3, 3), so your code is silently failing. Try running just that portion of your script and you'll see what I mean.

function test() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var test = spreadsheet.getRange(3, 3).getValues(); 
  Logger.log(test); // Error: Cannot find method getRange(number,number).
}

You can easily resolve this by defining a sheet. I'm not sure if you want to use the active sheet or not, but I'll assume you do. So your code could look like this, and it works.

/** @OnlyCurrentDoc */

function onEdit(e) {

  //This IF statement ensures that this onEdit macro only runs when cells A1:A2 are edited
  if (
    e.source.getSheetName() == "Finances 2020" &&
    e.range.getColumn() == 1 &&
    1<=e.range.getRow()<=2
  ) { 

    //Cells A1:A2 are checkboxes. This section ensures the following script only runs when the checkbox is checked (and not when unchecked).
    var checkboxtest = e.range.getValue()    
    if (checkboxtest == true) {

      //Some script to test if the above works (by grabbing some text from a cell near by and pasting it into another):
      var sheet = SpreadsheetApp.getActiveSheet();
      var test = sheet.getRange(3, 3).getValues(); 
      sheet.getRange('A3').setValues(test);
    }
  }
}