Is it possible to use a script to enable edition in more than one sheet on Google Sheet?

Question

I need to enable edition in more than one sheet to 60 users (using e-mail). The user need to edit a few cells and all the other the edition need to be blocked. Example: all the user should have the edition enable in sheets X, Y and Z, and the range of cells enabled to edition is B6:Q50. All the other cells need to be blocked and only the owned can edit.

Is it possible?

Please add more context on your post such as the research you've done or what you've tried so far. Also, if I understand your post correctly, you want to add multiple editors by their email addresses to multiple sheets inside of a spreadsheet file and only allow them to edit a specific range (e.g. B6:Q50) and the rest are restricted to the editors. Is this correct? — SputnikDrunk2, Jun 18 '21 at 15:34
@IrvinJayG. sorry. It's my first question here. I searched a solution to this problem but I didn't find it. Yes, that's correct — Matheus Felipe Bernardes, Jun 18 '21 at 15:44

SputnikDrunk2 · Accepted Answer · 2021-06-18T16:39:05.417

Solution:

On your main Google spreadsheet file, you can create a bound script and try this tweaked script below:

This script was based from Protect spreadsheet then unprotect specific cells and ranges with script & article of addEditors() method.

Script:

function main() { //Main function to run
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  addEditors();
  for(var x=0; x<sheets.length; x++){
    unlockCertainRanges(sheets[x]);
  }
}

function addEditors(){ //Function to add editors
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var emails = [
    'test1@example.com',
    'test2@example.com',
    'test3@example.com'
  ];
  sheet.addEditors(emails);
}

function unlockCertainRanges(currentSheet){ //Function to unlock certain ranges on your sheets
  var sheet = currentSheet;
  // Remove all range protections in the spreadsheet
  var protections = sheet.getProtections(SpreadsheetApp.ProtectionType.RANGE);
  for (var i = 0; i < protections.length; i++) {
    var protection = protections[i];
    protection.remove();
  }

  var protection = sheet.protect();
  //restrict editors to owner
  protection.getRange().getA1Notation();
  var eds = protection.getEditors();
  protection.removeEditors(eds);

  //set unprotected ranges
  var ranges = protection.getUnprotectedRanges();
  var data = ["B6:Q50"]; // ADD YOUR RANGES HERE TO BE EDITABLE
  data.forEach(res => { //LOOPS INTO EVERY ARRAY CONTAINING SPECIFIC RANGES
    ranges.push(sheet.getRange(res));
    protection.setUnprotectedRanges(ranges); //REMOVES THE PROTECTION ON THE RANGE
  });
}

Sample Result:

After running the main function, the emails added on addEditors function are automatically added as editors of the file & they'll be abe to edit range B6:Q50 and the rest of the cells will be protected (only the owner of the spreadsheet file will be able to edit):

Sample Images:

Image 1: A user is not allowed to edit cell A1 (outside of B6:Q50 range on all sheets)

Image 2: User is allowed to edit cell B6 (within B6:Q50 range on all sheets)

Thank you! It worked like I wanted! Just a question: if I change 'var data = ["B6:Q50"]' to 'var data = ["B6:Q50; "A1:A2"]', users can edit A1 and A2? — Matheus Felipe Bernardes, Jun 18 '21 at 18:32
You're welcome, yes, you can follow this sample structure instead if you want to add more ranges (separated by commas) ----> var data = ["B6:Q50","A1:A2"]; — SputnikDrunk2, Jun 18 '21 at 18:38

Is it possible to use a script to enable edition in more than one sheet on Google Sheet?

1 Answers1