Google-sheets – Duplicate sheet but keep protected range with exact same user as only editor

google sheetsgoogle-apps-script

The following code duplicates a sheet based on a template, and copies over the protected range permissions from the template sheet to the new sheet.

However, the permission range is now there in the new sheet, but not with the same user.

The template sheet has only one user that can edit, but for the new sheet, while the same range is restricted, all users from the entire spreadsheet can edit the new sheet.

Does anyone happen to know if I can make it be just the exact same user as the template? And not like the user that initiates the script but only the same user as the template. Here's the code:

function newSheet() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("New Sheet Template");
  var protections = sheet.getProtections(SpreadsheetApp.ProtectionType.RANGE);
  var dateI = Browser.inputBox('Enter Date: ','(DD/MM/YYYY)', Browser.Buttons.OK_CANCEL);
  var nameSheet= "Sheet - "+dateI
   if (dateI == "cancel" ) {
    }
    else {
      ss.insertSheet(nameSheet, {template: sheet});
      ss.getRange('B4').setValue(dateI);    
      for (var i = 0; i < protections.length; i++) {
        var sheet2 = ss.getSheetByName("Sheet - "+dateI);
        var p = protections[i];
        var rangeNotation = p.getRange().getA1Notation();
        var p2 = sheet2.getRange(rangeNotation).protect();
        p2.setDescription(p.getDescription());
        p2.setWarningOnly(p.isWarningOnly());
    }
    }
}

Best Answer

Try the below script

In Google Sheets how do I duplicate a sheet along with its permission

It works from the "owner" account ... not from "editor" account. I have tested and it is working

Code

var app = SpreadsheetApp.getUi();
var ss = SpreadsheetApp.getActive();

function onOpen() {
  app.createMenu("Ranges")
    .addItem("Set ranges", "setRanges")
    .addItem("Delete ranges", "deleteRanges")
    .addToUi();
}

function setRanges() {
  var text = app.prompt("Give string", "Like A1:A10, Sheet2!A4:B5, This is a test!A:A", app.ButtonSet.OK_CANCEL); 
  if(text.getSelectedButton() == app.Button.OK) {
    var ranges = text.getResponseText().split(",");  

    for(var i = 0, iLen = ranges.length; i < iLen; i++) {
      var value, range, index, sname, split, sh;
      value = ranges[i].trim(), split = value.split("!");

      switch(split.length) {
        case 1:
          range = value;
          sh = ss.getActiveSheet();
          break;
        case 2:
          range = value;
          sh = ss.getSheetByName(String(split[0].trim()));
          break;
        default:
          index = value.lastIndexOf("!");
          range = "'" + value.slice(0, index) + "'!" + split[split.length - 1].trim();
          sh = ss.getSheetByName(String(value.slice(0, index)));
          break; 
      }

      try {
        sh.getRange(String(range))
          .protect()
          .setDescription("Range" + i);
      } catch(e) {
        throw "Range: " + (i) + " doesn't exist";
      }
    }
  }
}

function deleteRanges() {
  var protections = ss.getProtections(SpreadsheetApp.ProtectionType.RANGE);
  for (var j = 0, jLen = protections.length; j < jLen; j++) {
    protections[j].remove();
  }
}

Explained

The first two variables are usable throughout the code. The onOpen() function creates a menu item upon file opening and adds two items. The setRanges() function initially creates a popup box (prompt) to capture the string. The response is then split using the comma as delimiter.

Now we need to iterate though the different ranges in order to prepare the protected ranges. Within the iteration, the response is validated once more by splitting it using the exclamation mark as delimiter. This will yield either one or more results. The switch method will determine the buildup of the sheet name and the range.

Now that the sheet has been defined, the range (getRange()) can be protected (protect()) and the try / catch allows for some error handling.

Screenshot

enter image description here

Note

This script can also cope with the following sheet name examples:

This is a test
Test!Test

Example

I've created an example file for you: setting up multiple protected ranges
Add this script in the script editor (Tools > Script editor) and press the bug button: enter image description here
Now you can use the script.

Google-sheets – Auto sort data but by sheet not range

The sort method belongs to the class Range, not to the class Sheet. Simply put, it always operates on a range. If you want that range to include all the data in a sheet, use the method getDataRange of the class Sheet.

var range = sh0.getDataRange();
range.sort({column: 2, ascending: false});

For completeness, I present a customizable script for automatic sorting.

  function onEdit() {
    var sheetToSort = "Sheet Name"; // enter name of sheet 
    var columnEdited = 1;           // sort when this column is edited
    var columnSort = 2;             // sort by this column
    var sortAscending = false;      // true or false here

    var sheet = SpreadsheetApp.getActiveSheet();
    var editedCell = sheet.getActiveRange().getColumnIndex();
    if (sheet.getSheetName() == sheetToSort && editedCell == columnEdited) { 
      var range = sheet.getDataRange();
      range.sort({column: columnSort, ascending: sortAscending});
    }
  }