You’re offline. This is a read only version of the page.
10
Hi All,
I have a list of products, and for each product, a SharePoint location is created with a naming convention like:
PROD-00315124_AC21839519EBEE11904D001DD80BA1D7 (format: ProductName_ProductGUID).
Inside each product's SharePoint location, I need to navigate to the path: Issuance → costcert, open an Excel file (the file name contains Costcert), and read data from a specific sheet named "Vendors Auth to do Bus in NJ".
This sheet contains multiple rows with unique account numbers and corresponding amounts in adjacent columns (sample screenshot attached).
My goal is to:
Read values for each product.
Sum the amounts grouped by account number across all selected products.
Generate a consolidated Excel file with the summed data
Provide an option to download the consolidated Excel to the user's local machine from a Canvas App.
To implement this, I built a POC where:
I select products in a Canvas App.
On clicking a Calculate button, I send all the SharePoint paths (comma-separated) to a Power Automate Flow.
The Flow reads the Excel files, sums the amounts, and updates a pre-existing Excel file.
Challenge:
The Excel files are not in a tabular format (i.e., no defined Excel table).
I had to manually convert the data into a table first to read it properly, which is not ideal.
Request for Help:
What is the best way to read Excel data (not formatted as a table) from SharePoint in Power Automate or Azure Functions?
How can I efficiently sum the values and generate a consolidated Excel file?
Also, how can I give an option in the Canvas App to download the file locally after processing?
Any suggestions or better approaches would be greatly appreciated!
Thanks in advance!
Share
Report
All responses (
Answers (
