Power Automate中动态填充含可变日期列Excel表格的问题

一、编写Office Scripts脚本 #

这个脚本会自动识别JSON中的所有列（包括新增的日期列），对比Excel现有列并补充缺失项，再将数据写入表格。

在Excel中打开目标工作簿，点击自动化 > 新建脚本，粘贴以下代码：

function main(workbook: ExcelScript.Workbook, jsonData: string) {
  // 解析输入的JSON数据
  const data: Record<string, string>[] = JSON.parse(jsonData);
  if (data.length === 0) return;

  // 替换为你的目标工作表名称
  const targetSheetName = "Sheet1";
  const sheet = workbook.getWorksheet(targetSheetName);
  
  // 提取所有唯一列名（包括Portfolio和动态日期列）
  const allColumns = new Set<string>();
  data.forEach(item => {
    Object.keys(item).forEach(key => allColumns.add(key));
  });
  let columnList = Array.from(allColumns);

  // 排序列：让Portfolio在最前，日期列按时间顺序排列
  columnList.sort((a, b) => {
    if (a === "Portfolio") return -1;
    const dateA = new Date(a + "-01");
    const dateB = new Date(b + "-01");
    return dateA.getTime() - dateB.getTime();
  });

  // 处理现有表头和数据
  const usedRange = sheet.getUsedRange();
  let existingColumns: string[] = [];
  let startDataRow = 2; // 默认表头在第1行，数据从第2行开始

  if (usedRange) {
    // 获取现有表头
    const headerValues = usedRange.getRow(0).getValues()[0];
    existingColumns = headerValues.filter(cell => cell !== "") as string[];

    // 清空现有数据行（保留表头）
    if (usedRange.getRowCount() > 1) {
      const lastCellAddress = usedRange.getAddress().split(":")[1];
      sheet.getRange(`A2:${lastCellAddress}`).clear(ExcelScript.ClearApplyTo.contents);
    }
  } else {
    // 工作表无数据，直接写入表头
    sheet.getRangeByIndexes(0, 0, 1, columnList.length).setValues([columnList]);
  }

  // 新增缺失的列到表头
  columnList.forEach(col => {
    if (!existingColumns.includes(col)) {
      const newColIndex = existingColumns.length;
      sheet.getRangeByIndexes(0, newColIndex, 1, 1).setValue(col);
      existingColumns.push(col);
    }
  });

  // 按现有列顺序整理数据行，缺失列填充空值
  const dataRows = data.map(item => {
    return existingColumns.map(col => item[col] || "");
  });

  // 写入数据到工作表
  if (dataRows.length > 0) {
    sheet.getRangeByIndexes(startDataRow - 1, 0, dataRows.length, existingColumns.length).setValues(dataRows);
  }
}

二、Power Automate流配置步骤 #

1. 在你的Power Automate流中，添加Excel Online (Business) > Run script动作
2. 配置动作参数：
   • 选择目标工作簿和对应的工作表
   • 在jsonData输入框中，传入你的JSON数据源（如果是对象格式，用string()函数转换为字符串，例如string(outputs('Parse_JSON')?['body'])）
3. 保存并测试流，新增的日期列会自动添加到Excel表格中，数据会按列对应填充

关键说明 #

• 脚本中的targetSheetName需要替换为你实际使用的工作表名称
• 如果需要追加数据而非覆盖现有数据，删除脚本中清空数据行的代码，改为通过getUsedRange().getRowCount()获取最后一行，从下一行开始写入数据
• 脚本会自动对日期列按时间排序，确保表格列顺序符合逻辑

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