Answered

Create an Excel with a few columns from a SharePoint site with the click of a button

(0) Share

Report

Posted on by MSFTPowerNoob

Hi All,

I have a SharePoint list with 40+ columns and I want to generate an invoice, in an Excel, when I press a button in a Power app that the list is connected to.

Details:

I have a SharePoint list that gets several columns populated by a user through a UI in a power app (mostly financial details). Once this is done, I want to give the user the capability to generate an Excel invoice with only 4-5 different (but constant) columns in the list. This process has to be done every month. The generated file has to follow a predefined Excel template (.xlsx).

I want to have the following columns in the Excel that would be generated at the press of the button

Department | Account | Cost incurred | Name (limited to 50 characters appended by "Charges" |

This file also needs to be named in a certain way - "Excel <Month> <Year>.xlsx".

I've tried various things like getting items from the SharePoint list and mapping the columns using Select and adding them as the row to the newly created Excel. However, due to too many dynamic components in the process, I was unable to get anything to work.

I'd like a fresh and new (hopefully easier) perspective and see if this is even possible.

Categories:

Building flows

I have the same question (0)

All responses (2)

Answers (1)

Verified answer

Mark Nanneman 991 Super User 2025 Season 2 on at

Like (1)

Report

You can do this with the standard Excel actions but you'll need your flow to make a copy file of the excel template, then add the rows one by one.

If you have so many line items / rows to add the Excel table that it's impractical to do it in a for each loop, then you can use an API call to add the rows in batch.

Here's a good post by Matthew Devaney on this:
https://www.matthewdevaney.com/quickly-add-multiple-rows-to-excel-table-in-power-automate/#Create-An-Array-Of-Multiple-Excel-Rows

IMO using basic power automate features it's much easier to create an HTML table or a CSV. You just map the Headers to the Values with a Select action then use the HTML or CSV option to convert the Select output to a CSV.

If you can you use email as your invoice (instead of an .xlsx file), you can just dump the HTML output into the email body, or you can attach the CSV output to the email as an attached file.

There are also fairly inexpensive connectors available in power automate that will convert CSV to Excel--some might be free. Just search for CSV to Excel. You could create your CSV with the OOTB action then call one of these connectors to convert that to xlsx.

I've built flows in the past that create invoices with Microsoft Word Templates. There's a bit of a learning curve to figuring out how to add the dynamic fields and repeating dynamic rows in a table to a Word document (for your table of line items), but once you learn how to do it's fairly straightforward how to populate it in a power automate flow then convert it to a pdf and send it in an email. You can just dump all the rows, even if there's hundreds, from your select operation straight into the word template.

Was this reply helpful? Yes No
Suggested answer

MSFTPowerNoob 45 on at

Like (0)

Report

Thanks, Mark Nanneman, for your suggestions. I was able to fulfil my requirements by following different blog post by the same author. This was sufficient to run through a list of 50 SharePoint list items in about 2 minutes, great for an MVP. I'll be trying out the blog post you shared shortly as it says it's quicker. The one I tried pretty much has your suggestions of using the Select action and using the mapped columns in the dynamically created Excel file. Thanks for your suggestions.

Was this reply helpful? Yes No