JoelBlogs - Joel Jeffery's Microsoft 365 Blog

Microsoft 365, SharePoint, Teams and Office 365 Architecture, Development, Administration and Training

  • Home
    • Sitemap
  • Articles
    • #SPThingADay
    • SharePoint Online
      • SharePoint Online – Drag and Drop and Large File Uploads
    • SharePoint 2016
    • SharePoint 2013
      • Content Database Changes to the AllUserData Table
    • SharePoint 2010
      • Administration
        • Disable CRL Checking
        • Excel 2010 & PowerPivot
        • Limits & Thresholds
        • PeoplePicker AD Errors
        • Recycle Bin Behaviour
        • Renaming a Server
        • Service Pack 1
        • Unattended Installs
        • Uninstall All SharePoint 2010 Solutions via PowerShell
        • User Alert Management
        • Virtualised SharePoint
        • Visio Stencils for Administrators
      • Development
        • Audience Membership Workflow Activity
        • Base Types, Lists & Content Types
        • BCS & Offline Sync
        • Debugger Skipping Lines
        • Development Laptop Spec
        • Enabling JavaScript IntelliSense
        • Event Receivers & Deployment Jobs
        • FavIcons & SPUrl
        • Google Maps Sandbox Web Part
        • Group By Content Type for List Views
        • Locale Stapler / Master or Default Locale
        • Removing Default Editor Parts
        • Sandbox Embedding Resources
        • Solution Sandbox Introduction
        • SPPersistedObject
        • Restoring Deleted SPSites in SP1
        • SPWebConfigModification 1
        • SPWebConfigModification 2
        • STSADM copyappbincontent vs. Install-SPApplicationContent
        • Workflows for Beginners
        • Workflow InitiationData Seralizer
    • SharePoint 2007
      • Alternate Access Mappings
      • Excel Services
      • Excel Services UDFs & Excel Client 2007
      • Experiences from the Field
      • InfoPath & Forms Server
      • Kerberos & SSRS
      • Records Management
      • Web Application Service
      • WSS vs MOSS
  • Training
    • SharePoint Admin Links
  • Downloads
    • Summary Slides for PowerPoint
    • CodePlex Projects
      • Audience Membership Workflow Activity
      • Google Maps Sandbox Web Part
      • Group By Content Type in List Views
      • Locale Stapler / Master or Default Locale
      • SharePoint Outlook Connector
  • Hire Me!
    • MCP Transcript
    • Résumé/CV

Microsoft Flow Tip #1 – Word Templates and Hiding Empty Repeating Sections

June 28, 2021 by Joel Jeffery

TL;DR: Put the Header Row in a Repeating Section of it’s own. To hide an entire table, pass empty arrays to both Repeating Sections (i.e. a pair square brackets) “[]”.

Microsoft Word and Microsoft Flow make a great combo for producing PDFs from data. You can insert several different field types and even include repeating sections, which can be formatted tables. If you haven’t tried it yet, start by looking through my LinkedIn article on creating PDF’s with Flow.

One question that keeps being asked is:

How can I hide a repeating section with a table if there is no data?

On the surface of it, if the forums are to be believed, there seems is no practical way to achieve this.

I’m going to show you how we do it.

Step 1 – Create a Word Document

First, let’s make our template Word document. Create a new document in Word, and put some header and footer text to show that content is definitely being output.

Microsoft Flow Tip #1 – Word Templates and Hiding Empty Repeating Sections

Step 2 – Insert a Table

Next, let’s insert a table – a 3×2 will do. 😊

Step 3 – Insert Placeholders

If you’ve already enabled the Developer tab in the ribbon (see this Microsoft article) you can insert some Plain Text placeholders.

Click in each cell and insert one. Your document should look something like this:

Next, for each placeholder in turn that you’ve just added, click on it, then hit the Properties button in the Developer ribbon, and give each one a Title and Tag. In my example I’ll use the same name for Title and Tag. I’ll call those on the first row H1, H2, H3 (for headings) and those on the second row C1, C2, C3 (for cells).

Top tip: make sure each placeholder has a *UNIQUE* Title and Tag so you can find them later in Flow.

Step 4 – Add the Repeating Sections

Select each of the rows in turn, and insert a Repeating Section Content Control.

After creating each one, hit Properties and give them a suitable Title/Tag combo. I’m going with Header and Data respectively.

If you miss that step, it can be a bit fiddly to select the Repeating Section and hit Properties.

Step 5 – Save your Template

Next, save this Word file somewhere Flow can get to it – e.g. OneDrive or SharePoint. I’m going to use OneDrive this time.

Step 6 – Create a Flow

