cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ganeshsanap
Super User
Super User

Is there any way to get the column headers from Excel file & exact match it with predefined array?

I have a predefined array of column names (these are the columns in SharePoint list) like:

["Z-Index2", "Material", "EAN"]

And excel file in below format:

 

ganeshsanap_0-1614764584515.png

I want to fetch the column headers from excel file and exact match it with the predefined array (given above). I want to add excel rows in SharePoint list only if all column headers are matching.

 

I tried solution given at Excel - check if columns are present, but it does not exact match the column names.

4 REPLIES 4
analmei
Microsoft
Microsoft

Hello @ganeshsanap , 

 

There is no way to get the excel column names with the action available in the connector. We did a couple of tests with Get rows and Get Table action and by checking the body output with a compose action, we verified that is only possible to get the records associated with the columns. 

 

Even if the option you shared worked, it will consume a lot of resources and it is not a good option in terms of performance, you will easily reach the throttling limits.  

 

Best regards,

Ana Almeida | Power Platform Support Engineer

 

Serbay
Advocate II
Advocate II

Hi,

I had also faced with same limitation. Actually, existing excel actions are quite limited.

The only way to get headers with using Typescript.(Office Script)

You can basically get the first row in spreadsheet and compare the values whether they match it or not.

 

If I had answered your question, please accept as a solution.

 

Regards,

Serbay

 

ganeshsanap
Super User
Super User

@Serbay 

 

Can you please explain in detail, how you are getting column headers using Typescript.(Office Script)?

 


Please click Accept as solution & ‌‌👍 if my answer helped you to solve your issue. This will help others to find the correct solution easily. If the answer was useful in other ways, please consider giving it ‌‌👍

Serbay
Advocate II
Advocate II

Hi @ganeshsanap ,
Sure, 
Go to automate tab -> Create new script 

Here is a sample code ;
function main(workbook: ExcelScript.Workbook, rangeAddress: string): string {
let sh = workbook.getFirstWorksheet();
let rg = sh.getRange(rangeAddress)
return JSON.stringify(rg.getValues());

Then save the script. Now you can create your flow and use 'Script Action' to call the script

As an example, 
Enter your range to capture the Header; in my case A1:F1

Then add compose action and pick 'Result' from dynamic content

Serbay_4-1615190739223.png

 

 

Serbay_3-1615190697509.png 


If I have answered your question, please Accept the post as a solution.

If you like my response, please give it a Thumbs Up.

 

Regards,
Serbay

 

Helpful resources

Announcements
MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

MPA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

secondImage

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

Top Solution Authors
Top Kudoed Authors
Users online (88,388)