Duplicate the google sheet using script while remaining the permissions for protected sheet

user_15002748 0
Viewed 807

I have a script to duplicate the source file. Inside the source file, there are 3 protected sheets (not protected range) where I already set the permissions. When I used the script, it duplicate the source file perfectly. However, the permissions for the 3 protected sheets in the duplicated files changed. Instead of the original permissions for protected sheet in the source file, the permissions in duplicated file changed to Only you can edit this range. May I know how should I modified my script so that the permission for the protected sheet will be duplicated as well? Below is my script:

function copyfilefromsource() {
  var ui = SpreadsheetApp.getUi();
  var sheet_merge = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("List");
  var last_row = sheet_merge.getLastRow();
  var newsheet_firstname = null;
  var newsheet_surname = null;
  var newsheet_email = null;
  var source_folder = null;
  var file_owner = null;
  var dest_folder = null;
  var sheets_created = 0;
  var new_file = null;
  var source_file = null;
  var employee_id = null;
  var google_domain = null;
  var range = sheet_merge.getRange(1, 1, last_row, 4);
  var temp = null;
  source_file = DriveApp.getFileById(range.getCell(1, 2).getValue()); 
  dest_folder = DriveApp.getFolderById(range.getCell(2, 2).getValue()); 
    file_owner = range.getCell(3, 2).getValue();  
    google_domain = range.getCell(4, 2).getValue();   
  if (last_row <= 6) {
    SpreadsheetApp.getUi().alert("No rows to process!");
    return
  }
  for (var i = 7; i <= last_row; i++) { 
    if (range.getCell(i, 4).getValue() == '') {   
    employee_id = range.getCell(i, 1).getValue();       
    newsheet_email =  employee_id + google_domain;  
    newsheet_firstname = range.getCell(i, 2).getValue(); 
    newsheet_surname = range.getCell(i, 3).getValue();  
    new_file = source_file.makeCopy(employee_id, dest_folder);
    new_file.setOwner(file_owner);  
    new_file.addViewer(newsheet_email); 
    new_file.setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.EDIT);
    SpreadsheetApp.getActiveSheet().getRange(i,1).setFormula('=HYPERLINK("' + new_file.getUrl() +'/","'+employee_id+'")');
    SpreadsheetApp.getActiveSheet().getRange(i,4).setValue(new_file.getId());
    sheets_created++; 
    }
  }
  ui.alert(sheets_created + " files were created!")
}

function testFolder(folderName){
  var exist = true;
  try{var testFolder = DocsList.getFolder(folderName)}
  catch(err){exist=false}
  return exist;
}

Any advise will be greatly appreciated!

google-apps-script google-sheets protected duplicates permissions
1 Answers

I believe your goal as follows.

  • You want to copy a Google Spreadsheet.
  • When the Google Spreadsheet is copied, you want to copy the protected ranges and sheets.

Issue and workaround:

Unfortunately, in the current stage, when a Google Spreadsheet is copied, it seems that the protected ranges are not copied. And, unfortunately, although the protected sheet can be copied, the condition of the protected sheet cannot be correctly copied. This situation is the same with the manual copy and the copy by a script. So in this case, in order to achieve your goal, it is required to copy the protected ranges and sheets after the source Spreadsheet was copied.

In this answer, I would like to propose to achieve your goal using Sheets API. Because I thought that when the Sheets API is used, the script might be able to be simpler.

When your script is modified, it becomes as follows.

Modified script:

Please add the following function to your script. And, please enable Sheets API at Advanced Google services.

function copyProtectedRanges(srcId, dstId) {
  const dstSS = SpreadsheetApp.openById(dstId);
  dstSS.getProtections(SpreadsheetApp.ProtectionType.SHEET).forEach(s => s.remove());
  SpreadsheetApp.flush();
  const dstSheets = dstSS.getSheets().map(s => s.getSheetId());
  const requests = Sheets.Spreadsheets.get(srcId, {fields: "sheets/protectedRanges"}).sheets.reduce((ar, s, i) => {
    if (s.protectedRanges && s.protectedRanges.length > 0) {
      const temp = s.protectedRanges.map(e => {
        delete e.protectedRangeId;
        e.range.sheetId = dstSheets[i];
        return {addProtectedRange: {protectedRange: e}};
      });
      ar = ar.concat(temp);
    }
    return ar;
  }, []);
  Sheets.Spreadsheets.batchUpdate({requests: requests}, dstId);
}

And, please modify your script as follows.

From:

new_file = source_file.makeCopy(employee_id, dest_folder);
new_file.setOwner(file_owner);
new_file.addViewer(newsheet_email);
new_file.setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.EDIT);
SpreadsheetApp.getActiveSheet().getRange(i, 1).setFormula('=HYPERLINK("' + new_file.getUrl() + '/","' + employee_id + '")');

To:

var dstSS = new_file = source_file.makeCopy(employee_id, dest_folder); // Modified
new_file.setOwner(file_owner);
new_file.addViewer(newsheet_email);
new_file.setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.EDIT);

copyProtectedRanges(source_file.getId(), dstSS.getId()); // Added

SpreadsheetApp.getActiveSheet().getRange(i, 1).setFormula('=HYPERLINK("' + new_file.getUrl() + '/","' + employee_id + '")');

Note:

  • I'm not sure about the number of copy of Spreadsheet in the for loop. So when the process time is over the maximum execution time of 6 minutes for Google Apps Script, please reduce the number of processes by modifying your script. Please be careful this.

References:

user_71086530
Related