none
Create folders in ODFB with PowerShell for multiple users (.csv file)

    Question

  • Hello,

    I'm trying to create 2 folders on each users' ODFB provisioned, and can't get the PowerShell script to work for multiple users at once.

    I used the same script to create my 2 folders for ONE user (after modifying a bit), and that worked correctly.
    Went to all possible websites on internet to look for a solution for the error I'm getting, still can't get it resolved :-(

    Script:
    ======

    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint.Client")
    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint.Client.Runtime")

    $spoUser = "<admin_acct>@<tenant_name>.onmicrosoft.com"
    $spoPassword = Read-Host -Prompt "Please enter your Admin password" -AsSecureString
    $creds = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($spoUser,$spoPassword)


    $FilePath = "C:\users\<localAcct>\Desktop\ODFBUsers.csv"
    $users = Import-Csv -Path $FilePath

    ForEach ($user in $users) {
    $spoOD4BUrl = ("https://<tenant_name>-my.sharepoint.com/personal/{0}_mydomain_co_uk" -f $user.user)
    $ctx = New-Object Microsoft.SharePoint.Client.ClientContext($spoOD4BUrl)
    $ctx.RequestTimeout = 16384000
    $ctx.Credentials = $creds
    $ctx.ExecuteQuery()

    $web = $ctx.Web
    $ctx.Load($web)

    $spoDocLibName = "Documents"
    $spoList = $web.Lists.GetByTitle($spoDocLibName)
    $ctx.Load($spoList.RootFolder)

    $spoFolder = $spoList.RootFolder
    $Folder1 = "FOLDER1"
    $newFolder1 = $spoFolder.Folders.Add($Folder1)
    $web.Context.Load($newFolder1)
    $web.Context.ExecuteQuery()

    $Folder2 = "FOLDER2"
    $newFolder2 = $spoFolder.Folders.Add($Folder2)
    $web.Context.Load($newFolder2)
    $web.Context.ExecuteQuery()
    }

    When I run this script I get the following error:
    =============================

    Exception calling "ExecuteQuery" with "0" argument(s): "The remote server returned an error: (401) Unauthorized."
    At C:\Users\<localAcct>\Desktop\MyPSScriptName.ps1:32 char:2
    +     $ctx.ExecuteQuery()
    +     ~~~~~~~~~~~~~~~~~~~
        + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
        + FullyQualifiedErrorId : WebException
     

    I have looked for this error everywhere, but cannot find anything to solve my problem (or that I could probably understand from a CSOM perspective :-) )

    Thanks for your help.


    FrenchSpeaker - MCTS

    Wednesday, April 13, 2016 8:53 AM

