Assuming you just wanted to get the number of rows in the Excel data, the best approach if your Excel file didn't have a defined Table would be to utilize Office Scripts. Below is a quick script that would allow you to pass in the Excel file and specify the name of the worksheet (default to Sheet1) and whether or not your Excel file has headers in the data.
The script is:
function main(workbook: ExcelScript.Workbook,
worksheetName: string = "Sheet1",
containsHeaders: boolean = true) {
// Return number of rows in the used range
return workbook.getWorksheet(worksheetName).getUsedRange().getRowCount() - (containsHeaders ? 1 : 0)
}
You can save the Office Script in your OneDrive or in an SharePoint Library. I'd suggest the SharePoint library if this is a script used for your business. Below is an example flow that would output the number of rows in the Excel data.
My example Excel file data (no Table defined).
My example flow.
And after running the flow.
However, if you wanted to get all the data back in JSON format then you could use the following Office Script which would just require you to specify the Worksheet name if it's different to the default Sheet1.
function main(workbook: ExcelScript.Workbook,
worksheetName: string = "Sheet1") {
// Get the worksheet
const sheet = workbook.getWorksheet(worksheetName)
// Get the current used range.
let range = sheet.getUsedRange()
// Get the text values in the range.
let texts = range.getTexts()
// Create an array of JSON objects that match the row structure.
let returnObjects: TableData[] = []
if (range.getRowCount() > 0) {
returnObjects = returnObjectFromValues(texts);
}
// Log the information and return it for a Power Automate flow.
console.log('Rows: ' + returnObjects.length)
return returnObjects
}
// This function converts a 2D array of values into a generic JSON object.
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 {}
This would return something like that shown below.
[
{
"Name": "Grant",
"Job Title": "IT Specialist",
"Department": "Information Technology",
"Phone": "555-3039044"
},
{
"Name": "Joe",
"Job Title": "Laywer",
"Department": "Law",
"Phone": "555-4994837"
},
{
"Name": "Mary",
"Job Title": "Programmer",
"Department": "Information Technology",
"Phone": "555-9948473"
}
]
And the full flow to get all the data.
