Google scripts sheet creation (protection issue)

Question

Ok, stackoverflow.

I'll jump right into my question. I have a main workbook with a lot of information supplying (via. a query/importrange) the correct information to several individual workbooks. I update the main workbook every week and create new sheets for the individual workbooks every week with the following code:

function onOpen() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var pasteSheet = [ {name: "Paste Sheet", functionName: "copySheet"}];
ss.addMenu("Copy to Spreadsheets", pasteSheet);
}

function copySheet() {
var source = SpreadsheetApp.getActiveSpreadsheet();
var sheet = source.getSheets()[0];
var sheet2 = source.getSheets()[1];
var sourceFile = DriveApp.getFileById(source.getId());
var sourceFolder = sourceFile.getParents().next();
var folderFiles = sourceFolder.getFiles();
var thisFile; 

while (folderFiles.hasNext()) {
  thisFile = folderFiles.next();
  if (thisFile.getName() !== sourceFile.getName()){
    var currentSS = SpreadsheetApp.openById(thisFile.getId());
    sheet.copyTo(currentSS);
    sheet2.copyTo(currentSS);
    currentSS.getSheets()[currentSS.getSheets().length-2].setName('W6');
    currentSS.getSheets()[currentSS.getSheets().length-1].setName('W6 ISSUES');
  }    
};    
}

This code works perfectly in creating two new sheets in every individual workbook.

My problem: I've been working on different ways to try and include protection when running this script. What I need is to protect the individual sheets (W6 and W6 issues). First of W6 should be entirely protected except for a range A3:A20, where i need the owners of the respective individual sheets to be allowed to edit. 'W6 Issues' should be entirely protected.

How would i proceed to make this happen? Any help would be appreciated.

utphx utphx · Accepted Answer · 2017-02-07T01:17:03

Protection information can be found here: https://developers.google.com/apps-script/reference/spreadsheet/protection

Here is a sample to protect "W6 Issues"

var W6issues = currentSS.getSheets()[currentSS.getSheets().length-1].setName('W6 ISSUES');
var protection = W6issues.protect().setDescription('Sample protected sheet');

Do the same thing for "W6" and then use .remove() for the range you don't want protected, https://developers.google.com/apps-script/reference/spreadsheet/protection#remove

Google scripts sheet creation (protection issue)

1 Answers