Become a SharePoint Online PowerShell Master

Ben Stegink

0 comments

Becoming a PowerShell master with SharePoint Online or just a PowerShell master, in general, takes time and a lot of effort.

This eBook documents the fundamental building blocks you will need to build your skills and develop. It won’t be an in-depth tutorial. Instead, it will guide you to the key areas you need to think about (whether you are experienced or totally new to PowerShell). It will give you examples to try out to help build your understanding and support your journey towards PowerShell mastery.

This eBook documents the building blocks you’ll need to become a master of PowerShell for SharePoint Online.

In this eBook, you will learn why PowerShell is such a useful and powerful tool when working with SharePoint Online or SharePoint on-premise. It will answer the question of why you might want to use PowerShell and tell you about the tools you’ll need to use when you begin your work building PowerShell.

It will also cover connecting to on-premise and SharePoint Online environments – each of which has different approaches and modules you may need to use. The book will finish off with some great use cases and examples to guide you in your journey towards Mastery.

Why Powershell?

To answer this first question, there are certain features in SharePoint that can be enabled and used ONLY by using PowerShell. They just don’t appear in the GUI (graphical user interface). This was the case for SharePoint on-premise and continues to be like this with SharePoint Online.

Some examples of these were hub sites when they were first released. If you wanted to try out hub sites or take advantage of them, the only way you could do that was with PowerShell. Eventually, hub sites did come to the UI (user interface) but not for some time.

Another one is enabling and disabling site scripts. Certain functionality works or doesn’t work in SharePoint based on whether site scripts are enabled. By default they are disabled. There is a global setting in the UI where you turn it on or off, but if you want to do this on a site by site basis, that’s in PowerShell only.

Another aspect is automating tasks. You can compare PowerShell to an assembly line in a manufacturing plant. There are a lot of correlations when it comes to managing SharePoint Online with PowerShell versus assembly lines. For example, in a manufacturing facility, they create assembly lines because they are striving for reliable repeatability and hence time savings. They lay each step out – one right after the other – and do it in a very repeatable and consistent manner.

The aim is to do the same thing to every engine that gets put together, every car that is built, every sneaker that is created. You do this by assembling machines in a set order, and those machines get put in place and tested.

It’s the same with PowerShell. By scripting something, the tasks that have to be done in the same manner each time can be repeated the same way. In PowerShell, you create scripts that don’t need to change. You write them once, in a way that can be run multiple times. This gives you repeatability and consistency.

PowerShell also helps you scale your solutions. The Script you write should work the same way on different tenants. If you’re writing a script that interacts with every site collection or every document library in your SharePoint library, you can write it once and run it against all of the sites and be certain that assuming all of these are configured the same way; it should run the same way.

If you start provisioning sites and use scripting for some of the setup tasks with PowerShell, all of your sites should be the same because they’ve all been provisioned the same way.

You can also update your sites in a consistent manner with PowerShell. As a tool in your armory, it gives you consistency, repeatability, and time-saving so you’re not clicking through the GUI. Every time you need to do a task, you just run a PowerShell script.

Another benefit PowerShell gives you is that you can schedule activities; however, you need to be aware of the differences between SharePoint Online and On-Premise that become obvious, especially when scheduling.

On-premise, you have SharePoint servers. You can set up scheduled tasks on these SharePoint servers. Using SharePoint Online, you don’t have access to the servers, so you can’t set up a timer job on these SharePoint Online servers to run scripts at a scheduled time. Instead, there are other tools you can take advantage of in the cloud to schedule PowerShell scripts – we will show this later in the eBook.

Finally, another way PowerShell adds value is when you use it to extend other tools. You can extend Microsoft Flow or extend Azure Logic apps to accomplish certain tasks that you couldn’t do out of the box with these applications.

Again, a brief diversion on this point. There are some licensing changes coming to Microsoft Flow (*correct at the time of writing). In particular, a connector to Azure services will be reclassified as a premium connector instead of a free connector. The next change will be to the Azure Logic app, so by the time you read this you may have to have premium connectors licenced to leverage some of the automation benefits, but it’s still of value to know it’s there for you to use.

Getting Started

To get started, a tool many people use for authoring PowerShell in Visual Studio Code. If you’ve been doing PowerShell for a while, you might have been using the ISE (Windows PowerShell Integrated Scripting Environment) but in this eBook, we will focus on using Visual Studio Code.

