在每个日期过去时自动锁定范围(列)吗? [英] Automatically locking a range (column) as each date passes?

问题描述

我有一张工作表，员工将每天更新有关当天完成的任务的信息.每列的标题行都有一个日期(本例中为第3行)，第二天结束后，我希望该列锁定，因此除我本人和其他人之外，无法对其进行进一步的编辑.这是为了防止人们掩盖错误或意外更改或删除数据.

I have a sheet that employees will update daily with information about tasks done that day. Each column has a date in the header row (row 3 in this case), and after the end of the following day I want that column to lock so it cannot be edited further except by myself and one other. This is to prevent people from covering up mistakes or accidentally changing or deleting data.

我正在寻找可以完成此任务的脚本或其他东西.该工作表有大约45个标签，我需要将相同的标签应用于所有标签. 我的想法可能是根据标题行中的日期在某个时间触发的脚本，因此，如果日期为2017年5月5日，则相应的列将在6日午夜锁定自己.

I am looking for a script or something that will accomplish this. This sheet has about 45 tabs and I need the same thing applied to all of them. My idea is possibly a script that triggers at a certain time based off the date in the header row, so if the date is May 5th 2017, the respective column would lock itself at midnight on the 6th.

指向我的工作表副本的链接，减去数据为这里.

A link to a copy of my sheet, minus data is here.

或者，如果有一种方法可以在输入最新数据后24小时内简单地锁定任何单元格，并防止除选定人员以外的所有人进行进一步编辑，如果无法采用理想的方法，这也可能会起作用.

Alternatively, if there is a way to simply lock any cell 24 hours after the most recent data is entered into it, and prevent further editing by everyone except select people, that could work too if the ideal method isn't doable.

推荐答案

是的，有一种方法可以实现.

Yes, there is a way to do this.

我将简要描述解决方案:

I will briefly describe the solution:

1. 假设第一行的1:1包含连续的日期.
2. 创建函数lockColumns，该函数将创建新的受保护的范围
3. 将功能lockColumns添加到时间触发，该触发每天在0:01到1:00 am之间触发.

1. Let's say that the first row has 1:1 contains consecutive dates.
2. Create function lockColumns which would create new protected range.
3. Add function lockColumns to time trigger, which triggers every day between 0:01 and 1:00 am.

现在有一些代码:

function lockColumns() {
  var ss = SpreadsheetApp.getActive().getSheetByName('Sheet 1')
  var range = ss.getRange('1:1').getValues()[0];
  var today = new Date();
  var todayCol = null;
  for (var i=0; i<range.length; i++) {       
    if (today.isSameDateAs(range[i])) {
      todayCol = i;
      break;
    }
  } 

  var rangeToProtect = ss.getRange(1, todayCol +1, ss.getMaxRows(), 1)
  var protection = rangeToProtect.protect().setDescription('Protected range');

  // Ensure the current user is an editor before removing others. Otherwise, if the user's edit
  // permission comes from a group, the script will throw an exception upon removing the group.
  var me = Session.getEffectiveUser();
  protection.addEditor(me);  
  protection.removeEditors(protection.getEditors());
  if (protection.canDomainEdit()) {
    protection.setDomainEdit(false);
  }
  protection.addEditor('[e-mail]'); // second person with edit permissions
}

/*
http://stackoverflow.com/a/4428396/2351523
*/
Date.prototype.isSameDateAs = function(pDate) {
  return (
    this.getFullYear() === pDate.getFullYear() &&
    this.getMonth() === pDate.getMonth() &&
    this.getDate() === pDate.getDate()
  );
}

这篇关于在每个日期过去时自动锁定范围(列)吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！