Office Script重复应用AutoFilter触发内部错误的原因及解决建议

我的脚本在桌面版Excel运行正常，但M365网页版里第二次应用筛选时触发错误：第97行：AutoFilter apply: 发生内部错误。流程是：应用自定义筛选→加公式→复制可见行到Data表→重置后重复流程，首次正常，二次筛选报错。试过移除筛选、指定工作表、改筛选条件都没用，代码如下：

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: "=" 
        });

}

问题出在这几个地方，对应修正方案： #

1. AutoFilter状态管理混乱
   网页版Excel对AutoFilter的状态校验更严格，你第一次调用apply(selectedSheet.getRange("A1"))是初始化筛选，但重置后直接调用apply二次筛选时，可能因为之前的筛选残留状态导致内部错误。建议每次重置时先移除AutoFilter，再重新创建，而不是只清条件：

   // 重置时替换原clearCriteria()
   if (selectedSheet.getAutoFilter()) {
       selectedSheet.getAutoFilter().remove();
   }
   // 重新初始化筛选
   selectedSheet.getRange("A1:DI1").autoFilter.apply();
   

2. 自定义筛选条件格式错误

   • 第一次筛选的criterion2: '<>'是无效的，自定义双条件需要明确逻辑关系（默认是AND），且"<>"单独用的时候不需要第二个条件，直接单条件即可。
   • 第二次筛选的criterion2: "="完全无效，如果你要筛选等于00/00/00的行，直接用单条件：

     // 第一次筛选：排除00/00/00和空值（假设你要这个逻辑）
     selectedSheet.getAutoFilter().apply(selectedSheet.getAutoFilter().getRange(), 33, { 
         filterOn: ExcelScript.FilterOn.custom, 
         criterion1: "<>00/00/00",
         operator: ExcelScript.FilterOperator.and,
         criterion2: "<>"
     });
     // 第二次筛选：只留00/00/00的行
     selectedSheet.getAutoFilter().apply(selectedSheet.getAutoFilter().getRange(), 33, { 
         filterOn: ExcelScript.FilterOn.custom, 
         criterion1: "=00/00/00"
     });
     

   另外，网页版对日期字符串的解析可能和桌面版不同，确保你的列是文本格式，或者用日期对象而不是字符串。

3. 可见行复制逻辑错误
   你写的visibleDataRange.getOffsetRange(1,0);没有赋值给变量，等于没执行，而且直接复制A2:DI${endRow}会把隐藏行也复制过去，应该改用可见行的范围：

   // 获取排除表头的可见行
   let visibleDataRange = dataRange.getVisibleView().getRange().getOffsetRange(1, 0);
   // 复制到Data表
   let dataLastRow = dataSheet.getUsedRange() ? dataSheet.getUsedRange().getRowCount() + 1 : 2;
   visibleDataRange.copyTo(dataSheet.getRange(`A${dataLastRow}`), ExcelScript.RangeCopyType.values);
   

4. UsedRange时机问题
   在添加公式后，UsedRange会扩大，导致后续的行号计算出错，建议提前固定数据范围，比如在初始化时就获取包含表头的完整范围：

   // 提前固定数据范围（假设表头在第一行，数据从第二行开始）
   let fullDataRange = selectedSheet.getRange("A1").getSurroundingRegion();
   let endRow = fullDataRange.getRowCount();
   

修正后的完整代码： #

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
    if (!dataSheet.getUsedRange()) {
        dataSheet.getRange("A1").copyFrom(selectedSheet.getRange("A1:DI1"), ExcelScript.RangeCopyType.all, false, false);
    }

    // 第一次筛选流程
    processFilterAndCopy(selectedSheet, dataSheet, 33, {
        filterOn: ExcelScript.FilterOn.custom,
        criterion1: "<>00/00/00",
        operator: ExcelScript.FilterOperator.and,
        criterion2: "<>"
    });

    // 重置并第二次筛选流程
    processFilterAndCopy(selectedSheet, dataSheet, 33, {
        filterOn: ExcelScript.FilterOn.custom,
        criterion1: "=00/00/00"
    });
}

// 封装筛选、公式、复制的通用函数
function processFilterAndCopy(selectedSheet: ExcelScript.Worksheet, dataSheet: ExcelScript.Worksheet, columnIndex: number, filterCriteria: ExcelScript.FilterCriteria) {
    // 移除旧筛选，重置状态
    if (selectedSheet.getAutoFilter()) {
        selectedSheet.getAutoFilter().remove();
    }
    // 清空公式列内容（保留表头）
    selectedSheet.getRange("DH2:DI1048576").clear(ExcelScript.ClearApplyTo.contents);

    // 初始化新筛选
    let headerRange = selectedSheet.getRange("A1:DI1");
    headerRange.autoFilter.apply();

    // 应用筛选条件
    selectedSheet.getAutoFilter().apply(headerRange, columnIndex, filterCriteria);

    // 获取完整数据范围
    let fullDataRange = selectedSheet.getRange("A1").getSurroundingRegion();
    let startRow = 2;
    let endRow = fullDataRange.getRowCount();

    // 设置Status1公式
    let formulaArray: string[][] = [];
    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.push([formula]);
    }
    selectedSheet.getRange(`DH2:DH${endRow}`).setFormulas(formulaArray);

    // 设置Status2值
    selectedSheet.getRange(`DI2:DI${endRow}`).setValue("Sorry");

    // 获取排除表头的可见行
    let visibleDataRange = fullDataRange.getVisibleView().getRange().getOffsetRange(1, 0);
    if (visibleDataRange.getRowCount() === 0) {
        console.log("没有符合条件的可见行");
        return;
    }

    // 复制到Data表
    let dataLastRow = dataSheet.getUsedRange() ? dataSheet.getUsedRange().getRowCount() + 1 : 2;
    visibleDataRange.copyTo(dataSheet.getRange(`A${dataLastRow}`), ExcelScript.RangeCopyType.values);
}

内容的提问来源于stack exchange，提问作者Anthony Smith