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

问题描述

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

问题是，我有一个用于编辑标题和文本的电子表格，我不希望在电子表格上更新特定单元格中的更新时间，但是当我编辑一系列单元格中的数据时，我确实想要更新时间变了.

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());
} ​

如果我编辑某些单元格(在B4:J6"范围内)，我只希望设置 G10 中的日期

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

推荐答案

有一个事件 作为 onEdit() 函数的参数提供，它包含有关编辑内容的必要信息.如果您想知道 (e) 的全部内容，就是这样.

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.

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

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屋！