Robust Sharepoint and onedrive mount location from registry

[jreyenga]

OS (e.g. Windows 10 or macOS Sierra)

Win10

Versions of xlwings, Excel and Python (e.g. 0.11.8, Office 365, Python 3.7)

xlwings 0.26, python 3.8.8

Describe your issue (incl. Traceback!)

I have found that at least for windows the current user registry contains all the URLs and mount points for both onedrive and sharepoint. Sample code below successfully takes OneDrive and Sharepoint URL path, scans multiple mounts in registry, and returns the local path. This should allow files to live on sharepoint with arbitrary mounts and move around without any custom environment variables...

I haven't hacked utils.py yet but the logic below 'should' work... at least for windows. It's not the cleanest code in the world, but the info is there to find the local path for sharepoint files.

Thoughts?

import winreg
import os


def URL2Local(url):
    REG_Path = r"SOFTWARE\SyncEngines\Providers\OneDrive"
    MountName = 'MountPoint'
    URLName = 'URLNamespace'

    # iterate through base OneDrive to fine all sub-keys
    reg_key = winreg.OpenKey(winreg.HKEY_CURRENT_USER, REG_Path, 0, winreg.KEY_READ)
    i = 0
    subkeys = []
    while True:
        try:
            subkey = winreg.EnumKey(reg_key, i)
            subkeys.append(subkey)
            i = i + 1
        except WindowsError:
            break
    winreg.CloseKey(reg_key)

    # iterate through subkeys to get a list of all mounts and urls
    URLs = []
    Mounts = []
    for subkey in subkeys:
        subREG_Path = os.path.join(REG_Path, subkey)
        reg_key = winreg.OpenKey(winreg.HKEY_CURRENT_USER, subREG_Path, 0, winreg.KEY_READ)
        value, regtype = winreg.QueryValueEx(reg_key, MountName)
        Mounts.append(value)
        value, regtype = winreg.QueryValueEx(reg_key, URLName)
        URLs.append(value)
        winreg.CloseKey(reg_key)

    # iterate through keys to find match with mount
    # Could technically combine with the previous iteration
    LocalPath = ''
    for idx, location in enumerate(URLs):
        if location in url:
            LocalPath = Mounts[idx]
            LocalPath = os.path.join(LocalPath, url[len(location):])
            LocalPath = os.path.normcase(LocalPath)
        else:
            pass

    return LocalPath

[fzumstein]

Oh, that's awesome! I wasn't aware that this is available in the registry! thanks, that helps a lot! From what I would guess, the current hacky implementation is still required on macOS where there's no registry. Also, if I am not completely mistaken, I think you can mount your sharepoint drives directly as network drive (?) in which case the info woud be missing in the registry. although I am not sure if that's still commonly done or if syncing via OneDrive app is the usual way these days.

[jreyenga]

FYI the below code seems to be working in utils.py in our windows environment, but over the next couple days will try to get a few more users to test it out to see if it breaks in the wild here. Only change to the test case was making the if conditional case insensitive.

I didn't drop the optional inputs, but they aren't being used.

Good luck on Mac ;)

def fullname_url_to_local_path(
    url,
    sheet_onedrive_consumer_config=None,
    sheet_onedrive_commercial_config=None,
    sheet_sharepoint_config=None,
):
    """
    When AutoSave is enabled in Excel with either OneDrive or SharePoint, VBA/COM's
    Workbook.FullName turns into a URL without any possibilities to get the local file
    path. While OneDrive and OneDrive for Business make it easy enough to derive the
    local path from the URL, SharePoint allows to define the "Site name" and "Site
    address" independently from each other with the former ending up in the local folder
    path and the latter in the FullName URL. Adding to the complexity: (1) When the site
    name contains spaces, they will be stripped out from the URL and (2) you can sync a
    subfolder directly (this, at least, works when you have a single folder at the
    SharePoint's Document root), which results in skipping a folder level locally when
    compared to the online/URL version. And (3) the OneDriveCommercial env var sometimes
    seems to actually point to the local SharePoint folder.

    Parameters
    ----------
    url : str
        URL as returned by VBA's FullName

    sheet_onedrive_consumer_config : str
        Optional Path to the local OneDrive (Personal) as defined in the Workbook's
        config sheet

    sheet_onedrive_commercial_config : str
        Optional Path to the local OneDrive for Business as defined in the Workbook's
        config sheet

    sheet_sharepoint_config : str
        Optional Path to the local SharePoint drive as defined in the Workbook's config
        sheet
    """
    REG_Path = r"SOFTWARE\SyncEngines\Providers\OneDrive"
    MountName = 'MountPoint'
    URLName = 'URLNamespace'

    # iterate through base OneDrive to fine all sub-keys
    reg_key = winreg.OpenKey(winreg.HKEY_CURRENT_USER, REG_Path, 0, winreg.KEY_READ)
    i = 0
    subkeys = []
    while True:
        try:
            subkey = winreg.EnumKey(reg_key, i)
            subkeys.append(subkey)
            i = i + 1
        except WindowsError:
            break
    winreg.CloseKey(reg_key)

    # iterate through subkeys to get a list of all mounts and urls
    URLs = []
    Mounts = []
    for subkey in subkeys:
        subREG_Path = os.path.join(REG_Path, subkey)
        reg_key = winreg.OpenKey(winreg.HKEY_CURRENT_USER, subREG_Path, 0, winreg.KEY_READ)
        value, regtype = winreg.QueryValueEx(reg_key, MountName)
        Mounts.append(value)
        value, regtype = winreg.QueryValueEx(reg_key, URLName)
        URLs.append(value)
        winreg.CloseKey(reg_key)

    # iterate through keys to find match with mount
    # Could technically combine with the previous iteration
    LocalPath = ''
    for idx, location in enumerate(URLs):
        if location.lower() in url.lower():
            LocalPath = Mounts[idx]
            LocalPath = os.path.join(LocalPath, url[len(location):])
            LocalPath = os.path.normcase(LocalPath)
        else:
            pass

    return LocalPath

[fzumstein]

thanks!

[fzumstein]

@jreyenga I've implemented this in #1871. If you like, you could try it out by installing this build:
xlwings-0.0.0.dev79382.tar.gz.

It will certainly improve things (hopefully by a lot) but it's still not perfect. For the sake of documenting, this is a combination on my system where it wouldn't work:

# ThisWorkbook.FullName
https://zoomeranalytics.sharepoint.com/sites/ZoomerAnalytics/Shared Documents/General/describe.xlsm
# Local FilePath
C:\Users\felix\Zoomer Analytics GmbH\Zoomer Analytics - General\describe.xlsm
# Registry URL
https://zoomeranalytics.sharepoint.com/sites/ZoomerAnalytics/Shared Documents/
# Registry MountPoint
C:\\Users\\felix\\Zoomer Analytics GmbH\\Zoomer Analytics - General

The current logic leads to

C:\Users\felix\Zoomer Analytics GmbH\Zoomer Analytics - General\general\describe.xlsm

instead of

C:\Users\felix\Zoomer Analytics GmbH\Zoomer Analytics - General\describe.xlsm