The great thing about Visual Studio Code is that it runs on Mac, Linux, and Windows. What you can write and test in terms of PowerShell on these platforms does vary, but you can at least author the PowerShell within Visual Studio Code on any of those platforms.

The next tool to introduce is Azure Automation. Azure Automation, at a very high level, is “PowerShell in the cloud.” Some people like to use Azure Functions too, so it may be useful to look into this further; however, many choose to go with Azure Automation over Azure Functions because of its integration with Logic Apps and Flow – more on this in the use case section of this eBook.

Key building blocks to understand in PowerShell are the Modules.

For SharePoint, there are two main PowerShell Modules to be aware of:

  • SharePointPnPPowerShellOnline
  • Microsoft.Online.SharePoint.PowerShell

These two modules vary quite a lot in what they can achieve and what they target. Coming up later in the eBook, we dive into both, look at when you would use them and what’s available in each. We also cover how they differ to help build your understanding.

Other SharePoint Online modules you may wish to learn more about after you’ve finished this eBook are the Exchange online module, the Azure AD module and perhaps the Microsoft Teams module. This is because, within Office365, Microsoft has started to blend many of its applications.

For example, if you are creating a Microsoft Team, you ALSO create a SharePoint site. As a SharePoint administrator, you may care about the Teams module and may need to interact with it. You can use PowerShell to do this in many circumstances.

In the case of the Exchange online module, you might need to use it to manage the Office365 groups, or there may be a requirement to work with the Exchange Online team to make sure that you can govern or repeat setup steps for your SharePoint sites. These modules can help you when you encounter customer needs.

The Right Tools For The Job

If you are going to be a master of anything, you need to have the right tools and understand how to use them. The next section covers:

  • Which tools you need for PowerShell
  • How to install modules
  • Using Visual Studio Code
  • Using the tools and modules

To begin, you will need Visual Studio Code. If you haven’t already got this you can go to code.visual studio.com and grab the download. Once installed, you will be good to go.

Once installed, go to your start menu or go to search (which is a quick way to launch applications from the bottom left-hand side of your desktop – next to the Start menu).

The first step once you launch Visual studio code is to set it up for PowerShell. By default, it won’t be configured. There are a number of ways to do this. The first of these is useful if you already have some access to some PowerShell scripts. Click “File” -> “Open a file” and pick a PowerShell source file. In the example we will use, a PowerShell file called “Modules” is selected.

As the file opens, at the bottom right of your screen, you will see a dialogue box. Here Visual Studio Code recommends an extension for you stating “The PowerShell extension is recommended for this file type.”

From here, you can either click “Install” or if you prefer, you can click “Show Recommendations” to see some of the other modules you might want to set up.

Another way to set up Visual Studio Code is to click on the “Modules Extensions” icon where you will see recommended extensions for any files you might want to open using Visual Studio Code.

Type “PowerShell” into the search area to see all of the different PowerShell modules. The module you need is called “PowerShell.” Find and click this to install it to give you full PowerShell functionality (some of which you may have experienced in the ISE if you have ever used it).

You will have the option to change the PowerShell color scheme to “PowerShell ISE” if you are more comfortable in that environment. To do this, click “Set Color Theme.” Type PowerShell ISE and then select it to change from the default ‘dark mode.’

Once you have installed the PowerShell Module, you will also notice at the bottom of the screen, that you have a PowerShell console window. This is used to write PowerShell or for debugging if you want to see the values within variables. The top half of the screen will now be set up as a PowerShell editor (you might need to exit the previous install screen if it’s still there).

To begin writing PowerShell, you will use the top portion of the window. Much like ISC, as you type, Visual Studio Code will dynamically pop up different cmdlets you might want to use. This is a good sign as to whether you have installed a module correctly. If you begin typing a cmdlet name and nothing appears, there is a good chance you have either not installed the module correctly or installed the wrong one.

Once you are sure you have the PowerShell module installed, go ahead and type some cmdlet names. Use the “tab” key to automatically complete the typing if you have found the one you need. You can also do this when searching for different parameters associated to a cmdlet. As each appears, you can click on the parameters information to help make your choice – much like you may have seen in ISE.

Earlier, in the chapter, we highlighted the SharePointPnPPowerShellOnline and Microsoft.Online.SharePoint.PowerShell modules. You will need these for your work. The command to install them is Install-Module then the name of the module (write this in the top PowerShell window).

It’s not always easy to remember these names so if you want to search for them, you can do that in the bottom console window. The command you will need is

Find-Module | ? {$_.Name -match “SharePoint”}

This will search the PowerShell gallery for any modules that may contain “SharePoint” in the name. When the list is returned, if you can’t see the full name of the entry that’s been returned, you can limit what is shown by adapting the command like this:

Find-Module | ? {$_.Name -match “SharePoint”} | ft Version, Name

“ft” shows results in a table and the use of column names limits the values the console will return to you. Having too much information on the return can chop off the important information you might want to see.

You can now copy the information returned and use it in conjunction with the Install-Module command shown above to set these up on your system.

At this point, it is worth noting that you may need to be logged on as an Administrator to your device to install modules (if you have User Access Control enabled). If your device is configured in a similar manner. Open a new Windows PowerShell window as an Administrator (Using “Run as Administrator” in the context menu when you right-click the Application Icon).

Once our PowerShell window loads, copy and paste the install commands for the two modules from Visual Studio Code over into your Administrator PowerShell window one at a time and press return. It’s a simple install, as you can see below and won’t take long.

You can follow this same process for any other modules you decide you need using the find capability in the Visual Code Console and copying the module name into your Administrator PowerShell Window as you need.

A brief point to note at this point is the difference between the two modules introduced. If you want to show all the commands that are in SharePointPNPPowerShellOnline – you can do this using:

Get-Command -Module SharePointPnPPowerShellOnline

You will see there is a huge list of commands available. Modify the line to add in a count:

(Get-Command -Module SharePointPnPPowerShellOnline).Count

And you can see there are 400 commands. Compare that with Microsoft.Online.SharePoint.PowerShell and which has a lot fewer (160). What you will find is that Microsoft.Online.SharePoint.PowerShell module contains a lot more for administering SharePoint as a whole and not as much capability on offer if you want to work with individual sites, lists, libraries, items on those lists.

If you want to work with document libraries, site templates, and list items within a specific site collection or library, the SharePointPNPPowerShellOnline module will be most useful.

Using Modules to Connect

Now the basic tools and modules have been covered; this section will show you how you might connect to different environments. The basis for connection lies within the modules you just configured.

Firstly, you will connect to Microsoft.Online.SharePoint.PowerShell module (or SPO as we’ll now refer to it) in a different way to how you connect with the SharePointPNPPowerShellOnline module (or PNP as we’ll call it from now). Each has a different cmdlet that it connects to (Connect-SPOService and Connect-PnPOnline).

When comparing these two different modules, the SharePoint Online module connects to the tenant-admin.sharepoint.com URL for your SharePoint environment whereas the PnP connection is made to the SPSite (SharePoint site) itself.

As you run different scripts and work with these modules, you may find that you have to connect with PnP as you go through your different sites more than you would with the SPO module primarily because you’re connecting to the PnP module to manage SharePoint which is a more common use case for PowerShell in this context.

Comparing and contrasting a little more, the SPO module supports MFA (Multi-Factor Authentication). If you simply attempt to connect to the SPO service as we’ll see in an example in a moment, it will pop up an MFA window, and you can log in. Nice and easy.

However, the behavior is a little different with the PnP module. It prompts for a “Normal” username and password, but you can extend this and utilize UseWebLogin which supports MFA. When you do this, it pops up a web dialogue in the background and allows you to log in with MFA to take advantage of being able to secure accounts with that method and still run PowerShell.

One example that highlights how these modules differ in their connection method (specifically around MFA) is where you are asked to schedule scripts, and you are required to run them automatically on a set schedule.

There are a couple of options available in this case. The PNP module supports an app ID and a secret. If all your users have MFA, you can use the app ID and the secret to log in and configure your schedule using PowerShell.

Unfortunately, the SPO module doesn’t have the ability to use an app ID and secret to connect. If you want a scheduled script that’s automated and wants to use the SPO module while you’re not sitting at the keyboard (manually logging in), you’ll need an account with MFA disabled. If your accounts have MFA enabled, you will be out of luck unless you can sit at the keyboard when using this module.

Different Types of Connection

In this next section, we will move on to connecting and running cmdlets in more detail, and we will use the modules we talked about installing earlier in the eBook.

In the example, we are using files we have already prepared so that you can see the steps as we progress.

We begin by Opening a Folder – in an earlier example, we opened a PowerShell file. A nice feature of Visual Studio Code is that it lets you open a whole folder.

In the screenshot above, you can see there is a “demos” folder (in this case it is stored in GitHub to help leverage the advantages of source control, but you can locate it anywhere).

Within the folder, you can see we have a ‘2019-09-Collab365-SPsummit’ folder. Here, we have some scripts already prepared. For example, we are using the ‘Connecting.ps’ script from this folder.

We start with a #CSOM script example below. This is a little bit more complicated than others we will cover, and unless you have a reason to need to use CSOM, easier ways are using the PnP or the SPO modules. However, it’s covered here to give visibility of the different methods for connecting.

If you do decide to go the CSOM route, you have to set up your list URL as shown in the first line above. Here, it’s been given a “list title” so that if we wanted to connect to a list, we could.

Next, add these files from the Microsoft SharePoint client library DLLs. To use CSOM you do have to add these DLLs added, or it won’t connect.

Then, grab a secure password, convert it to a secure string from plain text. You can then create a credential object with your username, with your secure password as shown below

Once you have that, create a SharePoint client context object and pass in the list URL that you set up earlier. Add the credentials to it using Context.Credentials. From here, you can take Context.Web.Lists and load in a list. The code shown on line 16 is loading in all of the lists from the website we set up originally. First, it loads them, then executes the query. The result is that you have an object that has all of your lists in it.

Next, we will do the same thing but use the SPO module instead. We will come back to the using PnP module shortly.

Remember, the SPO module is more of an admin type module. One aspect that surfaces here is that when you connect to the SPO service, you give it a URL to your tenant-admin site.

In the example below, we use the ‘intelligink-admin.sharepoint.com’ URL. As noted before, the SPO module supports MFA if you have it enabled on your tenant. Here, we connect to the SPO service and an MFA box pops up in the background.

When the login box appears, we can log in with our credentials and follow the MFA flow as prompted. This lets us into our SharePoint Online environment and we can interact with the site – for example, using Get –SPOSite to get a list of all the sites on your environment and know that we are connected.

The PnP online module is a little different. Here, as you see in the screenshot below, we don’t have the ‘-admin’ in the URL. Instead, we are connecting to an actual site collection. If we run the command shown we are promoted for a ‘normal’ username and password.

If we try to log in to an account with MFA set using just this ‘normal’ user name and password, we will get an error that it doesn’t match.

In this case, as the account is set to use MFA, it will never work. Instead, if we need to use MFA, there is a flag parameter -UseWebLogin that must be used. This will pop up a web login in the background where MFA can be used with the PNP module.

We can use the same Get – SPOSites command in Visual Studio Code to check that we are logged in correctly once we have completed these steps.

Cmdlets

Now that the three key ways to connect have been covered. This section will look in more depth at running some of the cmdlets, how some of these differ from each other and how similar cmdlets behave differently when using the two different modules.

Following the walkthrough above, we are already connected to SPO and PnP. Type Get -SPOSite to get a list of all the sites. In the example below, we select a single site.

Using the $sposite command in the console window will run a query and return the ‘URL’, the ‘owner’ and the “storage quota” on that site.

When using PnP, we connect to individual site collections. If you want to retrieve a site collection using the PnP cmdlets, the first thing we need to do is to connect to that site. We first type the commands into the console, as shown below, and log in using our site and credentials.

In the example, we are connected to a ‘demo’ site. We are not connected to a SharePoint environment as a whole, just a specific site collection. Instead of passing in a URL as we did with SPO, the $pnpsite holds the current site that you are connected to. If we inspect that $pnpsite, you’ll see it looks a little different.

There is no owner or storage quota displayed (items that are useful in an admin context). Instead, you can see the ‘Compatibility Level.’

To dig further into the differences, if we type $sposite and press ‘tab.’ We will see options like AllowDownloadingNonWebViewableFiles, AllowEditing, and AllowSelfServiceUpgrade. These are more aligned to being admin activities for a SharePoint Online site as opposed to what you can see if you look at the $pnpsite. There we will find activities like AllowCreateDeclarativeWorkflow and AllowDesigner.

As you cycle through the options, it’s possible to press the ‘tab’ after the ‘.’ to get the properties and the items that can be managed on the two objects in order. We are presented with two very different objects which can perform very different actions.

