动态编辑google工作表中的列

Question

上下文

我正在创建一个股票价格数据数据库。我目前正在使用下面的onEdit函数：

function onEdit(e) {
  // Get the spreadsheet that this will apply to, the tabs and columns
  var ss = e.source.getActiveSheet();
  var excludeTabs = ["Summary", "FTSE 250"];
  var excludeColumns = [1,2,12,13,14,15,16]; // the columns to isolate for special reasons
  var excludeCells = ["M1","I1","I2"];
  // What is the criteria for action to be taken? If the terms defined in excludeTabs are met, then move onto the next criteria (indexOf used because we stored it as an array
  if(excludeTabs.indexOf(ss.getName())===-1){
    // The range from the spreadsheet. 
    // Scripts expects an event in a cell
    var cell = e.range;
    // For the entire column of this cell
    var col = cell.getColumn();
    // Within the universe of tabs that this script applies to, we want to exclude the columns in the array
    if(excludeColumns.indexOf(col)===-1)
      // Within the universe of tabs and columns, we want to exclude certain cells
      if(excludeCells.indexOf(cell)===-1)
      // Need to make sure it only applies to formulas
        if(cell.getFormula() !== ""){ 
          var destination = ss.getRange(4, col, ss.getLastRow()-1, 1); 
          cell.copyTo(destination);  
      }//End of the remaining universe of data taking out the exceptions
     }//End Tab criteria
}//End function

这允许在编辑单元格时对某些列html" target="_blank">执行编辑。到目前为止，它是有效的，但有一些扭结。

问题1

有时，当我编辑列的第四行上方的单元格时，它会编辑整个列，尽管我告诉它从第四行开始。这发生在几分钟前的一个单元格中，我告诉它排除上面的“I2”。我为此编写的代码有什么问题吗？

问题2我尝试为代码创建其他异常，其中对于某些指定的范围，它将仅从不同的单元格范围进行编辑。不是每列的第四个单元格，而是第十个单元格。我试着把它添加到var destination=ss下面。getRange（4，col，ss.getLastRow（）-1，1）但它不起作用。我还尝试为不同的单元格位置创建一个单独的onEdit函数，但它也不起作用。

到目前为止，我一直在使用以下表格公式：IFERROR（IFS（C4=”Returns“，”和（C4=”，C5=”，C6=”，C7=”，C8=”）和（ISNUMBER（C4），ISNUMBER（C5），ISNUMBER（C6），ISNUMBER（C7），ISNUMBER（C8）），COVAR（$C4:$C8，'FTSE 250'！J5:J9）），”）

但这变得一团糟。有时单元格中有数据，所以它会使上面的公式毫无用处。下面这张图就是一个例子。

更新我希望onEdit从列的第10行开始并向下拖动，但仅限于该列（这是我将编辑的列中的行）。我还希望能够为其他列这样做（从不同的行开始自动向下复制过程）。

• 这是我试图编辑的范围

更新2

 if(!excludeTabs.includes(ss.getName()) &&  
     !excludeColumns.includes(col) &&
     !excludeCells.includes(cell.getA1Notation()) &&
     cell.getFormula() !== ""
    ){
      
    if(col==33){
      var destination = ss.getRange(8, col, ss.getMaxRows()-7, 1);
      cell.copyTo(destination);    
    }

   else if(col===30){
          var destination = ss.getRange(8, col, ss.getMaxRows()-7, 1);
          cell.copyTo(destination); 
        }

    else{
    var destination = ss.getRange(4, col, ss.getMaxRows()-3, 1);
    cell.copyTo(destination);    
      }


    }




Answer 1

试试这个：

function onEdit(e) {
  var sh = e.range.getSheet();
  var excludeTabs = ["Summary", "FTSE 250"];
  var excludeColumns = [1,2,12,13,14,15,16]; 
  var excludeCells = ["M1","I1","I2"];
  if(excludeTabs.indexOf(sh.getName())==-1 && excludeColumns.indexOf(e.range.columnStart)==-1 && excludeCells.indexOf(e.range.getA1Notation())==-1 && e.range.getFormula()!=""){    
      e.range.copyTo(sh.getRange(4, e.range.columnStart, sh.getLastRow()-3, 1));//The numbers of rows to the bottom is sh.getLastRow()-3 -1 will have to roll off of the bottom of the spreadsheet which will give you out of range errors  
    }
  }
}

Answer 2

问题：

>

此处if（不包括cells.indexOf（cell）=-1）存在问题：

var excludeCell=[“M1”、“I1”、“I2”]是一个字符串数组，var cell=e.range 是一个范围对象。实际上，您正在比较两种不同的东西（一个字符串与一个范围对象）

相反，您希望将：if（excludeCells.indexOf（cell）==-1）替换为if（excludeCells.indexOf（cell.getA1Notation（））====-1）。

改进：

>

此外，这个范围getRange（4， ol，ss.getLastRow（）-1,1）；也没有太大意义。使用ss.getLastRow（）-3更有意义，因为您是从3开始的。

而不是使用excludeCell。indexOf（cell.getA1Notation（））===-1这是一个长表达式，您可以像那样使用includes（）！排除细胞。包括（cell.getA1Notation（））。

function onEdit(e) {
  var ss = e.source.getActiveSheet();
  var excludeTabs = ["Summary", "FTSE 250"];
  var excludeColumns = [1,2,12,13,14,15,16]; // the columns to isolate for special reasons
  var excludeCells = ["M1","I1","I2"];
  var cell = e.range;
  var col = cell.getColumn();
  
  if(!excludeTabs.includes(ss.getName()) &&  
     !excludeColumns.includes(col) &&
     !excludeCells.includes(cell.getA1Notation()) &&
     cell.getFormula() !== ""
    ){         
      if(col==33){
       var destination = ss.getRange(10, col, ss.getMaxRows()-9, 1);
       cell.copyTo(destination);    
      }
      else{
       var destination = ss.getRange(4, col, ss.getMaxRows()-3, 1);
       cell.copyTo(destination);    
      }
     }
}

请注意:

• getLastRow（）返回包含内容的最后一行。例如，如果您有10列，第一个10的最后一行的内容为55，但在工作表末尾的20列中有一个随机值，比如说行900，那么900将是工作表中的最后一行。注意这一点，否则您将需要其他方法来获取包含内容的最后一行。公式也是内容。因此，一直到工作表底部的公式可能决定getLastRow返回的内容