6

I am looking for a way to derive the OneDrive file URL for a file cached to my local OneDrive folder? The only thing I can think of is hardcoding some root URLS for each of the OneDrive folders I have, but this seems nasty!

Does anyone know of any OneDrive client API that lets query a URL based on the local file path?

My use case: I am trying to attach to and open instance of an Excel workbook. I used to be able to do this Marshal.BindToMoniker(_workbookPath);

However it appears that Excel is now registering the OneDrive URL in the ROT rather than the local file path. This this happened with the update that brought the new auto-save feature to Excel 2016 I think, that seems to be about the time my existing code broke.

There is a similar unanswered question here: C# OneDrive for Business / SharePoint: get server path from locally synced file

John
  • 1,714
  • 21
  • 41
  • are you able to use BindToMoniker() on the onedrive URL? How? – Ben Jul 05 '18 at 17:55
  • This problem is very tough to solve in the general case. I have written a complete solution but unfortunately, it is VBA only. If you are desperate enough you may want to port it to C#, you can find it [here](https://stackoverflow.com/a/74165973/12287457). A solution that works by using the mapping found in the registry keys mentioned in the current answers here will not always work depending on how the files/folders are synchronized but it is much simpler. – GWD Jan 26 '23 at 11:11

3 Answers3

3

Might want to use the following key instead:

HKEY_CURRENT_USER\Software\SyncEngines\Providers\OneDrive

This includes the following registry values: UrlNamespace: (SharePoint site URL) MountPoint: (local driver location)

It does appear to include old values which are no longer synced - but it shouldn't be too hard to check against

HKEY_CURRENT_USER\Software\Microsoft\OneDrive\Accounts\Business1\ScopeIdToMountPointPathCache

or

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Explorer\SyncRootManager

for paths that are being actively synced.

Minkus
  • 335
  • 2
  • 13
  • Thanks for sharing this great catch. Answers partly the question. @minkus I would be curious to know how you could find this one out. Any link to some resources? or insider knowledge? One issue is when you don't sync the root level: The urlNameSpace points only to the root level and you can not identify for sure which subdirectory was synced from the registry entry. – Thierry Dalon Nov 30 '20 at 08:40
  • @ThierryDalon Nothing but a registry search for part of a unique string which is part of a synced folder's name! Glad it helps. I can't suggest anything more for the subdirectory than what's already here (have you tried %localappdata%\Microsoft\OneDrive\settings\Business1\{guid}.ini or other files in that folder? Post back here if you have! – Minkus Dec 01 '20 at 20:35
  • yes it was my first tentative see my partial answer – Thierry Dalon Dec 04 '20 at 21:11
1

I am also looking at a solution for getting the SharePoint url to a file in OneDrive Synced File Explorer. I have noticed you get under C:\Users%username%\AppData\Local\Microsoft\OneDrive\settings\Business1{GUID}.ini a mapping between your OneDrive synced folder and the SharePoint GUID Example:

libraryScope = 1 630c2a866d9c458b81060eff107887ed+1 5 "GUIDEs" "Documents" 4 "https://mytenant.sharepoint.com/teams/team_10000035" "8d4b558f-7b2e-40ba-ad1f-e04d79e6265a" e0266a43caf347238f684bab486f4e51 e0d25dcb1a014f5f86d787984f6327c2 4f86b3e3e54e42e0bb0f7a58eadf0335 0 "" 0 4cde5c00-3fe3-4162-b831-d8ef440e1593 libraryFolder = 0 1 8bbfe07dfeff41cea7ab5da4a554592a+1 1558084235 "D:\DSUsers\uid41890\TenantName\GUIDEs - General" 2 "General" bd0c1b7c-2a1f-4492-8b1b-8e152c9e0c26

You also have this mapping in the registry Computer\HKEY_CURRENT_USER\Software\Microsoft\OneDrive\Accounts\Business1\ScopeIdToMountPointPathCache

From the GUID you could get the path using SPWeb.GetFile(Guid)

If you can make a standalone function given a local OneDrive file path that returns the SharePoint url, I would greatly appreciate you share your solution here.

