delete a single row if a match is found in an array

I am having a problem deleting certain rows from a sheet.

The sheet contains only two columns, name and age:

Name Age
John 20
Alicia 10
John 20
Alissa 22
Alissa 22
Alicia 10

The script gets a list of names and the elements of this list are searched in the sheet to eliminate the row where these names are found. So if you pass a list to the script with the the integers [20, 10, 22], the sheet should only contain the rows of John, Alice and Alissa once. I.e. only the first occurrence is deleted. For now I have tried this:

const ss = SpreadsheetApp.openById("ID");
const sheet = ss.getSheetByName("Sheet1");
const data = sheet.getDataRange().getValues();

function clearRow() {
    let ages = [22, 20, 10];
    ages.forEach(age => {
        let ageFinder = sheet.createTextFinder(age.toString());

        var result = ageFinder.findAll();
        console.log(result.length);
        Logger.log("Delete row: " + result[result.length - 1].getRow());
        sheet.deleteRow(result[result.length - 1].getRow());
    })
}

The problem I am encountering is that all occurrences are removed, even though only 20, 22 and 10 appears once in the array.

Expected output for the array [20,22,10]:

Name Age
John 20
Alicia 10
Alissa 22

Could someone help me with this?

thanks in advance!

85 thoughts on “delete a single row if a match is found in an array”

Leave a Comment