单击复选框时如何冻结一系列单元格? [英] How to freeze a range of cells when a checkbox is clicked?

问题描述

工作表的屏幕截图:

当某个复选框被选中时，我只需要有人来帮助我编写简单的代码来冻结一系列单元格点击.

I just need someone to help me write simple code to freeze a range of cells when a certain checkbox is clicked.

我希望这样做，以便当我单击完成"复选框时，它上面的所有复选框都无法再进行编辑或更改.取消选中完成"复选框时，即可进行虎钳处理.这么简单.

I would like it so that when I click on the 'Complete' checkbox, all of the ones above it cannot be edited or changed anymore. Vise Versa when the 'Complete' checkbox is unchecked the ones above are editable. That simple.

这张纸的目的是参加一个课堂.当我完成出勤后，我不再希望更改它了(或者冒险单击错误的复选框).这就是为什么存在完整按钮的原因.

The purpose of the sheet is to take attendance for a class. When I am done taking the attendance I don't want to be able to change it anymore (or risk clicking on the wrong checkbox). That's why the complete button is there.

请问有人可以帮我写代码吗?

Can anyone write the code for me, please?

(冻结，密封或保护)

此代码不起作用(对不起，我是初学者)

This code is not working (I am a beginner so sorry)

function onEdit() {
  var sheet = SpreadsheetApp.getActive();;
  var completedRow = sheet.getDataRange();  
  
  for (i = 2; i < 18; i++){
    var isComplete = source.getRange(countRow, i).getValue();
    
    if (isComplete === true){
      source.getRange(2, i, countRow-1).protect();
    }
  }
  
}

推荐答案

尽管存在一些语法缺陷，但是您的代码反映了基本逻辑.希望这个答案将帮助您理解和适应该语法.

Your code reflects the basic logic, though there are some syntax flaws. Hopefully this answer will help you understand and adapt that syntax.

• 该代码没有利用事件对象可用于 onEdit(e)，其中包括已编辑单元格的行，列和值.使用事件对象不是强制性的，但是它们肯定会使生活更轻松.
未定义
• countRow ；并且因为您正在使用有限长度的电子表格(20行)；这可能是不必要的.但是，允许使用更大的电子表格是一个明智的主意.也许像 var countRow = sheet.getLastRow(); 这样的替代品
• 在某个阶段，您可能想取消保护"列；在新学期或新学年开始时说；因此检查第20行的值为"false"可能很有用.

• The code doesn't doesn't take advantage of the Event Objects that are available to onEdit(e), which include the row, column and value of the edited cell. It's not compulsory to use the Event objects, but they certainly make life easier.
• countRow isn't defined; and because you are working with a spreadsheet of finite length (20 rows); it is probably unnecessary. But it is a sensible idea to allow for bigger spreadsheets. Maybe something like var countRow = sheet.getLastRow(); would be a good alternative Doc Ref.
• isComplete - we know that this is always on row 20; we also know that it will have a value of "true" or "false". So, you don't need a loop to define this row.
• At some stage, you may want to "unprotect" a column; say at the start of a new term or year; so it's likely that checking row 20 for a value of "false" could be useful.

您的目标可能可以通过许多方式实现.以下内容应被视为只是一种选择.

Your goal can probably be achieved in many ways. The following should be considered as just one option.

• 主要功能是在 onEdit(e)中设置的自定义菜单(使用 onOpen )，您就可以查看所有受保护的列，并在需要时删除保护.
• 我还在代码中留下了一些 Logger.log 语句，这些语句使您可以在代码的关键阶段检查某些字段的值.
• 总而言之，此代码遵循与您的代码相同的逻辑，但有更多详细信息.
• 最后一件事，由于 var sheet = ss.getSheetByName(sheetname); ，此代码旨在在特定的工作表上工作，但是您可以轻松地将其更改为 varsheet = SpreadsheetApp.getActiveSheet(); 使其可以在电子表格中的多个工作表上使用.

• The main function is setup in an onEdit(e) simple trigger.
• I also setup a custom menu (using onOpen) that gives you access to view all the protected columns, and to remove protection if you need to.
• I've also left some Logger.log statements in the code that may enable you to check the value of certain fields at key stages of the code.
• All-in-all, this code follows the same logic as your code, but with some more detail.
• One last thing, this code is designed to work on a specific sheet by virtue of var sheet = ss.getSheetByName(sheetname); but you could just as easily change this to var sheet = SpreadsheetApp.getActiveSheet(); to make it work on multiple sheets in your spreadsheet.

