Copy Sheet with protections and change new Duplicate's name to a date

Question

I have a template sheet for group data entry. Most of the sheet is free entry, but there are title rows that I don't want edited so I have them protected. We have one of these tabs for each day of the month and a new Sheet for each month.

I want to copy the template 30-31 times depending on the month and have the title of the sheet be the corresponding date (MM.dd.yy ie: 11.02.20). I have the Date set in A2 (ie: 11/01/2020).

So far I tried combining a protections and a date change, but I keep getting variable errors and then sometimes it double creates sheets (like 11.06.20 and then stops).

This is the code I've tried (and edited and moved around a few times).

function duplicateSheetWithProtections() {
  var ss = SpreadsheetApp.getActiveSpreadsheet(); 
  var s = ss.getActiveSheet();
  var dateCell = "A2";
  sheet = ss.getSheetByName('Template.01.20');
  sheet2 = sheet.copyTo(ss).setName('11..20'); 
  var N = 30;
  var startDate = new Date(s.getRange(dateCell).getValue());
  var day = startDate.getDate();
  var month = startDate.getMonth();
  var year = startDate.getFullYear();
  for (var i = 0; i < N; i++) {
    var asn = s.copyTo(ss);
    var thisSheetDate = new Date(year, month, day+(i+1));
    asn.getRange(dateCell).setValue(thisSheetDate);
    asn.setName(Utilities.formatDate(thisSheetDate, "GMT-08:00", "MM.dd.yy"));
  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());
   }
  }
 }
}

Any help would be greatly appreciated. Also, new to this and if you couldn't tell, kind of a noob. So any references to help grow would be awesome. Thanks!

Answer 1

Issue:

You are using the same variable (i) for two different for loops, one nested inside the other. This is messing up with your dates, causing the error you're getting.

Solution:

Change the variable name of the inner loop (for example, to j):

  for (var j = 0; j < protections.length; j++) {
    var p = protections[j];

Further issues:

• You are setting protections to sheet2, which corresponds to the copied sheet with name 11..20, but not to the rest of sheets (actually, I'm not sure what's the point of making this copy, so I'd just delete the line sheet2 = sheet.copyTo(ss).setName('11..20');). In order to set the protections to each copied sheet, you should use asn instead:

var p2 = asn.getRange(rangeNotation).protect();

• Since you want to copy the file named Template.01.20, there is no point in getting the active sheet and storing it in s. I'd just change the mentions of s to sheet (and remove the line var s = ss.getActiveSheet();, since it's not needed):

var startDate = new Date(sheet.getRange(dateCell).getValue());
// ...
var asn = sheet.copyTo(ss);

• Since the number of sheets to copy depends on how many days the month has, I'd suggest you to dynamically find that number. You can do that using the following function, for example (credits to Juan Mendes):

function getDaysInMonth(year, month, day) {
  var date = new Date(year, month, day);
  var days = [];
  while (date.getMonth() === month) {
    days.push(new Date(date));
    date.setDate(date.getDate() + 1);
  }
  return days;
}

Which you could then call in your main function:

  var dates = getDaysInMonth(year, month, day + 1);
  for (var i = 0; i < dates.length; i++) {
    var asn = sheet.copyTo(ss);
    var thisSheetDate = dates[i];

Code sample:

Therefore, your code could be something like this instead:

function duplicateSheetWithProtections() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var dateCell = "A2";
  var sheet = ss.getSheetByName('Template.01.20');
  var startDate = new Date(sheet.getRange(dateCell).getValue());
  var day = startDate.getDate();
  var month = startDate.getMonth();
  var year = startDate.getFullYear();
  var dates = getDaysInMonth(year, month, day + 1);
  for (var i = 0; i < dates.length; i++) {
    var asn = sheet.copyTo(ss);
    var thisSheetDate = dates[i];
    asn.getRange(dateCell).setValue(thisSheetDate);
    asn.setName(Utilities.formatDate(thisSheetDate, "GMT-08:00", "MM.dd.yy"));
    var protections = sheet.getProtections(SpreadsheetApp.ProtectionType.RANGE);
    for (var j = 0; j < protections.length; j++) {
      var p = protections[j];
      var rangeNotation = p.getRange().getA1Notation();
      var p2 = asn.getRange(rangeNotation).protect();
      p2.setDescription(p.getDescription());
      p2.setWarningOnly(p.isWarningOnly());
      if (!p.isWarningOnly()) {
        p2.removeEditors(p2.getEditors());
        p2.addEditors(p.getEditors());
      }
    }
  }
}

function getDaysInMonth(year, month, day) {
  var date = new Date(year, month, day);
  var days = [];
  while (date.getMonth() === month) {
    days.push(new Date(date));
    date.setDate(date.getDate() + 1);
  }
  return days;
}