Search for and remove protected named ranges

Following on from this blog post to search for protected named ranges, we now look towards removing said protection once we have found it. The following Google Apps Script is designed to loop through the protection in a spreadsheet and find a name match with a specific one we are targeting, then remove the protection.

The below line gets an array of objects representing all protected ranges in the spreadsheet:

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

As we loop through getting the name of each named range from the above array (see previous blog post), we perform an additional if check to look for a specific name (studentDetails in this example):

if (name == 'studentDetails') {
      protections[i].remove();
      break;
    }

Upon finding the matching name we 'remove' the protection and 'break' out of the loop (as we no longer need to keep looping through the other names).

	function searchAndRemove() {
	var ss = SpreadsheetApp.getActive().getSheetByName('Sheet1');
	/*
	Get an array of objects representing all protected RANGEs in the spreadsheet.
	If looking for sheet protection would use: (SpreadsheetApp.ProtectionType.SHEET)[0]
	*/
	var protections = ss.getProtections(SpreadsheetApp.ProtectionType.RANGE);
	// loop through protections array *************************
	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);
	// look for name match and remove protected named range
	if (name == 'studentDetails') {
	protections[i].remove();
	break;
	}
	} // end of loop through protections array *****************
	}

view raw Search for and remove protected named ranges.gs hosted with ❤ by GitHub

Search for and remove protected named ranges.xlsx