Output Excel table data as JSON for usage in Power Automate
Excel table data can be represented as an array of objects in the form of JSON. Each object represents a row in the table. This helps extract the data from Excel in a consistent format that is visible to the user. The data can then be given to other systems through Power Automate flows.
Setup: Sample Excel file
This workbook contains the data, objects, and formatting expected by the script.
A variation of this sample also includes the hyperlinks in one of the table columns. This allows additional levels of cell data to be surfaced in the JSON.
Sample code: Return table data as JSON
Add the following script to the sample workbook and try the sample yourself!
Note
You can change the interface TableData structure to match your table columns. Note that for column names with spaces, be sure to place your key in quotation marks, such as with "Event ID" in the sample. For more information about working with JSON, read Use JSON to pass data to and from Office Scripts.
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
}
Sample output from the "PlainTable" worksheet
[{
"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"
}]
Sample code: Return table data as JSON with hyperlink text
Note
The script always extracts hyperlinks from the 4th column (0 index) of the table. You can change that order or include multiple columns as hyperlink data by modifying the code under the comment // 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
}
Sample output from the "WithHyperLink" worksheet
[{
"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"
}]
Use in Power Automate
For how to use such a script in Power Automate, see Create an automated workflow with Power Automate.
Office Scripts
Feedback
Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see: https://aka.ms/ContentUserFeedback.
Submit and view feedback for