SharePoint config: Prefer preconfigured paths to searching registry paths

[robertschulze]

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

Windows 10

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

Python: 3.10.13
xlwings: 0.30.13
Excel: Microsoft® Excel® for Microsoft 365 MSO (Version 2302 Build 16.0.16130.20848) 32-bit

Describe your issue (incl. Traceback!)

This is in reference to issue #1829 / pull request #1871. I have multiple shared (sub)folders from mutiple channels of a MS Business Team (i.e. hosted as multiple subfolders of a SharePoint). E.g. these two:

[HKEY_CURRENT_USER\SOFTWARE\SyncEngines\Providers\OneDrive\63d95dea23614820b35cee35dd73d8c6+1]
"MountPoint"="C:\\Users\\Robert\\Abc\\FO Migration - Financial Risk - 02_TEST"
"UrlNamespace"="https://abc.sharepoint.com/sites/FOMigration-FinancialRisk/Shared Documents/"

[HKEY_CURRENT_USER\SOFTWARE\SyncEngines\Providers\OneDrive\00d8a9b4cc2549fd8674fb3ac5d0ad38+1]
"MountPoint"="C:\\Users\\Robert\\Abc\\FO Migration - Financial Risk - 03_TEST"
"UrlNamespace"="https://abc.sharepoint.com/sites/FOMigration-FinancialRisk/Shared Documents/"

Since get_url_to_mount() in utils.py is collecting the mount points by urls, the entries are overwritten and only the last one prevails.

Consider now e.g. the file https://abc.sharepoint.com/sites/FOMigration-FinancialRisk/Shared Documents/02_TEST/test.xlsx, locally synched to C:\Users\Robert\Abc\FO Migration - Financial Risk - 02_TEST.

In fullname_url_to_local_path(), the check in line 577 is passed for MountPoint 03_TEST because the check only considers the startswith(url_namespace) even though the file is in 02_TEST. Clearly local_path.is_file(), thus, returns False and, ultimately, the subsequent search_local_sharepoint_path() fails with xlwings.XlwingsError("Couldn't find your SharePoint file locally, see: xlwings.org/error") (line 744).

    # SharePoint Online & On-Premises (Windows registry)
    url_to_mount = get_url_to_mount()
    for url_namespace, mount_point in url_to_mount.items():
        if url.startswith(url_namespace):
            local_path = Path(mount_point) / url[len(url_namespace) :]
            if local_path.is_file():
                return str(local_path)
            else:
                return search_local_sharepoint_path(
                    url, mount_point, sharepoint_config, sharepoint_config_name
                )
    # SharePoint Online & On-Premises (default top level mapping)
    pattern = re.compile(r"https?://[^/]*/sites/([^/]*)/([^/]*)/(.*)")
    match = pattern.match(url)
    # We're trying to derive the SharePoint root path
    # from the OneDriveCommercial path, if it exists
    root = sharepoint_config or (
        os.getenv("OneDriveCommercial").replace("OneDrive - ", "")
        if os.getenv("OneDriveCommercial")
        else None
    )

Unfortunately this cannot be avoided by creating a xlwings.conf sheet with SHAREPOINT_WIN because the latter is only checked AFTER the above check and, due to the raised error, is never executed. So for me a simple solution was to comment out the entire for loop above, but this is not a general solution.

I would see the following opportunities to fix the error:

1. Store the MountPoint / UrlNamespace pairs in a way that entries are not overwritten and amend the if-clause from startswith() to a more detailed check.
2. Not raise an error in search_local_sharepoint_path() in case there the file cannot be found and/or catch this error in fullname_url_to_local_path
3. Put the check for SHAREPOINT_WIN in xlwings.conf earlier in fullname_url_to_local_path(), such that the loop is never executed.

Include a minimal code sample to reproduce the issue (and attach a sample workbook if required!)

cf. above

[fzumstein]

This topic is an absolute nightmare and only exists because somebody at Microsoft thought it's a good idea to disrespect the official documentation (https://learn.microsoft.com/en-us/office/vba/api/excel.workbook.fullname) which says that FullName returns the path on disk (a stupid SharePoint URL is not a path on disk).

I am pretty sure there's an easier way to handle this more reliably, but I've spent far too much time on this, so I am not sure when I'll have the energy to circle back on this.

See also: #2160, but apparently, it's also not 100% reliable.

The ideas I have for an easier solution:
When running a Python script, we can probably just cache the local path/url mapping and use this for the translation. And for the RunPython VBA integration, it shouldn't really need the fullpath anyway, as the file is already open.

[robertschulze]

Hi Felix,
would you be open for a PR? I could draft up a solution, intuitively preferring proposal 3 above.
Best,
Robert

[fzumstein]

Sure. Also: how are your runnging things? i.e., are you running a python script or calling it via Excel?

[robertschulze]

I am running a Python script.

[robertschulze]

I created pull request #2397
Essentially this checks the preconfigured paths before searching through the mounted folders from the registry

[fzumstein]

closed in #2397