CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsspreadsheet-protection
weizer
weizer

Reputation: 1127

How to remain the same permission of protected ranges in a new duplicated google sheet created by using google apps script?

I have a script which I used to duplicate a source sheet. For the source sheet, I protected some of the ranges and only allow Student A and Student B to edit those protected ranges, the rest of the editors in the source sheet are not allowed to do anything within those ranges.

However, when I checked on the duplicated sheets created by the script, Student A and Student B are not allowed to edit the protected ranges anymore although all the protected ranges in the duplicated sheets remain the same. Only me, the owner of the source/duplicated sheets are able to edit the protected ranges.

May I know how should I modify my script so that the script is able to let whoever have the permission to edit the protected ranges in source sheet are able to edit the protected ranges in the duplicated sheets as well? Any help will be greatly appreciated! Thank you.

This is my script:

function copyfilefromsource() {
  var ui = SpreadsheetApp.getUi();
  var sheet_merge = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("List");
  var last_row = sheet_merge.getLastRow();
  var newsheet_email = null;
  var file_owner = null;
  var dest_folder = null;
  var new_file = null;
  var source_file = null;
  var student_id = null;
  var google_domain = null;
  var range = sheet_merge.getRange(1, 1, last_row, 4);
  
  source_file = DriveApp.getFileById(range.getCell(1, 2).getValue()); //gets the source file to copy  
  dest_folder = DriveApp.getFolderById(range.getCell(2, 2).getValue()); //gets the ID of the folder to place the copied files into
  file_owner = range.getCell(3, 2).getValue();  //person to own the files
  google_domain = range.getCell(4, 2).getValue();  //extension of the email address 
  
  for (var i = 7; i <= last_row; i++) {
    if (range.getCell(i, 4).getValue() == '') {   
      student_id = range.getCell(i, 1).getValue();      
      newsheet_email =  student_id + google_domain;  
      newsheet_firstname = range.getCell(i, 2).getValue(); 
      newsheet_surname = range.getCell(i, 3).getValue();     
      new_file = source_file.makeCopy(student_id, dest_folder)
      new_file.setOwner(file_owner);  
      new_file.addViewer(newsheet_email); 
      new_file.setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.EDIT); 
      var id_ss = new_file.getId();
      SpreadsheetApp.openById(id_ss).getSheets()[0].getRange(2, 2).setValue(student_id);
      SpreadsheetApp.getActiveSheet().getRange(i,1).setFormula('=HYPERLINK("' + new_file.getUrl() +'/","'+student_id+'")');
      SpreadsheetApp.getActiveSheet().getRange(i,4).setValue(new_file.getId());
    }
  }
}

Upvotes: 0

Views: 151

Answers (1)

arul selvan
arul selvan

Reputation: 624

Please look at the below example.

function duplicateSheetWithProtections() {
  var ss = SpreadsheetApp.getActiveSpreadsheet(); 
  sheet = ss.getSheetByName('Template');
  sheet2 = sheet.copyTo(ss).setName('My Copy'); 
  var protections = sheet.getProtections(SpreadsheetApp.ProtectionType.RANGE);
  for (var i = 0; i < protections.length; i++) {
    var p = protections[i];
    var rangeNotation = p.getRange().getA1Notation();
    var p2 = sheet2.getRange(rangeNotation).protect();
    p2.setDescription(p.getDescription());
    p2.setWarningOnly(p.isWarningOnly());
    if (!p.isWarningOnly()) {
      p2.removeEditors(p2.getEditors());
      p2.addEditors(p.getEditors());
      // p2.setDomainEdit(p.canDomainEdit()); //  only if using an Apps domain 
   }
  }
}

https://webapps.stackexchange.com/questions/86984/in-google-sheets-how-do-i-duplicate-a-sheet-along-with-its-permission

Upvotes: 1

Related Questions