时间戳

首页 > 解决方案 > 带有保护的复制表并将新副本的名称更改为日期

google-apps-script - 带有保护的复制表并将新副本的名称更改为日期

问题描述

我有一个用于组数据输入的模板表。大部分工作表都是免费条目，但有些标题行我不想编辑，所以我将它们保护起来。我们每个月的每一天都有一个这些选项卡，每个月都有一个新工作表。

我想根据月份复制模板 30-31 次，并将工作表的标题设置为相应的日期（MM.dd.yy 即：11.02.20）。我在 A2 中设置了日期（即：11/01/2020）。

到目前为止，我尝试结合保护和日期更改，但我不断收到可变错误，然后有时它会双重创建工作表（如 11.06.20 然后停止）。

这是我尝试过的代码（并编辑和移动了几次）。

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());
   }
  }
 }
}

任何帮助将不胜感激。此外，这是新手，如果你不知道，有点菜鸟。因此，任何有助于成长的参考资料都会很棒。谢谢！

标签： google-apps-scriptgoogle-sheetsduplicatesprotection

解决方案

问题：

您将相同的变量 ( i) 用于两个不同的for循环，一个嵌套在另一个循环中。这会弄乱您的日期，从而导致您遇到错误。

解决方案：

更改内部循环的变量名称（例如，更改为j）：

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

进一步的问题：

  • 您将 protections 设置为sheet2，它对应于带有 name 的复制工作表11..20，但不对应于其余工作表（实际上，我不确定制作此副本的意义何在，所以我只需删除该行sheet2 = sheet.copyTo(ss).setName('11..20');）。为了为每个复制的工作表设置保护，您应该使用asn：
var p2 = asn.getRange(rangeNotation).protect();
  • 由于您要复制名为 的文件Template.01.20，因此获取活动工作表并将其存储在s. 我只是将提及的内容更改s为sheet（并删除该行var s = ss.getActiveSheet();，因为不需要它）：
var startDate = new Date(sheet.getRange(dateCell).getValue());
// ...
var asn = sheet.copyTo(ss);
  • 由于要复制的纸张数量取决于该月有多少天，我建议您动态查找该数字。您可以使用以下函数来执行此操作，例如（归功于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;
}

然后你可以在你的 main 函数中调用它：

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

代码示例：

因此，您的代码可能是这样的：

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

推荐阅读