To summarise, as you’re working with sites, be aware that Get -PnPsite versus Get – SPOsite are two very different cmdlets and offer two different capabilities when connecting in different ways to sites.

Next, let’s start to look at lists and libraries. In this walkthrough, we look at the ExecuteQuery command that we used earlier.

In the example above, we have already connected to our environment on line 2. We execute a get -PNPlist to view all of the ‘lists’ that are in the site collection are connected.

In the next step, you’ll see we have a “Test List.” We copy that line and paste into the console and run it as in the next screenshot. When we type $list we have captured the “Test List.”

The next thing we try is $list.fields fields because you may want to see all the fields on that list. Initially, we get an error because it can’t enumerate through the collection, it hasn’t been initialized or requested. Even though the code has retrieved the list and should have a collection of associated fields, it can’t get to it.

To expand on why. We can begin by explaining how this is a good example of why there isn’t a huge need for the CSOM connection method covered earlier – unless you can’t use PnP.

Jump back to the earlier connection examples (see the screenshot below). The last section ended up getting the context and loading the list, and that’s how we retrieved the list – using the $Context.ExecuteQuery().

Going back to the example of the list we just used, PNP is using CSOM under the covers. We can just as easily get that same context without all those first ten lines of code by just doing the Connect -PnPOline as in line 2 and then using $context = Get -PNPContext.

Line 8 gets the current context just as if we were doing the CSOM so we can avoid the extra code, and it gets to the same place a lot more easily.

Another way to get the context is to type $context = $lists.Context (assuming you made an object with the lists earlier on). Sometimes you don’t have to get the context access to the items you need.

In the next few lines of PowerShell (line 12), we load the entire $List.Fields collection into the current context by using $list.context.load($List.Fields). Then execute the query as shown below, and it pulls all the information needed into the context. This is how we account for the error that we saw earlier.

Now, if we typed $List.Fields, we would see all the fields in the SharePoint list and would not receive an error.

This same approach works for lists and libraries. However, when you are changing properties or changing the settings, you may have to adapt the approach slightly. If we wanted to use $List.AddItem() to add an item, we still have to use $List.Context.ExecuteQuery() again.

Sometimes, you may need to ‘load’ the whole item again, just to make sure you have all the parts you need when you are making changes – you may not have loaded the entire list, for example. In this walkthrough, we only used .Load($List.Fields). If you’re making other updates to the list, you may have to do a $List.Context.Load($List) to then load the whole list. Don’t forget to also do an ExecuteQuery() if you do the load first.

As you work with SharePoint Online writing PowerShell using the PNP module especially don’t forget about the .Load() and then load the object in, and the .ExecuteQuery() when you see the errors such as the one highlighted at the start of this section where it talks about ‘Error enumerating the collection,’ or if you make an update. Even the $List.Update() command doesn’t always update until you use ExecuteQuery().

This is one of the key things to take from this eBook so it’s worth repeating, don’t forget the $Context.Load() and the Context.ExecuteQuery ().

Possible Use Cases

In this next section, we will begin to look more deeply at use cases and walk through how to put all of what’s been covered in the previous sections together so you can become that PowerShell superstar at your company.

Provisioning

The first strong use case for PowerShell is provisioning. There are a couple of different ways you can consider provisioning when it comes to SharePoint Online sites. It may be that you need to create a plain site. A site that you get out of the box. It takes a line of PowerShell to create those sites and then you can run a few lines of PowerShell to create new document libraries, add content types, document libraries, modify settings, enable/disable features. Everything you will do is similar to what you used to do on-premise, whether it’s PowerShell cmdlets to interact with lists, libraries, site settings, features, etc.

Provisioning with PowerShell plays into the teams and Office365 groups also because you may need a provisioning solution for them so that when they get created – and their associated SharePoint sites are created – you have the same type of provisioning process end to end. If you use this approach, you can go back and manage all of the sites and make updates to these SharePoint sites at a later date, if you provisioned them using PowerShell.

Perhaps you have properties that you added to the property bags on a SharePoint site. At a later date, when you go back and run SharePoint scripts because you have a new content type on your project site that needs to get added to document libraries, or you have a new format for your issues list – you can use those property bag settings that you’ve applied to SharePoint sites and apply changes only on the appropriate sites.

As an example, imagine a client where you need to add ten different values to the property bag on a site – whether it’s location, departments, type of site, who requested the site – so that later, you can use those properties for reporting. Later, you might be asked to make a change to the sites, perhaps it’s a template type in a document library or adding a new document library. Perhaps it’s slight tweaks to SharePoint permission.

As the client has grown, imagine they have a hundred or even 2000 SharePoint sites. That is not an activity that you want to do manually. If you start with a provisioning process and provision to all of the sites the same way, you can write PowerShell to loop through all those sites, look at various properties and apply settings to all of the sites knowing that they were uniformly created and that you can keep them in line by running PowerShell – rather than manually making all these changes in the GUI.

PnP Templates

This is an area that could probably have an eBook all on its own, but we will cover it briefly regardless.

When you get into the PnP templates, there are a vast number of PowerShell commands allowing you to create, edit, modify and apply PNP site templates to it. This is an area that – to be a PowerShell master – you will want to spend time learning. Unfortunately, there just isn’t the space in the eBook to do that so it’s referenced only to give an awareness that PNP templates exist, and the power that you have with them to create templates and apply them to your SharePoint sites. Doing it is a little different from writing basic PowerShell scripts. It can become a lot more complex and developed, but it is an area that – if you want to master PowerShell – you should dig into and take advantage of when it comes to creating uniform SharePoint sites and leveraging PowerShell to help you.

Reporting

Another use case to cover in this section is reporting. You might want to create a report containing a list of all your SharePoint lists and libraries. You might want to see how many documents are in each, for example, or when the libraries were last modified so you can find document libraries that haven’t been modified for some time. You may want to filter these lists using certain pieces of data such as site title or URL etc.

This can all be done using PowerShell scripts. Writing information to SQL databases or Azure SQL databases and then using power BI to pull that information in and build reports.

Another good use case you may want to develop an understanding of is that you can run scripts for activities such as reporting on an automated basis once a day, once a week, once a month, and report on the activity within your SharePoint Online environment.

Flow & Logic Apps

A final use case we mentioned early in this eBook is extending Flow and Logic apps. One example which we will cover in the walkthrough later is setting list item permissions using Azure automation from Logic apps or Flow.

You can also use PowerShell with Flow and Logic Apps to copy values between lists when there might be choice fields, or for managed metadata fields where you have multiple selections enabled.

If you have ever noticed in Flow or Logic apps, certain field types are not supported in some of the actions. You can use PowerShell and Azure automation in these cases. With reference to the earlier comment about licensing, these solutions won’t be quite as slick as simple PowerShell, but when a certain field type isn’t supported with a certain action, you can create an Azure automation step, and use PowerShell to take the actions on the field types that aren’t necessarily supported in the workflow automation tools. What you are doing here is taking advantage of PowerShell with Azure automation for activities that you can’t do in workflow alone, but know how to do in PowerShell.

Examples

This next section will give you some examples and share how you might use them in your day to day work. You will see how to set them up, how to use Azure automation and how Flow and Logic Apps can tie into the PowerShell as well.

The eBook is aiming to support a wide variety of skill sets with these examples so they won’t be really specific but equally, the aim is to give you some scripts that you can actually use whether you are new to PowerShell or just improving your skills. The goal is to give examples that will help improve your management of SharePoint Online with PowerShell.

The use cases covered in the section won’t be complex initially. Instead, they will be small ‘helper’ items that you may want to use from day to day.

Setting Multi-Value Fields

Multi-value fields have shortcomings when you start to deal with copying and working with items using Azure automation and Flow.

There are a couple of scripts to explore, which show you how you may wish to copy multi-value SharePoint fields. In the first of these (below), we pass in a number of parameters (the URL, the source list name, destination list, name, the item ID for both the source and destination and field names for both the source and destination). We then connect to PNP using stored credentials in Azure automation that we’ll examine in a moment. Retrieve various SharePoint list items using the PnP – because with as mentioned earlier, we are dealing with list items and fields, not overall SharePoint administration.

In the example, we will get some field values, set up an array and get all the departments that are in the list. In the example we use a multi-value choice field, leveraging a list of departments and then we set the PnP list item. To do this, we use a destination list, destination ID and we copy the values for that destination from the array that holds the source item values.

