{"id":22,"date":"2023-09-04T14:16:40","date_gmt":"2023-09-04T14:16:40","guid":{"rendered":"https:\/\/kerner.digital\/?p=22"},"modified":"2024-03-21T15:22:26","modified_gmt":"2024-03-21T15:22:26","slug":"populate-word-template-from-excel-table-in-power-automate","status":"publish","type":"post","link":"https:\/\/kerner.digital\/?p=22","title":{"rendered":"Populate word template from Excel table in Power\u00a0Automate"},"content":{"rendered":"\n

Here\u2019s some automation that can easily be done using no-code approach. Last week I got home late and saw my wife still working on some stuff. It turned out that she needed to make some description documents for around thousand invoices for one of her customers. She told me she planned to do that over the weekend. I looked at the data and immediately felt that this looks like a job for\u2026 power automate.<\/p>\n\n\n\n

It took me around 20 minutes to build the flow, half an hour researching which connector to use \ud83d\ude42 and almost one hour fighting with MS Excel to get the dates populate properly. I swear, world would be a happier place if Microsoft fixed the dates in Excel. Anyway, back to the guts of this post, here\u2019s what I did and what you might do as well.<\/p>\n\n\n\n

Format Excel data to be in a table <\/h3>\n\n\n\n

First of all, to make your life easier just format the data into a table. This allows Power Automate connector to iterate through rows and get column names correctly. To do that, select whatever data you have and click on Format as Table<\/em>on the Table Design<\/em> tab. Give it a name, we will need it later:<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Prepare Word template<\/h3>\n\n\n\n

We need to set up Word template so that our flow knows where to put the data it got from the Excel. That\u2019s where Content Controls come in handy. First, enable Developer tab from the settings (File -> Options -> Customize Ribbon:<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Second, select a place in the document you want to populate with the data from Excel and add Plain text content control. <\/em>It is important to select plain text, as for some reason rich text control is not visible in Power automate:<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Next, hit properties and set a title.<\/p>\n\n\n\n

Upload both files to SharePoint<\/h3>\n\n\n\n

I\u2019ve created two folders input for the Excel and Word template files and Output for generated documents. Feel free to choose otherwise\u00a0\ud83d\ude09<\/p>\n\n\n\n

Create Power Automate instant flow<\/h3>\n\n\n\n

Now, the bread and butter\u200a\u2014\u200anavigate to https:\/\/make.powerapps.com<\/a> and create an instant flow. I chose to start from a template, but it doesn\u2019t really matter. The trigger might be adding a document to SharePoint, clicking in Canvas App or anything else. For me it was a manual run.<\/p>\n\n\n\n

Next if you\u2019re like me, you\u2019d think we should first connect to SharePoint, open Excel file and fetch the rows. That\u2019s how most programmers would approach this and this the place where I lost my time looking for the correct connectors. It turned out to be a very simple and intuitive (for non-programmers) option: List rows present in a table<\/strong>. Yes, it\u2019s that simple. You just select that, put the location of your file and the name of your table:<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Now that we have that we simply select Populate a Microsoft Word template<\/strong>, select the template from SharePoint and you will see all your content components you\u2019ve created (no rich content component!) on your left, and you can select values from your table using column names.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Once you start filling the values from columns Power Automate will automatically add the for each component. <\/p>\n\n\n\n

Save document into new <\/h3>\n\n\n\n

This one is easy. Just hit that Create file from SharePoint connector and fill the data. In my case I just created them in the output folder and gave it a name from one of the columns. This way I can easily reference the file to the row in the Excel:<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

The whole flow should look more or less like this:<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Now run some tests and let\u2019s see what happens. It all looks promising, but upon further investigation of result files it turned out that some the data did not fill correctly. <\/p>\n\n\n\n

Fix Excel formatting <\/h3>\n\n\n\n

Of course. The date is not correct. Who would have thought, right? We need to fix this unless you\u2019re happy with having 45054.00 instead of 08\u201305\u20132023 (or whatever other format you use). I found it that the simpliest way is to and apostrophe symbol before the date changing it to text and removing all that unnecessary operations Excel tries to do. After that it should be OK. Be sure to copy the values instead of formulas. It might also save you some time figuring out what is wrong.<\/p>\n\n\n\n

Test again<\/h3>\n\n\n\n

Now it should work fine and files should appear in the folder:<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

One thing though, the first time I run this it only created 250 files. I needed to go to the Excel component in the flow and update the number of rows fetched.<\/p>\n\n\n\n

We’ve saved ourselves a weekend \ud83d\ude42<\/p>\n\n\n\n

Stay coding! <\/p>\n","protected":false},"excerpt":{"rendered":"

Here\u2019s some automation that can easily be done using no-code approach. Last week I got home late and saw my wife still working on some stuff. It turned out that she needed to make some description documents for around thousand invoices for one of her customers. She told me she planned to do that over […]<\/p>\n","protected":false},"author":1,"featured_media":33,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5,6],"tags":[],"class_list":{"0":"post-22","1":"post","2":"type-post","3":"status-publish","4":"format-standard","5":"has-post-thumbnail","7":"category-low-code","8":"category-power-platform","9":"czr-hentry"},"_links":{"self":[{"href":"https:\/\/kerner.digital\/index.php?rest_route=\/wp\/v2\/posts\/22","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/kerner.digital\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/kerner.digital\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/kerner.digital\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/kerner.digital\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=22"}],"version-history":[{"count":2,"href":"https:\/\/kerner.digital\/index.php?rest_route=\/wp\/v2\/posts\/22\/revisions"}],"predecessor-version":[{"id":55,"href":"https:\/\/kerner.digital\/index.php?rest_route=\/wp\/v2\/posts\/22\/revisions\/55"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/kerner.digital\/index.php?rest_route=\/wp\/v2\/media\/33"}],"wp:attachment":[{"href":"https:\/\/kerner.digital\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=22"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/kerner.digital\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=22"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/kerner.digital\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=22"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}