使用 JSON 将数据传递到 Office 脚本和从 Office 脚本传递数据

项目
11/29/2023

JSON (JavaScript 对象表示法) 是一种用于存储和传输数据的格式。每个 JSON 对象都是可在创建时定义的名称/值对的集合。 JSON 在 Office 脚本中很有用，因为它可以在 Excel 中处理范围、表和其他数据模式的任意复杂性。 JSON 允许分析来自 Web 服务的传入数据，并通过 Power Automate 流传递复杂对象。

本文重点介绍如何将 JSON 与 Office 脚本配合使用。建议首先从 W3 学校的 JSON 简介等文章中详细了解格式。

将 JSON 数据分析为区域或表

JSON 对象的数组提供了一种在应用程序和 Web 服务之间传递表数据行的一致方式。在这些情况下，每个 JSON 对象表示一行，而属性表示列。 Office 脚本可以循环访问 JSON 数组，并将其重新组合为 2D 数组。然后，此数组设置为区域的值并存储在工作簿中。还可以将属性名称添加为标头以创建表。

以下脚本显示了要转换为表的 JSON 数据。请注意，数据不是从外部源获取的。本文稍后将对此进行介绍。

/**
 * Sample JSON data. This would be replaced by external calls or
 * parameters getting data from Power Automate in a production script.
 */
const jsonData = [
  { "Action": "Edit", /* Action property with value of "Edit". */
    "N": 3370, /* N property with value of 3370. */
    "Percent": 17.85 /* Percent property with value of 17.85. */
  },
  // The rest of the object entries follow the same pattern.
  { "Action": "Paste", "N": 1171, "Percent": 6.2 },
  { "Action": "Clear", "N": 599, "Percent": 3.17 },
  { "Action": "Insert", "N": 352, "Percent": 1.86 },
  { "Action": "Delete", "N": 350, "Percent": 1.85 },
  { "Action": "Refresh", "N": 314, "Percent": 1.66 },
  { "Action": "Fill", "N": 286, "Percent": 1.51 },
];

/**
 * This script converts JSON data to an Excel table.
 */
function main(workbook: ExcelScript.Workbook) {
  // Create a new worksheet to store the imported data.
  const newSheet = workbook.addWorksheet();
  newSheet.activate();

  // Determine the data's shape by getting the properties in one object.
  // This assumes all the JSON objects have the same properties.
  const columnNames = getPropertiesFromJson(jsonData[0]);

  // Create the table headers using the property names.
  const headerRange = newSheet.getRangeByIndexes(0, 0, 1, columnNames.length);
  headerRange.setValues([columnNames]);

  // Create a new table with the headers.
  const newTable = newSheet.addTable(headerRange, true);

  // Add each object in the array of JSON objects to the table.
  const tableValues = jsonData.map(row => convertJsonToRow(row));
  newTable.addRows(-1, tableValues);
}

/**
 * This function turns a JSON object into an array to be used as a table row.
 */
function convertJsonToRow(obj: object) {
  const array: (string | number)[] = [];

  // Loop over each property and get the value. Their order will be the same as the column headers.
  for (let value in obj) {
    array.push(obj[value]);
  }
  return array;
}

/**
 * This function gets the property names from a single JSON object.
 */
function getPropertiesFromJson(obj: object) {
  const propertyArray: string[] = [];
  
  // Loop over each property in the object and store the property name in an array.
  for (let property in obj) {
    propertyArray.push(property);
  }

  return propertyArray;
}

提示

如果知道 JSON 的结构，则可以创建自己的接口，以便更轻松地获取特定属性。可以将 JSON 到数组的转换步骤替换为类型安全的引用。以下代码片段演示了这些步骤 (现在注释掉) 替换为使用新 ActionRow 接口的调用。请注意，这会使 convertJsonToRow 函数不再需要。

  // const tableValues = jsonData.map(row => convertJsonToRow(row));
  // newTable.addRows(-1, tableValues);
  // }

     const actionRows: ActionRow[] = jsonData as ActionRow[];
     // Add each object in the array of JSON objects to the table.
     const tableValues = actionRows.map(row => [row.Action, row.N, row.Percent]);
     newTable.addRows(-1, tableValues);
   }
   
   interface ActionRow {
     Action: string;
     N: number;
     Percent: number;
   }

从外部源获取 JSON 数据

可通过两种方法通过 Office 脚本将 JSON 数据导入工作簿。

作为具有 Power Automate 流的参数。
fetch通过调用外部 Web 服务。

修改示例以使用 Power Automate

Power Automate 中的 JSON 数据可以作为泛型对象数组传递。向脚本添加属性 object[] 以接受该数据。

