Google App脚本-遍历电子表格中的行 [英] Google app script - looping through the rows in a spreadsheet

问题描述

我试图遍历电子表格中的行，并确定特定行中是否具有关键字"hello".并将整行移动到新的电子表格中.

I am trying to loop through rows within a spreadsheet and identify if a particular row has the key word "hello" and move that entire row into a new spreadsheet.

我尝试了以下代码.该代码适用于第一行，但不会循环通过，并在第一行之后停止.将范围选择扩展到"C1:E32".没有帮助.

I have attempted the following code. The code works for the first row but doesn't loop through and stops after the first row. Expanding the range selection to "C1:E32" does not help.

function Edit(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var activatedSheetName = ss.getActiveSheet().getName();
  var ActiveSheet = ss.getSheetByName("ActiveSheet"); // source sheet
  var MoveDatatoThisSheet = ss.getSheetByName("MoveDatatoThisSheet"); //    target sheet
  var re = new RegExp(/(Hello)/i);
  var startRow = 1;
  var endRow = ss.getLastRow();
  var getRange = ss.getDataRange();
  var getRow = getRange.getRow();

  for (var ree = startRow; ree <= endRow; ree++) {
    // if the value in column D is "Approved", move the row to target sheet
    cellValue = ss.getRange("C1:E1");
    if (cellValue.getValue().match(re)) {
      // insert a new row at the second row of the target sheet
      MoveDatatoThisSheet.insertRows(2, 1);
      // move the entire source row to the second row of target sheet
      var rangeToMove = ActiveSheet.getRange(/*startRow*/ getRow, /*startColumn*/ 1, /*numRows*/ 1, /*numColumns*/ ActiveSheet.getMaxColumns());
      rangeToMove.moveTo(MoveDatatoThisSheet.getRange("A2"));
      // add date and time of when approved to target row in column E
      MoveDatatoThisSheet.getRange("E2").setValue(Date());
      // delete row from source sheet
      ActiveSheet.deleteRow(cellValue, 1);
    }
  }
}

推荐答案

您的循环从不使用变量ree，它仅与cellValue = ss.getRange("C1:E1")一起使用.

Your loop never uses the variable ree, it only operates with cellValue = ss.getRange("C1:E1").

另一个问题是删除操作会删除已删除的行，这可能导致后续操作对错误的行起作用.当您遍历一排行时，删除其中一些行，请使其自下而上，而不是自上而下.

Another problem is that deletion shifts the rows under the deleted one, possibly causing subsequent operations to act on a wrong row. When you go through an array of rows, deleting some of them, do it bottom up, not top down.

for (var ree = endRow; ree >= startRow; ree--) {   
  var rangeToCheck = ss.getRange(ree, 3, 1, 3); // 3 columns starting with column 3, so C-E range 
  if (rangeToCheck.getValues()[0].join().match(re)) {   // joining values before checking the expression
    MoveDatatoThisSheet.insertRows(2,1);
    var rangeToMove = ActiveSheet.getRange(/*startRow*/ getRow, /*startColumn*/ 1, /*numRows*/ 1, /*numColumns*/ ActiveSheet.getMaxColumns());
    rangeToMove.moveTo(MoveDatatoThisSheet.getRange("A2"));
    // add date and time of when approved to target row in column E
    MoveDatatoThisSheet.getRange("E2").setValue(Date());
    // delete row from source sheet
    ActiveSheet.deleteRow(ree);
  }
}

如果目标是仅检查D列(例如)，则代码会稍微简化

If the goal is to check only column D (say), the code simplifies slightly

  var rangeToCheck = ss.getRange(ree, 4); //  column D in row ree
  if (rangeToCheck.getValue().match(re)) {   // joining values before checking the expression

性能

按照 Google的建议，应避免多次调用getValues/setValues等方法. ，而不是立即获取所有必要的数据，进行处理，然后立即进行批量更改.例如，不要将其添加到另一个工作表中，而是将其添加到数组中；当循环结束时，将整个数组放在该工作表中.

Performance

As Google recommends, one should avoid multiple calls to getValues / setValues and such, instead grabbing all necessary data at once, processing it, and making batch changes at once. E.g., instead of placing it a row in another sheet, add it to an array; when the loop ends, place the entire array in that sheet.

这篇关于Google App脚本-遍历电子表格中的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！