当列>中的日期锁定时，24小时:Google表格-脚本编辑器? [英] Lock a sheet when date in column > 24 hours: Google Sheets - Scripteditor?

问题描述

为了防止其他贡献者造成数据丢失，我想为所有数据锁定整个工作表<今天.

In order to prevent data loss caused by other contributors, I'd like to lock an entire sheet for all data < today.

今天有必要进行输入和更改条目.

It needs to be possible to do input and make changes for entries today.

主文件的简单示例:示例-锁定<今天

因此，当A列中的日期<今天.

So, each row will lock for others when the date in column A < today.

此链接使我离我越来越近，但是我在

This link brought me closer but I'm having difficulties with

var range = ss.getRange('1:1').getValues()[0];

var range = ss.getRange('1:1').getValues()[0];

在第31行给我一个错误:"TYPE-ERROR:在对象中找不到函数getFullYear ..."

which gives me an error on line 31: "TYPE-ERROR: can't find function getFullYear in object..."

打开其他任何想法/代码.

Open to any other idea/code.

预先感谢您对我的帮助！

Thank you in advance for helping me out!

Qni

推荐答案

保护表(今天的行除外)

我从 pkowalczyk 中获取了链接到的代码，并对其进行了修改，以保护整个工作表，但不今天.此处.

I took the code you linked to from pkowalczyk and modified it to protect entire sheet except for the row that is for today. Much of this code is also available in the documentation here.

//https://developers.google.com/apps-script/reference/spreadsheet/protection#setUnprotectedRanges(Range)
//https://stackoverflow.com/a/43828395/7215091
function unlockTodaysRowFromSheetProtection() {
  var ss = SpreadsheetApp.getActive();
  var sh = ss.getSheetByName('ProtectRows');
  var protection=sh.protect().setDescription('Protected Sheet');
  protection.getRange().setBackground('#ffffff');
  var rg = sh.getRange(2,1,sh.getLastRow()-1,1);
  var vA = rg.getValues();
  var today = new Date();
  var todayRow = null;
  for (var i=0; i<vA.length; i++) {       
    if (today.isSameDateAs(vA[i][0])) {
      todayRow = i;
      break;
    }
  } 

  var rangeToUnProtect = sh.getRange(todayRow + 2,1,1,sh.getLastColumn());
  protection.setUnprotectedRanges([rangeToUnProtect]);
  protection.getRange().setBackground('#ffff00');//highlight protected range
  rangeToUnProtect.setBackground('#00ffff');//highlight unprotected range
  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
}

本节来自顺便说一句，您可以参考它以获取更多说明."this"是指代码中的今天，它只比较年，月和日.

This section came from Incidently and you can refer to it for more explanation. The 'this' refers to today in the code and it just compares year,month and day.

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

这是一个有趣的问题，因为我没有非常多地使用保护功能.我发现在运行代码时将受保护的图纸和范围"侧边栏设置得很方便.我突出显示了受保护和不受保护的范围，只是为了清楚它们是什么.

This was an interesting question as I've not used protection very much. I found it handy to have the Protected Sheets and Ranges sidebar up while running the code. I highlighted the protected and unprotected ranges just to be clear what they are.

尝试取消保护多行

这应该有所帮助.它会收集所有范围以在阵列中取消保护，并一次取消所有保护.

This should help. It collects all of the ranges to unprotect in an array and unprotects all at one time.

function unprotectTodaysRowsFromSheetProtection() {
  var ss = SpreadsheetApp.getActive();
  var sh = ss.getSheetByName('ProtectRows');
  var protection=sh.protect().setDescription('Protected Sheet');
  protection.getRange().setBackground('#ffffff');
  var rg = sh.getRange(2,1,sh.getLastRow()-1,1);
  var vA = rg.getValues();
  var today = new Date();
  var uprgA=[];
  for (var i=0; i<vA.length; i++) {       
    if (today.isSameDateAs(vA[i][0])) {
      uprgA.push(sh.getRange(i + 2,1,1,sh.getLastColumn()))
    }
  } 
  protection.setUnprotectedRanges(uprgA);
  protection.getRange().setBackground('#ffff00');
  for(var i=0;i<uprgA.length;i++){
    uprgA[i].setBackground('#00ffff');
  }
  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
}

这篇关于当列&gt;中的日期锁定时，24小时:Google表格-脚本编辑器?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！