Google-sheets – Apply protection to multiple sheets in a Google Spreadsheet

access-controlgoogle sheetsgoogle-apps-script

Ive got a Google Spreadsheet with 50+ data tabs, and 5 Summary tabs at the end.

The 50+ data tabs all have the same column headers.

I want to apply protection to the all these data "data tabs" to stop some people who have edit rights on the spreadsheet from editing the data. Eg. "John" who has edit rights on the spreadsheet would not be able to edit the data in the data tabs, column A:Z, except column E.

In the past Ive done this manually by applying protection to each tab, but its really fiddly, its also really fiddly when i need to edit the protection rules, is there a more automated way i could manage this ?

Perhaps by setting the protection rules as a template then applying that template to the tabs i need.

Best Answer

There isn't a built-in feature to protect or edit the protection of several sheets at once but Google Apps Script could be used to automated this task or an add-on created by yourself or by someone else.

Reference

Script to add protection and remove the user running it?

Code

function result(range) {
  var splitCol = 1; // split on column B
  var output2 = [];
  for(var i=0, iLen=range.length; i<iLen; i++) {
    var s = range[i][splitCol].split("\n");    
    for(var j=0, jLen=s.length; j<jLen; j++) {
      var output1 = []; 
      for(var k=0, kLen=range[0].length; k<kLen; k++) {
        if(k == splitCol) {
          output1.push(s[j]);
        } else {
          output1.push(range[i][k]);
        }
      }
      output2.push(output1);
    }    
  }
  return output2;
}

Explanation

The script evaluates each row, and particularly the second column of each row (in JavaScript arrays are zero based, so column 2 corresponds to index 1 of the array). It splits the contents of that cell, into multiple values and uses the "\n" as delimiter (line feed). After that it adds the existing info to an array and only add the individual results, when it hits index 1 (k == 1). The newly prepared row is then added to another array, that's being returned to show the result.

Screenshot

data
enter image description here

result
enter image description here

Example

I've created an example file for you: multi-line cells into new rows.

Add the script under Tools > Script editor.
Save the script, the name doesn't matter.
Use the custom function in a cell just like a built in (see the screenshot above).

Google-sheets – How tomport a Range from one Google Spreadsheet into another

Only after having requested that the documents were to be shared with us, was I able to determine what's going on.

The response sheet is new Google Sheet proof:
enter image description here

The sheet, in which you want to pull the data in, doesn't have this mark.

You are trying to import the data via the complete URL (new Sheets style) from within an old Google Spreadsheet. This is where Google says: No Can Do !!

If you use the key instead, it works just fine:

 =IMPORTRANGE("1zRHatplJ6AVUSWr1HXoycUrvtK05dw4-ZqeEYN1xJ4Y", "B:B")

See references for more information on this topic.

Best Answer

Related Solutions

Google-sheets – Google sheets split multi-line cell into new rows (+ duplicate surrounding row entries)

Code

Explanation

Screenshot

Example

Google-sheets – How tomport a Range from one Google Spreadsheet into another

References

Related Topic