Office Script提示写入Data工作表但无内容且无报错求助

问题现象 #

运行Office Script无报错，提示写入完成，但"Data"工作表无内容。即使使用仅写入A1单元格的简单脚本也无效，工作簿为本地新建、无保护设置。

核心排查与修复方向 #

• 工作表名称校验：确认脚本中"Data"与工作表实际名称完全一致（注意大小写、空格等细节）
• UsedRange逻辑修正：原脚本中新建工作表后仅通过values赋值表头，未触发Excel对UsedRange的更新，导致后续行计算错误
• 写入方式优化：优先使用单个单元格的setValue()方法，避免因范围引用或数组格式问题导致写入失效

修复后的完整代码 #

/**
 This script submits data from the "Setup" sheet to the "Data" sheet,
 adds the data to the next available line (skipping a line between entries),
 and clears only the contents of the input fields on the "Setup" sheet, retaining validation.
 **/
function main(workbook: ExcelScript.Workbook) {
  // Access the "Setup" and "Data" sheets
  const setupSheet = workbook.getWorksheet("Setup");
  let dataSheet = workbook.getWorksheet("Data");

  // Create "Data" sheet if it doesn't exist
  if (!dataSheet) {
    dataSheet = workbook.addWorksheet("Data");
    // Set up headers in the "Data" sheet
    const headers = ["G4", "G6", "G8", "D10", "D11", "D12", "D15", "D23"];
    headers.forEach((header, colIndex) => {
      dataSheet.getCell(0, colIndex).setValue(header);
    });
  }

  // Get data from specified cells on the "Setup" sheet
  const values = [
    setupSheet.getRange("G4").getText(),
    setupSheet.getRange("G6").getText(),
    setupSheet.getRange("G8").getText(),
    setupSheet.getRange("D10").getText(),
    setupSheet.getRange("D11").getText(),
    setupSheet.getRange("D12").getText(),
    setupSheet.getRange("D15").getText(),
    setupSheet.getRange("D23").getText(),
  ];

  // Find the next available row on the "Data" sheet, skipping a row
  const lastRowIndex = dataSheet.getUsedRange()?.getLastRow().getRowIndex() || 0;
  const nextRowIndex = lastRowIndex + 2; // Skip a row after last data row

  // Add data to the next row
  values.forEach((value, colIndex) => {
    dataSheet.getCell(nextRowIndex, colIndex).setValue(value);
  });

  // Clear input cell contents on "Setup" sheet
  const inputRanges = ["G4", "G6", "G8", "D10", "D11", "D12", "D15", "D23"];
  inputRanges.forEach(range => {
    setupSheet.getRange(range).clear(ExcelScript.ClearApplyTo.contents);
  });
}

关键修复点说明 #

1. 表头写入优化：使用getCell().setValue()逐个写入表头，确保Excel正确识别已使用区域，避免UsedRange为空的问题
2. 行索引计算修正：通过getLastRow().getRowIndex()获取实际最后行的索引（从0开始），计算下一行时逻辑更准确
3. 数据写入稳定性：遍历数组逐个写入单元格，减少因范围引用或数组格式不匹配导致的写入失效
4. 代码简化：用数组遍历简化重复的单元格操作，提升代码可维护性

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