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

4
votes

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 = ['[email protected]','[email protected]','[email protected]','[email protected]','[email protected]'];
  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. [email protected] 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.

1
google-apps-scriptgoogle-sheets
Hi Yaniv, could you please update your question with what is the value of sheetProtections or, to be more precise, what is the value of its length property? Additionally, what are the values of i and j after the loop? Obviously, i and j overflow the last index of the sheet, if you confirm that, we can say more – Oleg Valter
Hi @OlegValter, The length of sheetProtections is typically 1, but not necesseraly static. The expected i is 0 and the expected j is higher (I think 15). – Yaniv Aflalo
Well, see Alan's answer then - j is the likely culprit - why would you expect it to not error if you try to access rangeProtections[15]? It will be undefined (which is exactly what the script told you about) – Oleg Valter
The issue was not that but as @AlanWells stated - it simply did not find the sheet or range by name (if it did - i and j wouldn't be larger than the array's length and no error would appear). as it is not critical for me how the relevant sheet and range are found - I tried a different approach (see my comment to Alan). Thanks – Yaniv Aflalo
Hi Yaniv - you should add a @ if you want to notify - I only chanced upon your comment. Glad you worked it out. I may've been not clear enough - you couldn't have received a "getRange of undefined" error if the j wasn't the issue, but of course the reason why it caused an error was the failing condition (Alan's answer correctly states that sheetProtections[i] equaled to undefined due to getProtections failing to find any - whatever the reason) – Oleg Valter

1 Answers

2
votes

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 = ['[email protected]','[email protected]','[email protected]','[email protected]','[email protected]'];

  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);
    }
  }
}