How to open Sharepoint cloud Office files directly via Finder (OneDrive for Business/Microsoft Office 365)

16 April 2018, 20:00

Update May 2018: It appears that the Office 2016 apps on Mac now automatically do what’s described below, so this fix is no longer necessary. If you open a file in your OneDrive folder via Finder, the relevant Office app (Word, Excel, PowerPoint) will now automatically activate the AutoSave switch, and you can share/co-edit with others without a second thought. However, I’ll leave this post here for those for whom this feature doesn’t work correctly — or in case Microsoft ever remove or switch-around this feature.

=-=-=-=-=-=-=-=-=-=-=-=-=-=

Recent releases of Microsoft Office 2016 for Mac have been integrated pretty tightly with the cloud. Unfortunately, if you access your Microsoft Office files using Finder – as most of us do – there’s no way to open cloud files directly. You merely open a local version of the file. Features like live editing with colleagues aren’t available.

Until now. Read on to learn how.

The problem
For an Office 365 business user working within a larger business, “the cloud” equates to Sharepoint cloud integration – or OneDrive for Business, as it’s also confusingly known.

There’s a switch at the top left of Word, Excel and PowerPoint that activates autosaving to the cloud, and a Share button at the top right that means you can quickly provide a link so colleagues can access the file too. A new file open/save dialog box in Office apps offers “Online Locations”, and so features Sharepoint/OneDrive for Business integration.

This is great if you only ever open Sharepoint files using the Office apps’ Open File dialog box. But Microsoft’s done almost zilch to integrate Sharepoint access into the rest of macOS and Finder. The OneDrive app will sync your Sharepoint/OneDrive for Business files to your Mac’s hard disk, but if you double-click to open any of them via Finder then you’re only opening the copy on your hard disk. You are NOT opening the Sharepoint copy in the cloud. This means the autosave and share features don’t work.

It can be very confusing. If you click the Share button you’re immediately prompted to “move” or “copy” the file to the Sharepoint cloud to make it available to colleagues. But surely it’s actually already there…?! However, unless you copy or move the file you aren’t able to share it.

Wouldn’t it be better just to be able to open the Sharepoint cloud file within the Office docs, simply by double-clicking it within the OneDrive listing of macOS’ built-in Finder?

Here’s how.

This is a bit of a hack using AppleScript, so comes without warranty or guarantee. But it works for me. (The search and replace subroutine in the code comes from the Mac OS X Automation site, with thanks.)

It’s worth briefly mentioning (so that I don’t get hundreds of comments below) that it’s sometimes possible to setup a webdav connection to Sharepoint so you can access the files via Finder. However, many institutions have implemented two-factor authentication, which makes that impossible. And some companies just turn off this feature anyway.

What we’re going to do
You’re going to want to setup this trick if you share a lot of files with colleagues via the cloud for simultaneous editing, or to avoid sending copies via email (and receiving hundreds of copies back). It’s means you can actually use Microsoft Office in the cloud, as Microsoft would like you to.

And to do this, I’m going to tell you how to create your own DIY handler app to open Sharepoint cloud files.

In a split second this little app checks to see if the file you’ve just double-clicked in Finder is in your OneDrive folder. If it ISN’T then it’s opened as usual within the Office app, just as it would be normally. If the file IS within OneDrive then the little app instructs the Office app to open the Sharepoint cloud version instead of the file on your hard disk. However, any edits you make to the cloud file will still be near-instantly synced to your hard disk thanks to the OneDrive for Business app.

Notably, for all this to work you’ll need to be logged into Sharepoint/OneDrive for Business within each of your Office apps (that is, Word, Excel and PowerPoint). This can be done using the File > Open dialog box, clicking the Online Locations dialog box, and then clicking Add a Place. Then click the OneDrive for Business option, and provide your login details.

Gathering information
You’ll need to discover two things before starting. The first is the file path to your OneDrive folder. This will probably be something like:

/Users/username/OneDrive/

… And yes we need to include that trailing slash at the end when writing this down, and when inserting it into the code later on. “username” will your your Mac username. If you work for a company the file path might be something like /Users/username/OneDrive – Corporation PLC/.

One trick is to open a Terminal window, and drag and drop a file from the OneDrive folder on top of it. You’ll then see the path you need to note. Just remove the filename from the end.

Next, you need to know your personal Sharepoint path that matches the OneDrive folder. In other words, if you view whatever is at the Sharepoint path you’ll see exactly what you see when you view the OneDrive folder on your computer.

One way to discover this is to visit the Sharepoint folder in the File Open dialog box of any of the Office apps, then right-click any file and select Share > Copy Path. Then paste within a word processor. You should see the path listed there, and again you can just trim away the filename at the end. Alternatively, ask your IT person if they can help.

Whatever you end-up with will look something like the following:

https://example-my.sharepoint.com/personal/johnsmith/Documents/

… or if your organisation hosts its own Sharepoint server it could look something like this:

https://sharepoint.example.com/personal/johnsmith/Documents/

Creating the handler apps
Here are the steps required to create the handler apps. This need only be done once, obviously.

Open Script Editor, which you’ll find in the Utilities folder of the Applications list in Finder.

Click New Document at the bottom left of the file open dialog box.

Click File > New two times, so that you now have three empty Script Editor windows.

Save each of the new files you’ve created in your Documents folder, or another safe location where you know they won’t be deleted. Call the first one Word Sharepoint Handler, the second Excel Sharepoint Handler, and the third PowerPoint Sharepoint Handler. When saving each, select “Application” from the File Format dropdown list.

