Strange behaviour when trying to delete more then one row

Question

I am having trouble with using Google Apps Script and the two functions the deleteRow() and deleteRows() methods.

The code below uses a string matches testing if a string exists in column. If the string exists then I want to do something, in this case delete a couple of rows in my active sheet.

The problem is that it deletes the first row or one row, then does not seem to delete the next row in my code below.

I have been able to verify that even if I do sheet from the end of the sheet starting at last row and going to first row or like below from first row to last row the same problem exist.

It deletes one row but never deletes more then one row. In my case no error is produced just row does not get deleted using either method.

    var data = ws.getRange("A2:U" + ws.getLastRow()).getValues();  

    var i = 0;
    var W = 23;
    var deleted = "DELETED"; 

    var sheetDataAsArr = SpreadsheetApp.getActiveSheet().getDataRange().getValues(); // 2020Members 

    sheetDataAsArr.forEach(function(row,i)     {   

    // The i is a index 
       if ( row[W] = 'DELETED' ) {
          // DO SOMETHING HERE
            Logger.log('EMAIL DELETED ROW row is :' + row[B]);  // The email 
            ws.deleteRow(i+1);  // It deleted the record
            i--; // Decrement i because record was deleted 
       }
     i++; 
    });

The problem is very strange, I have been able to duplicate using both functions. It does not delete the next row but I get to see the next row because I show the correct email address but does not do the delete the second time through the loop but also does not produce any error message just does not do the delete the second one but deletes the first row just fine.

It only allows me to delete one row and no more then one row

   var ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("2020Members");

         The following above works fine now 

     var myValue = ws.getRange(i+1, W).getValue(); 

This is the value in the sheet in the W column and the if works now also !

Whether I go from the last row to the first row but not the header and it still only allows me to delete one row and stop on the next row so if i traverse the sheet from first to last or last to first it only will not let me delete only one row and stops.

Do I need to sleep or flush something before doing the second delete?

Second example of code above I even tried to use ws.deleteRows(i+1,1); and same problem happens.

     The following above works fine now 

     var myValue = ws.getRange(i+1, W).getValue(); 

      This is the value in the sheet in the W column and the if works now also !

Answer 1

One "big problem" with the script is

i--; // Must auto decrement i

inside of

sheetDataAsArr.forEach(function(row,i)     {

}

The above because Array.prototype.forEach() increment i on each iteration. If you want to do reverse iteration, use for

Related

• Delete rows based on values within cell, optimising

Answer 2

Offset issue

Let's image a hypothetical situation where you have 3 rows with 2 cols each. Second column contains the checked "DELETE" value. Your loop will do three iterations, since values length equals 3:

//first iteration:

pos 1, idx 0 -->  row1 | col1 | DELETE | -- match
                  row2 | col1 | DELETE |
                  row3 | col1 | col2   |

//second iteration:

                  row2 | col1 | DELETE |
pos 2, idx 1 -->  row3 | col1 | col2   | -- no match

//third iteration:

                  row2 | col1 | DELETE |
                  row3 | col1 | col2   |
pos 3, idx 2 -->  ---- | ------------- | -- out of bounds

As you can see, each time forEach moves to next iteration, if a row is deleted, the result using your algorithm becomes "off" by 1. That is if we use the fixed version that actually does the comparison, otherwise the second iteration will delete a row as well.

Keeping track of deleted rows

More importantly, as can be seen from the above, on non-sequential matches the resulting offset will become more and more as number of rows increases. You need to track rows that were deleted and compensate. This sample should give you a good idea of what to do:

function testDeleteRow() {

    const deletedRowNumbers = [];

    const ss = SpreadsheetApp.getActiveSpreadsheet();

    const testName = "test_deletion";

    const sh = ss.insertSheet();

    sh.setName(testName);

    const dimensions = [1, 1, 5, 2];

    const matrix3x2 = sh.getRange(...dimensions);

    matrix3x2.setValues([
        ["column1", "DELETE"],
        ["column1", "KEEP"],
        ["column1", "DELETE"],
        ["column1", "KEEP"],
        ["column1", "DELETE"]
    ]);

    const testRng = sh.getRange(...dimensions);

    const testVals = testRng.getValues();

    let offset = 0;

    testVals.forEach((rowOfValues, i) => {

        const [, toDelete] = rowOfValues;

        if (toDelete === "DELETE") {
            const offsetted = (i + 1 - offset) || 1;

            sh.deleteRow(offsetted);

            deletedRowNumbers.push( i + 1 );

            offset++;
        }

    });

    const ui = SpreadsheetApp.getUi();
    ui.prompt(`Deleted rows: ${deletedRowNumbers.join(", ")}`);

    ss.deleteSheet(sh);
}

Notes

1. As mentioned times and times again, get rid of the i declaration and increment - it is shadowed by forEach method's callback function i argument, thus increment literally does nothing.
2. Will the return false cause the break out - no, it won't. Please, read the documentation on forEach. There is a huge disclaimer explaining that you can't stop the execution unless an exception is thrown (forEach literally means "for each element do", it does not make sense to use it unless you want to create a side-effect for each element in the array).
3. If you need to go from last row to first, just start not from 0, but from the last index of the array of values.
4. if(row[W] = 'DELETED') is the same as writing if(true). The condition will evaluate to true due to how assignment works - it returns the value that is assigned, thus if statement actually evaluates if("DELETED").
5. Do I need to sleep or flush something - no, you don't need to. The only reaosn to flush changes is to let users know that something is happening while your script performs heavy lifting or if it depends on non-atomic operation to finish, which is not your case.