Get all ranges with data

Question

I have a spreadsheet with data spread out in different cells on different sheets. I would like to have a function called protect that would set all unprotected cells with data to protected, preferably without having to iterate through each individual cell in each sheet, as this is something I have done just before this protect function should run.

Is there a way to get a range or range list for all cells/ranges that are not empty or at least combine ranges that are adjacent.

For example so that: range("A2") + range("A3") + range("A4") -> range("A2:A4")

Running protect() for the sheet in the picture should retrieve the selected ranges and set each of them to protected.

Oh I think you're not relying on user selections, but simply looking for cells with data. I don't think there's a built-in method for that. You could use something like .getDataRange(), but it will pick up empty cells too. To get what you want, you'll likely have to write a function. — Diego
You could use getDataRange and you'd need to iterate throw the entire array including empty cells while using a condition to act depending on that. You could also use getNextDataCell to skip the empty cells, but it'll probably have a worst performance. — Andres Duarte
"Is there a way to get a range or range list for all cells/ranges that are not empty or at least combine ranges that are adjacent." Which of these two options do you want? — Tedinoz
@Tedinoz, Either. As I am already iterating through all the cells to get the data, I can store each cells A1 notation in a list and make a range list. However I would rater have as many ranges as possible combined as it is likely that e.g. all cells in range A1:P1 except G1 have data. In that case these could be combined into the 2 ranges A1:F1 and H1:P1 instead of 15 individual cells. Though as the sentence implies, the former is preferable. — Kajsa

Tedinoz Tedinoz · Accepted Answer · 2019-11-17T08:45:02

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

Get all ranges with data

1 Answers