Google Spreadsheet SCRIPT检查编辑单元格是否在特定范围内 [英] Google Spreadsheet SCRIPT Check if edited cell is in a specific range

问题描述

我需要检测是否在某个数据范围内对电子表格进行了更改，如果是，请设置当前更新时间。


问题是，我有一个电子表格，可以编辑标题和文本，而且我不希望在电子表格中更新特定单元格中的更新时间，但是当我编辑我希望更新时间改变。

这是我必须更新时间。

 函数onEdit（e）
 {
 var ss = SpreadsheetApp.getActiveSpreadsheet（）。getActiveSheet（）; 
 
 ss.getRange（G10）。setValue（new Date（））; 
 
 $ / code> 

如果我编辑某些单元格，一个范围B4：J6） 事件作为参数提供给您的 onEdit（）函数，它包含关于什么编辑。如果您想知道（e）是什么，那就是它。

由于 onEdit（）函数为每个编辑调用，您应该尽可能少地处理确定是否应该退出。通过使用传入的事件，您将需要更少的服务呼叫，因此效率更高。 Rasmus的答案将A1表示法转换为列和行编号的方式很好，但如果编辑范围是固定的，则可以简单地使用常量值进行比较 - 同样可以减少所需的处理时间。

 函数onEdit（e）
 {
 var editRange = {// B4：J6 
 top：4，
 bottom：6，
 left：2，
 right：10 
}; 
 
 //如果我们超出范围，退出
 var thisRow = e.range.getRow（）; 
 if（thisRow< editRange.top || thisRow> editRange.bottom）return; 
 
 var thisCol = e.range.getColumn（）; 
 if（thisCol< editRange.left || thisCol> editRange.right）return; 
 
 //我们在范围内;时间戳编辑
 var ss = e.range.getSheet（）; 
 ss.getRange（thisRow，7）//G是第7列
 .setValue（new Date（））; //设置编辑时间为G
} 
  

I need to detect if changes made to a spreadsheet are being made within a certain range of data and if so, set the current update time.

The issue is, I have a spreadsheet that I edit headers and text on and I do not want the update time in a specific cell to be updated on the spreadsheet but when I edit the data in a range of cells, I DO want the update time changed.

Here's what I have to update the time.

function onEdit(e) 
{
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  ss.getRange("G10").setValue(new Date());
} ​

I only want the date in G10 set if I edit certain cells (in a range "B4:J6")

解决方案

There is an Event provided as a parameter to your onEdit() function, and it contains the necessary information about what was edited. If you were wondering what that (e) was all about, this is it.

Since an onEdit() function is called for every edit, you should invest as little processing as possible in determining whether you should exit. By using the event that's passed in, you will require fewer Service calls, so will be more efficient. The way that Rasmus' answer converts the A1 notation to column and row numbers is good if you need to be flexible, but if the edit range is fixed, you can simply use constant values for comparisons - again, to reduce the processing time required.

function onEdit(e) 
{
  var editRange = { // B4:J6
    top : 4,
    bottom : 6,
    left : 2,
    right : 10
  };

  // Exit if we're out of range
  var thisRow = e.range.getRow();
  if (thisRow < editRange.top || thisRow > editRange.bottom) return;

  var thisCol = e.range.getColumn();
  if (thisCol < editRange.left || thisCol > editRange.right) return;

  // We're in range; timestamp the edit
  var ss = e.range.getSheet();
  ss.getRange(thisRow,7)   // "G" is column 7
    .setValue(new Date()); // Set time of edit in "G"
} ​

这篇关于Google Spreadsheet SCRIPT检查编辑单元格是否在特定范围内的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！