Google表格:将当前日期行排除在保护范围之外 [英] Google Sheets: Exclude the current date row from protection

问题描述

我在Google表格中有一个表格，格式为:

I have a table in Google Sheets in the format:

A B C

日期日期库存需求

第2天第2天库存需求

等

其他人每天都需要填写库存和需求.但是，它们只应填写当天的需求和库存.还不允许他们更改以前的数据或提前填写未来几天的库存和需求. 因此，除了当前日期所在的行，我想保护整个工作表免受其他人的编辑.例如今天(23.09)，他们应该能够编辑B为23.09.2019的行，而没有其他内容.

Others are required to fill in inventory and demand every day. However, they should only fill out demand and inventory of the current day. They are also not allowed to change previous data or in advance fill in future days inventory and demand. Therefore, I want to protect the whole sheet from being edited by others, except the row with the current date. For example today (23.09) they should be able to edit the row where B is 23.09.2019 and nothing else.

有人可以帮我吗?谢谢.

Could somebody please help me? Thank you.

推荐答案

工作流程如下

• 浏览所有工作表行，将B列中的日期与今天进行比较
• 返回具有今天日期的行索引
• 删除所有现有的工作表保护
• 创建保护，以保护整个工作表，但以下情况除外包含今天日期的行与setUnprotectedRanges

The workflow can be as following

• Loop through all sheet rows comparing the date in column B against today
• Return the row index with today's date
• Remove all existing sheet protections
• Create a protection that protects the whole sheet with exception of the row containing today's date with setUnprotectedRanges

示例:

function myFunction() {
 var sheet=SpreadsheetApp.getActive().getActiveSheet();
 var range=sheet.getDataRange();
 var values=range.getValues();
 var today=new Date();
 today.setHours(0);
 today.setMinutes(0);
 today.setSeconds(0);
 today.setMilliseconds(0);
 var todayMs=today.getTime();
  for(var i=0;i<values.length;i++){
    var date=values[i][1];
    var dateMs=date.getTime();
    if(dateMs==todayMs){
      var row=i+1;
      break;
    }
  }  
  var protections=sheet.getProtections(SpreadsheetApp.ProtectionType.SHEET); 
 for (var i = 0; i < protections.length; i++) {
  var protection = protections[i];
  if (protection.canEdit()) {
    protection.remove();
   } 
 } 
 var myProtection = sheet.protect().setDescription('Sample protected range'); 
 var rangeToday=sheet.getRange(row, 1, 1, sheet.getLastColumn());
 myProtection.setUnprotectedRanges([rangeToday]);
 myProtection.removeEditors(myProtection.getEditors());
}

注意:此代码在找到包含以下内容的第一行后退出循环 今天的日期.如果您有几行具有相同的日期，则您 需要通过将row转换为可以 包含各种条目.

Note: This code exits the loop after finding the first row containing today's date. Should you have several rows with the same date, you need to modify the code by converting row into an array that can contain various entries.

这篇关于Google表格:将当前日期行排除在保护范围之外的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！