在特定时间锁定 Google 表格中的单元格

问题描述

我想对我的 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 表格中的单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，WP2