Google-sheets – How to make a copy and remove all protected ranges from the copied sheet

google sheetsgoogle-apps-script

What I want to do is

Make a copy of the sheet and remove all protected ranges from copied sheet.

var ss = SpreadsheetApp.getActiveSpreadsheet();
var timezone = "GMT-5"
var timeformat = "yyyy-MM-dd"
var date = Utilities.formatDate(new Date(), timezone, timeformat);

var file = DriveApp.getFileById(SpreadsheetApp.getActiveSpreadsheet().getId()).makeCopy(date + " " + ss.getName());

var editors = DriveApp.getFileById(SpreadsheetApp.getActiveSpreadsheet().getId()).getEditors();
  for (var i = 0; i < editors.length;i++) {    
      file.addEditor(editors[i])    
  }

  var viewers = DriveApp.getFileById(SpreadsheetApp.getActiveSpreadsheet().getId()).getViewers();

  for (var i = 0; i < viewers.length;i++) {    
      file.addViewer(viewers[i])

this is what I'm doing to make a copy of the sheet.

Best Answer

Looks like all you already have the copying part done, with file being the handle of the copy. All that remains to do is to open the file with SpreadsheetApp and then use the methods of Protection class:

var ss = SpreadsheetApp.open(file); 
var protections = ss.getProtections(SpreadsheetApp.ProtectionType.RANGE);
for (var i = 0; i < protections.length; i++) {
  protections[i].remove();
}

Related Solutions

Google Sheets – Remove Protection from Range

Try this code:

function start() {
  var idFolder = '0B79ClRnKS87QcW9XbVhrcFZxd28'; // change
  var sheetNames = ['1', '2', '3']; // change
  var strRange = 'A1'; // change

  deleteProtection(idFolder, sheetNames, strRange) 

}


function deleteProtection(idFolder, sheetNames, strRange) {

  var folder = DriveApp.getFolderById(idFolder);
  var contents = folder.getFiles();
  var file;
  var sheet;
  var sheets;
  var sheetName;
  var range;
  var strRangeProtect;
  var protections;
  var protection;

  var app = SpreadsheetApp;

  while(contents.hasNext()) {
    file = app.openById(contents.next().getId());
    sheets = file.getSheets();

    for(var i = 0; i < sheets.length; i++) {
      sheet = sheets[i];
      sheetName = sheet.getName();
      protections = file.getProtections(app.ProtectionType.RANGE);

      for (var i = 0; i < protections.length; i++) {
        var protection = protections[i];
        var range = protection.getRange();
        var sheetName = range.getSheet().getName();
        strRangeProtect = range.getA1Notation();
        if (strRangeProtect == strRange && sheetNames.indexOf(sheetName) > 0) {
          protection.remove();
        }      
      }           
    }  
  }  
}

Befor you start:

First you need to paste all the files into one folder, then copy it's id:

and change this lines:

  var idFolder = '0B79ClRnKS87QcW9XbVhrcFZxd28'; // change
  var sheetNames = ['1', '2', '3']; // change
  var strRange = 'A1'; // change

Google Sheets – Copy Columns on Cell Value Change

You should install a trigger to fire on edits and use its event object (denoted e above), for example as follows.

function CopyDataToLogbook(e) {
  var sss = e.source;            // edited spreadsheet
  var sheet = e.range.getSheet();    // edited sheet
  if (sheet.getName() == 'Recherche') {  // check where the edit is
    if (e.range.getColumn() >= 2 && e.range.getColumn() <= 5) { // check that the edit was to certain columns 
      var row = e.range.getRow();   // row where edit happened; 
      var values = sheet.getRange(row, 2, 1, 4).getValues(); // columns 2-5 in that row
      var tss = SpreadsheetApp.openById('...'); // target spreadsheet
      var ts = tss.getSheetByName('Logbook');   //  log sheet
      ts.appendRow(values[0]);                  // append row with our values 
    }
  }
}

Best Answer

Related Solutions

Google Sheets – Remove Protection from Range

Google Sheets – Copy Columns on Cell Value Change

Related Topic