Thursday 21 February 2019

Loop delete specific rows with counter

The following Google Apps Script has been created to help with a recent difficulty I was having with deleting rows containing a specific value, where the loop was losing the row position due to the spreadsheet data all shifting up a row each time one was deleted. Some searching online suggested looping through a spreadsheet in reverse (from bottom up) to avoid the shift, but that seems more like a workaround than a direct solution and could have implications elsewhere.
Screenshot of table of names with marked ones for deletion
Example table with specific rows to delete
In the image above rows 3 and 5 have been marked for deletion, but as a script is typically designed to loop through each row in turn once it reaches row 3 and deletes it the rest of the data below all moves up one row - which means Mickey Mouse is no longer row 5 but instead row 4 - see below.
Screenshot of table of names with row 3 now deleted
Table with row 3 now deleted and data shifted up

For this reason it is necessary to add a counter in the loop which increments each time a row is actually deleted (as opposed to the loop counter which increments after every iteration regardless of finding a match).

Below I have extrapolated the 3 lines of code needed to achieve this by creating a variable which is subtracted from the typical 'deleteRow' function.
var rowsDeleted = 0;

ss.deleteRow((i+1) - rowsDeleted);

The code below provides a working example of this so you can see how it works yourself:

Loop delete specific rows with counter.xlsx

No comments:

Post a Comment