无法通过Google Apps脚本检测Google表格中的行插入? [英] Unable to detect Row inserts in Google sheets through google apps scripts?

问题描述

我是Google应用脚本的新手.当前，我正在尝试创建一个插入行检测器，并根据该行顶部的值更新该新行中的值.阅读文档和在网上其他东西上，我创建了以下代码.但是问题在于它不起作用.

I'm new to Google app scripting. Currently, am trying to create a insert row detector and update values in that new row based on values of the row right on top of it. Reading the docs & other stuff online, I have created the following code. But the issue is that it doesn't work.

如何创建通用行检测器或如何解决此问题?

How can I create a generic row detector or how can I fix this?

代码:

function onChange(e){ 
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange('A1').setBackground('green');
  //e.range.setNote('Last modified: ' + new Date().toLocaleTimeString() + ' getActiveUser().getEmail(): ' + Session.getEffectiveUser() );
  if (e.changeType == "INSERT_ROW") {
    sheet.getRange('A1').setBackground('red');
    // a row or many rows were inserted

    if (e.source.getActiveSheet().getName() == "UIDAICheck") {
      // Let's set the formula for calculating the End Date / Time of inserted lines    
      sheet.getRange('A1').setBackground('blue');
      var iRow = e.source.getActiveSheet().getActiveRange().getRow();
      var iRows = e.source.getActiveSheet().getActiveRange().getNumRows(); 

      var aRange = SpreadsheetApp.getActiveSpreadsheet().getActiveRange();
      aRange.setValue('555')

    }
  }
}

如果有与代码无关的更改，例如安装可安装的触发器或其他东西，请分享我可能如何连接这些点以及如何自动填充行插入中的单元格值?诚然，我是这整个领域的新手.

If there are non-code related changes like installing installable triggers or something, please share how I maybe able to connect those dots as well to being able to automatically fill cell values on row inserts? Admittedly, I'm new to this whole territory..

PS:我认为这段代码的任何部分都不起作用.这是我的执行成绩单:

PS: I dont think any part of this code is working. This is my execution transcript:

[17-09-23 13:25:46:950 IST] SpreadsheetApp.getActiveRange() [0 seconds]
[17-09-23 13:25:46:950 IST] Range.getRow() [0 seconds]
[17-09-23 13:25:46:950 IST] Range.getLastRow() [0 seconds]
[17-09-23 13:25:46:950 IST] Range.getColumn() [0 seconds]
[17-09-23 13:25:46:950 IST] Range.getLastColumn() [0 seconds]
[17-09-23 13:25:47:049 IST] Session.getActiveUser() [0.098 seconds]
[17-09-23 13:25:47:050 IST] User.getEmail() [0 seconds]
[17-09-23 13:25:47:050 IST] SpreadsheetApp.getActiveSpreadsheet() [0 seconds]
[17-09-23 13:25:47:058 IST] Starting execution
[17-09-23 13:25:47:103 IST] Execution failed: Script function not found: onEdit [0 seconds total runtime]

推荐答案

我还没有测试过，但是可以尝试一下.

I haven't tested this but give it a try.

function MyChange(e){ 
  var sheet = SpreadsheetApp.getActiveSheet();
  if(sheet.getRange('A1').getBackground()!='#ff0000')
  {
    sheet.getRange('A1').setBackground('#00ff00');
  }
  if (e.changeType == "INSERT_ROW") 
  {
    sheet.getRange('A1').setBackground('#ff0000');
    if (sheet.getName()=="UIDAICheck") 
    {
      sheet.getRange('A1').setBackground('#0000ff');
      sheet.getActiveRange().setValue('555');
    }
  }
}

安装onChange触发器:

Installing the onChange trigger:

这篇关于无法通过Google Apps脚本检测Google表格中的行插入?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！