Google 应用程序脚本 - 遍历电子表格中的行 [英] 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 应用程序脚本 - 遍历电子表格中的行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!