Тёмный

Populate a Word Template table Dynamically with Power Automate 

Andrew Hess
Подписаться 9 тыс.
Просмотров 15 тыс.
50% 1

In this video we populate a word template dynamically based on a list in SharePoint. We use the repeating content control in MS Word, SharePoint and Power Automate to do that.
Come check it out we are using the new modern look & feel of power automate too!
Populating word repeating sections dynamically with Power Automate involves using the application's ability to automate tasks and process data within Microsoft Word documents. Specifically, you can create a flow that takes input data, such as text or values from forms, databases, or other sources, and inserts this data into predefined sections of a Word template. This process typically uses Word templates with content controls or placeholders that are identified and filled out by the flow based on the input. For example, you might have a Word document with repeating sections for items in an invoice, and Power Automate can dynamically fill these sections based on a list of items from an order processing system. This approach streamlines document creation, ensures consistency, and reduces manual errors by automating repetitive tasks and data entry.
My name is Andrew Hess please like and subscribe if this content is helpful!

Опубликовано:

 

14 окт 2024

Поделиться:

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист
Посмотреть позже
Комментарии : 73   
@Chaddcl0ps813
@Chaddcl0ps813 8 месяцев назад
Great stuff as always! Thanks Andrew!
@andrewhess123
@andrewhess123 8 месяцев назад
Thanks Chadd for the continuous support!
@JPPVCC
@JPPVCC 15 дней назад
It's like we work on the same stuff. I want to use the collection i create from colXXXX.attachments as the repeating section source? Havent used automate much...yet.... Many thanks for all this effort.
@andrewhess123
@andrewhess123 15 дней назад
Noice! 🤩 That's why I love sharing, learn so much from the community and these are my own notes too
@dxbranag163
@dxbranag163 24 дня назад
Amazing work ❤ very useful next time if possible for you in your upcoming video consider the use case of adobe sign after generate this kind of word invoice the convert in pdf + get signature even use of premium connector of adobe esign. 😊
@andrewhess123
@andrewhess123 18 дней назад
Hey! Yeah I can write this one down normally I don't use adobe sign but I do have a license to check this out. Thanks for the question!
@MsRodrovisk
@MsRodrovisk 8 месяцев назад
Good work and very useful.
@andrewhess123
@andrewhess123 8 месяцев назад
@MsRodrovisk Thanks so much! Glad it's useful!
@TheBigBosske
@TheBigBosske 8 месяцев назад
Thanks for that. Well, the premium Word connector works well, I managed to do populate templates... however, it is very limited, I wish there was a way to run a VB script to deal with specificities, etc. We run templates at my office but in several languages, so we have macro that deals with styles to use, table/column size specificities, etc. I haven't tested this list/Word connector in other languages such as Arabic, Chinese. So at the end, considering the limitation above, I have created a Power App that get the data from the list and allow client to enter further data using a form.... then PowerApp with PowerAutomate will create a data file (TXT) that is sent by email.... then I have an Outlook macro that get the TXT file, parse it (read properties, read data for bookmarks, etc.), launch MS Word, fill the bookmarks and do all the specificities and upload output file in Sharepoint and send the template to client by email. This is a workaround until VB script can be run on the cloud. It has downsides but it works great, we have more than 100 templates/categories in six language.
@andrewhess123
@andrewhess123 8 месяцев назад
That's really cool actually. I wonder if Copilot Studio could help you create that also. Or even ChatGPT with a custom LLM. I know Microsoft has some Azure components in cognitive services also for translating in multiple languages too. That could be hooked to a Power Automate or a Logic App. But of course I think adding a Power App front end is golden! Thanks for sharing BigBoskee!
@TomGeorge-gl7dd
@TomGeorge-gl7dd 5 месяцев назад
Great video, Im having trouble where my select is going inside an apply to each, i dont understand how the select in your video does not do the same? I'm unable to use the output in the word document because of this as well. Thanks
@ashkaanmoinzadeh4618
@ashkaanmoinzadeh4618 3 месяца назад
Andrew, thank you so much for your content! You're seriously a life saver. I'm running into an additional issue on this: I'm able to populate one "repeating section content control" using the Populate a Microsoft Word Template action. However, I have two tables that I'm trying to populate using two separate "repeating section content control" actions, but the Microsoft Word Template action is not detecting the second repeating section. Have you been able to populate multiple repeating section content controls using power automate? If you could provide some guidance, it would allow for some very comprehensive word reports to be generated. Thank you.
@andrewhess123
@andrewhess123 3 месяца назад
Hi Ashkaan, that's a good question, I have actually not tried the double repeating sections but I feel like it should work. This is something I would need to test out. In the documentation here it does say you can "Nest" repeating content controls so I do not see any limitations as of yet. learn.microsoft.com/en-us/office/client-developer/word/content-controls-in-word My suggestion is to make sure you are using the Start/End tags when you do the repeating content control. Try to isolate the issue. Try putting the 2nd one first and switch the order, does the 2nd one then show up in Power Automate?
@ashkaanmoinzadeh4618
@ashkaanmoinzadeh4618 3 месяца назад
@@andrewhess123 Wow Andrew, such a quick turnaround! 😁 I have swapped the orders of the repeating sections and have gotten PowerAutomate to recognize the previously-unrecognizable section. I likewise used start/end tag notation for the repeating sections, but again, only one section gets detected. I’ve bypassed this for now by using the Encodian connector, which has simple syntax for adding fields (including arrays). Their documentation is also straight forward. I managed to populate multiple arrays this way, but they have a pretty hefty price tag if you want to go past their limited free tier services ($600 for 1 year up front). I’ll keep tinkering around with this. Hopefully someone out there has made it work out! I haven’t found anything in the stack exchange either. Regardless, thanks for all the help! Wouldn’t have gotten here without ya :)
@IAmaProgrammers
@IAmaProgrammers 28 дней назад
Hi @Andrew Hess , Thank you for your suggestion. I’ve identified a solution. However, I have a question: Is it possible to dynamically select a specific template at runtime based on input values? For instance, if we have more than 40 templates (e.g., Temp1, Temp2, Temp3, etc.), can the system choose the correct template depending on the provided input? Looking forward to your guidance.
@andrewhess123
@andrewhess123 28 дней назад
I do think this could be possible. 100% possible with SharePoint premium that is like the easy button, with a cost. Otherwise if there's a way to identify the differences and have a power automate flow categorize it.
@andrewhess123
@andrewhess123 28 дней назад
SharePoint premium is way awesome, I know that premium word is a turn off though. But as a non Microsoft employee I'm loving it.
@mohamedawad1299
@mohamedawad1299 8 месяцев назад
goooooood A very distinctive explanation
@andrewhess123
@andrewhess123 8 месяцев назад
Thanks Mohamed!
@dynamics365series2
@dynamics365series2 26 дней назад
Do you have a video for calculating total as you have mentioned in this video?
@andrewhess123
@andrewhess123 26 дней назад
I do not have a video on that yet. Although I had 1 user share they did it in power automate.
@dynamics365series2
@dynamics365series2 24 дня назад
@@andrewhess123 Oh but i thought you mentioned that you got one video on it.
@GuilhermeDSa
@GuilhermeDSa 8 месяцев назад
Perfect Video, really, I tried to do this, and after some days, I did it, but, here I don´t have the premium license. However, I did using the html of the word, kkk, and it's working, but I had to remember a sort of thing oh html, but its possible, U just need save the doc in html, and open in the note reader, copy and initialize a variable in the flow, and another for the lines from sharepoint. And finally create the archive, but just in html, so U will have to open with Word, and after save the doc. Its a way to do it without premium. I hope its useful. Nice video, I love it.
@andrewhess123
@andrewhess123 8 месяцев назад
Good point Guilherme, thanks for sharing. That sounds like a good alternative doing it in html, then pushing it to the word file. Thanks for the input!!!
@victordauden9173
@victordauden9173 8 месяцев назад
Thx! Pls show us how it's done Andrew. @@andrewhess123
@ivanalita948
@ivanalita948 3 месяца назад
Can you also update the table in the Word document template dynamically too? May I know what connector do you use? Thank you
@ivanalita948
@ivanalita948 3 месяца назад
Can you do a tutorial on how to populate word documents with the table dynamically but without the premium connectors? Thank you 🙏
@Paul_Spence
@Paul_Spence 8 месяцев назад
Firstly great video and thanks for sharing. My returned Word .docx document content controls are not editable though, is there I way I can make them editable so that the end user can change some of the content or add to it? My end user would not have access to the Developer tab in Word.
@andrewhess123
@andrewhess123 8 месяцев назад
Hi Paul, this could be possible. One option especially if the flow is run on a schedule is to set it that column back to Not Complete and edit the SharePoint columns, either in a Power App or in the SP list themselves. It should then recreate the document under the exact same name and update the document on that schedule. There are other options too, but maybe that is one of the better options?
@Soulebi03
@Soulebi03 2 месяца назад
hello Andrew, if the input for the word document is from the sharepoint list, does the users has to have contribute access to the document template too?
@andrewhess123
@andrewhess123 2 месяца назад
So this depends on how the Power Automate is set up, depending on the trigger if manually ran (for selected item or list) yes, would need access. If, scheduled or automated only the account running the flow. I believe also the only user that needs access to the document template is the account that runs the action Populate the Document Template action in Power Automate. Which is a premium action. This is difficult question because the answer actually is: "It depends".
@thomasschnooboord9926
@thomasschnooboord9926 Месяц назад
Can the names be different in the word table and SharePoint? Does this create hickups in Automate?
@andrewhess123
@andrewhess123 Месяц назад
They can be different, it may just cause some confusion but you can pass the names to different names in Word from SharePoint.
@SushilPandey-dq7p
@SushilPandey-dq7p Месяц назад
Great video! How can I remove the tags that still appears in the destination file after being populated. I want to edit the content further and these tags and populated content seems hindering. Any suggestions? Thanks
@andrewhess123
@andrewhess123 Месяц назад
Hi Sushil, in the properties of the tag in MS word. You can hide the tags in the template.
@ThreeDigitIQ
@ThreeDigitIQ 18 дней назад
How do you populate repeating sections inside repeating sections (like an array inside and array) ? [ { "PortfolioName": "Portfolio A", "Projects": [ { "ProjectName": "Project 1" }, { "ProjectName": "Project 2" }, { "ProjectName": "Project 3" } ] }, { "PortfolioName": "Portfolio B", "Projects": [ { "ProjectName": "Project 4" }, { "ProjectName": "Project 5" } ] } ] Word seems to allow it, buy Power Automate doesn't recognize the embedded repeating section
@andrewhess123
@andrewhess123 18 дней назад
Hey Digit IQ, I wonder if you could create a html table inside one of the table cells? Not sure of this one I have never done an array inside an array with a Word Template. Normally because the loop statements get so long. Or what about converting your array into a comma delimited text?
@markusfranz3756
@markusfranz3756 Месяц назад
Hey Andrew I a not able to mark the whole table and the properties do not show up blank so i can not add the start and end tag. Plus the table does not show up in the populate word template action. i only have my tags in the action and can add the dyamic content to it. plus it is not possible to open the app because it thows errors saying: word found unreadable content. any ideas?
@andrewhess123
@andrewhess123 Месяц назад
Hey Markus, I think it would be important to be able to select the whole table. Have you tried with just simple single line text fields? It was a little difficult to select the whole table for me, there's just a certain way to click in there. The errors with unreadable content I believe is due to the content controls having an incompatible format.
@IAmaProgrammers
@IAmaProgrammers Месяц назад
Hi. After you created and populated .docx file can we remove placeholder from Docx file ?
@andrewhess123
@andrewhess123 Месяц назад
You can hide the content control properties in the template or afterwards in the word file. learn.microsoft.com/en-us/office/client-developer/word/content-controls-in-word#content-control-improvements-in-word-2013
@jessicachala
@jessicachala 4 месяца назад
Hello, very good video. But I had a question, is it possible to create a table that includes images in each row? For example, I have a table where column 1 is first name, column 2 is last name and column 3 is photo. All data is stored in a sharepoint. I use the steps to get item and get file content, and I store it in an array. In the word template I have the box with their respective controls and the row repeater "Content control for section repetition". But when you open the template in Power automate, this tells me the repeater with the controls of Name and surname but not the one of Photo. Then by putting the array where the information was stored, it creates the table in word with all the corresponding first and last names but not adding the photo. I want to know if you know if in this case only text is handled or it is not possible to make a table with images Thank you
@andrewhess123
@andrewhess123 4 месяца назад
If you want to do the repeating sections with images there are some third party tools that can do it like Plumsail. Or you can try to maneuver around using HTML and a PDF instead of using the Word Connector. I do believe that is a limitation of the Word Connector so no images that I'm aware of yet in repeating sections.
@premiumytb-bh4jd
@premiumytb-bh4jd 4 месяца назад
Tks, I could do it follow him, but, when I view docx file, I can hide the tag on table, "mytable" title and tag on my destination file. Could you help for hide it
@buildinglegacywithderricka6492
@buildinglegacywithderricka6492 3 месяца назад
Hi Andrew, How can I do this for just One Item. I have a table with 4 doctors, with 4 different addresses and phone numbers. I want to use a form to select the doctor and the Word template be updated automatically with the correct address and phone number in the template. How can I do this dynamically? I want to have the list or Excel file be the source of truth for the information, so when the templates are used, they pull the data from the list/Excel spreadsheet for the header as needed.
@andrewhess123
@andrewhess123 3 месяца назад
Hi Derrick, so instead of using the Get Items from SharePoint you would use the Get Items from Excel, to get items from excel the repeating section must be a table and then use the Select statement on Excel Get Items
@andrewhess123
@andrewhess123 3 месяца назад
If you want to have a form to select the doctors, most likely what you are trying to do I would create a Power App for the selection piece and to make it dynamic. It's not something easy to explain in a comment. Hope this helps some though!
@Henriquez3100
@Henriquez3100 3 месяца назад
hello! I don't need to copy or archive and edit the copy? Is this because in case some people execute the flow could cause a problem, or not?
@andrewhess123
@andrewhess123 3 месяца назад
Hi Henrique, whoever executes the flow they need to have a Premium Power Automate license, so that could cause problems.. but if you want to copy or archive and edit the copy that decision is up to you and your business process.
@ParascosmPoems
@ParascosmPoems 3 месяца назад
Hi! I appreciate your videos a lot. I was wondering if it is possible to achieve this without using a premium connector
@andrewhess123
@andrewhess123 3 месяца назад
The repeating rows without premium, most likely can only be done with a pdf and not a word document.
@ivanalita948
@ivanalita948 3 месяца назад
May I know how? ​@@andrewhess123
@andrewhess123
@andrewhess123 2 месяца назад
@@ivanalita948 With PDF you can write HTML and make a table, so you would take your repeating rows and create a table and then print to pdf. There are some limitations currently while the PDF feature is in preview, last time I checked its only 1 page per print to pdf.
@bluric0
@bluric0 3 месяца назад
Is it possible to transpose the data in Microsoft word. So that I have the Headers on the left and the values on the right? Is there a way to do this?
@andrewhess123
@andrewhess123 Месяц назад
Not sure about this one, I'm sure there is a way though especially on the Power Automate side.
@enakomiya6019
@enakomiya6019 7 месяцев назад
Is it possible to insert repeating images? I have images posted as a list attachment and convert those to base64.
@turox182
@turox182 8 месяцев назад
Can you do this with images?
@andrewhess123
@andrewhess123 8 месяцев назад
This method will not allow images in the repeating sections. I have done images just 1 image at a time. There are other html ways or 3rd party tools.
@JeevanKumar-ir3io
@JeevanKumar-ir3io 7 месяцев назад
Hi Andrew Great video and thanks for the detailed explanation. However, could you please explain how to convert populated word document in SharePoint to Pdf. I tried with "Convert Word Document to PDF" and it is not working. Kindly help.
@jeannequielavale-wycoco594
@jeannequielavale-wycoco594 5 месяцев назад
is it possible to remove blank rows?
@andrewhess123
@andrewhess123 5 месяцев назад
Hi Jeanne, Yes I would think so, you would need to build that into your Filter Query of your Get Items Action to not pull in Blank Rows
@JB-ch9ws
@JB-ch9ws 4 месяца назад
@@andrewhess123 how would you do that?
@andrewhess123
@andrewhess123 4 месяца назад
@@JB-ch9ws Depends on your flow and how it's configured, could be multiple ways, but here is an example, they use ne null: powerusers.microsoft.com/t5/Building-Flows/Write-Filter-Query-to-Eliminate-Blank-Rows/td-p/2206158
@ricardofreestyle2278
@ricardofreestyle2278 3 месяца назад
dynamic with images ?? its possible?
@andrewhess123
@andrewhess123 3 месяца назад
3rd party tool or html with pdfs, could be possible, last time I checked. Things change fast with Microsoft though 😀
@IAmaProgrammers
@IAmaProgrammers Месяц назад
The Plain text control not removed after template is populated and created .docx file
@andrewhess123
@andrewhess123 Месяц назад
In the template on the content control. Go to properties and turn off the bounding box.
@andrewhess123
@andrewhess123 Месяц назад
Set to None. learn.microsoft.com/en-us/office/client-developer/word/content-controls-in-word#improved-visualization
@IAmaProgrammers
@IAmaProgrammers Месяц назад
Thanks @AndreWhess , but after data is loaded in content control if we want to edit , it is not edited, instead it removes value along with content control.
@andrewhess123
@andrewhess123 Месяц назад
@@IAmaProgrammers Are you trying in browser or in client? Try actually opening Word in client? I am not seeing the same functionality where it does not allow you to edit and removes the value... If that keeps happening try on a very basic content control.. try to isolate the issue.
@IAmaProgrammers
@IAmaProgrammers Месяц назад
@@andrewhess123 Thanks for your time to explain to me , if you see the above video 17:33 the table is populated values and highlighted the text , can you try to edit the table cell values and see it is possioble to edit the values ?.
@elkinleguizamon779
@elkinleguizamon779 4 месяца назад
Pasar un ritchtext que tiene imagenes y texto a un word template is possible
@wilsonfarrell4774
@wilsonfarrell4774 7 месяцев назад
P r o m o S M 🤦
Далее
PUBG Mobile СТАЛ ПЛАТНЫМ! 😳
00:31
Просмотров 108 тыс.
Power Automate: How to populate a Word Template 📃
24:10