Thierry Dalon
  • 779
  • 5
  • 21
  • Hi, what you want is possible but unfortunately way more complicated than you might expect. To solve all different types of synchronization it is not enough to look at the *Business1\{GUID}.ini* file or the registry. A complete solution exists [here](https://stackoverflow.com/a/74165973/12287457) but unfortunately only written in VBA. If you really need a solution, you may be able to port it to C#, or maybe I'll do it at some point in the future if you want. – GWD Jan 26 '23 at 11:19
  • Thanks @GWD. I was looking for a solution to be called in an AutoHotkey script. I had a look to the VBA solution. It looks like it is reading some .dat files. It would be nice if the implementation would be explained a bit. Maybe it is easily portable to AHK but I would need to reverse engineer the solution from the code. – Thierry Dalon Jan 27 '23 at 10:55
  • 1
    Hi @ThierryDalon, yes it is reading many files including some .dat files. I have now uploaded the solution with some more comments and explanations as [a gist here](https://gist.github.com/guwidoe/6f0cbcd22850a360c623f235edd2dce2). I doubt that it is easily portable to AHK because the solution uses many VBA inbuilt functions and contains *a lot* of logic. – GWD Jan 27 '23 at 13:12
  • 1
    I have an idea for a much simpler solution if performance is not a big issue for you. You could use AHK to do the following: Create a new Excel file in the folder you want to find the Url of. Open it and make sure it's actively syncing with OneDrive. Add the formula `=LEFT(CELL("filename";A1);FIND("[";CELL("filename";A1))-1)` to any cell, it will return the url you are looking for (if the file is actively synced by OneDrive). Somehow get the result from the excel sheet and delete the excel file again. If AHK can do all these steps that would be much much simpler but of course also much slower. – GWD Jan 27 '23 at 13:22
  • I like the idea with the Excel formula (pretty clever). It shall be feasible in AutoHotkey. The performance is not a big deal since I would only update the mapping file on manual trigger if the sync locations are changed (which does not happen that much) Thanks for your gist as well. – Thierry Dalon Jan 27 '23 at 14:17
  • I have implemented such a function in AutoHotkey: see https://tdalon.blogspot.com/2023/02/sharepoint-sync-get-url.html It caches the mapping sync locations to SharePoint urls in a text file. I've gone the way with these temporary Excel file because using the VBA module was taking quite long. Thanks @GWD for your help. – Thierry Dalon Feb 22 '23 at 09:50
0

I have implemented a solution in AutoHotkey see documentation here It is based on @GWD idea (see comments below) to generate a temporary excel file with the formula CELL("filename") at each sync locations to extract this mapping information to a text file that is then later parsed to do the reverse mapping from local file to SharePoint url.

Here is the code part extracted from the linked post:

SharePoint_UpdateSync(){
; SharePoint_UpdateSync()
; Update SPSync.ini file creating temporary Excel files in each sync directory

sIniFile := SharePoint_GetSyncIniFile()
FileRead, IniContent, %sIniFile%

oExcel := ComObjCreate("Excel.Application") 
oExcel.Visible := False ; DBG
oExcel.DisplayAlerts := false

Loop, Reg, HKEY_CURRENT_USER\Software\SyncEngines\Providers\OneDrive, K
{
    
    RegRead MountPoint, HKEY_CURRENT_USER\Software\SyncEngines\Providers\OneDrive\%A_LoopRegName%, MountPoint
    MountPoint := StrReplace(MountPoint,"\\","\")

    ; Exclude Personal OneDrive
    If InStr(MountPoint,"\OneDrive -")
        Continue

    FoundPos := InStr(MountPoint, "\" , , -1)
    sOneDriveDir = SubStr(MountPoint,1,FoundPos-1)  

    If InStr(IniContent,MountPoint . A_Tab) ; already mapped
        Continue
    xlFile := MountPoint . "\SPsync.xlsx"
    ; Create Excel file under MountPoint
    
    xlWorkbook := oExcel.Workbooks.Add ;add a new workbook
    oSheet := oExcel.ActiveSheet
    oSheet.Range("A1").Formula := "=CELL(""filename"")" ; escape quotes
oSheet.Range("A1").Dirty
    ; Save Workbook https://learn.microsoft.com/en-us/office/vba/api/excel.workbook.saveas
    Try ; sometimes return error "Enable to get the SaveAs property" but still work
        xlWorkbook.SaveAs(xlFile,xlWorkbookDefault := 51)
    
    ; Calculate Formula
    oSheet.Calculate 

    ; Get value
    UrlNamespace := oSheet.Range("A1").Value 
    ;Find last /
    FoundPos:=InStr(UrlNamespace,"/",,0)
    UrlNamespace := SubStr(UrlNamespace,1,FoundPos-1)
    
    xlWorkbook.Close(False)
    
    ; Delete temp file
    FileDelete, %xlFile%

    If (UrlNamespace = "") {
        sTrayTip := "Error in getting url path for synced location '" . MountPoint . "'!"
        TrayTip Check Mapping in SPsync.ini! , %sTrayTip%,,0x2
        Run "%sIniFile%"
    }
    
    FileAppend, %MountPoint%%A_Tab%%UrlNamespace%`n, %sIniFile%

} ; end Loop
Thierry Dalon
  • 779
  • 5
  • 21
  • 1
    Hi, I'm glad it works! Maybe the `oSheet.Recalculate` didn't work because you need to use [`Range.Dirty`](https://learn.microsoft.com/en-us/office/vba/api/excel.range.dirty) first? Also, the link in this answer doesn't work at the moment (the one in your comment does). Maybe it would be beneficial to just copy some of your blog post into this answer anyways? Nice work anyways! – GWD Feb 22 '23 at 11:13
  • 1
    Thanks. The Dirty trick works so the file can be updated without excel popping out :-) – Thierry Dalon Mar 06 '23 at 09:53