Google Sheets Apps Script: if a neighboring checkbox is checked then copy and paste that cell to the next empty cell of another column

Question

I am new to Google Sheets Apps Script, and I've tried dozens of scripts to make this work but haven't been successful. I'm trying to write a script that copies Topic Data from Column 'C' if a neighboring checkbox in Column 'B' is checked, and pastes that Topic Data into the next empty cell starting from the 5th row in Column 'A'. Any help will be greatly be appreciated! Thank you.

Sample Sheet: https://docs.google.com/spreadsheets/d/1g9tn907Ve4rGFo7UI1NwYDBQqK5Y26TOSD_KnEfSWKw/edit?usp=sharing

This is my latest attempt:

function onEdit(){
  var ss = SpreadsheetApp.getActive().getActiveSheet();
  var selection = ss.getActiveCell().getA1Notation().split("");
  var row = ss.getActiveCell().getRow();
  //Gets the checkbox value
  var checkBoxValue = ss.getActiveCell().getValue().toString();

  if(selection[0] != "B") return; 

  switch(checkBoxValue){
    case checkBoxValue = "true":
      ss.getRange("C"+row).copyTo(ss.getRange('A5:A').getValues().filter(String).length + 5);
      break;
  }
}

I've been able to retrieve Data when a checkbox is checked but I can't figure out how to paste the Data into the next empty cell in Column 'A'. The script above was the latest iteration but this one in particular was one that I borrowed from other boards to see if I could adapt it...No Luck.

Tanaike · Accepted Answer · 2022-11-03T05:32:28.943

I believe your goal is as follows.

When the checkbox of column "B" is checked, you want to copy the value of column "C" to the next row of the last row of column "A".
From your showing script, you want to use onEdit.

In this case, how about the following modification?

Modified script:

When onEdit is used, the event object can be used. In this modification, the event object is used.

function onEdit(e) {
  // Ref: https://stackoverflow.com/a/44563639
  Object.prototype.get1stNonEmptyRowFromBottom = function (columnNumber, offsetRow = 1) {
    const search = this.getRange(offsetRow, columnNumber, this.getMaxRows()).createTextFinder(".").useRegularExpression(true).findPrevious();
    return search ? search.getRow() : offsetRow;
  };

  var sheetName = "Research";
  var { range } = e;
  var sheet = range.getSheet();
  if (sheet.getSheetName() != sheetName || range.columnStart != 2 || !range.isChecked()) return;
  range.offset(0, 1).copyTo(sheet.getRange(sheet.get1stNonEmptyRowFromBottom(1) + 1, 1), { contentsOnly: true });
}

In this case, please check the checkbox of column "B". By this, the script is run. When you directly run this script, an error occurs. Please be careful about this.

Note:

In the above modification, the 1st empty row is searched from the bottom. If you want to search it from the top, please use the following modified script.

  function onEdit(e) {
    // Ref: https://stackoverflow.com/a/44563639
    Object.prototype.get1stEmptyRowFromTop = function (columnNumber, offsetRow = 1) {
      const range = this.getRange(offsetRow, columnNumber, 2);
      const values = range.getDisplayValues();
      if (values[0][0] && values[1][0]) {
        return range.getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow() + 1;
      } else if (values[0][0] && !values[1][0]) {
        return offsetRow + 1;
      }
      return offsetRow;
    };

    var sheetName = "Research";
    var { range } = e;
    var sheet = range.getSheet();
    if (sheet.getSheetName() != sheetName || range.columnStart != 2 || !range.isChecked()) return;
    range.offset(0, 1).copyTo(sheet.getRange(sheet.get1stEmptyRowFromTop(1), 1), { contentsOnly: true });
  }

If you want to uncheck after the value was copied, please add range.uncheck() to the last line of the script.

Google Sheets Apps Script: if a neighboring checkbox is checked then copy and paste that cell to the next empty cell of another column

1 Answers1

Modified script:

Note:

References: