SharePoint: People Picker To Text Column Magic!

by Andrew McMorgan 48 views

Hey Plastik Magazine readers! Ever found yourself wrestling with SharePoint's People Picker column, wishing you could just get the user's name (or email, or whatever) in a simple text format? Yeah, we've all been there! Well, guess what, guys? There's a way to pull off this SharePoint magic! Today, we're diving deep into the mystical world of SharePoint Online, specifically focusing on how to convert those pesky People Picker fields into good ol' text columns. This is super useful for all sorts of reasons, like making it easier to filter data, use it in Power Automate flows, or just get a cleaner view of your info. We'll be using some clever JSON formatting and a bit of column customization to achieve this. Trust me, it's easier than it sounds, and the results are totally worth it! Get ready to level up your SharePoint game and make your lists way more user-friendly. Let's get started, shall we?

The Problem: People Picker Blues

So, what's the deal with the People Picker column, and why do we even want to convert it? Well, the People Picker is fantastic for connecting to your organization's user directory (Azure Active Directory, if you're using SharePoint Online). It lets you select users, and it stores a bunch of information about them: their name, email, job title, profile picture, and more. But sometimes, you don't need all that info. Maybe you just want the user's display name or email address in a simple text format for use in other columns, workflows, or integrations. The People Picker column, in its default state, doesn't easily give you that. It stores the user's information as a complex object, making it harder to extract the specific data you need. Filtering and sorting can also be a pain because you're dealing with those complex objects. That's where our conversion comes in handy! We're creating a new text column that automatically grabs the data you want from the People Picker column and puts it right there, ready to use. This makes your lists cleaner, more manageable, and much more versatile. So, say goodbye to the People Picker blues and hello to a streamlined, user-friendly experience! Get ready to transform those complex People Picker fields into something that's simple, clear, and perfectly tailored to your needs. This is all about making your life (and your colleagues' lives) easier while working in SharePoint. Let's keep the good times rolling!

Why Convert?

Why bother converting, you ask? Well, there are several compelling reasons:

  • Enhanced Readability: Text columns are incredibly easy to read at a glance. They make your data cleaner and more straightforward. Imagine quickly scanning a list and seeing the user's name directly instead of having to click into the People Picker field. That's the power of text format.
  • Improved Filtering and Sorting: Filtering and sorting become much more intuitive with text columns. You can easily filter by the user's name, email, or any other text-based information you choose to display. No more struggling with the complex object structure of the People Picker.
  • Seamless Integrations: Text columns are super compatible with other SharePoint features and third-party tools. You can effortlessly integrate them into Power Automate flows, Power Apps, and other applications, opening up a world of automation and customization possibilities.
  • Simplified Calculations: If you need to perform calculations or use the user's information in formulas, text columns make the process much smoother. You can easily reference the text column in your formulas and calculations.
  • Better Data Export: When exporting your data to Excel or other formats, text columns are often much cleaner and easier to work with than the complex data stored in People Picker fields.

Basically, converting to a text column gives you more control, flexibility, and a much better user experience. It's all about making your SharePoint lists work for you, not against you!

The Solution: JSON Formatting to the Rescue

Okay, here's where the magic happens! We're going to use JSON formatting to transform the People Picker column into a text column. Don't worry, it's not as scary as it sounds. JSON (JavaScript Object Notation) is essentially a way of describing data in a structured format. We'll be using it to tell SharePoint how to display the data from the People Picker column in our new text column. Here's the general idea:

  1. Create a new text column in your SharePoint list. Let's call it "PeopleTextColumn".
  2. Go to the column settings for "PeopleTextColumn" and select "Column formatting".
  3. In the "Column formatting" panel, choose "Advanced mode".
  4. Paste in the JSON code that tells SharePoint how to display the data from the People Picker column. This is where we extract the information we want (e.g., the user's display name or email) and tell SharePoint to show it in the text column.
  5. Save the changes, and boom! Your text column should now display the desired information from the People Picker column.

This method is super flexible. You can customize the JSON code to display any information you want from the People Picker column. Want the user's job title or their department? No problem! Just adjust the JSON code to extract that information. Let's get down to the nuts and bolts of the JSON code itself. It will look like this, and we'll break it down.

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "@currentField.email"
}

Understanding the JSON Code

Let's break down this JSON code, piece by piece, so you understand what's going on:

  • "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json": This line tells SharePoint that we're using the column formatting schema. It's basically a housekeeping line that tells SharePoint the structure of the JSON we're using.
  • "elmType": "div": This specifies that we want to create a <div> element to display the content. A <div> is a basic HTML element that acts as a container.
  • "txtContent": "@currentField.email": This is the crucial part! "txtContent" tells SharePoint what to display inside the <div>. @currentField refers to the current field (in our case, the People Picker column), and .email specifies that we want to display the user's email address. You could change .email to .title to display the display name, or use other properties depending on what info you want. For example, if you wanted the user's display name, you could use @currentField.title.

Customization Options

This is where it gets really fun! You can customize this JSON code to display all sorts of information from the People Picker column. Here are some common options:

  • Display Name: @currentField.title (This is usually the user's display name)
  • Email Address: @currentField.email
  • Department: @currentField.department
  • Job Title: @currentField.jobTitle
  • Picture: @currentField.picture (This will display the user's profile picture. However, you might need to adjust the formatting to display it correctly, such as using an <img src="@currentField.picture"> tag within the JSON).

Advanced Formatting

Need even more control? You can also use conditional formatting to change the appearance of the text based on certain criteria. For example, you could highlight a user's name in red if they have a specific role or tag a certain person. You could also include multiple fields within the txtContent to show the name and email, for instance.

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "=concat(@currentField.title, ' - ', @currentField.email)"
}

In this example, we're using the concat() function to combine the user's display name and email address, separated by a dash and a space. This shows a very nice display of the name and email together! You can experiment with different HTML elements, formatting options, and conditional logic to create truly custom displays that meet your specific needs. The possibilities are really only limited by your imagination and your JSON skills! (Don't worry, there are tons of online resources to help you with the JSON part!)

Step-by-Step Guide: Making it Happen

Okay, guys, time to roll up our sleeves and get our hands dirty (virtually, of course!). Here's a step-by-step guide to convert your People Picker column to a text column using JSON formatting:

  1. Create a New Text Column: In your SharePoint list, create a new column of type "Single line of text". Give it a descriptive name like "PeopleTextColumn" or whatever makes sense for your list.
  2. Access Column Formatting: Go to the list view, click on the column header of the new text column you created. Then, select "Column settings", and then "Format this column".
  3. Advanced Mode: In the "Format column" panel that appears, click on "Advanced mode". This will open a text box where you can paste your JSON code.
  4. Paste the JSON: Copy the JSON code provided above (the simple email example or the more advanced example, depending on your needs). Paste the code into the text box.
  5. Modify the JSON (If Needed): If you want to display a different property (like the display name or job title), modify the @currentField. part of the code accordingly. Refer to the customization options discussed above.
  6. Preview and Save: You should see a preview of how the column will look. If you're happy with the result, click "Save".
  7. Test and Refine: Check your list view to make sure the text column is displaying the data correctly. If not, go back to the column formatting and adjust the JSON code until you get the desired result.

And that's it! You've successfully converted your People Picker column into a text column! Now you can enjoy the benefits of cleaner data, easier filtering, and seamless integrations. Way to go!

Common Issues and Troubleshooting

Sometimes, things don't go perfectly the first time. Here are some common issues you might encounter and how to fix them:

  • Nothing is Displaying: Double-check that you've correctly entered the JSON code and that there are no typos. Also, make sure the column you're referencing in the @currentField. part of the code actually exists in the People Picker column. Also, ensure the SharePoint column is correctly identified.
  • Incorrect Data is Displaying: Verify that you're referencing the correct property in the JSON code. For example, make sure you're using .title for the display name and .email for the email address. Ensure that the column names are consistent throughout.
  • JSON Errors: If you're getting an error message, it usually means there's a problem with your JSON code. Use an online JSON validator to check for syntax errors. These validators will quickly help you identify missing commas, extra brackets, or other common issues. You can find free JSON validators online with a simple search. Also, it helps to start simple and build up your JSON formatting step by step.
  • Picture Not Showing Up Correctly: If you're trying to display a profile picture, you might need to use HTML tags within the JSON code to render the image properly. Look up the proper way to use the <img> tag and how to reference the image source using the @currentField.picture property. The image size might also be a factor.
  • Data Not Updating: If the text column doesn't update immediately after you've made changes to the People Picker column, it may take a little while for SharePoint to process the changes. Also, ensure that the JSON formatting is correctly applied to the column. If the problem persists, try refreshing the page or clearing your browser cache. Consider checking the column settings to confirm that the changes were saved correctly.

Don't be afraid to experiment, and don't get discouraged if things don't work perfectly the first time. The beauty of SharePoint is that you can always go back and adjust your column formatting until you get it just right!

Conclusion: Your SharePoint Lists, Transformed!

Alright, folks, you've now got the knowledge to transform your SharePoint lists, making them more user-friendly and efficient! We've covered the why, the how, and even how to troubleshoot any issues you might encounter. By converting your People Picker columns to text columns using JSON formatting, you're unlocking a whole new level of flexibility and customization. Remember, the key is to experiment, have fun, and make your SharePoint environment work for you. Use the techniques you've learned to build lists that are perfectly tailored to your needs. This is just one of many ways to supercharge your SharePoint experience. Keep exploring, keep learning, and keep making those lists awesome!

So, go forth and conquer those People Picker columns! And as always, happy SharePoint-ing! If you have any questions, hit us up in the comments below. We're always here to help you navigate the wonderful world of SharePoint.