CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-apifile-permissions
Jimmy Hughes
Jimmy Hughes

Reputation: 13

Managing Google Protected Cells in Google Sheet

I have a Google Drive folder that has 30+ Google Sheets in it. In each sheet I have 5+ tabs, and each of these tabs has at least one protected set of cells, or the tab itself. I was wondering, is it possible to feed all of these permissions for the protected cells into one Google Sheet, as text,to be able to quickly look and potentially manage the permissions. My long-term goal is then to manage the protected cells straight from that one Google Sheet. I have been looking, but haven't found any resources to send me down the right track.

Upvotes: 0

Views: 351

Answers (1)

Heybrajham
Heybrajham

Reputation: 26

I writed this script for make the task that you want,

to run the script you need to open a Spreadsheet, or ceate a new one an then Go to tool->script editor to create it, then copy/paste the code.

Change the "#########################" for the ID of your container folder, to determine the ID of your folder you can open the folder an then copy the URL part corresponding to the ID https://drive.google.com/drive/folders/#########################

After you add the Menu, you need to refresh to see it.

Use: Click on Custom Utilities->Get permisions list Here, then it will create "sheet #" that will have all the information

here is the code:

function onOpen(){
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Custom Utilities').addItem('Get permisions list Here','testfunction').addToUi();
}

function testfunction() {
  //Add a new sheet in the current Spreadsheet
  var activeSheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet().activate();
  activeSheet.appendRow(['FileName','ID','Protection Description','Range','Type','Users']);
  activeSheet.getRange("A1:F1").setFontWeight('bold');

  //get all the Google Spreadsheet's files
  var files = DriveApp.getFolderById("#########################").getFilesByType(MimeType.GOOGLE_SHEETS);
  while (files.hasNext()) {
   var file = files.next();
   var ss = SpreadsheetApp.openById(file.getId());

   //get the permisions in the current file, and print the data to the previous created sheet
   var protectionsRange = ss.getProtections(SpreadsheetApp.ProtectionType.RANGE);
    for (var i = 0; i < protectionsRange.length; i++) {
      var protection = protectionsRange[i];

      activeSheet.appendRow([file.getName(),file.getId(),protection.getDescription(),protection.getRange().getA1Notation(),protection.getProtectionType(),protection.getEditors().join(";")]);
      //Logger.log(file.getName() + " | " + file.getId() + " \n| " + protection.getDescription() + " | " + protection.getRange().getA1Notation() + " | " + protection.getProtectionType() + " | " + protection.getEditors().join(";"));
    }
    var protectionsSheet = ss.getProtections(SpreadsheetApp.ProtectionType.SHEET);
    for (var i = 0; i < protectionsSheet.length; i++) {
      var protection = protectionsSheet[i];
      activeSheet.appendRow([file.getName(),file.getId(),protection.getDescription(),protection.getRange().getA1Notation(),protection.getProtectionType(),protection.getEditors().join(";")]);
      //Logger.log(file.getName() + " | " + file.getId() + " \n| " + protection.getDescription() + " | " + protection.getRange().getA1Notation() + " | " + protection.getProtectionType() + " | " + protection.getEditors().join(";"));
    }
  }
}

Upvotes: 1

Related Questions