Run script on multiple users (using Excel table)

Question

Hello everyone,

im trying to build a PowerShell script that runs a command multiple times but with different URLs, but i cant quite understand how to build it.

The point is to remove a user from the Share Point sites listed in the column D.

So lets say the user "max.muster@microsoft.com" needs to be removed from those 2 OneDrive Sites.

$User = "max.muster@microsoft.com"

$AdminSite = ****************************

Connect-SPOService -URL $AdminSite -creds

Import-Excel C:\temp\testim.xlsx -startcolumn 4

    Remove-SPOUser -Site $tesi -LoginName $User

Im quite new to PowerShell, but havent found the right method to optimize this procedure.

Thanks in Advance.

Emiliano

Answer 1

Import-Excel returns a series of PSCustomObjects. The properties of the objects are the names from the column headers in the worksheet.

Give this a try:

$User = "max.muster@microsoft.com"
$AdminSite = ****************************
Connect-SPOService -URL $AdminSite -creds

Import-Excel C:\temp\testim.xlsx -startcolumn 4 |
    ForEach-Object{
        Remove-SPOUser -Site $_.testi -LoginName $User
    }

Answer 2

Here is a good document to refer to if you are learning Powershell.

https://www.sapien.com/books_training/Windows-PowerShell-4

In the statements that you posted, you have Import-Excel but you don't assign the data to any variable. How would you reference the data?

There are plenty of examples on the internet.

https://www.bing.com/search?q=powershell+import-excel+examples

Review a few examples and see how others read spreadsheets.

Build your script block by block. That is, focus on processing the spreadsheet first. Figure out how to read it and to display the contents before you try to add in additional functionality (Sharepoint calls).

Use Powershell_ISE to test your script. It is already installed on Windows.