You have a sheet where there is no distinguishable pattern for cells that have a value. You want to run a script that will find each cell with a value and protect it (if it is not already protected).
Props to @TheMaster for the answer in How to format UnprotectedRanges? which I have adapted here.
The following script uses the Spreadsheet `Protection Class' ref
The logic is:
sheet.getProtections(SpreadsheetApp.ProtectionType.RANGE);
: get the "protections".
var rngList = protections.map(function(pro) {return pro.getRange().getA1Notation();});
: get an array of the A1-notation value of protected cells
if (outputname[o] !==""){
: loop through each cell by column and test for non-blank cells
var indexOfFirst = rngList.indexOf(a1note);
: check whether the the non-blank cell is listed in the array of protected cells
var warning = outrange.protect().setWarningOnly(true);
: If not, then protect the cell.
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");
}
}
}
}
}
BEFORE
AFTER
SpreadsheetApp.getActiveRangeList()
? – Diego.getDataRange()
, but it will pick up empty cells too. To get what you want, you'll likely have to write a function. – Diego