Why am I getting an Internal Error on AutoFilter Apply? (Office Bu)

Question

My script runs fine on Desktop Excel--this seems to be a limitation of the M365 Web App or a Bug.

I'm new to OfficeScript, and I've been trying to create an automation that will apply a custom filter, add a formula, copy visible rows excluding header to a second sheet, and then repeat those steps for the next set of filters and formulas. The first formula and filter set work perfectly, right up until I clear the filter and then attempt to apply a new one, at which point I receive an error stating:

I'm trying to apply a filter, run some logic, clear the filter, then apply a new filter.

I get this error when I try to apply the new filter.

Line 97: AutoFilter apply: An internal error has occurred.

I've tried removing the auto filter before the second apply, changing the syntax of the second apply to explicitly use the worksheet name, setting the second filter to only use a single criterion, but it always seems to fail.

 function main(workbook: ExcelScript.Workbook) {
    let selectedSheet = workbook.getActiveWorksheet();
    let sheetName = selectedSheet.getName()

    // Add "Data" worksheet if it does not exist
    let dataSheet = workbook.getWorksheet("Data");
    if (!dataSheet) {
        dataSheet = workbook.addWorksheet("Data");
    }

    // Set "Status1" and "Status2" in DH1 and DI1
    selectedSheet.getRange("DH1").setValue("Status1");
    selectedSheet.getRange("DI1").setValue("Status2");

    // Auto fit the columns of all cells on selectedSheet
    selectedSheet.getUsedRange().getFormat().autofitColumns();

    // Copy the top row (headers) from selectedSheet to dataSheet
    dataSheet.getRange("A1").copyFrom(selectedSheet.getRange("A1:DI1"), ExcelScript.RangeCopyType.all, false, false);


    // Clear existing filters on the selected sheet
    selectedSheet.getAutoFilter().clearCriteria();

//Set Formulas

    selectedSheet.getAutoFilter().apply(selectedSheet.getRange("A1"));

    // Apply new filters on the selected sheet
    selectedSheet.getAutoFilter().apply(selectedSheet.getAutoFilter().getRange(), 33, { 
        filterOn: ExcelScript.FilterOn.custom, 
        criterion1: "<>00/00/00",
        criterion2: '<>'  // Filter settings, adjust as needed.
    });

//  selectedSheet.getAutoFilter().apply(selectedSheet.getUsedRange(), 33, {
//      filterOn: ExcelScript.FilterOn.custom,
//      criterion1: '<>',
//      criterion2: '<>"00/00/00"',
//  });

    // Create an array formula for "Status1" to automatically adjust references for each row
    let dataRange = selectedSheet.getUsedRange();
    let startRow = 2; // Start from the second row (adjust as needed)
    let endRow = dataRange.getRowCount();

    let formulaArray: string[][] = new Array(endRow - startRow + 1).fill([]);
    for (let i = startRow; i <= endRow; i++) {
        let formula = `=IF(AND(AH${i}<=AL${i}, AH${i}>=AK${i}), "Okay", IF(AND(AH${i}-1<=AL${i}, AH${i}+1>=AK${i}), "Yep", "Nope"))`;
        formulaArray[i - startRow] = [formula];
    }
    let formulaArray2: string[][] = new Array(endRow - startRow + 1).fill([]);
    for (let i = startRow; i <= endRow; i++) {
        let formula2 = "Sorry";
        formulaArray2[i - startRow] = [formula2];
    }

// Set the formulas for "Status1"
    selectedSheet.getRange(`DH2:DH${endRow}`).setFormulas(formulaArray);

    // Set the formulas for "Status2"
    selectedSheet.getRange(`DI2:DI${endRow}`).setFormulas(formulaArray2);

    // Get visible data within the table, excluding the first row
    let visibleDataRange = dataRange.getVisibleView().getRange();
    visibleDataRange.getOffsetRange(1,0);

    // Copy visible data to the "Data" sheet at the bottom (excluding the first row)
    let dataLastRow = dataSheet.getUsedRange().getRowCount() + 1;
    let dataRangeToCopy = selectedSheet.getRange(`A2:DI${endRow}`);

    dataSheet.getRange(`A${dataLastRow}`).copyFrom(dataRangeToCopy, ExcelScript.RangeCopyType.values, false, false);


// RESET
// RESET
// RESET

    // Clear existing filters on the selected sheet
    selectedSheet.getAutoFilter().clearCriteria();


    // Reset Formula Columns
    selectedSheet.getRange("DH:DI").clear(ExcelScript.ClearApplyTo.contents);
    selectedSheet.getRange("DH1").setValue("Status1");
    selectedSheet.getRange("DI1").setValue("Status2");

    

    //Set Formulas

    selectedSheet.getAutoFilter().apply(selectedSheet.getAutoFilter().getRange(), 33, { 
        filterOn: ExcelScript.FilterOn.custom, 
        criterion1: "=00/00/00",
        criterion2: "=" 
        });

I keep getting an error when it tries to apply the filter again. Can you offer any advice on what I"m doing wrong?

Answer 1

• Assumes the auotfiler is applied on column A.

Below snippet is a sample how to apply a new autofilter.

  let autoFilter = selectedSheet.getAutoFilter();

  // Clear auto filter on selectedSheet
  autoFilter.clearCriteria();

  // Apply single value filter on selectedSheet
  autoFilter.apply(autoFilter.getRange(), 0, {
    filterOn: ExcelScript.FilterOn.values,
    values: ["00/00/00"]
  });

  // // Clear auto filter on selectedSheet
  autoFilter.clearCriteria();

  // Apply multiple values filter on selectedSheet
  autoFilter.apply(autoFilter.getRange(), 0, {
    filterOn: ExcelScript.FilterOn.values,
    values: ["00/00/00", "00/00/01", "00/00/03"]
  });  

---

There are several options to update formulas using Office Scripts:

• Option 1: Build formulas for each cell and update formulas with setFormulas.
• Option 2: Update formulas with setFormulaLocal.

Option 2 is more efficient than Option 1. Additionally, changing the calculation mode to manual before updating formulas is a good approach to improve efficiency.

function main(workbook: ExcelScript.Workbook) {

    let application = workbook.getApplication();
    // Set workbook calculation mode
    application.setCalculationMode(ExcelScript.CalculationMode.manual);
    let selectedSheet = workbook.getActiveWorksheet();

    // Option 1
    let dataRange = selectedSheet.getRange("C1:C3");
    let formulaArray: string[][] = dataRange.getFormulas();
    for(let i=1; i<4; i++){
        formulaArray[i-1][0] = `=A${i}+1`
    }
    selectedSheet.getRange("C1:C3").setFormulas(formulaArray);

    // Option 2
    selectedSheet.getRange("B1:B3").setFormulaLocal("=A1+1");

    application.setCalculationMode(ExcelScript.CalculationMode.automatic);
}

Microsoft documentation:

setCalculationMode(calculationMode)