您有一个工作表,其中没有可区分的模式以用于具有值的单元格。您想运行一个脚本,该脚本将找到每个具有值的单元格并对其进行保护(如果尚未被保护)。
我已在此处修改了{@ 3}}中对答案的支持@TheMaster。
以下脚本使用电子表格“保护类” How to format UnprotectedRanges?
逻辑是:
-
sheet.getProtections(SpreadsheetApp.ProtectionType.RANGE);
:获得“保护”。
-
var rngList = protections.map(function(pro) {return pro.getRange().getA1Notation();});
:获取受保护单元格的A1表示值的数组
-
if (outputname[o] !==""){
:逐列遍历每个单元格并测试非空白单元格
-
var indexOfFirst = rngList.indexOf(a1note);
:检查非空白单元格是否在受保护单元格的数组中
-
var warning = outrange.protect().setWarningOnly(true);
:如果没有,请保护电池。
function so5884236203(){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheetname = "Sheet1";
var sheet = ss.getSheetByName(sheetname);
// get sheet protectiuons info
var protections = sheet.getProtections(SpreadsheetApp.ProtectionType.RANGE);
// Logger.log(protections); //DEBUG
var rngList = protections.map(function(pro) {
return pro.getRange().getA1Notation();
});
// Logger.log(rngList); //DEBUG
// get sheet dimensions,range and values
var LR = sheet.getLastRow();
var LC = sheet.getLastColumn();
var range = sheet.getRange(1,1,LR,LC);
//Logger.log(range.getA1Notation()); //DEBUG
var values = range.getValues();
// loop though the values by column
for (var i=0;i<LC;i++){
// get values for just a single column
var outputname = values.map(function(e){return e[i];});//[[e],[e],[e]]=>[e,e,e]
var outlen = outputname.length;
// Logger.log("DEBUG: i="+i+" "+outputname)
// loop through the cells in the column
for (var o=0;o<outputname.length;o++){
// Logger.log("DEBUG: o="+o+",value = "+outputname[o]);
// test for blank cells
if (outputname[o] !==""){
// cell is not blank
// get the range and A1Notation
var outrange = sheet.getRange(o+1,i+1);
var a1note = outrange.getA1Notation();
//Logger.log("this cell is "+a1note);
//test if this cell is in the list of protected cells
var indexOfFirst = rngList.indexOf(a1note);
if (indexOfFirst != -1){
// cell is protected
// Logger.log("DEBUG: Cell "+a1note + " is protected");
}
else {
// cell is not protected
//Logger.log("DEBUG: cell "+a1note+" is NOT protected");
// protect the cell
var warning = outrange.protect().setWarningOnly(true);
// optionally colour the cell to indicate protection
//outrange.setBackground("wheat");
}
}
}
}
}
之前
ref
之后
本文链接:https://www.f2er.com/3108068.html