Excel Script报错：Range setFormula参数无效，求可变记录数XLOOKUP自动化方案

核心错误点 #

• 模板字符串语法错误：使用双引号包裹公式时，${rowCount}不会被解析为变量值，直接作为无效文本传入公式，导致Excel无法识别，触发参数无效报错。
• autoFill方法缺少必要参数：autoFill()必须指定填充的目标范围，无参数调用无法完成自动填充操作。
• copyFrom目标范围不匹配：源范围是A1:M1（13列），但目标范围仅选L1（1列），会导致粘贴内容被截断。

修复后的完整代码 #

function main(workbook: ExcelScript.Workbook) {
    let sheet1 = workbook.getWorksheet("Sheet1");
    let usedRange = sheet1.getUsedRange();
    let rowCount: number = usedRange.getRowCount();
    console.log(rowCount);
    let selectedSheet = workbook.getWorksheet("data");

    // 用反引号包裹公式，正确解析rowCount变量
    selectedSheet.getRange("L2").setFormula(`=XLOOKUP(I2,Sheet1!$A$2:$A${rowCount},Sheet1!$A$2:$M${rowCount})`);

    // 获取data表I列有效行数，确定填充目标范围
    let dataUsedRows = selectedSheet.getRange("I:I").getUsedRange().getRowCount();
    let fillRange = selectedSheet.getRange(`L2:L${dataUsedRows}`);
    // 指定源范围和目标范围执行自动填充
    selectedSheet.getRange("L2").autoFill(fillRange, ExcelScript.AutoFillType.fillDefault);

    // 匹配源表头列数，设置正确的粘贴目标范围
    let sourceHeaderRange = sheet1.getRange("A1:M1");
    let targetHeaderRange = selectedSheet.getRange("L1").getResizedRange(0, sourceHeaderRange.getColumnCount() - 1);
    targetHeaderRange.copyFrom(sourceHeaderRange, ExcelScript.RangeCopyType.all, false, false);

    // 自动调整对应列的宽度
    selectedSheet.getRange(`L:${String.fromCharCode(76 + sourceHeaderRange.getColumnCount() - 1)}`).getFormat().autofitColumns();
}

关键修复说明 #

• 模板字符串修正：将公式的双引号替换为反引号（），使${rowCount}`被替换为实际行数，生成合法的Excel单元格引用。
• autoFill参数补充：先获取data表中I列的有效行数（XLOOKUP依赖I列内容），生成从L2到数据末尾的填充范围，再调用autoFill并指定填充类型。
• copyFrom范围匹配：通过getResizedRange将目标范围扩展为与源表头相同的列数，确保表头完整粘贴，之后自动调整对应列的宽度。

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