My Loop isn't functioning as intended, I'm really struggling with what the issue is, as it seems to be working, I have to repeat the script manually for it to complete the task...

working backwards, I delete the pre-selected rows from the document, going from the last row to the first, to not shift the order of cells and delete data, I then remove the last element from the array and delete the next row.

The loop doesn't seem to complete and I'm not sure what the issue is, it's deleting rows perfectly fine in the spreadsheet, but doesn't delete all the listed rows, unless I run the code multiple times...

  Logger.log(toBeDeleted);
  Logger.log(toBeDeleted.length);
  for(var i=0; i<toBeDeleted.length; i  ){
    rowTBD = toBeDeleted[toBeDeleted.length - 1];
    Logger.log(rowTBD);
    sheet.deleteRow(rowTBD);
    toBeDeleted.splice(-1);
  }
  Logger.log(toBeDeleted)
  Logger.log(toBeDeleted.length)

Primary output

1:15:34 PM  Info    [{}, 1.0, 2.0, 3.0, 5.0, 7.0, 9.0, 11.0, 14.0, 18.0, 35.0, 36.0, 37.0, 39.0, 41.0, 44.0, 46.0, 50.0, 52.0, 66.0, 71.0]
1:15:34 PM  Info    21.0
1:15:34 PM  Info    71.0
1:15:34 PM  Info    66.0
1:15:35 PM  Info    52.0
1:15:35 PM  Info    50.0
1:15:35 PM  Info    46.0
1:15:35 PM  Info    44.0
1:15:35 PM  Info    41.0
1:15:35 PM  Info    39.0
1:15:35 PM  Info    37.0
1:15:36 PM  Info    36.0
1:15:36 PM  Info    35.0
1:15:36 PM  Info    [{}, 1.0, 2.0, 3.0, 5.0, 7.0, 9.0, 11.0, 14.0, 18.0]
1:15:36 PM  Info    10.0

Secondary output: - after running script for a second time

1:16:59 PM  Info    [{}, 1.0, 2.0, 3.0, 5.0, 7.0, 9.0, 11.0, 14.0, 18.0]
1:16:59 PM  Info    10.0
1:16:59 PM  Info    18.0
1:16:59 PM  Info    14.0
1:16:59 PM  Info    11.0
1:16:59 PM  Info    9.0
1:16:59 PM  Info    7.0
1:17:00 PM  Info    [{}, 1.0, 2.0, 3.0, 5.0]
1:17:00 PM  Info    5.0

Tertiary Output: - Third time running script

1:17:44 PM  Info    [{}, 1.0, 2.0, 3.0, 5.0]
1:17:44 PM  Info    5.0
1:17:44 PM  Info    5.0
1:17:44 PM  Info    3.0
1:17:44 PM  Info    2.0
1:17:45 PM  Info    [{}, 1.0]
1:17:45 PM  Info    2.0

Quaternary Output - fourth time

1:18:37 PM  Info    [{}, 1.0]
1:18:37 PM  Info    2.0
1:18:37 PM  Info    1.0
1:18:37 PM  Info    [{}]
1:18:37 PM  Info    1.0

CodePudding user response：

This is because as the loop proceeds future the value of i increases and the value of toBeDeleted.length decreases. This keeps on happening till the value of i becomes equal to or greater than toBeDeleted.length Example:

Now at the time on 3rd iteration the loop condition fails and loop exits. Hence an toBeDeleted array of length 2 is left intact.

Try using this loop:

for( var i = toBeDeleted.length - 1 ; i >= 0; i-- ) {
    rowTBD = toBeDeleted[i];
    Logger.log(rowTBD);
    sheet.deleteRow(rowTBD);
    toBeDeleted.splice(-1); //this line is no longer needed but you can use if needed
}

CodePudding user response：

Issue:

This is because toBeDeleted.length is modified each iteration. So the condition i<toBeDeleted.length fails earlier than expected.

Solution:

Set toBeDeleted.length to a variable and use the stored length.

Snippet:

const storedLength = toBeDeleted.length;
for(let i = 0; i < /*toBeDeleted.length*/storedLength; i  ){

CodePudding user response：

Deleeting while incrementing through data using a delete counter

function deletenonuniquewhileincrementingthroughdata() {
  const ss = SpreadsheetApp.getActive();
  const id = ss.getId();
  const sh = ss.getSheetByName('Sheet0');
  const rg = "Sheet0!A2:J"   String(sh.getLastRow());
  const vs = Sheets.Spreadsheets.Values.get(id, rg).values;
  //Logger.log(JSON.stringify(vs));
  let d = 0;
  for(let i = 0;i < vs.length; i  ) {
    for(let j = 0;j<vs[i].length;j  ) {
      let r = vs[i].slice();
      r.splice(j,1)
      let idx = r.indexOf(vs[i][j]);
      if(idx > -1) {
        sh.deleteRow(i   2 - d  );//delete rows that don't have unique elements
        break;
      }
    }
  }
}

Sheet0 before:

Sheet0 after: