Power Automate flow to batch convert each item in a SharePoint list into individual PDF files

[chadangrisani]

Hi all,

I created an inspection app in Power Apps that saves the results of each inspection to a SharePoint list on the company's intranet. Everything works as expected, no issues whatsoever. I began playing with Power Automate after the app launched to format the collected data into something more user-friendly and visually appealing. Now whenever an inspection is submitted and a new list item is created, I have a Power Automate flow that inserts the new list item's data into an HTML-formatted document in OneDrive, converts that HTML into a PDF document in OneDrive, then saves the PDF to a library folder back on the company SharePoint and deletes the HTML from my OneDrive. All of this works wonderfully.

However, because I didn't get this flow set up until a couple of months after launching the app, my SharePoint list of inspections has around 200 items that were created before my archive flow was created. And you better believe I want those inspections to also be archived in the same manner as all current and future inspections, because of course I do. Herein lies my dilemma.

I created another flow to archive the existing list items (well, technically, I've now created at least four different flows, and they all work-just not how I want them to). Every step works, and I can save the output to either SharePoint or my OneDrive, but the output isn't exactly how I envisioned it unless I manually specify each list item's ID in the Filter Query of the initial Get Items step, which clearly defeats the purpose of making a flow, right? No matter how many different ways I've set up the loop, I just can't wrap my head around how to fix the logic.

I thought that by comparing the list item ID with an integer variable that gets incremented after each loop run, that would force the flow to move down to the next list item and ignore the previous item(s), but that just isn't happening. The first time it runs, everything works exactly right and I end up with a PDF of the first list item. And every time the loop runs, I get a new PDF with the next list item... but it also includes the data from the previous list item(s), as well. Just to be totally clear about the output results, the first time the flow runs, I get a PDF with Item 1. Then when it loops, I get another PDF that includes Item 1 followed by Item 2. The next time, the PDF includes Item 1, then Item 2, then Item 3; and so on. 

Unfortunately, the "Convert file to PDF" step doesn't support CSS page breaks in the HTML, so I can't just run the flow once to get all of the list items in a single file and then separate them into individual PDFs in Acrobat. If each list item started on a new page in the PDF, I'd be done with this project and you wouldn't be reading this message right now.

So my question is: What am I missing? Does anyone have any suggestions as to how I set up the loop and/or increment a variable to force the flow to ignore the data from the previous list item(s) and produce a PDF that only includes the data from a single list item each time it cycles through?

Following are some screenshots of the flow as it currently stands:

It all works!Drilling down into 5th step in flow...Each list item ID gets compared to a variable...

and if list item ID = variable, then the PDF is created.

Thanks in advance for your time and any ideas you care to share.

[lbendlin]

Not sure why you need that loop in a loop.  But - have you thought about changing your "Get items from SP list" query to exclude items that already have a PDF?  That way you could brute force the conversion for your orphaned items. worst case by running the flow 200 times.

[chadangrisani · Accepted solution]

Hi lbendlin, thank you for the comment.  You were right about the loop-within-a-loop being superfluous, but that didn't have anything to do with the issue I was experiencing.  After banging my head against the wall for a couple of days, I finally figured out what was causing it.  If you look at the last screenshot I posted, the first item in the "If Yes" condition is "Append to string variable..."  That's where I went wrong - I should have been using "Set Variable" instead of "Append to Variable."  The append step was keeping the data from each previous loop run within the variable and then adding each subsequent list item's data to it, instead of setting a new string variable for each individual list item.  Such a silly mistake!