How to filter Excel rows in function of specific criteria using Office Script

Question

Filtering Excel rows in function of specific criteria using Office Script

Hello everyone, I made this code on office script to try and filter rows in a sheet on some specific criteria :

Get all rows from sheet that have 1 or 2 :

1 : "TMUK" in column P

2 : "TPCE" in column P and "TMUK B" in column AE

With the console logs I was able to get matches so it seems that it works, however when it's time to filter, I end up with nothing (there were about 20k rows, and 1400 that respected the criteria, but after the script was done running, I was left with nothing...). Can anyone tell me what I did wrong ?

Additionally, I would like to be able to find an efficient way to delete the rows that were hidden due to the filter, I don't want to leave them hidden.

For context, initially, I created code that did the same thing but by going through all rows individually (check row and delete row if it does not verify the criteria), which is to me inefficient and can be upgraded using this method.

Here is the code in question :

function filterRows(workbook: ExcelScript.Workbook) {
  const sheetName: string = "HYPERION DATA";
  const sheet = workbook.getWorksheet(sheetName);
  const usedRange = sheet.getUsedRange();
  console.log("Used Range:", usedRange.getAddress());

  const columnPValues = usedRange.getColumn(15).getValues(); // Column P
  const columnAEValues = usedRange.getColumn(30).getValues(); // Column AE

  // Define filtering criteria
  const criteria1 = "TMUK";
  const criteria2 = "TPCE";

  // Initialize an array to store row indices that match the criteria
  let matchingRows: number[] = [];

  // Iterate through rows to find matching criteria
  for (let i = 0; i < columnPValues.length; i++) {
    if (columnPValues[i][0] === criteria1 || (columnPValues[i][0] === criteria2 && columnAEValues[i][0] === criteria1)) {
      console.log("MATCH");
      // Match found, store row index
      matchingRows.push(i + 1); // Add 1 to convert to Excel's 1-based index
    }
  }
  console.log("Matching Rows:", matchingRows);

  // Convert row indices to string array
  let matchingRowsStr: string[] = matchingRows.map(row => row.toString());
  console.log("Matching Rows as Strings:", matchingRowsStr);

  // Apply filter to display matching rows
  console.log("Applying filter to range:", usedRange.getAddress());
  console.log("Filter values:", matchingRowsStr);
  sheet.getAutoFilter().apply(usedRange, 0, {
    filterOn: ExcelScript.FilterOn.values,
    values: matchingRowsStr
  });
}