// For Power Automate, replace the main signature in the previous sample with this one
// and remove the sample data.
function main(workbook: ExcelScript.Workbook, jsonData: object[]) {

然后，你将在 Power Automate 连接器中看到一个选项，可添加到jsonData“运行脚本”操作。

Excel Online (Business) 连接器，其中显示了包含 jsonData 参数的“运行脚本”操作。

修改示例以使用 `fetch` 调用

Web 服务可以使用 JSON 数据回复 fetch 调用。这会为脚本提供在 Excel 中保存所需的数据。阅读 Office 脚本中的外部 API 调用支持，详细了解fetch和外部调用。

// For external services, replace the main signature in the previous sample with this one,
// add the fetch call, and remove the sample data.
async function main(workbook: ExcelScript.Workbook) {
  // Replace WEB_SERVICE_URL with the URL of whatever service you need to call.
  const response = await fetch('WEB_SERVICE_URL');
  const jsonData: object[] = await response.json();

从范围创建 JSON

工作表的行和列通常表示其数据值之间的关系。表的一行在概念上映射到编程对象，其中每一列都是该对象的属性。请考虑以下数据表。每行表示电子表格中记录的事务。

ID	日期	Amount	供应商
1	6/1/2022	$43.54	最适合你的有机公司
2	6/3/2022	$67.23	自由面包店和咖啡馆
3	6/3/2022	$37.12	最适合你的有机公司
4	6/6/2022	$86.95	Coho Vineyard
5	6/7/2022	$13.64	自由面包店和咖啡馆

每个事务 (每一行) 都有一组与之关联的属性：“ID”、“Date”、“Amount”和“Vendor”。这可以在 Office 脚本中建模为对象。

// An interface that wraps transaction details as JSON.
interface Transaction {
  "ID": string;
  "Date": number;
  "Amount": number;
  "Vendor": string;
}

示例表中的行对应于接口中的属性，因此脚本可以轻松地将每一行转换为 Transaction 对象。输出 Power Automate 的数据时，这很有用。以下脚本循环访问表中的每一行，并将其添加到 Transaction[]。

function main(workbook: ExcelScript.Workbook) {
  // Get the table on the current worksheet.
  const table = workbook.getActiveWorksheet().getTables()[0];

  // Create an array of Transactions and add each row to it.
  let transactions: Transaction[] = [];
  const dataValues = table.getRangeBetweenHeaderAndTotal().getValues();
  for (let i = 0; i < dataValues.length; i++) {
    let row = dataValues[i];
    let currentTransaction: Transaction = {
      ID: row[table.getColumnByName("ID").getIndex()] as string,
      Date: row[table.getColumnByName("Date").getIndex()] as number,
      Amount: row[table.getColumnByName("Amount").getIndex()] as number,
      Vendor: row[table.getColumnByName("Vendor").getIndex()] as string
    };
    transactions.push(currentTransaction);
  }

  // Do something with the Transaction objects, such as return them to a Power Automate flow.
  console.log(transactions);
}

// An interface that wraps transaction details as JSON.
interface Transaction {
  "ID": string;
  "Date": number;
  "Amount": number;
  "Vendor": string;
}

上一脚本的控制台输出，显示对象的属性值。

使用导出 JSON `fetch`

与使用导入数据 fetch非常相似，可以使用类似的命令从工作簿发送数据。命令 POST 采用任何字符串化的 JSON 数据，并将其发送到指定的终结点。

若要查看此操作的操作，请将上一示例中的行 console.log(transactions); 替换为以下代码。这会向测试服务器发出 POST 命令，然后读回数据。

  const response = await fetch('https://jsonplaceholder.typicode.com/posts', {
    method: 'POST',
    body: JSON.stringify(transactions),
    headers: {
      'Content-type': 'application/json; charset=UTF-8',
    },
  });
  const jsonData: object[] = await response.json();
  console.log(jsonData);

使用泛型对象

前面的示例假定表标头值是一致的。如果表具有变量列，则需要创建一个通用 JSON 对象。以下脚本显示了一个脚本，该脚本将任何表记录为 JSON。

function main(workbook: ExcelScript.Workbook) {
  // Get the table on the current worksheet.
  const table = workbook.getActiveWorksheet().getTables()[0];

  // Use the table header names as JSON properties.
  const tableHeaders = table.getHeaderRowRange().getValues()[0] as string[];
  
  // Get each data row in the table.
  const dataValues = table.getRangeBetweenHeaderAndTotal().getValues();
  let jsonArray: object[] = [];

  // For each row, create a JSON object and assign each property to it based on the table headers.
  for (let i = 0; i < dataValues.length; i++) {
    // Create a blank generic JSON object.
    let jsonObject: { [key: string]: string } = {};
    for (let j = 0; j < dataValues[i].length; j++) {
      jsonObject[tableHeaders[j]] = dataValues[i][j] as string;
    }

    jsonArray.push(jsonObject);
  }

  // Do something with the objects, such as return them to a Power Automate flow.
  console.log(jsonArray);
}

通过

使用 JSON 将数据传递到 Office 脚本和从 Office 脚本传递数据

将 JSON 数据分析为区域或表

从外部源获取 JSON 数据

修改示例以使用 Power Automate

修改示例以使用 `fetch` 调用

从范围创建 JSON

使用导出 JSON `fetch`

使用泛型对象

另请参阅

其他资源

通过

使用 JSON 将数据传递到 Office 脚本和从 Office 脚本传递数据

将 JSON 数据分析为区域或表

从外部源获取 JSON 数据

修改示例以使用 Power Automate

修改示例以使用 fetch 调用

从范围创建 JSON

使用 导出 JSON fetch

使用泛型对象

另请参阅

其他资源

修改示例以使用 `fetch` 调用

使用导出 JSON `fetch`