Google-sheets – A Script that Allows a Checkbox to Recognise a Value via Formula and Copy to Another Cell

copy/pastegoogle sheetsgoogle-apps-script

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.

Best Answer

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;
}
Related Topic