Issue with protection script for google sheets - Cannot read property 'getRange' of undefined

user_12209532 0
Viewed 445

Script summery:

  1. The script should loop over sheets protection and ranges protection and find a target sheet and target range.
  2. Then, check if the current user has editor's permissions.
    1. If not - the script checks if the number of users with editor's permission for the target sheet and target range is smaller than the number in the permitted user emails list.
      1. If so - the script removes all permissions from target sheet and target range and re-set them to the permitted user emails list.

My problem: I get this error:

TypeError: Cannot read property 'getRange' of undefined at [the getRange properties of both while loops conditions]

The script:

function someFunction() {
  var ss = SpreadsheetApp.getActive();
  var sheetName = 'some sheet name';
  var rangeName = 'some range name';
  var targetSheet = ss.getSheetByName(sheetName);
  var targetRange = ss.getRangeByName(rangeName);
  // set permissions
  var permittedUserEmails = ['user1@gmail.com','user2@gmail.com','user3@gmail.com','user4@gmail.com','user5@gmail.com'];
  var sheetProtections = ss.getProtections(SpreadsheetApp.ProtectionType.SHEET);
  var rangeProtections = ss.getProtections(SpreadsheetApp.ProtectionType.RANGE);
  var i = 0;
  while (sheetProtections[i].getRange().getSheet() != targetSheet {
    i++;
  }
  if (sheetProtections[i].canEdit() == false) {
    if (rangeProtections[i].getEditors().length < permittedUserEmails.length) {
      sheetProtections[i].remove();
      sheetProtections[i].addEditors(permittedUserEmails);
    }
  }
  var j = 0;
  while (rangeProtections[j].getRange() != targetRange {
    j++;
  }
  if (rangeProtections[j].canEdit() == false) {
    if (rangeProtections[j].getEditors().length < permittedUserEmails.length) {
      rangeProtections[j].remove();
      rangeProtections[j].addEditors(permittedUserEmails);
    }
  }
}

Note:

  1. If I use Browser.msgBox(sheetProtections[i].getRange()) and Browser.msgBox(rangeProtections[j].getRange()) just before the corresponding while loops I do not get an error at all.
  2. user1@gmail.com is the ss owner.

Can someone please explain what is going on and how to fix this?

Update: The error was caused due to i and j exceeded the sheetProtections and rangeProtections length, correspondingly without meeting the loops conditions.

A different approach to indicate the target sheet and range solved the problem.

google-apps-script google-sheets
1 Answers

The error occurs because there were no sheet or range protections found of that name. The getProtections method returns an empty array if there are no protections found. It doesn't return undefined or null if there are no protections. It returns a truthy value of an empty array if there are no protections found. That means that the empty array will have a length of zero. You are starting the count of the variable i at zero, which is a valid index, and doesn't return an error at the point of sheetProtections[i] even though the value at index zero is undefined.

There are multiple ways that you can deal with that situation, below is one possibility.

function someFunction() {


  var ss = SpreadsheetApp.openById('1RwwWliVHscvvRLOZ4jTQNtQJOgoTSBAo43VRDMhKzoo');

  var sheetName = 'some';
  var rangeName = 'some';
  var targetSheet = ss.getSheetByName(sheetName);
  var targetRange = ss.getRangeByName(rangeName);
  // set permissions
  var permittedUserEmails = ['user1@gmail.com','user2@gmail.com','user3@gmail.com','user4@gmail.com','user5@gmail.com'];

  var sheetProtections = ss.getProtections(SpreadsheetApp.ProtectionType.SHEET);
  var rangeProtections = ss.getProtections(SpreadsheetApp.ProtectionType.RANGE);

  Logger.log('sheetProtections: ' + sheetProtections)

  var L = sheetProtections.length;
  Logger.log("L: " + L)
  var i = 0;

  while (i < L) {//If i reaches the L number then it stops - If L is zero and i is zero then zero is not less than zero
    Logger.log('i ' + i)
    if (sheetProtections[i].getRange().getSheet() === targetSheet;) {
      break;
    }

    i++;//decrement
  }

  if (sheetProtections[i].canEdit() == false) {
    if (rangeProtections[i].getEditors().length < permittedUserEmails.length) {
      sheetProtections[i].remove();
      sheetProtections[i].addEditors(permittedUserEmails);
    }
  }

  L =  rangeProtections.length;

  var j = 0;

  while (j < L) {
    if (rangeProtections[j].getRange() === targetRange) {
      break;
    }
    j++;
  }

  if (rangeProtections[j].canEdit() == false) {
    if (rangeProtections[j].getEditors().length < permittedUserEmails.length) {
      rangeProtections[j].remove();
      rangeProtections[j].addEditors(permittedUserEmails);
    }
  }
}
user_29468730
Related