function onEdit(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheetname = "Sheet1";
  var sheet = ss.getSheetByName(sheetname);

  // set variable for last column


  //Logger.log(JSON.stringify(e))
  // set variables for edited cells, 
  var edittedRow = e.range.rowStart;
  var edittedColumn = e.range.columnStart;
  var newValue = e.value;
  var headerrange = sheet.getRange(1, edittedColumn);
  var headervalue = headerrange.getDisplayValue();
  //Logger.log("DEBUG: The header range is "+headerrange.getA1Notation()+", and the value is "+headervalue);

  // test if edit row =20, and the checkbox was ticked
  if (edittedRow === 20 && newValue === "TRUE") {
    //Logger.log("DEBUG: The 'ON' leg applies");
    //Logger.log("DEBUG: edittedRow = "+edittedRow+", Editted column = "+edittedColumn+", and value = "+newValue);

    // define the range to protect
    var protectRangeOn = sheet.getRange(1, edittedColumn, 19, 1);
    // protect the range - warning only.
    protectRangeOn.protect().setDescription(headervalue)
      .setWarningOnly(true);
    //Logger.log("DEBUG1: protection set for "+protectRangeOn.getA1Notation());
  }

  //test if edit row=20, and the checkbox was unticked
  if (edittedRow === 20 && newValue === "FALSE") {
    //Logger.log("DEBUG: The 'OFF' leg applies");
    //Logger.log("DEBUG: edittedRow = "+edittedRow+", Editted column = "+edittedColumn+", and value = "+newValue);

    // define the range to unprotect
    var protectRangeOff = sheet.getRange(1, edittedColumn, 19, 1);
    var protections = sheet.getProtections(SpreadsheetApp
      .ProtectionType.RANGE)
    for (var i = 0; i < protections.length; i++) {

      Logger.log("protections range name = " + protections[i]
        .getDescription() + " - Header value = " + headervalue);
      if (protections[i].getDescription() === headervalue) {
        //Logger.log("DEBUG: OFF matches")
        protections[i].remove();
      }
    }
    //Logger.log("DEBUG2: protection unset for "+protectRangeOff.getA1Notation());
  }


}

// Add a custom menu to the active spreadsheet to access Utilities
function onOpen(e) {
  SpreadsheetApp.getUi()
    .createMenu('Protection Utilities')
    .addItem('Show all protections', 'uigetprotections')
    .addItem('Remove all protections', 'removeallprotections')
    .addToUi();
}

function removeallprotections() {
    // remove all protections
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheetname = "Sheet1";
  var sheet = ss.getSheetByName(sheetname);
  var protections = ss.getProtections(SpreadsheetApp.ProtectionType
    .RANGE);
  Logger.log(protections);
  for (var i = 0; i < protections.length; i++) {
    var protection = protections[i];
    Logger.log(protection.getEditors())
    if (protection.canEdit()) {
      protection.remove();
    }
  }
  // Display confirmation dialog
  var ui = SpreadsheetApp.getUi();
  var response = ui.alert('REMOVE ALL PROTECTION',
    'Confirmed: Removed all protections', ui.ButtonSet.OK);
}

function uigetprotections() {
    // generate a list of all RANGE protections
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheetname = "Sheet1";
  var sheet = ss.getSheetByName(sheetname);
  var protections = ss.getProtections(SpreadsheetApp.ProtectionType
    .RANGE);
  //Logger.log(protections);
  var ui = SpreadsheetApp.getUi();
  var protectioninfo = "";
  if (protections.length != 0) {
    for (var p = 0; p < protections.length; p++) {
      //Logger.log("DEBUG: Date = "+protections[p].getDescription()+", Range = "+protections[p].getRange().getA1Notation());
      protectioninfo = protectioninfo + "Date: " + protections[p]
        .getDescription() + ", Range = " + protections[p].getRange()
        .getA1Notation() + "\n";
    }
    var response = ui.alert('SHOW ALL PROTECTIONS', protectioninfo, ui
      .ButtonSet.OK);
  } else {
    var response = ui.alert('SHOW ALL PROTECTIONS',
      "There were no protected ranges", ui.ButtonSet.OK);
  }
}

这篇关于单击复选框时如何冻结一系列单元格?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！