Использование JSON для передачи данных в скрипты Office и из нее

JSON (Нотация объектов JavaScript) — это формат для хранения и передачи данных. Каждый объект JSON представляет собой коллекцию пар "имя-значение", которые можно определить при создании. Json полезен для сценариев Office, так как он может обрабатывать произвольные сложности диапазонов, таблиц и других шаблонов данных в Excel. JSON позволяет анализировать входящие данные из веб-служб и передавать сложные объекты через потоки Power Automate.

В этой статье рассматривается использование JSON со скриптами Office. Рекомендуется сначала узнать больше о формате из таких статей, как введение в JSON от W3 Schools.

Анализ данных JSON в диапазоне или таблице

Массивы объектов JSON обеспечивают согласованный способ передачи строк табличных данных между приложениями и веб-службами. В таких случаях каждый объект JSON представляет строку, а свойства — столбцы. Скрипт Office может выполнить цикл по массиву JSON и повторно собрать его в виде двухмерного массива. Затем этот массив задается в качестве значений диапазона и сохраняется в книге. Имена свойств также можно добавить в качестве заголовков для создания таблицы.

Следующий скрипт показывает, как данные 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 из внешних источников

Существует два способа импорта данных JSON в книгу с помощью сценария Office.

• Как параметр с потоком Power Automate.
• fetch С помощью вызова внешней веб-службы.

Изменение примера для работы с Power Automate

Данные JSON в Power Automate можно передать в виде универсального массива объектов. Добавьте свойство 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 в действие Выполнить скрипт .

Изменение примера для использования fetch вызова

Веб-службы могут отвечать fetch на вызовы с данными JSON. Это дает скрипту необходимые данные, сохраняя при этом в Excel. Дополнительные сведения о fetch внешних вызовах и о внешних вызовах см. в статье Поддержка внешних вызовов API в скриптах Office.

// 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 из диапазона

Строки и столбцы листа часто подразумевают связи между значениями данных. Строка таблицы концептуально сопоставляется с программным объектом, при этом каждый столбец является свойством этого объекта. Рассмотрим следующую таблицу данных. Каждая строка представляет транзакцию, записанную в электронной таблице.

Идентификатор	Дата	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
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);
}

См. также

• Поддержка внешнего вызова API в сценариях Office
• Пример. Использование вызовов внешней выборки в сценариях Office
• Запуск сценариев Office с помощью Power Automate