允许复选框通过公式识别值并复制到另一个单元格的脚本

Question

是否有可能：

• 一组复选框，用于识别列表(分组在一个单元格中)(通过公式)。
• 若要让相同的复选框在列表返回"TRUE“时将列表复制到另一个单元格，请执行以下操作

目前，下面的内容是在脚本中输入的，但是只有后者。

我希望找到一种解决方案，允许复选框同时完成这两项任务。

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);
}

有关更详细的解释，请参阅附件中的样本表。

Answer 1

您正在尝试使用复选框将Sheet2上给定列中的值复制到与复选框(Sheet1)相同的工作表上的给定列。

您已经编写了一个脚本来从复选框工作表(Sheet1)上的单元格(基于公式)复制数据。

下面的脚本使用Sheet1上的复选框将数据从工作表2复制到与Sheet1上的复选框相同的H列/同一行。

• Sheet1复选框行号与相关的Sheet2数据列相同-减1。外部函数(columnToLetter(column))用于建立等效的列字母。
• 可以将Sheet2上的数据行数作为给定值；但是，为了使脚本尽可能动态，相关的Shhet2线圈中的数据行数由变量var colvals = sourcesheet.getRange(colletter + sourcestartrow+":" + colletter).getValues();和var collast = colvals.filter(String).length;计算。
• Sheet2上的值作为2d数组返回，但目标是单个单元格。因此，Javascript方法.join("\n")用于将数组转换为单个值。在这种情况下，"\n"是一个回车，它允许每个值出现在单元格内的一个单独的行上。

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

如果C列中的复选框为true，如果我们在表1中，此脚本将将相应的值粘贴到列B和H中。

码

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()
      }
    }
  }
}

解释了

只有在第1页和第3列中(请为行添加自己的限制)时，脚本才会触发。然后设置偏移范围，如果复选框为true，则将数据添加到偏移范围中，否则清除它们。数据是动态收集的，因为工作表1中的行可以用作列，2d数组被平放，数组通过空行字符连接。