CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
jcom
jcom

Reputation: 101

GAS - script to get all named ranges included protected ones - did not working

function getNameProtectedRanges() {
  var ss = SpreadsheetApp.getActive().getSheetByName('Requerimento');
  var protections = ss.getProtections(SpreadsheetApp.ProtectionType.RANGE);

  Logger.log('protections.length: ' + protections.length);
  Logger.log('protections: ' + protections);
  Logger.log('typeof protections: ' + typeof protections);


  var rngList = protections.map(function(pro) {
    return pro.getRange().getA1Notation();
  });

  var rngList1 = protections.map(function(pro) {
   return pro.getRange().getRangeByName();
  });



  Logger.log('rngList: ' + rngList);
  Logger.log('rngListName: ' + rngList);

  for (var i=0; i<protections.length; i++) {

    // get the name of each named range
    var name = protections[i].getRangeName();
    Logger.log('Named range is: ' + name);
   }
}

Register:
[20-03-30 18:42:05:090 PDT] protections.length: 7
[20-03-30 18:42:05:097 PDT] protections: Protection,Protection,Protection,Protection,Protection,Protection,Protection
[20-03-30 18:42:05:099 PDT] typeof protections: object
[20-03-30 18:42:05:145 PDT] TypeError: pro.getRange(...).getRangeByName is not a function
    at [unknown function](Código:275:25)
    at getNameProtectedRanges(Código:274:30)

Upvotes: 0

Views: 349

Answers (1)

Diego
Diego

Reputation: 9571

To get all named ranges, you can call getNamedRanges() and loop through that, calling getName() on each range.

To list just protected named ranges, you have to get the range protections in a spreadsheet and call getRangeName() on each protection. You have to keep Google's note about protected named ranges in mind, though:

Note that scripts must explicitly call setRangeName(rangeName) to associate a protected range with a named range; calling Range.protect() to create a protection from a Range that happens to be a named range, without calling setRangeName(rangeName), is not sufficient to associate them. However, creating a protected range from a named range in the Google Sheets UI associates them automatically.

function listNamedRanges() {
  var ss = SpreadsheetApp.getActive();

  // List all named ranges
  var namedRanges = ss.getNamedRanges();
  for  (var i = 0; i < namedRanges.length; i++) {
    Logger.log("Range name is: " + namedRanges[i].getName());
  }

  // List protected range names, or null, if protected range is not named
  var protections = ss.getProtections(SpreadsheetApp.ProtectionType.RANGE);
  for (var i = 0; i < protections.length; i++) {
    Logger.log("Protected range name is: " + protections[i].getRangeName());
  }
}

You were getting the error "TypeError: pro.getRange(...).getRangeByName is not a function", because you're calling getRangeByName() on a Range object. That method only exists on the Spreadsheet object, however.

Upvotes: 1

Related Questions