在特定时间锁定 Google 表格中的单元格 [英] Locking cells in Google Sheets at a specific time

问题描述

我想对我的 Google 表格单元格进行编码，使某些单元格每天在特定时间自动锁定.我应该可以编辑它，但不能编辑我的合作者.

我该如何解决这个问题?

在示例表中(此处链接)，您将看到某些日期(15/7/2015-17/7/2015).我想对其进行编码，以便每天晚上 10 点锁定每个日期(例如 A1:B6).

解决方案

As @NightShadeQueen 建议 时间驱动的触发器和protect()-可以使用 Class Range 的方法.

1. 在工作表中打开脚本编辑器(工具 > 脚本编辑器...)
2. 创建锁定范围的代码，可能如下所示:

<小时>

如果您的工作表看起来不像示例工作表，您将不得不修改代码.特别是 lockRanges() 函数，它定义了哪些范围应该受到保护.目前，它从单元格 A1 开始，一次向下 7 行，直到到达工作表的末尾.

var ss = SpreadsheetApp.getActiveSpreadsheet();var sheet = ss.getSheetByName('Sheet1');//在此处插入工作表名称功能锁范围(){//第一个要锁定的单元格var 行 = 1;var col = 1;//获取工作表中包含数据的最后一行var lastRow = sheet.getLastRow();//循环直到通过sheet中的最后一行while(row <= lastRow){//锁定当前单元格锁定范围(行，列)；//从当前(下一个日期)转到第 7 行行 = 行 + 7；}}功能锁范围(行，列){var range = sheet.getRange(row, col);//创建保护对象.设置描述，任何你喜欢的.var protection = range.protect().setDescription('Protected, row ' + row);//在删除其他人之前，确保当前用户是编辑者.否则，如果用户的编辑//权限来自组，删除组时脚本将抛出异常.var me = Session.getEffectiveUser();protection.addEditor(me);protection.removeEditors(protection.getEditors());如果(保护.canDomainEdit()){protection.setDomainEdit(false);}}

3. 添加时间驱动触发器
   1. 仍在脚本编辑器中；转到资源 > 当前项目的触发器.
   2. 创建一个新的触发器
   3. 选择Run = lockRanges
   4. 选择 Events = Time-driven、Day timer、晚上 10 点到晚上 11 点(或任何你想要的时间)

<块引用>

注意:时间可能略有随机(阅读更多)

<小时>

就是这样，现在范围将在您选择的时间受到保护.与您手动保护的范围一样，它们显示在数据 > 受保护的工作表和范围中.

有什么不清楚的吗?问吧！

I want to code my Google Sheets cells in a way that certain cells automatically lock at a specific time every day. I should be able to edit it, but not my collaborators.

How do I pull this off?

In the sample sheet (link here), you will see certain dates (15/7/2015-17/7/2015). I want to code it so that each date (eg A1:B6) is locked daily, 10 pm.

解决方案

As @NightShadeQueen suggested a combination of Time-driven triggers and the protect()-method of the Class Range can be used.

1. Open the script editor in the sheet (Tools > Script editor...)
2. Create code that locks the ranges, might look something like this:

---

You'll have to modify the code if your sheet doesn't look like the Sample sheet. Especially the lockRanges()-function, which defines which ranges should be protected. Currently, it starts with cell A1, and steps down 7 rows at a time until the end of the sheet is reached.

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('Sheet1'); //INSERT SHEET NAME HERE

function lockRanges() {
  //First cell to lock
  var row = 1;
  var col = 1;

  // Get last row with data in sheet
  var lastRow = sheet.getLastRow();

  //Loop until last row in sheet is passed
  while(row <= lastRow){
    //Lock current cell
    lockRange(row, col);

    // Go to row 7 steps down from current (the next date)
    row = row + 7;
  }
}



function lockRange(row, col){
  var range = sheet.getRange(row, col);

  // Create protection object. Set description, anything you like.
  var protection = range.protect().setDescription('Protected, row ' + row);

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

3. Add the Time-driven trigger
   1. Still in the Script editor; go to Resources > Current project's triggers.
   2. Create a new trigger
   3. Choose Run = lockRanges
   4. Choose Events = Time-driven, Day timer, 10pm to 11pm (or whenever you want)

NOTE: The time may be slightly randomized (read more)

---

That's it, now the ranges will be protected at the time you chose. As with ranges you protect manually, they show up in Data > Protected sheets and ranges.

Something unclear? Ask away!

这篇关于在特定时间锁定 Google 表格中的单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！