Password Protected Sharepoint file only opens as Read-Only on Excel 365 for Mac

Question

This is a very odd situation that we have been struggling with for months so any advice would be greatly appreciated.  

We have one particular password protected Excel file, saved on Sharepoint, that is giving us fits.  

For all of our Mac users, when opening the file from the Sharepoint browser interface, using Safari or Chrome (no we have not tried other browsers),

1. Excel responds correctly and forces the file to opened in the Excel for Mac app
2. However, the file will only open in Read-Only mode and
3. the "CHECK OUT REQUIRED:" yellow banner will NOT appear.  

That is, except for one Mac user who does get the yellow banner and is able to CheckOut the file normally.  

BTW that one user is the most recent user to gain access to the file.  

Many of those same Mac users also have Parallels/Win10Pro installed on their machines.  When they try accessing the file from their Windows-side using Chrome, Sharepoint and Excel for Windows handle the file perfectly

1. forcing them to open the password protected file in the local Excel for Windows app,
2. opening the file in Read/Write mode and
3. having the yellow banner appear asking them to CheckOut the document.  

Therefore.  

This does not appear to be a user access issue as all of our Mac users can open the file from a Windows interface

This does not appear to be a Excel for Mac issue because the file does open properly on one Mac

We all run the latest Microsoft365 products.   

Can anyone shed any light?  Of course, I would be more than happy to answer any questions.  

But please, I am far from a Sharepoint expert, so please dumb down your questions/answers as much as possible.  

Thank you in advance for any advice.

Answer 1

Dear Ed,

Welcome to the forum here.

From your description, it seems that you set the Check-Out setting in the site library in SharePoint Online. If yes, it seems that all users using Mac don't see the Check-Out messages and only one user sees the message on his Mac. Could you confirm it?

If yes, are the affected users able to edit and save the file? Do they encounter some error messages when saving the file?

Based on my test, the issue may happen when the file is checked out by a user and isn't checked in by him. I'd like you to contact the administrator>let him install SharePoint Online Management Shell and SharePoint Online PnP and let him run the following example script to check whether there are some Mac or Windows users checking out and not checking in the file.

Connect-PnPOnline -Url https://xxx.sharepoint.com/sites/xxx -Credentials (Get-Credential)

$lists = Get-PnPList

foreach ($list in $lists | where {$_.Title -eq "documentlibraryname"})

{

    $Items = Get-PnPListItem -List $list

    foreach ($Item in $Items) 

    {

        if ($Item.FieldValues.CheckoutUser -ne $null)

        {

            write-host $list.Title -ForegroundColor Green

            write-host ($Item.FieldValues).FileLeafRef -ForegroundColor Yellow

            write-host $Item.FieldValues.CheckoutUser.Email -ForegroundColor Red

        }

    }

}

If yes, let the users check in the file, let the users sign out of the browser, clear the browser cache, close all Office for Mac applications and back up all files, perform the following steps, sign into SharePoint Online with their accounts and then do a test to see the result.

a. Open Spotlight by selecting the magnifying glass at the top right of your desktop.

b. Search for and open Keychain.

c. In the search field, enter adal.

d. Select all items whose type is MicrosoftOffice15_2_Data:ADAL:<GUID>, and then press Delete.

e. In the search field, enter office.

f. Select the items that are named Microsoft Office Identities Cache 2 and Microsoft Office Identities Settings 2, and then press Delete.（Please note that the two Office credential caches must be hidden. To unhide the 2 caches, click on "show visible items" in the "Keychain" menu top bar (keychain -> view -> "show visible items") Once the hidden items are visible, search for "Office" and you will see below 2 items listed.）(In some situations, they are Microsoft Office Identities Cache 3 and Microsoft Office Identities Settings 3)

g. Quit Keychain Access.

h. Navigate to <YourUserName>Library > Group Containers.

i. Find the folders below and move them to Trash.

UBF8T346G9.ms

UBF8T346G9.Office

UBF8T346G9.OfficeOsfWebHost

Warning: Outlook data will be removed when you move the three folders listed in this step to Trash. You should back up these folders before you delete them.

j. Restart the Macs.

k. Open the Excel for Mac applications and re-log into them.

Best Regards,

Cliff

Answer 2

Thank you for the long and detailed reply.  I really appreciate it. 

Unfortunately, after proceeding through each of your steps, the password protected Excel file, stored on Sharepoint, exhibits the same behavior.  On my Mac, the file will open in Read-Only mode in Excel for Mac and the yellow banner, asking me to check out the file, does not appear.  

I tested on my WindowsVM and the file opened as expected with the banner.  

There are only 7 of us who use this file.  Some of the users can no longer open the file because they do not have WindowsVM's.  The rest of us are extremely careful on the Checkout/Checkin procedures.  Therefore, I do not feel that the lack of reliable Checkins would be in issue.  

However, if you would still like me to ask our Sharepoint administrator to install that software and run your script, I will be happy to.  That may take a few days.  

Other questions:

1. in your Step f, I had both Cache 2 and Cache 3 files.  I deleted both pairs of files for a total of 4 deletes
2. in your Step I, you warned that Outlook data would be lost and therefore I made copies of all 3 files on my desktop.  After the reboot and re-opening Outlook, I see no missing data.  The only shortcoming was that I had to relog into Outlook.  Am I safe to delete those copies?

Thank you again for your feedback and suggestions.

Answer 3

Dear Ed,

Thanks for your updates.

It seems that you have performed all steps to clear the Office for Mac applications' cache and credentials and the issue persists in your environment.

Yes, if your Outlook for Mac's data is fine after performing the steps, you can remove the copies.

If it is convenient, I 'd like you to contact the administrator and run the script to see if the file is checked out on some devices and tell us the outcome as I'd like to make sure that the file isn't checked out in your environments.

Welcome to share any updates when you have time if you need further help on this issue.

Appreciate your time and effort.

Cliff

Answer 4

Sorry for the delayed reply

Our admin says:

I had to get “creative” to get any output from the script. The end result is at this time, the file is not checked out.

I could only get output without errors when I changed the following line:

        if ($Item.FieldValues.CheckoutUser -ne $null)

to

        if ($Item.FieldValues.CheckoutUser -eq $null)

Which resulted in

Shared Documents

Watchword.xlsx

Answer 5

Dear Ed,

Thanks for your effort and time to run the script and make sure that the file isn't checked out by any Mac users in your environment.

If it is convenient, could you do a test in a new site library in another site with some Mac users to see the result as I'd like to confirm whether the issue only happens in the specific site library in the specific site?

Could you confirm the version number of the Excel for Mac application you are using for our reference?

Best Regards,

Cliff