Google Apps Script - move data range based on for loop matching to value = "Yes"

Question

I am attempting to find certain values in a datasheet using a for loop, and based on whether the value is = "Yes" it will move the row to another sheet. The loop should further look for the value of requesttype (in this case SRM, CC, or TM1) and move only certain parts of the row to the other sheet.

Hence, the for loop should copy the row based on inputs from two different columns in the data sheet: "All Outputs"

So while my code works in general when I run it, it only seems to execute the function for half of the rows where "Yes"is entered.

Each time I run the script half of the rows that should be moved, get moved but the other half stays in the original sheet. I can run the code until there is only one row left, in which case nothing happens anymore.

Here is what I have:

function myonEdit2() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getActiveCell();

  var sheetNameToWatch = "Open Requests";
  var columnNumberToWatch = 38;
  var watchColumn = 2;
  var valueToWatch = "Yes";

  var datarange = sheet.getRange(9, 2, 900, 36).getValues();
  var sheetNameToMoveTheRowTo = "Closed Requests" 

  for (i = 0; i < datarange.length -1; i++) {
    if (datarange[i][35] == "Yes") {
      var targetSheet = ss.getSheetByName(sheetNameToMoveTheRowTo);
      if (datarange[i][0] == "SRM") {
        var targetRange3 = targetSheet.getRange(targetSheet.getLastRow() + 1, 2);
        sheet.getRange(i, 2, 1, 8).moveTo(targetRange3);
        sheet.deleteRow(rowstart + i);
      }     
      else if (datarange[i][0] == "GC") { 
        var targetRange3 = targetSheet.getRange(targetSheet.getLastRow() + 1, 2);
        sheet.getRange(i, 2, 1, 8).moveTo(targetRange3);
        sheet.deleteRow(i);
      }
      else if (datarange[i][0] == "TM1") { 
        var targetRange4 = targetSheet.getRange(targetSheet.getLastRow() + 1, 2, 1, 6);
        sheet.getRange(i, 2, 1, 6).moveTo(targetRange4);
        var targetRange5 = targetSheet.getRange(targetSheet.getLastRow(), 10, 1, 1)
        sheet.getRange(i, 13, 1, 1).copyTo(targetRange5)
        sheet.deleteRow(i);
      }
    }
  }

As I said the code works fine but it does not execute for all the rows it should execute for.

As you can see from above, I define the for loop range as the complete datasheet in which the table is in. Then I create the first (main) if condition namely the 36th column of the data range being "Yes". This should define for which rows the script will be executed.

The second if condition defines which exact columns of the row should be copied and depends on whether the first column is = SRM, CC or TM1.

If anybody could help me figure out why the code does not execute for all rows that would be great.

Example sheet

As mentioned, you are forward iterating and also mutating the underlying range being iterated, without compensating for said mutation. — tehhowch
Thank you for your answer. However I am not sure how to compensate for the mutation. I understand that if I need to do backwards iteration by doing for (i = lastrow; i>9,i--) However, when I run it, it still does the same as before even if I try to run only the delete row part. — Fabian Kraus
Make sure you show code in your question that reflects what you have tried and describe the outcomes of those attempts. Also understand the difference between JavaScript Array element indices, and Range row and column numbers — tehhowch

shabnam bharmal shabnam bharmal · Accepted Answer · 2018-10-16T10:38:53

The similar kind of problem has happened with me and this also seems the issue here . you are taking the data range and adding a for loop to it to process each row. Now, you are deleting the rows in between but there is a defined data array in which you have added the for loop. So, when the previous rows will get deleted, the for loop array structure will still remain same but the sheet structure will be changed as example consider the 2nd row got deleted and your condition matched at i=5 but the sheet structure would now have that 5th row moved upwards.

So, you can try adding the rows to keep in an array and clear the sheet once and add that array to the sheet in one go after the processing has been done to copy the sheet data or try something to remove the deleted rows from the sheet after processing has been done to move to another sheet

Google Apps Script - move data range based on for loop matching to value = "Yes"

1 Answers