Ausgeben von Excel-Tabellendaten als JSON für die Verwendung in Power Automate
Excel-Tabellendaten können als Array von Objekten in Form von JSON dargestellt werden. Jedes Objekt stellt eine Zeile in der Tabelle dar. Dies hilft, die Daten aus Excel in einem konsistenten Format zu extrahieren, das für den Benutzer sichtbar ist. Die Daten können dann über Power Automate-Flows an andere Systeme übergeben werden.
Setup: Excel-Beispieldatei
Diese Arbeitsmappe enthält die Daten, Objekte und Formatierungen, die vom Skript erwartet werden.
Eine Variante dieses Beispiels enthält auch die Links in einer der Tabellenspalten. Dadurch können zusätzliche Ebenen von Zelldaten im JSON-Code angezeigt werden.
Beispielcode: Zurückgeben von Tabellendaten als JSON
Fügen Sie der Beispielarbeitsmappe das folgende Skript hinzu, und probieren Sie das Beispiel selbst aus!
Hinweis
Sie können die interface TableData Struktur so ändern, dass sie ihren Tabellenspalten entspricht. Beachten Sie, dass Sie bei Spaltennamen mit Leerzeichen den Schlüssel in Anführungszeichen setzen, z. B. mit "Event ID" im Beispiel. Weitere Informationen zum Arbeiten mit JSON finden Sie unter Verwenden von JSON zum Übergeben von Daten an und aus Office-Skripts.
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
}
Beispielausgabe des Arbeitsblatts "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"
}]
Beispielcode: Zurückgeben von Tabellendaten als JSON mit Linktext
Hinweis
Das Skript extrahiert Hyperlinks immer aus der 4. Spalte (0 Index) der Tabelle. Sie können diese Reihenfolge ändern oder mehrere Spalten als Hyperlinkdaten einschließen, indem Sie den Code unter dem Kommentar ändern. // 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
}
Beispielausgabe des Arbeitsblatts "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"
}]
Verwenden in Power Automate
Informationen zur Verwendung eines solchen Skripts in Power Automate finden Sie unter Erstellen eines automatisierten Workflows mit Power Automate.
Office Scripts