I am trying to get excelscript to filter out a bunch of rows based on the value in one column, but i keep getting an error. What did i do wrong?

Question

I am trying to automate truncating the input from a scanner using excelscript from the automate feature in excel. I need to get rid of everything below and above a certain range. I am trying to use a custom filter to look for everything outside of the range and delete all of the entirety of those rows. I'm trying to make a simplified version of this to work the kinks out before i throw it into a larger script that's doing the full analysis I'm writing the script for. The full script is as follows:

function main(workbook: ExcelScript.Workbook) {
    let selectedSheet = workbook.getActiveWorksheet();
    // Toggle auto filter on selectedSheet
    selectedSheet.getAutoFilter().apply(selectedSheet.getRange("A:A"));
    // Apply custom filter on selectedSheet
    selectedSheet.getAutoFilter().apply(selectedSheet.getAutoFilter().getRange(), 1, { filterOn: ExcelScript.FilterOn.custom, criterion1: "<9", criterion2: ">30", operator: ExcelScript.FilterOperator.or });
    // Delete all cells on selectedSheet
    selectedSheet.getRange().delete(ExcelScript.DeleteShiftDirection.up);
}

the error I keep getting is:

The argument is invalid or missing or has an incorrect format.

Like 6 is the long one that looks like it says the same thing twice in a row

I've not tried much aside from fiddling with the syntax for the criterion but i haven't gotten any luck.

Answer 1

• OfficeScript (TypeScript) uses zero-base index. The 2nd argument of getAutoFilter().apply() should be zero in your code.

function main(workbook: ExcelScript.Workbook) {
    let selectedSheet = workbook.getActiveWorksheet();
    // Toggle auto filter on selectedSheet
    selectedSheet.getAutoFilter().apply(selectedSheet.getRange("A:A"));
    // Apply custom filter on selectedSheet
    const criterion = { 
        filterOn: ExcelScript.FilterOn.custom, 
        criterion1: "<9", 
        criterion2: ">30", 
        operator: ExcelScript.FilterOperator.or };
    selectedSheet.getAutoFilter().apply(selectedSheet.getAutoFilter().getRange(), 0, criterion);
}