A Script that Allows a Checkbox to Recognise a Value via Formula and Copy to Another Cell

Question

Is it possible for:

• a set of checkboxes to recognise lists (grouped in one cell) on entry (via formula)
• to then have the same checkboxes copy the list to another cell as it returns "TRUE"

Currently the below is entered in Scripts though only does the latter.
I am hoping to find a solution that allows the checkboxes to do both.

function onEdit(e) {
  const sheet = e.range.getSheet();
  if (sheet.getName() !== 'Sheet1' || e.range.columnStart < 3 || e.range.columnEnd > 3 || !String(e.value).match(/^(true)$/i)) {
    return;
  }
  const valueToCopy = sheet.getRange(e.range.rowStart, 2).getValue();
  const targetRange = sheet.getRange(e.range.rowStart, e.range.columnStart + 5);
  targetRange.setValue(valueToCopy);
}

Please see the Sample Sheet attached for a more detailed explanation.

Answer 1

You are trying to use a checkbox to copy values in a given column on Sheet2 to a given column on the same sheet as the checkbox (Sheet1).

You have already written a script to copy data from a cell (based on a formula) on the checkbox sheet (Sheet1).

The following script uses a checkbox on Sheet1 to copy data from Sheet 2 to Column H/same row as the checkbox on Sheet1.

• The Sheet1 checkbox row number is the same as the relevant Sheet2 data column-minus one. An external function (columnToLetter(column)) is used to establish the equivalent column letter.
• the number of rows of data on Sheet2 could be taklen as a given; however, in order to make the script as dynamic as possible, the number of rows of data in the relevant Shhet2 coilumn is calutaed by the variables var colvals = sourcesheet.getRange(colletter + sourcestartrow+":" + colletter).getValues(); and var collast = colvals.filter(String).length;.
• the values on Sheet2 are returned as a 2d array, but the target is a single cell. so the Javascript method .join("\n") is used to convert the array to a single value. in this case "\n" is a carriage return which enables each value to appear on a separate line within the cell.

---

function onEdit(e) {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var checksheetname = "Sheet1";
  var sourcesheetname = "Sheet2";
  
  // reference the relevant sheets
  var checksheet = ss.getSheetByName(checksheetname)
  var sourcesheet = ss.getSheetByName(sourcesheetname);
  
  // reference the edited cells
  var editedrange = e.range;
  var editedvalue = e.value;
  var editedCol = editedrange.getColumn();
  var editedRow = editedrange.getRow();
  var editedSheet = editedrange.getSheet().getName();
  
  // reference the range of checkboxes
  var checkCol=3;
  var checkrowMin = 2;
  var checkrowMax = 4;
  
  // reference the start row on Target
  var sourcestartrow=3;
  
  // Logger.log("DEBUG: onEdit - sheet = "+editedSheet+", range = "+editedrange.getA1Notation()+", the edited row = "+editedRow+", the edited column = "+editedCol+", value = "+e.value);
  
  // test for complinace with parameters.
  if (editedSheet !== checksheetname || editedCol < checkCol || editedCol > checkCol || editedRow < checkrowMin || editedRow > checkrowMax || !String(e.value).match(/^(true)$/i)  ){
      //Logger.log("DEBUG IF: onEdit - sheet = "+editedSheet+", the edited column = "+editedCol+". edited row = "+ editedRow);
      // Logger.log("DEBUG edit does not satisfy required parameters");
      return;
  }
  
  
  // continue processing
  // Logger.log("DEBUG: edit satisfied parameters");
  
  // get the list parameters
  var colnum = editedRow-1; // this is the column number on Sheet2
  var colletter = columnToLetter(colnum); // utility converts number to letter
  // Logger.log("DEBUG: edited row = "+editedRow+", refers to column"+colletter+" on the source sheet, which equals column#: "+colnum)

  // get the number of rows of data in the sheet2 column
  var colvals = sourcesheet.getRange(colletter + sourcestartrow+":" + colletter).getValues();
  var collast = colvals.filter(String).length;
  // Logger.log("DEBUG: number of rows of data = "+collast);
  
  
  // get the range and values for the data on Sheet2
  var sourcerange = sourcesheet.getRange(colletter + sourcestartrow+":" + colletter+(+ sourcestartrow+collast-1));
  // Logger.log("DEBUG: the source value range = "+sourcerange.getA1Notation());
  var sourceValues = sourcerange.getValues();
  // Logger.log(sourceValues); //DEBUG

  // join the values
  var joinedValues = sourceValues.join("\n");
  // Logger.log(joinedValues); // DEBUG

  // define the 'copy to' location
  // column = H = 8
  var targetRange = checksheet.getRange(editedRow,8);
  // Logger.log("DEBUG: the target range = "+targetRange.getA1Notation());

  // update the values to column H
  targetRange.setValue(joinedValues);
  
 return;
}

function columnToLetter(column){
  var temp, letter = '';
  while (column > 0)
  {
    temp = (column - 1) % 26;
    letter = String.fromCharCode(temp + 65) + letter;
    column = (column - temp - 1) / 26;
  }
  return letter;
}

Answer 2

This script will paste the corresponding value into column B and H when the check box in column C is true and if we're in sheet 1.

Code

const onEdit = (e) => {
  if(e.range.columnStart === 3) {
    if(e.range.getSheet().getName() === 'Sheet1') {
      const osB = e.range.offset(0,-1)
      const osH = e.range.offset(0,5)
      if(e.range.isChecked()) {
        const data = SpreadsheetApp.getActive().getSheetByName('Sheet2').getSheetValues(3,e.range.rowStart-1,4,1).flat().join('\n') 
        osB.setValue(data)
        osH.setValue(data)
      } else {
        osB.clearContent()
        osH.clearContent()
      }
    }
  }
}

Explained

The script only fires when in sheet 1 and in column 3 (please add your own restrictions for row). Then it sets the offset ranges and if the check box is true, add the data to the offset ranges or else clear them. The data is dynamically collected, as the rows in sheet 1 can be used as columns, the 2d-array is flattened, and the array is joined through the empty line character.