How do I automatically insert username into the Sharepoint OneDrive URL

Question

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

Answer 1

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.

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)

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

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.

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

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.

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.

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.

Answer 2

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:

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.

Answer 3

Thank you Ling Zhou. This is very helpful.