Create a new excel file with multiple SharePoint list item

[PowerApps999]

I wanted to know a possibility before trying it out.

Scenario:

I have 14 SharePoint lists in which the title (Serial No.) is same and common for all the lists, basically all the lists are different parts of the same aircraft. I have a Gallery connected to one of the list and on select, it’ll show 14 forms with all the details. As the title is same, it fetches all the data from the 14 lists to the 14 forms. Is there a way to create an automated flow to create an excel for that particular item selected in gallery and have the information from all the 14 SharePoint lists so that it will be the whole report of that respective item selected (Serial No.).

Kindly let me know if this is possible.

Thank you.

[FLMike · Accepted solution]

@PowerApps999 

Hi, yes it would be quite easy

(So that is the answer you wanted before trying it). Here are some cursory steps, so if you mark this as answered and open a new post, (if you need help) that would be great.

1. Create a flow that uses the Power Apps (V2) connector

2. Add an Input that is a string (which will be your RowId of the selected Item)

3. Use a Get Item Action (SharePoint), to get the main record from the primary list, using the Input Row ID

4. How.. after that you have to decide, if you wanted to send ALL rows ids from ALL 15 lists (in total) to the flow at one time. If so your life is easier.

5. You would need to use Get Item Actions for all 15 lists. But if you passed in all the RowIDs for each List, then you just use Get Item action (not Get items), using the passed in RowId, for each respective List

6. Now you need to Already HAVE an Excel File, that acts like a template. It is NOT a template, it is simply an excel that is saved in OneDrive or SharePoint, that has a Table around the layout you want to put the data. This is required if you want to put data into it. 

7. If the file (template) exists, I would do a Copy File (SharePoint/OneDrive) to make a copy of it for this particular Report, as you cannot build the Excel files otherwise.

8. After you have Copied the file, you can now use Add a row to a table (Excel action) to add data from your 15 previous Get Item actions.

9. Email a link to or whatever you want to do.

10. In your canvas app add the Flow

11. In your canvas app, add a button to send the Row(s) ids of all or the main or whatever you want to the Flow.

And done.

If I have helped you, I would really appreciate if you please Mark my answer as Resolved/Answered, and give it a thumbs up, so it can help others

Cheers

Thank You
Michael Gernaey MCT | MCSE | MCP | Self-Contractor| Ex-Microsoft
https://gernaeysoftware.com
LinkedIn: https://www.linkedin.com/in/michaelgernaey

[sandeepstw]

Hi There,

Yes, you can achieve this using Power Automate and Power Apps. Using this flow -

In Power Apps, when an item in the gallery is selected, invoke a Power Automate Flow and pass the Serial No. (title) to the Flow.

Initialize an array variable to store all the data.

For each SharePoint list. Use the "Get Items" SharePoint action filtered by Serial No. Append the fetched data to the array variable. Use the "Create Table" Excel Online (Business) action to create a table with the collected data. Use the "Add a Row" Excel Online (Business) action, inside a loop, to populate the Excel table with the data from the array.

Once the Flow completes, you can either notify the user of the completion or directly provide a download link to the generated Excel.

Ensure that the Flow account has the necessary permissions to all SharePoint lists, the Excel Online location, and that throttling limits are observed if the lists contain a large number of items.

Please give kudos and mark as solution if it helps.  

Thanks, 

Sandeep Mishra 

[PowerApps999]

Thanks for the reply.

How do I transfer the title to flow from PowerApps? I have a variable when I select the gallery in which all the other 14 forms get this variable and knows which item to show

Set(varNoselg, ThisItem.Title)

Should I use this variable in flow too, if yes, how can I achieve that? Or is there any other way to transfer the title to flow?

[sandeepstw · Accepted solution]

In your gallery, select the element (e.g., a button or the gallery itself) that you want users to click or tap to select an item. This is usually done in the 'OnSelect' property of the element.

n the OnSelect property of the element, use the PowerApps function to trigger a Power Automate Flow. You can use the PowerApps function with the Power Automate action to invoke the Flow. Here's an example formula-

PowerAutomate.Run("<Your Flow Name>", {SerialNumber: ThisItem.Title})

[PowerApps999]

Thank you for the reply. One more question :

I have attachment column in my sharepoint list which has .jpg files. Can I export this column too to the excel file? If yes will it be the hyperlink for the file or the exact file?

[PowerApps999]

In the 8th step, when using 'Add a new row' action, I am not able to see the tables in my template excel.