How can I get this to protect a sheet on scripting basis (Google Apps Script)?

Antonio Santos

I got this piece of code running with no error; The resulting spreadsheet does show a locker next to the sheet name; Protected Sheets and Ranges show only specific range is unprotected, but the other user opening the file can edit protected ranges:

      var editors = newSpreadsheet.getEditors();
      for (var i = 0; i < editors.length; i++) {
        newSpreadsheet.removeEditor(editors[i]);
      };

      var sheetToProtect = newSpreadsheet.getSheetByName('CheckList');
      var rngMonitorUnprotect = sheetToProtect.getRange("F11:F14");
      var protection = sheetToProtect.protect();
      protection.setUnprotectedRanges([rngMonitorUnprotect]);

What am I missing here?

Marios

Explanation / Issue:

As per the official documentation, this is the correct way to apply a protection to the sheet.

  • The issue is that you didn't remove the list of editors from the protection object. What you did instead was to remove them from the spreadsheet file itself.

  • Essentially, when you add a protection to a sheet, all the current editors automatically have the right to edit the sheet (or sheet range) regardless of the protection. So your script needs to remove them from that right, this is why we execute this:

    protection.removeEditors(protection.getEditors());
    

Protect only the range F11:F14 of the sheet:

function myFunction() {
  // Protect range F11:F14, then remove all other users from the list of editors.
  var sheetToProtect = SpreadsheetApp.getActive().getSheetByName('CheckList'); 
  var range = sheetToProtect.getRange('F11:F14'); 
  var protection = range.protect();

  // Ensure the current user is an editor before removing others. Otherwise, if the user's edit
  // permission comes from a group, the script throws an exception upon removing the group.
  var me = Session.getEffectiveUser();
  protection.addEditor(me);
  protection.removeEditors(protection.getEditors());
  if (protection.canDomainEdit()) {
    protection.setDomainEdit(false);
  }
}

Protect the full range of the sheet except for F11:F14:

function myFunction() {
  var sheetToProtect = SpreadsheetApp.getActive().getSheetByName('CheckList'); 
  var protection = sheetToProtect.protect();
  var rngMonitorUnprotect = sheetToProtect.getRange("F11:F14");  
  protection.setUnprotectedRanges([rngMonitorUnprotect]);    
  var me = Session.getEffectiveUser();
  protection.addEditor(me);
  protection.removeEditors(protection.getEditors());
  if (protection.canDomainEdit()) {
    protection.setDomainEdit(false);
  }
}

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

Using Google Apps Script, how can I replace text in a Google Sheets template to make a new Sheet?

Using Apps Script, how can I display a google doc checklist from information in a google sheet?

How can I use a For loop to loop tabs on sheet index numbers in Google Apps Script?

How can I fix the range of a Google Sheet MATCH function when using a move rows apps script?

How can I move urls from sheet ranges while building the links at destinations using Google Apps Script?

How can I redirect back to my website after google apps script sheet submission?

How do I get 5 rows from the end of my sheet in Google Sheets using Apps Script?

How can I possibly get the last row from a range of a sheet using apps script?

How to get specific sheet by id in Google Apps script?

How can I change sheet direction from apps script

How can I assign a Google Sheet script to only one sheet?

get last update of google sheet in apps script

Can I use Google Apps Script to make a Google Form display randomized text from a Google Sheet?

How to request or get the permission of google spread sheet access in the apps script of other sheet?

Protect ranges with google apps script

How to run a Google Apps Script on a hidden sheet

How to Preserve Sheet Formats and Translate? ( Google Apps Script, google sheet )

Can I use Google Apps Script to replace text across multiple tabs in a Google Sheet?

How can I tell google script where to print in sheet?

How can I write the data in Firestore to Google Sheet with app script?

I can't get Google Apps Script run for my Gmail

How can I add choices in a Google Form with Google Apps Script

How could I get the link of an specific sheet with Google script?

How to password protect a google sheet

How can I get past the "Insufficient authentication scopes" error in Google Apps Script?

How can I get a calendar event object when create/save on Google Apps Script?

How to get information on hidden rows from Google Sheet API without Google Apps Script

How to get Error Message from a Google sheet cell using Google Apps Script?

Get #ERROR! and #NAME? in cell sheet when I use the .setFormula () in Google Apps Script