Office Scripts Delete Entire Rows Based on Multiple Data in Certain Cells

I am a non-programmer and quite new to Office Scripts and I would love some help.

I am currently trying to delete entire rows if the cell in the “Change Flag” column, which happens to be the second column on the Excel sheet, contains the word “Delete” or “Deleted”.

I managed to delete rows if they contain the word “Delete” with the following script but could not make the script cells with “Deleted” too. I tried throwing brackets in there but it unfortunately did not work.

Can someone suggest a best practice to handle the deletion of rows based on multiple text matches (delete if x = y OR z)?

function main(workbook: ExcelScript.Workbook) {
    // Get the used range on the current sheet.
    const currentSheet = workbook.getActiveWorksheet();
    let table = workbook.getTables()[0];
        // Get the RangeAreas object for each cell with a formula.
    const usedRange = currentSheet.getUsedRange();   
  //get the values of the range
    let values = usedRange.getValues();
    //get the row count of the range
    let rowCount = usedRange.getRowCount();
    //starting at the last row, check if the cell in column '1' equals to 'Delete'. If it is, then delete the entire row.
    for (let i = rowCount - 1; i >= 0; i--) {
        if (values[i][1] == "Delete") {
            usedRange.getCell(i, 1).getEntireRow().delete(ExcelScript.DeleteShiftDirection.up)
        }
    }
}

Answer

Your if statement should look like the below:

if (values[i][1] == "Delete" || values[i][1] == "Deleted")

If you are looking for more general tutorials, here is a page I found that explains if statements a little more: https://www.w3schools.com/jsref/jsref_if.asp

Advertisement

Answer