How do I automatically insert username into the Sharepoint OneDrive URL

brichardi 311 Reputation points
2023-07-11T18:08:21.54+00:00

Hello Sharepoint/OneDrive expert.

I am in the process of migrating user's HomeDrive to Sharepoint OneDrive.

I followed Microsoft learm document and was able to but migrate user HomeDrive to OneDrive with sucess. But one issue I have is I have to create a spreadsheet that has user's home directory path, and insert user logon name into Sharepoint OneDrive URL This task takes very long time because some of our department has more than 300 users, and inserting each user name into Sharepoint OneDrive URL really takes up a lot of my time ( Please see attachment for the spreadsheet example.).

My question is, is there a way to automate the process of inserting user's name into Sharepoint OneDrive URL?

Thank you for your help.OneDriveMigrationBatchFile.JPG

OneDrive
OneDrive
A Microsoft file hosting and synchronization service.
731 questions
SharePoint
SharePoint
A group of Microsoft Products and technologies used for sharing and managing content, knowledge, and applications.
9,188 questions
0 comments No comments
{count} votes

Accepted answer
  1. Ling Zhou_MSFT 11,625 Reputation points Microsoft Vendor
    2023-07-13T07:34:28.67+00:00

    Hi @brichardi,

    Of course, I could show each step in detail. I have coded the last answer with your private information.

    Here are the steps:

    1.First we have a table that records the user's name.

    User's image

    2.Select B1 and enter the formula in the upper input box. Press the Enter key.

    Formula of B1: 
    =MID(A1,FIND("s",LEFT(A1,10)),100)
    

    User's image

    3.Select all rows to be calculated. Press Ctrl+D to auto-fill the table.

    User's image

    User's image

    4.Select C1 and enter the formula in the upper input box. Press the Enter key.

    Functions of C1: 
    =MID(B1,FIND("\",LEFT(B1,10))+1,100)
    

    5.Select all rows to be calculated. Press Ctrl+D to auto-fill the table.

    User's image

    User's image

    6.Select D1 and start by entering the first half of your URL, for example: https://doamin.sharepoint.com/XXXXX/

    User's image

    7.You will notice that the grid is automatically recognized as a URL, we need to unlink it first. Select the table and then right-click and choose Remove Hyperlinks.

    User's image

    8.Select D1 and enter the formula in the upper input box. Press the Enter key.

    Formula of D1:
    
    ="https://doamin.sharepoint.com/XXXXXX/"&C1&"XXXXXXX"   
    
    Here please note that replacing your URL
    

    a9.Select all rows to be calculated. Press Ctrl+D to auto-fill the table.

    User's image

    User's image

    If you have any other questions, please don't hesitate to contact me.


    If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    1 person found this answer helpful.
    0 comments No comments

2 additional answers

Sort by: Most helpful
  1. Ling Zhou_MSFT 11,625 Reputation points Microsoft Vendor
    2023-07-12T05:29:49.3933333+00:00

    Hi @brichardi,

    Thank you for posting in this community.

    Perhaps you can splice the username into the SharePoint OneDrive URL via an excel function, as I see the SharePoint OneDrive URL is quite regular and they simply have different names.

    This is the result of the test on my side:

    enter image description here

    Functions of B1:
    =MID(A1,FIND("s",LEFT(A1,10)),100)
    
    Functions of C1:
    =MID(B1,FIND("\",LEFT(B1,10))+1,100)
    
    Functions of D1:
    ="https://doamin.sharepoint.com/XXXXXX/"&C1&"XXXXXXX" 
    
    Here please note that replacing your URL
    

    For other users, you can automatically calculate the results through the autofill (When the mouse moves into the lower right corner of the grid and turns into a black cross, hold down the left mouse button and drag it downward), please note that must be in the order of column B, C, D to fill the results of the column. Because the latter column needs to use the value of the previous column.


    If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


  2. brichardi 311 Reputation points
    2023-07-13T11:52:45.8833333+00:00

    Thank you Ling Zhou. This is very helpful.

    0 comments No comments