How to retrieve information from a non-table excel file?

[babyjb1979]

Hi Power Automate Guru,

I am new of Power Automate, I really need your help.

I am creating a power automate flow. The goal is when an excel file is updated or modified in a SharePoint folder A, the flow is triggered. I want to create another excel file in SharePoint folder B, with only three columns: "file name", "updated time" and "row counts" to recode the updated excel file in folder A. I already created a flow, but there is a flaw. The sample excel file in folder A is file with table, but if the updated file in folder A is non table excel file, the flaw is failed. As the connector "List rows present in a table" only retrieve a table. What I can do ? Covert the non table file into a table file? How to do it?

[grantjenkins]

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.

[AlexEncodian]

@babyjb1979 

Encodian has various actions that work for Excel files that do not have tables. Encodian’s Get Rows from Excel action would do the job here:

 

 

[babyjb1979]

@grantjenkins  Thank you for the solution! Can you provide a scrip that I can retrieve "file name" and "updated time"? Thank you!

[grantjenkins]

You should be able to get the file name and last modified date directly from the trigger.