Once that’s done in line 30, we perform a system update so it doesn’t show that the script doesn’t show last modified by me (instead it’s the system performing the update) and then because we use Azure automation, we perform an Out-Null (this helps with an issue where Azure Automation might freeze and not complete the job.

Copying Multi-Value Lookup Field Values between lists

Another example is the Multi-Value SharePoint lookup fields.

In the example above, we use the same process setting multi-value fields. Instead of a choice field, we use a lookup field. We have to tweak the PowerShell a little bit because it uses LookupID and LookupValue, but in the end, we are still building an array and then updating a destination, using a $destItem.Update.

This is an example where you don’t have to do the Context.ExecuteQuery at the end. You can do an update and then apply the update as in the example.

This, and the examples right beforehand are cases where you can call them from Azure automation. We will show you how in the next section.

Limiting Group Creation

Another commonly used example is limiting group creation. This has some added complexity however, as a SharePoint Admin, you may want to talk to your Office365 admin because if they allow anybody to create Office365 groups, it is also limiting your ability as an admin to be able to manage SharePoint and govern your SharePoint environment.

In the example, we use an Azure AD setting (line 4). We need to do a Connect-AzureAD to limit group creation as we have in the example if we are trying to limit SharePoint site creation.

It’s worth noting that it is a little hard going back and forth between SharePoint and Office365 because as we mentioned when we initially wrote about modules, a lot of SharePoint admin activity is intertwined and affected by Azure AD or by Office365 groups or Exchange Online. The lines are a lot more blurred nowadays.

Creating SharePoint Online Sites

In another use case, you may also choose to create a script as we have done where it is just focused on creating SharePoint Online sites. You might want to go into Office365, create a SharePoint Online list, add a new list item and your script goes off and does a new PNP site or a new SPO site. Then you want it to create the new site collections so that you do not have to go into SharePoint admin.

You might use the example above because you want your service desk to be able to create new site collections, but you don’t want to give them access to SharePoint administration. Do it with a list, tie it into Logic apps or Flow, have that Flow call PowerShell and create the SharePoint sites for you.

Azure Automation

Azure Automation is another tool in your arsenal, but it has made a big difference to many SharePoint admins and the clients they work with in terms of being able to automate scripts with SharePoint Online without having to spin up a server or use a separate service.

In this example, we begin at portal.azure.com.

Azure automation can do a LOT, imagine it as PowerShell in the cloud for scheduled jobs. To begin with, we search for ‘services.’ Start typing in “automation” and we see “Automation Accounts.”

This will pop up an automation account. We can create new accounts by just clicking “add” and “new account.” It is a very straightforward process to go through it. We click add, fill in name, subscription, resource group location, run as account, click “create.” That’s all there is to it.

This is free for the first 500 minutes of PowerShell that runs in your environment. After that, it’s very cheap to run but does carry a cost.

In this example, we are using a demo that’s already been configured so some of the key highlights can be mentioned. We’re not going to look at everything.

Modules

The very first thing to look at is “Modules.” In the same way, as SharePoint PNP and SharePoint Online modules weren’t installed by default in Visual Studio Code, they are also not installed in Azure automation by default, so we need to install them.

The first action taken in this example is to spin up an Azure automation account. Browse the gallery, search for SharePoint and find “SharePointPnP”.

We may need to update them, so to do that we click on “import” and click okay.

We do exactly the same thing for the SharePoint Online module. As those updates take place, we will start seeing in our modules that it begins importing newer versions.

Credentials

The next item to know about is “Credentials.” In the example, there are a couple of credentials already – A PowerShell Admin, Provisioning and SQL reporting set.

To add a credential, we give it a name, a username, a password, click create and it’ll create this as a variable or a credential that we can use in our Runbooks or in our PowerShell script. Those are the two most important tasks to get set up.

Runbooks

This is where your PowerShell scripts are. In the example below, you will notice some of the same scripts we showed earlier in this section –MultiValueSPField, MultiValueSPLookupField, GetUnifiedGroups etc. Here, we create a new “Runbook,” give it a name, a run book type of PowerShell and click create.

This activity creates a run book that we edit. We can do a Connect-PnpOnline (note: this interface does not have ‘tab’ complete, often people still write all of their PowerShell scripts for Azure automation in Visual Studio Code for this reason and then copy and paste).

In the example above, you can see we have connected to a site and passed a credential variable but we don’t actually have that credential variable yet. To create that variable and do a connection, we leverage some of the capabilities within this interface. If we look under assets, we see we have the credentials created beforehand. Next, we click “Add to canvas” and it writes the line for us.

This works if we are connecting to PNP Online, or SPO Online as long as we don’t have MFA enabled. This is where a break glass account works. You could do something similar to what we have above with variables by storing a client secret in a variable and use an App ID and client secret. If you are working with PnP this is where you can store some of that information to pass it in before you connect to your SharePoint Online environment.

In all the examples above, we have essentially introduced PowerShell in the cloud. A lot of admins will write scripts and store them in this way. If it’s just a one-time script, you can probably just run it from your computer or run from a server. However, if you have a recurring script that you want to call from a workflow, maybe using Flow or Logic Apps, that’s where the real power of Azure Automation comes in. Examples would be a reporting script or where you may want to build the site index.

Schedules

Something to notice in Runbooks is that you have “Schedules,” when you click on “Schedules,” you have the option to add a schedule.

If you click, “Add a schedule,” you can “link to a schedule to your runbook,” and you can configure one. If you want to pass in different parameters you can do that. In our example, we don’t use any, so we click “OK.” This gives a daily schedule where Runbook will go and run every day to create an index.

This is very handy for writing recurring runbooks where you don’t want to leave a server running, but you want to be able to run PowerShell against your SharePoint Online environment on a regular basis.

You can do all kinds of things with Azure Automation. You can add webhooks; you can call Runbooks from other Runbooks. All of these capabilities are linked together to make use of PowerShell in the cloud.

An Example Using Logic Apps

We will explore this next example in Logic Apps rather than Flow – the same can be done in Flow – but the reason we do it this way is because of a licensing change that’s going to happen with Flow. That change will turn this connection for Azure automation into a premium connector, which means you will have to pay more to be able to do this from Flow. It does work the exact same way in Logic Apps as it does in Flow.

Firstly, we load up Logic apps.

You can see a few examples in the screenshot below. We have edited the “Demo” example.

In the example, if we click “Edit” we will see we already have some connections to SharePoint, “Get item,” “Create item.” If we click “New step,” we will create a new item and add an Azure automation job to it.

When we click on “New step”, and search for “Automation” and we can see that there is an Azure automation connector available.

If we select that click on “Create job” and choose the subscription, resource group, and automation account. The option is then available to decide whether to “Wait for job” or not.

Many people almost select “Yes” here. This question is asking, “Do you want the PowerShell to run before I move on?” Usually, you will want to wait for the job because subsequent steps are dependent on something here. Or if this PowerShell job modifies the same item that another step is working on, you can get conflicts where it is trying to be edited twice in SharePoint.

So in most cases, you will put “Yes” there. Next, we can see “Add new parameter.” This is where we select “Runbook Name” and it will go and examine the subscription for all these Runbooks (in the example you can see the Runbooks we showed earlier in this section. These show once we tick the option to add Runbook name as the new parameter.)

This is where Azure Automation is really helpful. As soon as we select one of the Runbooks, we will see all of the additional parameters that are set when you configure a runbook. We can select fields from where we used “Get item” and “Create item” and pass all of those values into the Runbook. This step happens automatically. It goes and collects all of those Runbook parameters.

One thing to be careful of if you are using Runbooks is that you have to make sure that they’re published. If they aren’t the steps here may not work.

If the Runbooks are not published, your Logic App won’t be able to get all of the values you need and execute that PowerShell Runbook.

This is how you can take PowerShell and incorporate it into your workflows when working with SharePoint Online lists, libraries, whether you’re doing provisioning, and you want to use logic apps for your provisioning, or you are working on other things. If you want to use the Azure automation, to re-iterate, the recommendation would be to use Logic Apps instead of Flow, unless you already have Flow premium.

Summary

In this eBook, we didn’t spend the whole time looking in-depth, step-by-step at PowerShell. Instead, it aimed to you give an awareness of the main tools that you might put in your hands to help you become a PowerShell master. We hope it has given you some ideas around how you can leverage PowerShell, how you connect to services and how various different tools all fit together. You can Google some of the areas covered to get a deeper understanding or contact the presenter from the session this eBook is based on (Ben Stegink). His details are coming right up. For now, we hope you and enjoyed reading and thanks again for reading.

0000-00-00 00:00:00


Leave a Reply

Your email address will not be published. Required fields are marked

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}