Copy Contents of Cell When Checkbox is Checked - Google Scripts

Question

Preamble: never used javascript before, but trying to learn through google sheets since I work with tons of data.

Hello, I'm attempting to write a google script that will take the contents of a cell 8 columns to the left of a column of checkboxes and copy it somewhere else. When the checkbox is checked, that would be the trigger to copy the contents. I'm hesitant around selecting the entire K:K range, but don't know how to have the function look at all checkboxes and trigger on a change from FALSE to TRUE. Also have no idea if an IF statement is correct here.

function checkbox_track() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var request_log = ss.getSheetByName("Tracker")
  var checkbox_range = request_log.getRange("K:K");
  var timestamp = new Date();
  var user = Session.getEffectiveUser().getEmail();
  var tracker = ss.getSheetByName("Tracker");

  if (checkbox_range == "TRUE") {
    var metric_name = checkbox_range.offset(0,-8).getValue();

    request_log.appendRow([timestamp, metric_name, user]);

}
}

Answer 1

Ok, so this is the closest I can get. Only problem now is some type of permissions issue not passing the user_name field to the tracker. That's going to be another issue entirely, but the functionality is there.

Using onEdit(e) is the easiest way to track the checkboxes in realtime, I found out. Then, because onEdit looks at every cell in the sheet, this is added to have it only look at the sheet where the checkboxes are and where e.value turns to TRUE:

if (activeSheet.getName() !== "Request Log" || e.value !== "TRUE") return;

Since I had no other cells on the sheet that will ever be "TRUE" then it will always be confined to the checkboxes.

function onEdit(e) {


  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var activeSheet = ss.getActiveSheet();
  var checkbox_range = e.range;
  if (activeSheet.getName() !== "Request Log" || e.value !== "TRUE") return;
  var timestamp = new Date();
  var tracker = ss.getSheetByName("Tracker");
  var metric_name = checkbox_range.offset(0,-8).getValue();
  var user_name = Session.getActiveUser().getEmail();

  tracker.appendRow([timestamp, metric_name, user_name]);


}