How to get Column Names of Table from an Excel Sheet in MS Flow

[GLPALLAVI]

Hi All,

I am trying to fetch only the Column Names of a predefined table from an Excel Sheet in SP Doc Library in MS Flow.

I do not want to use List rows present in table connector , because it locks the excel file for 6 min .

I do not want the file to be locked as i want to update the status of excel file in doc library  after verifying the columns.

Is there any other way in which i can read the columns of excel ?

[rzaneti]

Hi @GLPALLAVI ,

If your organization does not restrict it, you can use Office Scripts to achieve it. The Office Scripts are a kind of "VBA", but based in JavaScript, available for Excel Web and integrated to Power Automate.

Let's take this table as example:

Create the following Script:

function main(workbook: ExcelScript.Workbook) {
    const tbl = workbook.getTable('Table1')

    const allColumns = tbl.getColumns()

    const columnNames:string[] = []
    for(let i = 0; i < allColumns.length; i++){
        columnNames.push(allColumns[i].getName())

    }
    return columnNames
}

In const tbl = workbook.getTable('Table1'),  replace the text in red for your table name. 

In Power Automate, insert a 'Run Script' action, from Excel connector, and select the file containing the table and the script that you created:

The script outputs will be stored in 'result' property from your 'Run script' action (highlighted in yellow):

For this example, the script is returning the column names as elements from an array, that's why I initialized the variable with this data type.

And this is the output of 'columns' variable after run the flow:

If this is the first time writing Office Scripts, follow the steps from this blog post: http://digitalmill.net/2023/06/19/get-started-with-office-scripts/ 

Let me know if it works for you or if you need any additional help!

-------------------------------------------------------------------------
If this is the answer for your question, please mark the post as Solved.
If this answer helps you in any way, please give it a like.

http://digitalmill.net/ 

[GLPALLAVI]

Hi ,

Even if i use Run as Script , it locks the file for 6 min plus .

When i try to update the file properties of the same file it throws an error saying File is locked.

Any other way in which  firstly i need to : 1) read the excel column 2) Do Some validation based on the columns 3) Update the Excel file back without waiting 

[rzaneti · Accepted solution]

Hi @GLPALLAVI ,

In Microsoft Docs, there is a reference of the possibility of the file to be locked for up to 6mins when using Excel connector in Power Automate. Personally, I never saw it take that long (but it is possible, according to the docs).

Some possible workarounds:

1) If your business rule does not require a fast flow run, include a delay after your 'Run Script' action to prevent the locking issue (just some seconds may be enough, but it is possible that you will need to wait up to 6mins, according to the docs).

2) Apparently, you are saving a file extracted from your email attachments. Maybe you can create a clone from the file (which will contains the same columns from the original one) only to perform the 'Run Script' action without lock the original file.

Let me know if it works from your end. 

[Chriddle]

You can use a Select action for this.

You need to do a Get rows and then investigate the outcome using xpath:

Select's From:

xpath(
	xml(json(concat('{"Root":', first(outputs('List_rows_present_in_a_table')?['body/value']),'}}'))),
	'/*/*'
)

Select's Map:

xpath(item(), 'name(/*)')

This returns an array of the table's row names.

Downsides:

1. The table must have at least one entry.
2. The special characters (including spaces) of the row names are encoded in XML style and it can be tedious to convert them.

[GLPALLAVI]

In the second option when you say clone the document - Does it mean i save the file temp in a different  library ? Or Is there an option to extract the columns from Attachment Body itself ?

[rzaneti]

Hi @GLPALLAVI ,

The 'clone' idea is related to create a second file as temp in a document library or in OneDrive. As Excel actions require the Excel file location, I believe that it won't be possible to reference the attachment file directly, without saving it somewhere.