如何用Office Script获取表格指定列空单元格行号并自动填充分类

核心逻辑 #

对应你提供的VBA功能，Office Script的实现将完成以下步骤：

• 读取目标表格table_transactions和匹配规则表格AutoCatReference（位于Lists工作表）的数据
• 筛选出table_transactions中category列为空的行
• 对每一行空category，检查其description是否包含匹配规则中的matchWord（不区分大小写）
• 匹配成功时，将对应的matchCategory填充到空的category单元格

完整代码实现 #

function main(workbook: ExcelScript.Workbook) {
    // 获取交易记录表
    const transactionsTable = workbook.getTable("table_transactions");
    if (!transactionsTable) {
        console.error("未找到table_transactions表格");
        return;
    }

    // 获取匹配规则表（Lists工作表的AutoCatReference表格）
    const lookupTable = workbook.getWorksheet("Lists").getTable("AutoCatReference");
    if (!lookupTable) {
        console.error("未找到Lists工作表中的AutoCatReference表格");
        return;
    }

    // 提取匹配规则数据，跳过表头
    const lookupData = lookupTable.getRange().getValues();
    const lookupRules = lookupData.slice(1).map(row => ({
        matchWord: row[lookupTable.getColumnByName("matchWord").getIndex()].toString().toUpperCase(),
        matchCategory: row[lookupTable.getColumnByName("matchCategory").getIndex()]
    }));

    // 获取交易表列索引
    const descColIndex = transactionsTable.getColumnByName("description").getIndex();
    const catColIndex = transactionsTable.getColumnByName("category").getIndex();
    // 提取交易表数据行，跳过表头
    const transactionRows = transactionsTable.getRange().getValues().slice(1);

    // 遍历处理空category行
    transactionRows.forEach((row, rowIndex) => {
        if (!row[catColIndex]) {
            const description = row[descColIndex].toString().toUpperCase();
            // 遍历匹配规则
            for (const rule of lookupRules) {
                if (description.includes(rule.matchWord)) {
                    // 填充category（行号+1是因为跳过了表头，Excel行号从1开始）
                    transactionsTable.getRange().getCell(rowIndex + 1, catColIndex).setValue(rule.matchCategory);
                    // 如需仅保留第一个匹配项，取消下面的注释
                    // break;
                }
            }
        }
    });
}

关键细节说明 #

1. RangeAreas遍历方案：你最初获取的emptyCatCells是RangeAreas对象，若要直接遍历空单元格，可使用以下片段：

const emptyCatCells = transactionsTable.getColumnByName("category").getRange().getSpecialCells(ExcelScript.SpecialCellType.blanks);
emptyCatCells.getAreas().forEach(area => {
    const cellCount = area.getCellCount();
    for (let i = 0; i < cellCount; i++) {
        const cell = area.getCell(i);
        const rowIndex = cell.getRowIndex();
        // 获取对应行的description并执行匹配逻辑
        const descValue = transactionsTable.getRange().getCell(rowIndex, descColIndex).getValue().toString().toUpperCase();
        // ...匹配规则逻辑
    }
});

不过直接遍历表格所有行的方式逻辑更简洁，无需处理RangeAreas的区域拆分问题。

2. 不区分大小写匹配：通过toUpperCase()统一转换文本格式，和VBA中UCase的逻辑完全一致。

3. 匹配规则优先级：若多个匹配规则同时命中，代码会用最后一个匹配的matchCategory覆盖单元格。如需仅保留第一个匹配项，在填充后添加break;即可。

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