如何仅在有人添加新行时运行脚本 [英] How can I run a script only when someone adds new rows

问题描述

我有以下脚本，它在 OnEdit 中运行，但我想将此脚本调整为仅在有人向工作表添加新行时运行.

I have the following script, that runs in OnEdit, but I want to tune this script to run only when someone adds new rows to the sheet.

function DataValidation2(e)
{
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getActiveSheet();
  if (s.getName() == "Sheet1"){
  var cell = s.getRange('C3:C');
  var rule = SpreadsheetApp.newDataValidation().setAllowInvalid(false).requireValueInList(['0:15:00', '0:20:00']).build();
  cell.setDataValidation(rule);
  }
}

如果可能，仅当有人使用表单末尾的 boton add include 添加新行时才执行此脚本?

If possible execute this script only when someone add new row using the boton add include at the end of the sheet?

此致，

推荐答案

有一个特殊的触发器来检测此类更改:onChange

There is a special trigger to detect such changes : onChange

您甚至可以按照文档中的描述以编程方式创建它:

You can even create it programmatically as described in the documentation :

var sheet = SpreadsheetApp.getActive();
 ScriptApp.newTrigger("myFunction")
   .forSpreadsheet(sheet)
   .onChange()
   .create();

然后像这样的函数将在每次更改时调用，该函数查看行数并在更改时执行某些操作……如果更改是其他内容，则不执行任何操作.

and then a function like this that will be called on every change that looks at the number of rows and do something if it changed...and does nothing if the change was something else.

您可以添加更多条件来处理其他情况，例如删除的 id 行要做什么

You can add more conditions to handle other cases, for example what to do id rows where deleted

function myFunction(){
  var numRows = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getMaxRows();
  if(ScriptProperties.getProperty('numberOfRows')){
    var nRows = Number(ScriptProperties.getProperty('numberOfRows'));
    if(nRows<numRows){
      //do something because rows have been added in this sheet
      ScriptProperties.setProperty('numberOfRows',numRows);// update value with current value
    }
  }
  ScriptProperties.setProperty('numberOfRows',numRows);// create a start value with current value of simply update if the trigger was called for another reason
}

<小时>

根据 Wchiquito 的评论进行编辑

这是另一个(更简单的)版本，效果也很好:

here is another (simpler) version that works pretty well too :

function initializeTrigger(){ // run this only once to create a trigger if necessary
  var sheet = SpreadsheetApp.getActive();
 ScriptApp.newTrigger("myFunction")
   .forSpreadsheet(sheet)
   .onChange()
   .create();
}

function myFunction(e){
  Logger.log(e.changeType);
  if(e.changeType=='INSERT_ROW'){
    // do Something
    Browser.msgBox('New row(s) added');
  }
}

这篇关于如何仅在有人添加新行时运行脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！