google sheet protection - basic need won't work

Question

I have a basic question and have been pulling my hair out trying to figure it out.

I want a google sheet to be view only for anyone with the link. I want to be able to edit anything as the owner (duh) I want to protect a sheet except for certain cells (say A1) to be editable and add 1 person with a google account (say [email protected]) so they can only edit A1.

All my attempts have led to anonymous users being view only as desired, but [email protected] can edit every cell and isn't limited to A1.

Am I possibly doing something wrong, or is this scenario just not possible?

I've tried many guides including these: https://support.google.com/docs/answer/144687?hl=en http://www.appscare.com/2015/02/set-permissions-protected-sheets/

Thanks!

Answer 1

Try this. I set share on my sample sheet to anyone with a link can view and then added the email address with can edit. The user of the email address can edit "A1" and, of course, I can edit everything.

function protectionTest(){
 // Protect the active sheet except A1, then remove all other users from the  list of editors.
 var sheet = SpreadsheetApp.getActiveSheet();
 var protection = sheet.protect().setDescription('Sample protected sheet');
 var unprotected = sheet.getRange('A1');
 protection.setUnprotectedRanges([unprotected]);

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

It took a while, but this will identify the colored ranges and unprotect them. Set up Set up can edit as above.

 function cellsToUnprotect(){
 //Find cells of specific color and create array of ranges.
 var ss = SpreadsheetApp.getActiveSpreadsheet()
 var sheet = ss.getSheetByName("Sheet1")// Change Sheet Name as needed.
 var rng = sheet.getRange("A1:C10").getBackgrounds()// Adjust Range as  needed.
 var arrayBG=[]
{
  for(var i=0;i<rng.length;i++){
  for(var j=0;j<rng[1].length;j++){
  //if(rng[i][j] != "#ffffff"){ //Any background color not equal to white. I  prefer this.
  if(rng[i][j] == "#ffff00"){ //background color defined ("#ffff00" is  yellow)
  var r=i+1
  var c=j+1
  var range=sheet.getRange(r, c)
  arrayBG.push(range)
    }}}
  unprotect(arrayBG)
  }}

function unprotect(arrayBG){
//Protect Sheet1 and unprotect ranges in passed array.
var ss = SpreadsheetApp.getActiveSpreadsheet()
var sheet = ss.getSheetByName("Sheet1") // Change Sheet Name as needed.
var protection = sheet.protect().setDescription('Sample protected sheet');
protection.setUnprotectedRanges(arrayBG);
// Ensure the current user is an editor before removing others. Otherwise,if  the user's edit
// permission comes from a group, the script will throw an exception upon     removing the group.
var me = Session.getEffectiveUser();
protection.addEditor(me);
protection.removeEditors(protection.getEditors());
if (protection.canDomainEdit()) {
  protection.setDomainEdit(false);
}}

Answer 2

Sorry I took so long to answer, I was on vacation. This will handle multiple sheets. If this works for you please approve the answer.

function cellsToUnprotect(){
//Find cells of specific color and create array of ranges.
 var ss = SpreadsheetApp.getActiveSpreadsheet()
 var sheet = SpreadsheetApp.getActiveSheet();
 var sheetNameToWatch=[] //Array of Sheet names
 var names = ss.getSheets()
  for( j=0;j<names.length;j++) {
    var n= names[j].getSheetName();
      if(n!="Done"){ //If Sheet name not "Done" add to array. Change sheet  name to exclude sheets. 
       sheetNameToWatch.push(n)
      }}
   for(var k=0;k<sheetNameToWatch.length;k++){
      var rng =  ss.getSheetByName(sheetNameToWatch[k]).getRange("A1:C10").getBackgrounds()//  Adjust Range as needed.
      var arrayBG=[]
{
  for(var i=0;i<rng.length;i++){
  for(var j=0;j<rng[1].length;j++){
  //if(rng[i][j] != "#ffffff"){ //Any background color not equal to white. I   prefer this.
  if(rng[i][j] == "#ffff00"){ //background color defined ("#ffff00" is   yellow)
     var r=i+1
     var c=j+1
     var range=ss.getSheetByName(sheetNameToWatch[k]).getRange(r, c)
  arrayBG.push(range)
    }}}
  unprotect(arrayBG,sheetNameToWatch,k)
}}}

function unprotect(arrayBG,sheetNameToWatch,k){
 //Protect Sheet1 and unprotect ranges in passed array.
 var ss = SpreadsheetApp.getActiveSpreadsheet()
 var sheet = ss.getSheetByName(sheetNameToWatch[k]) // Change Sheet Name as needed.
var protection = sheet.protect().setDescription('Sample protected sheet');
protection.setUnprotectedRanges(arrayBG);
// Ensure the current user is an editor before removing others. Otherwise,if   the user's edit
// permission comes from a group, the script will throw an exception upon   removing the group.
var me = Session.getEffectiveUser();
protection.addEditor(me);
protection.removeEditors(protection.getEditors());
if (protection.canDomainEdit()) {
  protection.setDomainEdit(false);
}}