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

Question

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);
}

Michelle Ran · Accepted Answer · 2022-11-01 18:07:14Z

1

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!

answered Nov 1, 2022 at 18:07

Michelle Ran

636 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related