Hey, I found some code to check if a specified range in your Google Sheet has protections enabled. Here’s a quick breakdown of how it works:

function has_protection(range_string) {

  var sheet = SpreadsheetApp.getActiveSheet();

  var range = sheet.getRange(range_string);

  var range_has_protection = false;      

  var protections = sheet.getProtections(SpreadsheetApp.ProtectionType.RANGE);

  if (protections != "") {

    var rngList = protections.map(function(pro) {

      return pro.getRange().getA1Notation();

    });

    var all_ranges_with_protection = sheet.getRangeList(rngList);

    var array_of_all_ranges_with_protection = all_ranges_with_protection.getRanges();

    for (var r = 0; r < array_of_all_ranges_with_protection.length; r++) {

      if (range.getA1Notation() == array_of_all_ranges_with_protection[r].getA1Notation()) {

        range_has_protection = true;

      }

    }

  }

  return range_has_protection;

}

This function takes a range_string as input (in A1 notation) and checks if the specified range has protection enabled, returning a boolean value.

Has anybody done something similar to this ? is there a way to do this check using formulas ?

4 days later

Hi Utsav,

I used to protect sheets by clicking ‘Data -> Protect sheets and ranges’, but now I find using app scripts interesting. Can you tell us more about it?

Zach

you can protect ranges on a sheet using apps script - here is some sample code below - the function needs the sheet and a list of ranges over which the protection needs to be applied

// This function protects certain parts of a sheet
function protect_range(sheet, range_list) {
  for (var i = 0; i < range_list.length; i++) {
    var range = sheet.getRange(range_list[i]);
    var protection = range.protect().setDescription('Protected by script at ' + Date());
    protection.removeEditors(protection.getEditors());
    if (protection.canDomainEdit()) protection.setDomainEdit(false);
  }
}
    5 days later

    Karoline from Sheetgo yes this documentation is the main source of how to protect ranges on sheets using google apps script - thanks for sharing

      Powered by: FreeFlarum.
      (remove this footer)