javascript - Google app script - looping through the rows in a spreadsheet - Stack Overflow

admin管理员组

文章数量:1287988

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.

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);
    }
  }
}

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.

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);
    }
  }
}

• javascript
• arrays
• for-loop
• google-apps-script
• google-sheets

Share Improve this question Follow edited Aug 28, 2020 at 2:34 Wyatt 333 bronze badges asked Dec 15, 2016 at 14:46 AnjaliAnjali 4311 gold badge11 silver badge88 bronze badges 1

• 1 There is a specific add-on for that. Just Move It chrome.google./webstore/detail/just-move-it/… – Anees Hameed Commented Dec 15, 2016 at 23:15

Add a ment  | 

1 Answer 1

Sorted by: Reset to default 5

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);
  }
}

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

Performance

As Google remends, 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.

本文标签： javascriptGoogle app scriptlooping through the rows in a spreadsheetStack Overflow