Вывод табличных данных Excel в виде JSON для использования в Power Automate
Данные таблицы Excel можно представить в виде массива объектов в формате JSON. Каждый объект представляет строку в таблице. Это помогает извлекать данные из Excel в согласованном формате, который виден пользователю. Затем данные могут быть переданы другим системам с помощью потоков Power Automate.
Настройка: пример файла Excel
Эта книга содержит данные, объекты и форматирование, ожидаемые скриптом.
Вариант этого примера также включает гиперссылки в одном из столбцов таблицы. Это позволяет отображать дополнительные уровни данных ячейки в ФОРМАТЕ JSON.
Пример кода: возврат данных таблицы в формате JSON
Добавьте следующий скрипт в пример книги и попробуйте его самостоятельно!
Примечание.
Структуру можно изменить в interface TableData соответствии со столбцами таблицы. Обратите внимание, что для имен столбцов с пробелами обязательно поместите ключ в кавычки, например в "Event ID" примере. Дополнительные сведения о работе с JSON см. в статье Использование JSON для передачи данных в скрипты Office и из нее.
function main(workbook: ExcelScript.Workbook): TableData[] {
// Get the first table in the "PlainTable" worksheet.
// If you know the table name, use `workbook.getTable('TableName')` instead.
const table = workbook.getWorksheet('PlainTable').getTables()[0];
// Get all the values from the table as text.
const texts = table.getRange().getTexts();
// Create an array of JSON objects that match the row structure.
let returnObjects: TableData[] = [];
if (table.getRowCount() > 0) {
returnObjects = returnObjectFromValues(texts);
}
// Log the information and return it for a Power Automate flow.
console.log(JSON.stringify(returnObjects));
return returnObjects
}
// This function converts a 2D array of values into a generic JSON object.
// In this case, we have defined the TableData object, but any similar interface would work.
function returnObjectFromValues(values: string[][]): TableData[] {
let objectArray: TableData[] = [];
let objectKeys: string[] = [];
for (let i = 0; i < values.length; i++) {
if (i === 0) {
objectKeys = values[i]
continue;
}
let object: {[key: string]: string} = {}
for (let j = 0; j < values[i].length; j++) {
object[objectKeys[j]] = values[i][j]
}
objectArray.push(object as unknown as TableData);
}
return objectArray;
}
interface TableData {
"Event ID": string
Date: string
Location: string
Capacity: string
Speakers: string
}
Пример выходных данных на листе PlainTable
[{
"Event ID": "E107",
"Date": "2020-12-10",
"Location": "Montgomery",
"Capacity": "10",
"Speakers": "Debra Berger"
}, {
"Event ID": "E108",
"Date": "2020-12-11",
"Location": "Montgomery",
"Capacity": "10",
"Speakers": "Delia Dennis"
}, {
"Event ID": "E109",
"Date": "2020-12-12",
"Location": "Montgomery",
"Capacity": "10",
"Speakers": "Diego Siciliani"
}, {
"Event ID": "E110",
"Date": "2020-12-13",
"Location": "Boise",
"Capacity": "25",
"Speakers": "Gerhart Moller"
}, {
"Event ID": "E111",
"Date": "2020-12-14",
"Location": "Salt Lake City",
"Capacity": "20",
"Speakers": "Grady Archie"
}, {
"Event ID": "E112",
"Date": "2020-12-15",
"Location": "Fremont",
"Capacity": "25",
"Speakers": "Irvin Sayers"
}, {
"Event ID": "E113",
"Date": "2020-12-16",
"Location": "Salt Lake City",
"Capacity": "20",
"Speakers": "Isaiah Langer"
}, {
"Event ID": "E114",
"Date": "2020-12-17",
"Location": "Salt Lake City",
"Capacity": "20",
"Speakers": "Johanna Lorenz"
}]
Пример кода: возврат данных таблицы в формате JSON с текстом гиперссылки
Примечание.
Скрипт всегда извлекает гиперссылки из 4-го столбца (индекс 0) таблицы. Вы можете изменить этот порядок или включить несколько столбцов в качестве данных гиперссылки, изменив код в примечании. // For the 4th column (0 index), extract the hyperlink and use that instead of text.
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
}
Пример выходных данных на листе WithHyperLink
[{
"Event ID": "E107",
"Date": "2020-12-10",
"Location": "Montgomery",
"Capacity": "10",
"Search link": "https://www.google.com/search?q=Montgomery",
"Speakers": "Debra Berger"
}, {
"Event ID": "E108",
"Date": "2020-12-11",
"Location": "Montgomery",
"Capacity": "10",
"Search link": "https://www.google.com/search?q=Montgomery",
"Speakers": "Delia Dennis"
}, {
"Event ID": "E109",
"Date": "2020-12-12",
"Location": "Montgomery",
"Capacity": "10",
"Search link": "https://www.google.com/search?q=Montgomery",
"Speakers": "Diego Siciliani"
}, {
"Event ID": "E110",
"Date": "2020-12-13",
"Location": "Boise",
"Capacity": "25",
"Search link": "https://www.google.com/search?q=Boise",
"Speakers": "Gerhart Moller"
}, {
"Event ID": "E111",
"Date": "2020-12-14",
"Location": "Salt Lake City",
"Capacity": "20",
"Search link": "https://www.google.com/search?q=salt+lake+city",
"Speakers": "Grady Archie"
}, {
"Event ID": "E112",
"Date": "2020-12-15",
"Location": "Fremont",
"Capacity": "25",
"Search link": "https://www.google.com/search?q=Fremont",
"Speakers": "Irvin Sayers"
}, {
"Event ID": "E113",
"Date": "2020-12-16",
"Location": "Salt Lake City",
"Capacity": "20",
"Search link": "https://www.google.com/search?q=salt+lake+city",
"Speakers": "Isaiah Langer"
}, {
"Event ID": "E114",
"Date": "2020-12-17",
"Location": "Salt Lake City",
"Capacity": "20",
"Search link": "https://www.google.com/search?q=salt+lake+city",
"Speakers": "Johanna Lorenz"
}]
Использование в Power Automate
Сведения об использовании такого сценария в Power Automate см. в статье Создание автоматизированного рабочего процесса с помощью Power Automate.
Совместная работа с нами на GitHub
Источник этого содержимого можно найти на GitHub, где также можно создавать и просматривать проблемы и запросы на вытягивание. Дополнительные сведения см. в нашем руководстве для участников.
Office Scripts