Office Scripts循环开发需求：修复Excel Online公式引用错误

以下是符合需求的Office Script代码，可直接在Excel Online中创建并通过Power Automate每日运行：

async function main(workbook: ExcelScript.Workbook) {
    // 1. 读取Ref工作表中的用户名列表（假设用户名在A列，第一行为表头）
    const refSheet = workbook.getWorksheet("Ref");
    const usernameValues = refSheet.getUsedRange().getValues();
    const usernames: string[] = [];

    // 提取用户名（跳过表头行）
    for (let i = 1; i < usernameValues.length; i++) {
        const username = usernameValues[i][0]?.toString().trim();
        if (username) {
            usernames.push(username);
        }
    }

    if (usernames.length === 0) {
        console.log("未从Ref工作表读取到用户名");
        return;
    }

    // 2. 遍历所有工作表（排除Ref工作表）
    const worksheets = workbook.getWorksheets();
    await context.sync();

    for (const sheet of worksheets) {
        if (sheet.getName() === "Ref") {
            continue;
        }

        const usedRange = sheet.getUsedRange();
        if (!usedRange) {
            continue;
        }

        // 3. 对当前工作表批量替换错误引用
        for (const username of usernames) {
            // 跳过当前工作表对应的用户名（无需替换自身引用）
            if (username === sheet.getName()) {
                continue;
            }

            // 构建查找和替换文本：移除用户名前缀（如JSmith[列名] → [列名]）
            const findPattern = `${username}[`;
            const replacePattern = `[`;

            // 执行替换操作
            usedRange.replace(findPattern, replacePattern, {
                matchCase: true,
                matchEntireCellContents: false,
                searchOrder: ExcelScript.SearchOrder.byRows
            });
        }
    }

    // 保存工作簿（确保修改生效）
    workbook.save();
    await context.sync();
    console.log("公式引用修复完成");
}

使用说明 #

• Ref工作表格式要求：用户名需放在A列，第一行为表头（如"用户名"），从第二行开始依次填写JSmith、JDoe等用户名
• 脚本创建步骤：在Excel Online中点击「自动化」→「新建脚本」，粘贴上述代码后保存（命名如FixFormulaReferences）
• Power Automate配置：添加「运行脚本」动作，选择该工作簿和脚本，设置每日定时触发即可

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