Control protected sheets in Google Sheets with Apps Script

Walden Systems Geeks Corner News Control protected sheets in Google Sheets with Apps Script Rutherford NJ New Jersey NYC New York City North Bergen County

Working with google sheets one quickly realizes there are many good reasons to automate many tasks. Even simple tasks, like creating folders or resizing columns and rows can take 4 to 5 mouse clicks plus a couple of keyboard clicks, to get accomplished. This is more than tedious, annoying and time consuming. In this blog we will go over a useful function and class to protect your sheet from users editing your data. This is part of a larger effort in order to manage scripts automatically overriding ranges that other users are in the process of editing.

I often work with data sets that are populated from a database or another spreadsheet to analyze big chunks of data. Once I have my spreadsheet built out from my dataset, I automate many of the tasks from within my Google Apps Script code base.


Since most of my sheets are shared with other people in the group, we need to be able to control when and who edits the sheet. This is particularly important when running scripts that manipulate data on the sheet and take a few or more seconds to run. Scripts need static data to work with and to write back results in specific ranges. If other users are editing or manipulating the sheet at the same time, you can easily lose access to specific data ranges and consistent data.

We will cover protecting specific ranges in another blog, for now let's start with protecting an entire sheet in a Spreadsheet. Once you have the entire sheet protected, you can also carve out a small range in the sheet in order to allow the unprotected range within a protected sheet to be edited by others.

We will utilize the Protection Class to prevent other editors of the Spreadsheet to have access to our sheet, while our script is running. The Protection Class belongs to a range of classes under the Spreadsheet Service which manages access to ranges and sheets. We first grab hold of the sheet in the spreadsheet and ask for the ‘Protection’ object to be returned by calling ‘sheet.protect()’ method.

Let's start with our function signature that takes three parameters: ‘sheet’, ‘me’, ‘editorUsers’. 


function protectSheet(sheet, me, editorUsers)
The first parameter is a ‘sheet’ we want to protect from others. Second parameter ‘me’ will leave myself as the only editor of the sheet. Third parameter ‘editorUsers’ will remove all other users from editing the sheet. Putting it all together: 

function protectSheet(sheet, me, editorUsers) {
  // Protect the active sheet and remove all editors-users except myself.
  var protection = sheet.protect().setDescription('Sheet is protected');
  
  protection.removeEditors(editorUsers);
  protection.addEditor(me);
  if (protection.canDomainEdit()) {
    protection.setDomainEdit(false);
  }
  return(protection);
};

function myMainBlog() {
  var sSheet = SpreadsheetApp.getActiveSpreadsheet();
  var firstSheet = sSheet.getSheets()[0];
  var editorUsers = sSheet.getEditors();
  var me = Session.getEffectiveUser();
  var protectedSheet = protectSheet(firstSheet, me, editorUsers);
  Logger.log(protectedSheet.getEditors());
};