Create a Flow, and for test purposes and to make it easy, I’m going to trigger my with a Manual push button:

Next, add a “Populate a Word Template” step:

When you select your template file, it should show the available fields for you to test:

In for my first run, I’m going to add two data rows and one header:

You can populate this with whatever data you need to.

This technique also works as with block of JSON for the whole payload if you have advanced scenarios.

Next, add a step to save the populated file somewhere. I’m going to save it in the same folder in my OneDrive:

I’ve added a timestamp to my filename, just for kicks and grins.

Step 7 – Test with Data

The first test run is going to show that we can populate the table in our template with the data we entered above.

Save and Test your Flow.

Next, head over to the the library where you saved the output:

Now let’s open up that word doc:

Adequate. Data. Yay.

Step 8 – Test with Empty Arrays

Go back to the Flow, and let’s swap out the data for empty arrays.

We do this by hitting the [T] icon in the top right of the Populate a Word Template step:

That toggles the data entry to a block of JSON:

So, let’s replace that with the JSON for an empty array:

Then repeat for the other Repeating Section:

Step 9 – Save and Test Again!

Hopefully, after launching the Flow again, you’ll have this document in your library:

Clicking through to Word should show you the Repeating Section is now empty:

Step 10 – Optional Bonus Step! – Populate from JSON

For bonus points, you might want to chuck a whole bunch of JSON at the “Populate a Word Template” step in one go, instead of doing it field by field. This is useful when you’re programmatically constructing the data in bulk to build your document content.

View your last successful Flow history where you passed all the data to the “Populate a Word Template” step.

For me, it’s the second one in that list. Click through to view the details, and pop open the “Populate a Word Template” step:

Click through to “Show the raw inputs”.