In the Word Sharepoint Handler window, paste in the following code – you can select all of this at once, and just paste it right into Script Editor. Once you’ve pasted, click the little hammer icon on the toolbar to check the code – if it instantly color-codes without an error then it’s fine:

on open theDroppedItems
set originalFilename to POSIX path of theDroppedItems
if originalFilename contains "OneDrive" then
set newFilename to replace_chars(originalFilename, "ONEDRIVE-PATH", "SHAREPOINT-PATH")
do shell script "open -a 'Microsoft Word' " & quoted form of newFilename
display dialog "Fetching from Sharepoint and opening... " & newFilename buttons "I'm waiting..." giving up after 3
else
do shell script "open -a 'Microsoft Word' " & quoted form of originalFilename
end if
end open
on replace_chars(this_text, search_string, replacement_string)
set AppleScript's text item delimiters to the search_string
set the item_list to every text item of this_text
set AppleScript's text item delimiters to the replacement_string
set this_text to the item_list as string
set AppleScript's text item delimiters to ""
return this_text
end replace_chars

In the code above, replace ONEDRIVE-PATH with the OneDrive path you discovered earlier (e.g. something like /Users/johnsmith/OneDrive/), and replace SHAREPOINT-PATH with the Sharepoint path you discovered (e.g. something like https://example-my.sharepoint.com/personal/johnsmith/Documents/). Don’t delete any quote marks from the code!

Repeat the above step for the Excel handler window, pasting in the following and entering your OneDrive and Sharepoint paths:

on open theDroppedItems
set originalFilename to POSIX path of theDroppedItems
if originalFilename contains "OneDrive" then
set newFilename to replace_chars(originalFilename, "ONEDRIVE-PATH", "SHAREPOINT-PATH")
do shell script "open -a 'Microsoft Excel' " & quoted form of newFilename
display dialog "Fetching from Sharepoint and opening... " & newFilename buttons "I'm waiting..." giving up after 3
else
do shell script "open -a 'Microsoft Excel' " & quoted form of originalFilename
end if
end open
on replace_chars(this_text, search_string, replacement_string)
set AppleScript's text item delimiters to the search_string
set the item_list to every text item of this_text
set AppleScript's text item delimiters to the replacement_string
set this_text to the item_list as string
set AppleScript's text item delimiters to ""
return this_text
end replace_chars

In the PowerPoint handler window, paste in the following replacing the OneDrive and SharePoint paths:

on open theDroppedItems
set originalFilename to POSIX path of theDroppedItems
if originalFilename contains "OneDrive" then
set newFilename to replace_chars(originalFilename, "ONEDRIVE-PATH", "SHAREPOINT-PATH")
do shell script "open -a 'Microsoft PowerPoint' " & quoted form of newFilename
display dialog "Fetching from Sharepoint and opening... " & newFilename buttons "I'm waiting..." giving up after 3
else
do shell script "open -a 'Microsoft PowerPoint' " & quoted form of originalFilename
end if
end open
on replace_chars(this_text, search_string, replacement_string)
set AppleScript's text item delimiters to the search_string
set the item_list to every text item of this_text
set AppleScript's text item delimiters to the replacement_string
set this_text to the item_list as string
set AppleScript's text item delimiters to ""
return this_text
end replace_chars

Save all the files, and close Script Editor.

macOS’ security feature might block you using your new apps. Therefore, you now need to tell the Gatekeeper security feature that the apps are OK to open in future. This only needs to be done once. Authorise the first of your apps by right-clicking it in Finder, then selecting Open, and selecting Open in the dialog box that appears. Nothing will appear to happen when you do this! That’s fine. But repeat this step for the other two apps.
Next, find a Microsoft Word file anywhere on your hard disk (that is a .docx file), and right-click it. Select Get Info from the menu that appears.

In the dialog box that appears, click the dropdown list beneath Open With, and then click Other at the bottom of the list that appears. Then navigate to your new Word Sharepoint Handler app in the Documents folder (or wherever you saved it). Select it. Back in the Get Info dialog box, click the Change All button. Close the Get Info dialog box.

Repeat the above step for Excel files – that is to say, find an Excel file, right-click it, and switch over the Open With association to the Excel Sharepoint Handler app you created. Repeat again for a PowerPoint file.

Your handler apps should now work. Test them by opening a file within your OneDrive folder. You should find you open the Sharepoint cloud version instead – it’ll probably take a few seconds for the file to be downloaded, and you’ll notice the Autosave switch at the top left of the Office app window is activated.

Try opening a file outside the OneDrive folder. You should find it opens normally, as it did before you setup the handler apps.

If you want to undo any of the above, repeat the steps to change the file associations and then select Microsoft Word, Excel and PowerPoint for each of the Office filetypes. Then simply delete the handler apps you created.

There are some slight irritations with this method. One of them is that Office files lose their Word, Excel or PowerPoint icons. This can be fixed, but I haven’t detailed that here. The other is that opening a file in Sharepoint takes a few seconds even for the smallest file, because it’s cached from the cloud. For larger files such as big presentations it can take a long time. This is partially why I added the display of a dialog box to the code – I wanted the user to know that something had been kicked-off. You just have to know to be patient if nothing appears to be happening.

Name	Remember
Email
Website
Message	Textile help

How to open Sharepoint cloud Office files directly via Finder (OneDrive for Business/Microsoft Office 365)

Leave a comment...