CODEWITHSUNDEEP
google-apps-script
Kristian
Kristian

Reputation: 83

Google Script - Set Sheet Protection (with unprotected cells) for more than one different Spreadsheet

I am trying to set sheet protections with unprotected ranges for more than one sheet at a time. I feel that I am close to managing it, but the myVariables part seems to be wrong. The incremental part of the variable protections0, protections1, ... does not work.

Thank you very much for your valuable support. 

function UpdateProtection() 
{

var protections0 = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx/edit').getSheetByName('Ratenprogramm') 
var protections1 = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx/edit').getSheetByName('Ratenprogramm')  
var protections2 = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxE/edit').getSheetByName('Ratenprogramm') 
var protections3 = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx/edit').getSheetByName('Ratenprogramm') 
var protections4 = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx/edit').getSheetByName('Ratenprogramm') 
var protections5 = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx/edit').getSheetByName('Ratenprogramm') 

var myVariables = {};
myVariables["protections"] = 1

  for(i = 0; i <= 5; i++)
  {  
    myVariables[ ("protections" + i) ] = i;

    var sheet = myVariables;
    var protection = sheet.protect().setDescription('Protection');
    var unprotected = [];

    unprotected[0] = sheet.getRange('A1:A2');
    unprotected[1] = sheet.getRange('D10:F40');
    unprotected[2] = sheet.getRange('F10:G20');  

    protection.setUnprotectedRanges(unprotected);

    var me = Session.getEffectiveUser();
    protection.addEditor(me);
    protection.removeEditors(protection.getEditors());
    if (protection.canDomainEdit()) 
    {
      protection.setDomainEdit(false);
    }  
  }
}

Upvotes: 0

Views: 440

Answers (1)

Andres Duarte
Andres Duarte

Reputation: 3340

You don't need the myVariables array at all:

function UpdateProtection() {
var protections0 = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx/edit').getSheetByName('Ratenprogramm') 
var protections1 = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx/edit').getSheetByName('Ratenprogramm')  
var protections2 = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxE/edit').getSheetByName('Ratenprogramm') 
var protections3 = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx/edit').getSheetByName('Ratenprogramm') 
var protections4 = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx/edit').getSheetByName('Ratenprogramm') 
var protections5 = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx/edit').getSheetByName('Ratenprogramm')

  for(i = 0; i <= 5; i++) {  
    var sheet = eval("protections" + i);
    var protection = sheet.protect().setDescription('Protection');
    var unprotected = [];

    unprotected[0] = sheet.getRange('A1:A2');
    unprotected[1] = sheet.getRange('D10:F40');
    unprotected[2] = sheet.getRange('F10:G20');  

    protection.setUnprotectedRanges(unprotected);

    var me = Session.getEffectiveUser();
    protection.addEditor(me);
    protection.removeEditors(protection.getEditors());
    if (protection.canDomainEdit()) {
      protection.setDomainEdit(false);
    }  
  }
}

I used eval() function [1] to convert the variable name in string to the actual variable value.

Next time, you could put all the URLs in an array an use them inside the loop with something like this:

var sheet = SpreadsheetApp.openByUrl(UrlArray[i]).getSheetByName('Ratenprogramm')

[1] https://www.w3schools.com/jsref/jsref_eval.asp

Upvotes: 1

Related Questions