Answers

  • Yeah, that'll always fail. You're doing it wrong, twice ;)

    Looking just at this version (PS, the code snippet function is great, use it!):

    $users = "user2@mydomain.co.uk","user3@mydomain.co.uk"
    
    ForEach ($MyUsers in $users) {
    $spoOD4BUrl = ("https://<TenantName>-my.sharepoint.com/personal/"+$MyUsers -replace "@mydomain.co.uk","_mydomain_co_uk")
    Write-Host ("URL is: " + $spoOD4BUrl)


    Let's break it down a bit:

    First, your variable name could be better. You've got a collection of users, and then you go through each of them and call them 'MyUsers'. That's not great as it implies they are plural, and it isn't descriptive as it's not 'User' objects, it's just a username. Let's change that to 'fullUserName'

    $users = "user2@mydomain.co.uk","user3@mydomain.co.uk"
    
    ForEach ($fullUserName in $users) {
    $spoOD4BUrl = ("https://<TenantName>-my.sharepoint.com/personal/"+$fullUserName -replace "@mydomain.co.uk","_mydomain_co_uk")
    Write-Host ("URL is: " + $spoOD4BUrl)

    This will still fail.

    So, we need to get the username bit, before the @ sign. If you're good at PowerShell you can do this sort of thing in a single, short, line. You aren't good at PowerShell, so don't try to. Let's split your process up and add some write-hosts:

    $users = "user2@mydomain.co.uk","user3@mydomain.co.uk"
    
    ForEach ($fullUserName in $users) {
    #Get the username without the @domain bit
    $shortUserName = $fullUserName -replace "@mydomain.co.uk"
    Write-Host ("Full username ={0}, Short username ={1}" -f $fullUserName, $shortUserName)
    
    #Use the new $shortUsername to build the full path
    $spoOD4BUrl = ("https://<TenantName>-my.sharepoint.com/personal/"+ $shortUserName" + "_mydomain_co_uk")
    Write-Host ("URL is: " + $spoOD4BUrl)

    That's not the way i'd do it but it should work.

    When you're using the CSV then you should replace the $fullUserName with $userRow and then get the username by using $userRow.MyUsers.

    Try it and post your full code, in the code snippet tool.

    As to your 401 error. I suspect your previous scripts haven't done what you think and you don't have permission on that site. Check through the GUI before going any further.

    Monday, April 18, 2016 4:20 PM

All replies

  • Have you granted permissions to each individual ODFB site collection for that admin account?
    Wednesday, April 13, 2016 6:36 PM
  • Yes I did (using another script) 



    FrenchSpeaker - MCTS

    Thursday, April 14, 2016 8:57 AM
  • You need to be site collection admin on that personal site, even if your account has global administrator role. Check your status by directly logging to the OD4B site in the browser.

    ---
    Rajesh | Blog

    Sunday, April 17, 2016 4:22 AM
  • Hi Rajesh,

    Thanks for the answer. 
    I am a SCA on each ODFB's users, and the script works for 1 user.

    As soon as I use a .csv file OR even put $users = "user1@domain.com","user2@domain.com" then I get the error.


    FrenchSpeaker - MCTS

    Sunday, April 17, 2016 4:19 PM
  • Bring in your SharePoint online credentials object also inside the for each loop where you are connecting to new site collection every time. Its worth trying...

    $creds = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($spoUser,$spoPassword)


    ---
    Rajesh | Blog

    Monday, April 18, 2016 12:22 AM
  • Hi,

    Tried it but still the same error :-(

    Exception calling "ExecuteQuery" with "0" argument(s): "The remote server returned an error: (401) Unauthorized."
    At C:\Users\<localAcct>\Desktop\CreateODFBFoldersForTWOUsers_test.ps1:55 char:2
    +     $web.Context.ExecuteQuery()
    +     ~~~~~~~~~~~~~~~~~~~~~~~~~~~
        + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
        + FullyQualifiedErrorId : WebException


    FrenchSpeaker - MCTS

    Monday, April 18, 2016 9:15 AM
  • Which $ctx.ExecuteQuery() is it failing on?

    Also the most obvious thing to go wrong is your script using the wrong values for the loop. Break out the add library behaviour into a function and then call that from a loop that goes through the CSV users.

    That allows you to break your code down into component parts and test it more effectively.

    My gut feeling is that you're structuring your users data wrongly. This doesn't look like a valid URL but if you're using the example 'user' code you're passing in that's what you're asking for:

    https://<tenant_name>-my.sharepoint.com/personal/user1@domain.com_mydomain_co_uk

    Write out the Site string after you've generated it to provide a reality check.

    Monday, April 18, 2016 11:15 AM
  • Hi Alex,

    I noticed the .csv file data would add the full User Principal Name in the URL like you mentioned https://<tenant_name>-my.sharepoint.com/personal/user1@domain.com_mydomain_co_uk

    I changed the csv file with only user1 (no @mydomain.co.uk), but even with that, nothing works.

    Tried also so many combination in the script (variable // .TrimEnd("@mydomain.co.uk") // function // .split // etc...) nothing works and always got the 401 Unauthorized error.

    Losing my mind after 2 weeks on that.... 


    FrenchSpeaker - MCTS

    Monday, April 18, 2016 1:00 PM
  • Break it out as a function.

    Failing that include the following code 

    ForEach ($user in $users) {
    $spoOD4BUrl = ("https://<tenant_name>-my.sharepoint.com/personal/{0}_mydomain_co_uk" -f $user.user)
    #New Line below
    Write-Host ("URL is: " + $spoOD4BUrl)
    $ctx = New-Object Microsoft.SharePoint.Client.ClientContext($spoOD4BUrl)
    That will print out the URL you are using, you can then check to see if its' a real URL.

    Monday, April 18, 2016 1:19 PM
  • So it returns:

    URL is: https://<TenantName>-my.sharepoint.com/personal/_mydomain_co_uk

    Seems the {0} in the line /personal/{0}_mydomain_co_uk" -f $user.user seems not to be taken into consideration, but I'm not expert enough to know is that's the correct syntax :-(

    Then I got the 401 error still.

    I haven't any [0] (in brackets - not curly brackets) anywhere in my script, don't know if I need that somewhere?

    Also when I try the variable "$users", it returns what I got in the .csv file - good.
    But "$user.user" like in the initial script, I got nothing.
    Should that be "-f $users.user" ? (with an S on the variable?)


    FrenchSpeaker - MCTS

    Monday, April 18, 2016 2:12 PM
  • Try like below,

    $spoOD4BUrl = "https://<tenant_name>-my.sharepoint.com/personal/{0}_mydomain_co_uk" -replace "{0}", $user.user

    Also make sure that in your csv file for the user column, you have properly escaped the special characters with underscore(_)

    For example:

    rajesh.sitaraman => rajesh_sitaraman


    ---
    Rajesh | Blog

    Monday, April 18, 2016 2:46 PM
  • You're looking in the right place. If there is no 'user' property on the 'user' object then it'll return a null value.

    $user.user means take the $user object (which is a line in your CSV) and get the value from the column labeled 'user'. So in that case your csv should look something like:

    User,FullName

    AlexB,Alex Brassington

    FrenchS,French Speaker

    The first line is always assumed to be the 'title' of the column, so if you're missing it or using a different value then PowerShell won't find your term.

    You really should learn how to use Functions. One of the benefits of them is that by doing them you tend to find and fix these issues a lot faster.

    Post your latest version of your code and also a copy of your CSV (with some fake usernames).

    Then read this: https://blogs.technet.microsoft.com/heyscriptingguy/2015/07/08/fun-with-powershell-functions/

    Monday, April 18, 2016 4:00 PM
  • You could do that but you're not going to get any real difference in this case between the -f (-format) operation and the replace function. It's also a bit more clunky.

    http://ss64.com/ps/syntax-f-operator.html

    Monday, April 18, 2016 4:03 PM
  • @Rajesh,
    Tried but doesn't work :-( But used the "-replace" later on....

    In the meantime I tried (again) the script (which is working) for 1 user + I added the following line from Alex

    #Variable
    $users = "user1@mydomain.co.uk"


    ForEach ($user in $users) {
    $spoOD4BUrl = ("https://<TenantName>-my.sharepoint.com/personal/user1_mydomain_co_uk")
    #New Line
    Write-Host ("URL is: " + $spoOD4BUrl)

    This returns
    ========
    URL is: https://<TenantName>-my.sharepoint.com/personal/user1_mydomain_co_uk

    *** Now I've done the same with multiple users (user2 and user3) ***

    $users = "user2@mydomain.co.uk","user3@mydomain.co.uk"

    ForEach ($MyUsers in $users) {
    $spoOD4BUrl = ("https://<TenantName>-my.sharepoint.com/personal/"+$MyUsers -replace "@mydomain.co.uk","_mydomain_co_uk")

    Write-Host ("URL is: " + $spoOD4BUrl)

    This returns:
    ========
    URL is: https://<TenantName>-my.sharepoint.com/personal/user2_mydomain_co_uk

    Then 401 error...

    My .csv file is formatted like this:
    ====================

    MyUsers
    "user2@mydomain.co.uk",
    "user3@mydomain.co.uk"

    It looks like it's not looping through the users in the .csv file, nevertheless, still not creating the folders for "user2"...


    FrenchSpeaker - MCTS

    Monday, April 18, 2016 4:07 PM
  • Yeah, that'll always fail. You're doing it wrong, twice ;)

    Looking just at this version (PS, the code snippet function is great, use it!):

    $users = "user2@mydomain.co.uk","user3@mydomain.co.uk"
    
    ForEach ($MyUsers in $users) {
    $spoOD4BUrl = ("https://<TenantName>-my.sharepoint.com/personal/"+$MyUsers -replace "@mydomain.co.uk","_mydomain_co_uk")
    Write-Host ("URL is: " + $spoOD4BUrl)


    Let's break it down a bit:

    First, your variable name could be better. You've got a collection of users, and then you go through each of them and call them 'MyUsers'. That's not great as it implies they are plural, and it isn't descriptive as it's not 'User' objects, it's just a username. Let's change that to 'fullUserName'

    $users = "user2@mydomain.co.uk","user3@mydomain.co.uk"
    
    ForEach ($fullUserName in $users) {
    $spoOD4BUrl = ("https://<TenantName>-my.sharepoint.com/personal/"+$fullUserName -replace "@mydomain.co.uk","_mydomain_co_uk")
    Write-Host ("URL is: " + $spoOD4BUrl)

    This will still fail.

    So, we need to get the username bit, before the @ sign. If you're good at PowerShell you can do this sort of thing in a single, short, line. You aren't good at PowerShell, so don't try to. Let's split your process up and add some write-hosts:

    $users = "user2@mydomain.co.uk","user3@mydomain.co.uk"
    
    ForEach ($fullUserName in $users) {
    #Get the username without the @domain bit
    $shortUserName = $fullUserName -replace "@mydomain.co.uk"
    Write-Host ("Full username ={0}, Short username ={1}" -f $fullUserName, $shortUserName)
    
    #Use the new $shortUsername to build the full path
    $spoOD4BUrl = ("https://<TenantName>-my.sharepoint.com/personal/"+ $shortUserName" + "_mydomain_co_uk")
    Write-Host ("URL is: " + $spoOD4BUrl)

    That's not the way i'd do it but it should work.

    When you're using the CSV then you should replace the $fullUserName with $userRow and then get the username by using $userRow.MyUsers.

    Try it and post your full code, in the code snippet tool.

    As to your 401 error. I suspect your previous scripts haven't done what you think and you don't have permission on that site. Check through the GUI before going any further.

    Monday, April 18, 2016 4:20 PM
  • Alex,

    I want to thank you SO MUCH for the time you've spent and for solving my problem !!! (^_^)

    Copied and modified accordingly the script you pasted above, the error "401 Unauthorized" disappeared and all works perfectly with a .csv file.

    @Rajesh - Thanks for helping too!

    Thanks again!
    Cheers.


    FrenchSpeaker - MCTS

    Tuesday, April 19, 2016 8:35 AM
  • Glad to know your 2 weeks of frustration came to an end. Happy coding !!

    ---
    Rajesh | Blog

    Tuesday, April 19, 2016 11:06 AM
  • Can you post the working script? I am trying to accomplish the same thing and I am not able to decipher what was done here to compile a working script.

    I am essentially trying to create a folder called Homework in all users OD4B accounts.

    Thanks,

    BD83

    Friday, June 24, 2016 7:12 PM