Google Sheets – How to Make Duplicates with Permissions

google sheetsgoogle-apps-script

I am currently working on trying to make duplicates with permissions using google spreadsheet. There was a link that I saw that explained how to approach this(Google Sheet Duplicate with Permission). I used scenario 2 in which it explains the permission for it. The code below is what the responder came up with and is something that I want to implement for the situation that I am trying to work on.

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 
        } 
    }
}

However, I am running into an issue where I get an error in line 4 from the response the user put in the link I have attached. In line 4 I get an error that says

TypeError: Cannot read property 'copyTo' of null (line 4, file "Code")

I have been trying to debug the problem, but I still not understanding why the problem still occurs. Would definitely like to know what I missed or did wrong.

Best Answer

The referred error message means that ss.getSheetByName('Template') in

sheet = ss.getSheetByName('Template');

returned null. This occurred because your spreadsheet doesn't include a sheet named Template.

Related Solutions

Google Sheets Conditional Formatting – Change Row Color Based on Value

In new Google Sheets if you select A3:Z20 and Format, Conditional formatting... and enter:

=$N3<>""

for Custom formula is with a colour of your choice and Range: A3:Z20 then in the range A3:Z20 every cell should be coloured if the cell in Column N for the corresponding row is not empty.

Since such formatting is conditional the IF is not normally required.

Google Apps Script – Loop Through All Pages Except the First

First, the error: shs is an array, since you populate it with ss.getSheets(), so it doesn't have a method getActiveCell(). However, the elements of the array has that method.

You are also referencing a variable sheet, which you haven't defined anywhere.

Currently, your code is more suitable for going through a single sheet, you need to iterate through the shs array to have it operate on all sheets.

The following function will go through all cells in all sheets:

function onEdit(event){  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var shs = ss.getSheets(), num = shs.length;
  var sheetName = [] 

  for (var sheetIdx = 0; sheetIdx < shs.length; sheetIdx++) {
      var sheet = shs[sheetIdx];

      for(var i = 1; i<num; i++) {
      ...
   }
}

But you wanted to skip the first sheet, so let's start with element 1 in the array (since the array is 0-based, element 1 is the second sheet):

function onEdit(event){  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var shs = ss.getSheets(), num = shs.length;
  var sheetName = [] 

  for (var sheetIdx = 1; sheetIdx < shs.length; sheetIdx++) {
      var sheet = shs[sheetIdx];

      for(var i = 1; i<num; i++) {
        var editedCell = shs.getActiveCell();
        var columnToSortBy = 1;
        var tableRange = "A2:G99"; // What to sort.

        if(editedCell.getColumn() == columnToSortBy){   
          var range = sheet.getRange(tableRange);
          range.sort( { column : columnToSortBy, ascending: false });
        }
      }

   }
}

As for your comment, your code

function onEdit(event) {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    for (var n in ss.getSheets()) {
        var sheet = ss.getSheets()[n];
        var name = sheet.getName();
        if (name == 'Dashboard') 
           return;
        var editedCell = ss.getActiveCell();
        var columnToSortBy = 1;
        var tableRange = "A2:G99";
        if (editedCell.getColumn() == columnToSortBy) {
            var range = sheet.getRange(tableRange);
            range.sort({column: columnToSortBy, ascending: false});
        }
    }
}

could work, if you replace return with continue. You don't want return, it ends the script. continue skips to the next element in the for loop.

Best Answer

Related Solutions

Google Sheets Conditional Formatting – Change Row Color Based on Value

Google Apps Script – Loop Through All Pages Except the First

Related Topic