CODEWITHSUNDEEP
ms-officeoffice-scripts
Fi333
Fi333

Reputation: 3

Office Script - Delete rows from Excel table not containing specific text

I have been working with an Office script in Excel (in conjuction with Power Automate) to delete all rows from a table. I now need to modify this so it excludes rows which contain specific text in one of the columns.

Is anyone aware of a way to do this? For example, if a column C row contains "No", do not delete the row.

The script used to delete all rows in the table is below but unsure how to modify this to exclude certain rows

function main(workbook: ExcelScript.Workbook) {
  let table = workbook.getTable("Table1");
  let rowCount = table.getRowCount();
  table.deleteRowsAt(0, rowCount);
}

Upvotes: 0

Views: 1173

Answers (1)

Michelle Ran
Michelle Ran

Reputation: 63

You can try the following script:

function main(workbook: ExcelScript.Workbook) {
    let table = workbook.getTable("Table1");
    let columnToCheck: number = table.getColumn("ColumnName").getIndex();

    let row = 1;
    let rowCount = table.getRowCount();
    while (row <= rowCount) {
        let text = table.getRange().getCell(row, columnToCheck).getText();
        if (!text.includes('No')) {
          table.deleteRowsAt(row - 1, 1);
          rowCount--;
        } else {
          row++;
        }
    }
}

It loops through the table, checks each value in "ColumnName" (be sure to replace this with the name of the column you want to check), and deletes the row if the value does not contains "No". Hopefully this helps!

Upvotes: 1

Related Questions