We care about the sections called “dynamicFileSchema/*”.

Internally, the Word placeholders have numerical random numbers as their identifiers. We need to pass those through for all repeating sections instead of the Title/Tag name.

Grab that JSON into the clipboard, and paste it into your favourite text editor. Notepad will suffice. 😀

We’re going to turn it into this:


{
	"792800456": [
            {
                "C1": "R1C1",
                "C2": "R1C2",
                "C3": "R1C3"
            },
            {
                "C1": "R2C1",
                "C2": "R2C2",
                "C3": "R2C3"
            }
        ],
     "-1088994840": [
            {
                "H1": "H1",
                "H2": "H2",
                "H3": "H3"
            }
        ]
}    

Next, use a Compose or Variable step to hold the path to your Word Document, and pass that to the “Populate a Word Template” shape, instead of using the file picker:

This replaces the text fields with a single field: dynamicFieldSchema.

Paste your block of JSON into the empty box:

Testing this should result in a fully populated Word document.

To hid the repeating sections, just swap the data for empty arrays:

Step 11 – Extra Bonus Step! – PowerShell Script to Show All Placeholder ID Numbers

If you do a lot of this, you will probably get bored of opening each Word template in a dummy “Populate a Word Template” step, just to have it report the field numbers to you.

Take the code below, save it as “Get-WordPlaceholders.ps1” and use it like this:

.\Get-WordPlaceholders.ps1 -Path "C:\Path\To\My\File\TemplateTest.docx"

Here’s the script!

param(
	[System.IO.FileInfo]$Path
)

function ConvertTo-Signed {
	param(
		$uint
	)
	$num = [int64]::Parse($uint);
	if($num -le [int32]::MaxValue) {
		return [int32]$num;
	} else {
		return [int32]::MinValue - ([int32]::MaxValue - $num) - 1l
	}
}

function Get-WordPlaceholders {
	param(
        [ValidateScript({
            if(-Not ($_ | Test-Path) ){
                throw "File or folder does not exist"
            }
            if(-Not ($_ | Test-Path -PathType Leaf) ){
                throw "The Path argument must be a file. Folder paths are not allowed."
            }
            if($_ -notmatch "(\.docx|\.doc|\.dotx|\.dot)"){
                throw "The file specified in the path argument must be a Word document"
            }
            return $true 
        })]
        [System.IO.FileInfo]$Path
    )
    $filepath = Resolve-Path $Path;
	$word = New-Object -ComObject Word.Application;
	$word.Visible = $false; 
	$doc = $word.documents.open($filepath.ToString(), $false, $true);           
	$doc.ContentControls | 
			select @{"Name"="Id"; "Expression"={(ConvertTo-Signed -uint $_.Id)}}, Tag, @{"Name"="Repeating";"Expression"={$null -ne $_.RepeatingSectionItems};}, @{"Name"="Parent";"Expression"={(ConvertTo-Signed -uint $_.ParentContentControl.ID)};};
	$doc.Close($false);
	$word.Quit();
}

if($null -ne $Path) {
	Get-WordPlaceholders -Path $Path;
}

Step 12 – Bonus Tip #3! – Use a Single Array

As an alternative for the Lazy Developer, you can build your table this way:

  1. Create your table with a header row and a data row, and apply formatting.
  2. Delete your data row.
  3. Add Plain Text Placeholders to the header cells.
  4. Select this row, and wrap it with a Repeating Section.
  5. In your Flow, pass the Repeating Section a single array where the first row contains the name of your header cells, and subsequent rows contain the data cells.
  6. To skip or hide the table, pass in an empty array.

Conclusion

Hopefully this well help someone trying to make a Word document in Flow able to hide tables that would otherwise be empty.

Enjoy! 😊

Filed Under: Development, Microsoft Flow, SharePoint, SharePoint Online Tagged With: Development, microsoftflow, SharePoint Development, SharePoint Online, sharepointonline

SharePoint PowerShell Tip #1 – Select-Object and FieldValues

June 27, 2021 by Joel Jeffery

Welp, this is embarrassing. ¯\_(ツ)_/¯

I’ve been writing PowerShell scripts for SharePoint for a very long time now.

And several times in every project, I find myself wanting to select just a specific subset of columns for use or display… or something.

And every time, like a doofus, I Get-PnPListItem, then iterate around each Item, picking out individual fields from FieldValues, and adding to a PSCustomObject.

Like this:

Get-PnPListItem -List Lists/MyList | % { [PSCustomObject]@{"Column1"=$_.FieldValues.Column1; "Column2"=$_.FieldValues.Column2; } }

And so on.

For years.

Like I say, like a doofus.

Anyway. Last week, I was wondering if I could improve on that. I mean, at least I’m not one of those guys who has to invoke Add-Member for each field for each item. You know who you are.

Anyway.

The completely-obvious-in-hindsight approach I can now share with you is this: You can cast FieldValues as a HashTable.

SharePoint PowerShell Tip #1
Get-PnPListItem -List Lists/MyList | % { [HashTable] $_.FieldValues } | Select Column1, Column2;

If you ever need to pull out a people column, MMS or lookup, you’ll need to use an expression. Something like this should do:

Get-PnPListItem -List Lists/MyList | % { [HashTable] $_.FieldValues } | Select Column1, Column2, @{"Name"="Person1";"Expression"={$_.Person1.LookupValue}};

I can’t believe I’ve been playing this game on idiot mode for so long.

Here’s a slightly optimised version that obviates the Where-Object (%):

[HashTable[]] (Get-PnPListItem -List Lists/MyList).FieldValues | Select Column1, Column2, @{"Name"="Person1";"Expression"={$_.Person1.LookupValue}};

SMH.

Filed Under: Code Samples and Downloads, Development, SharePoint, SharePoint Online Tagged With: PnP, PowerShell, SharePoint, SharePoint Online, Tips

Next Page »

Joel is a full-stack cloud architect who codes. He is a Microsoft Certified SharePoint Online, SharePoint Server and Azure specialist and Microsoft Certified Trainer.
He has over 20 years' experience with SharePoint and the Microsoft .NET Framework.
He's also co-founder of Microsoft Gold Partner JFDI Consulting Ltd. Read More…

Recent Posts

  • Microsoft Flow Tip #1 – Word Templates and Hiding Empty Repeating Sections
  • SharePoint PowerShell Tip #1 – Select-Object and FieldValues
  • Popular Misconceptions – Microsoft Teams relationship with SharePoint
  • Course: Microsoft 365 Certified Teamwork Administrator
  • Audience Targeted Searches in Modern SharePoint Online
MCT 2020-2021
Microsoft Teamwork Administrator Associate
Joel's Acclaim Profile
Joel's Microsoft Profile

Tags

Administration Architecture Certification Cloud Development Information Architecture intranets MCP Microsoft Microsoft Architecture Microsoft Azure migration Mobile Development MOSS MOSS 2007 office365 Office 365 Office 365 PowerShell SaaS SharePoint SharePoint 2010 SharePoint 2010 Training SharePoint 2013 SharePoint Administration SharePoint Administrator SharePoint Architecture SharePoint Designer 2010 SharePoint Developer SharePoint Development SharePoint Online sharepointonline SharePoint Search SharePoint Training SharePoint Videos Silverlight SOA Solution Sandbox SPThingADay TechEd 2007 Training Videos Visual Studio 2010 Windows Phone 7 WSS

Copyright © 2022 Joel Jeffery, SharePoint Architect