如果选中了复选框，请使用脚本清除行中指定的单元格，并在脚本运行后清除复选框 [英] If Checkbox is checked, use a script to clear specified cells in row and clear checkbox after script is run

问题描述

我试图获得一个复选框，以在其所在的行上运行脚本.它需要遍历指定工作表中的所有行.

I'm trying to get a checkbox to run a script on the row it is in. It needs to loop through all rows in the specified sheet.

如果在F列中选中了一个复选框，我想安排一个脚本来清除B-F列.

If a checkbox is checked in Column F, I want to schedule a script to clear columns B-F.

B是应设置为FALSE的复选框 C是经过数据验证的输入 D是数据验证输入 E是一个依赖C和D中数据的公式.应保留该公式.

B is a checkbox that should be set to FALSE C is a data validated input D is data validated input E is a formula that relies on data in C and D. The formula should remain.

F为真是触发复选框

如果选中F，则清除B，C，D，F中的内容(由于它是一个公式，因此不应清除E)

If F is checked, then clear contents in B,C,D, F (E shouldnt be cleared since it's a formula)

我尝试从此处和其他链接修改脚本没有成功:

I've tried modifying scripts from here and other links without success:

将复选框重置为false

如果选中复选框，则使用脚本从Google表格中的行生成文档

Google表格脚本，如果选中了复选框，则会隐藏行

 function try_It(){
 deleteRow(5); //// choose col = 2 for column C, 5 for F
 }

 function deleteRowContents (col){ // col is the index of the column to check for checkbox being true

 var sh = SpreadsheetApp.getActiveSheet();
 var data = sh.getDataRange().getValues();
 var targetData = new Array();
 for(n=0;n<data.length;++n){
 if(data[n][col]!="TRUE" && data[n][col]!="FALSE"){ targetData.push(data[n])};
 }
 Logger.log(targetData);
 sh.getDataRange().clear();
 sh.getRange(1,1,targetData.length,targetData[0].length).setValues(targetData);
 }

预期结果: 如果选中了F列，请清除同一行中的B:F.(我知道上面的脚本甚至还不完整).

Expected results: If Column F is checked, clear B:F in same rows.(I know this script above is not even close to being complete).

实际结果: 这将删除整个工作表上的所有格式. 即使在F列复选框中，它也无法正确清除范围.

Actual results: This deletes all the formatting on the entire sheet. It doesn't clear range properly even in the column F checkbox.

推荐答案

• 当列"F"的复选框为true时，要清除列"B"至"D"和列"F"的值.
  • 在这种情况下，您要将复选框更改为false.
  • You want to clear the values of the column "B" to "D" and the column "F" when the checkbox of the column "F" is true.
    • In this case, you want to change the checkbox to false.

    如果我的理解是正确的，那么该修改如何?请认为这只是几种解决方案之一.修改后的脚本的流程如下.

    If my understanding is correct, how about this modification? Please think of this as just one of several solutions. The flow of the modified script is as follows.

    • 创建范围列表以从检索到的值中删除列.
    • 清除范围列表的内容.

    function deleteRowContents (col){ // col is the index of the column to check for checkbox being true
      var col = 6; // If the column "F" is 6, please set 6.
      var sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet4"); // Modified
      var data = sh.getDataRange().getValues();
    
      // Below script was modified.
      var deleteRanges = data.reduce(function(ar, e, i) {
        if (e[col - 1] === true) { // Modified
          return ar.concat(["B" + (i + 1) + ":D" + (i + 1), "F" + (i + 1)]);
        }
        return ar;
      }, []);
      if (deleteRanges.length > 0) { // or if (deleteRanges.length) { // Added
        sh.getRangeList(deleteRanges).clearContent();
      }
    }
    

    参考文献:

    • Class RangeList
    • clearContent()

    References:

    • Class RangeList
    • clearContent()

    如果我误解了您的问题，而这不是您想要的结果，我深表歉意.

    If I misunderstood your question and this was not the result you want, I apologize.

    这篇关于如果选中了复选框，请使用脚本清除行中指定的单元格，并在脚本运行后清除复选框的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！