在Google Spreadsheets中使用onEdit实时编辑单元格内容 [英] Using onEdit in Google Spreadsheets to edit cell contents live

问题描述

我很难让我的onEdit脚本正常工作.我的工作有一个电话日志表，可用于跟踪电话(日期，时间，分配的技术等).我们还有另一个工作表设置为数据透视表报告，称为技术电话".该工作表应该可以自动计算每项技术每天的通话次数.它可以正常工作，但是问题是，只要在电话日志"工作表的分配的技术"列中的单元格中添加了额外的空格，技术电话"工作表就将其视为另一个人，因此好像有4种不同的鲍勃等.

I'm having the hardest time getting my onEdit script to work. My work has a Phone Log sheet that we use to track phone calls (Date, Time, Assigned Tech, etc). We have another sheet set up as a pivot table report called Phone Calls by Tech. This sheet is supposed to automatically count the number of calls per day per tech. It works, but the problem is any time extra whitespace gets added to a cell in the Assigned Tech column in the Phone Log sheet, the Phone Calls by Tech sheet treats that as a whole other person, so it appears as though there's 4 different "Bob"s, etc.

我已经成功编写了一个脚本，可以通过修剪Assigned Tech列的每个单元格中的空格来清理此空白，并且我正尝试将其转换为一个onEdit脚本来完成相同的任务.

I've already successfully written a script to clean this up by trimming the whitespace in each cell of the Assigned Tech column, and I'm trying to translate this into an onEdit script to accomplish the same thing live.

function onEdit(e) 
{ 
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getActiveSheet();
  var r = ss.getActiveRange();
}

function myOnEdit() 
{
  function replacer( str, p1, p2, offset, s ) {
    return p1 
      }

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var cell = ss.getActiveCell().getA1Notation();
  var tsheet = "Phone Log";
  var sname = ss.getName();

  if (sname = tsheet) //Are we in the Phone Log sheet?
  {

    var r = ss.getActiveRange();
    var column = r.getColumn();

    if (column == 4) //Are we editing a cell in the "Assigned Tech" column?
    {

      var value = cell.getValue();
      if ( value == String )
      {
        value = value.replace( /^\s*(.*?)\s*$/gm , replacer );
        cell.setValue( value) ;
      } 
    }
  }

}

因此，基本上，我正在尝试获取要编辑的单元格值，即"Bob"，删除空格并将输入的值替换为已编辑的"Bob".当我在脚本编辑器中运行上述脚本时，没有出现任何错误，但是它不起作用.我确实安装了onMyEdit()作为onEdit触发器.

So basically, I'm trying to get the cell value being edited, i.e. " Bob ", remove the whitespace and replace the inputted value with the edited one, "Bob". I get no errors when I run the above script in script editor, but it just doesn't work. I did install onMyEdit() as an onEdit trigger.

有什么想法吗?

推荐答案

我很惊讶这对您有用.由于getA1Notation()调用，cell包含一个字符串，因此将不支持.getValue()或.setValue()方法.

I'm surprised this works for you. Because of the getA1Notation() call, cell contains a String, so which won't support the .getValue() or .setValue() methods.

有效"工作表的概念单元格不适用于onEdit()触发函数；它们在自己的执行实例中被调用，并且事件被传递给它们以提供上下文.请参见了解事件.

The concept of "Active" Sheets & Cells isn't really applicable to onEdit() trigger functions; they are invoked in their own execution instance, and an event is passed to them to provide context. See Understanding Events.

您还使用=犯了一个简单但灾难性的错误.单个=是分配操作，并且有两种比较形式:==和===. (第一个比较两个对象的内容，第二个也比较这些对象的类型.)当您具有if (sname = tsheet)时，它不是在比较工作表的名称，而是将tsheet的内容分配给sname ，然后对sname进行布尔测试. (这种类型的错误不会被解释器捕获，因为它是完全有效的……这不是您想要的.)

You are also making a simple-but-catastrophic mistake with =. A single = is an assignment operation, and there are two flavors of comparison, == and ===. (The first compares the content of two objects, the second also compares the type of those objects.) When you have if (sname = tsheet), it is not comparing the names of the sheets, it's assigning the content of tsheet to sname, then doing a boolean test of sname. (This type of error is not caught by the interpreter, because it's perfectly valid... it's just not what you wanted.)

在检查if ( value == String )时，是否意味着要检查值的TYPE是否为字符串?如果是这样，可以这样进行:if (typeof value === 'string')

When you are checking if ( value == String ), do you mean to check if the TYPE of the value is a string? If so, that would be done like this: if (typeof value === 'string')

function myOnEdit(e) 
{
  function replacer( str, p1, p2, offset, s ) {
    return p1 
      }

  var ss = e.source;
  var cell = e.range;
  var tsheet = "Phone Log";
  var sname = ss.getName();

  if (sname === tsheet) //Are we in the Phone Log sheet?
  {

    var column = cell.getColumn();

    if (column == 4) //Are we editing a cell in the "Assigned Tech" column?
    {

      var value = cell.getValue();
      if (typeof value === 'string')
      {
        value = value.replace( /^\s*(.*?)\s*$/gm , replacer );
        cell.setValue( value );
      } 
    }
  }
}

这篇关于在Google Spreadsheets中使用onEdit实时编辑单元格内容的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！