如何使用 Office 脚本将 Excel 表转换为特定 JSON 格式

Question

我正在尝试从 Office 脚本获取特定的 JSON 输出，以便使用 Power Automate 进行 API 调用。我从 Power Automate 收到的输出不符合 API 文档中所需的格式（链接到下面的 API 文档）。尝试修改脚本以获得所需的输出，但不幸的是，我刚刚开始使用js，所以我无法弄清楚我需要什么。

现在，输入必须来自 Excel 表格。如果需要，我可以为此流程设置不同的 Excel 表格格式，但尽管如此，输入必须来自 Excel 表格。现在，Excel 表格如下所示：

这是我正在使用的 Office 脚本，来自此博客文章： https://learn.microsoft.com/en-us/office/dev/scripts/resources/samples/get-table-data :

function main(workbook: ExcelScript.Workbook): TableData[] {
 // Get the first table in the "WithHyperLink" worksheet.
 // If you know the table name, use `workbook.getTable('TableName')` instead.
 const table = workbook.getWorksheet('WithHyperLink').getTables()[0];

 // Get all the values from the table as text.
 const range = table.getRange();

 // Create an array of JSON objects that match the row structure.
 let returnObjects: TableData[] = [];
 if (table.getRowCount() > 0) {
 returnObjects = returnObjectFromValues(range);
}

// Log the information and return it for a Power Automate flow.
console.log(JSON.stringify(returnObjects));
return returnObjects
}

function returnObjectFromValues(range: ExcelScript.Range): TableData[] {
 let values = range.getTexts();
 let objectArray : TableData[] = [];
 let objectKeys: string[] = [];
 for (let i = 0; i < values.length; i++) {
 if (i === 0) {
  objectKeys = values[i]
  continue;
}

let object = {}
for (let j = 0; j < values[i].length; j++) {
  // For the 4th column (0 index), extract the hyperlink and use that instead of text. 
  if (j === 4) {
    object[objectKeys[j]] = range.getCell(i, j).getHyperlink().address;
  } else {
    object[objectKeys[j]] = values[i][j];
  }
}

objectArray.push(object as TableData);
 }
 return objectArray;
}

interface TableData {
  "Event ID": string
  Date: string
  Location: string
  Capacity: string
  "Search link": string
  Speakers: string
 }

这就是当我运行 Office 脚本时，我在 Power Automate 中得到的输出：

[
 {
  "Line": "",
   "Id": "0",
   "Description": "nov portion of rider insurance",
   "Amount": "100",
   "DetailType": "JournalEntryLineDetail",
   "JournalEntryLineDetail": "",
   "PostingType": "Debit",
   "AccountRef": "",
   "value": "39",
   "name": "Opening Bal Equity"
 },
{
   "Line": "",
   "Id": "",
   "Description": "nov portion of rider insurance",
   "Amount": "100",
   "DetailType": "JournalEntryLineDetail",
   "JournalEntryLineDetail": "",
   "PostingType": "Credit",
   "AccountRef": "",
   "value": "44",
   "name": "Notes Payable"
 }
]

但是，我需要的架构如下所示（它基于此 API 文档 https://developer.intuit.com/app/developer/qbo/docs/api/accounting/all-entities/journalentry）：

{
 "Line": [
   {
     "Id": "0",
     "Description": "nov portion of rider insurance",
     "Amount": 100.0,
     "DetailType": "JournalEntryLineDetail",
     "JournalEntryLineDetail": {
     "PostingType": "Debit",
     "AccountRef": {
            "value": "39",
            "name": "Opening Bal Equity"
          }
    }
  },
  {
    "Description": "nov portion of rider insurance",
    "Amount": 100.0,
    "DetailType": "JournalEntryLineDetail",
    "JournalEntryLineDetail": {
    "PostingType": "Credit",
          "AccountRef": {
            "value": "44",
            "name": "Notes Payable"
          }

   }
  }
 ]
}

有很多差异，显然，当我尝试制作API 调用时，我收到 400“错误请求”错误。有谁知道我必须如何修改脚本或 Excel 表或在 Power Automate 中执行不同的操作才能获得我需要的特定架构？

任何帮助将不胜感激。谢谢！！

Answer 1

我认为发生的事情的核心是您的脚本正在解析所有内容以匹配您在将其发送到 Stringify 方法之前遵循的教程中给出的线性“TableData”接口，但您的数据与该接口不匹配，因此它尽其所能并将每一行输出到一个对象数组中。当 Stringify 被调用时，它会看到一个普通对象数组，因此它只是将所有内容转换为字符串。

我认为您希望它更加结构化，这意味着您需要手动编码为每一行传递的对象。基本上，您的 JSON 模式告诉您的是您的数据类型应该是这样的：

Interface AccountRefPart {
value: string
name: string
}

Interface JournalEntryLineDetailPart {
PostingType: string
AccountRef: AccountRefPart
}

Interface LinePart {
ID?: string
Description: string
Amount: number
DetailType: string
JournalEntryLineDetail: JournalEntryLineDetailPart
}

Interface TableData {
Line: LinePart[]
}

如果您只想将单个 Line 元素作为 JSON 传递（最外面的大括号建议的内容），您需要将其字符串化TableData 类型的单个对象，并且您希望使用表行中的数据构造此对象。 （我实际上看不到您的表格，但我相信上面有您需要的信息。）