Excel OneDrive file not Hookable [workarounds]

Hi, I am trying to link Excel files that reside within Onedrive but Hook does not allow me to copy the link if I have the Excel file open and am editing it. If I find the file within Finder, I can link files using Hook. Is that a bug?

I have the same issue. Is there any way to link to OneDrive / Sharepoint files from Hook?

Welcome to the Hook Productivity Forum @tomkerswill, and thank you and to @pdoak for raising this issue. This came up before on the forum. Microsoft app’s AppleScript API for getting the path of a file return different results when the files reside in a local OneDrive folder vs. elsewhere on the actual Mac filesystem. In the former case they are invalid as far as macOS is concerned. So Hook cannot currently handle them.

That is to say that Hook does not currently support OneDrive.

There are inherent complexities because the same locally synced OneDrive resource has two different identities, one in the MS Drive space, one in macOS. If you move a file outside of OneDrive, and move it back, I don’t think OneDrive would necessarily treat them as the same. If you unsync OneDrive from your Mac, then move the file asynchronously (on the web), and then re-sync OneDrive onto your Mac, macOS would have no way of knowing ‘the file’ is the same.

Other users worked around it by moving their files out of OneDrive.

Having said that, I suspect we will be able to work-around some of these issues. (Apart from it being very relevant to Hook’s Microsoft users, it’s an intrinsically interesting problem.) If/when we add additional support, we’ll have to add some caveats along the lines above.

For now your, you can access those files via the Finder and Hook them from there. You can also use Hook to copy links to other resources and paste them in the documents File > Properties > comments.

@tomkerswill, @pdoak

Oh, I meant to check one work around, which is to disable OneDrive, which actually seems to work! If you quit OneDrive from the OneDrive menu bar icon, and reopen the files, Hook works as expected. That’s your best bet.

I’ve moved this into Discussion because Hook does yet officially claim to support OneDrive. As noted above, first step is to specify the correct behavior, and publicly state that we align with it. The Discussion topic is useful for helping define that.

We’ve published the first version of Using Hook in OneDrive. That’s simpler than my text above. Also, it points to some interesting robustness. We’ll update that document as we move forward.

Thanks, Luc!

Yes; I’ve noticed the same thing. Disabling OneDrive - or even just disabling auto save, works fine as they are just normal files. You can do all the normal Mac things, like dragging from the window Title. You can only do this before opening the file; otherwise, once opened, it seems to not be able to find the original file, and will ask you for a location to save to.

The problem is that in my organisation, people frequently collaborate on files and so most of the time OneDrive needs to be on; and so quitting it just to make a link doesn’t make work in terms of the time taken.

I like the idea of using the Finder file and linking to that file. The problem is (and this is not at all a Hook problem) I don’t think that Word can even get you back to the underlying file once it’s opened.

If OneDrive is on, then I think Word just uses the file on the disk to figure out the online link and from then on uses that and really has no link back to your own drive… I think any edits possibly even go directly to the server and synced back from server onto Finder versus OneDrive.

One thing that occurred to me is that Word has a “share” link that links back to the online version of the file. It’s perhaps theoretically possible to use this link and figure out what the path to the file is. Not sure if that’s feasible to look at.

My workaround (similar to your guide) is to use DevonThink for now.

Tom

1 Like

Hi!

I think I’ve got a workaround for this. I’ve tried this with Word files.

Word actually has really good Applescript support - I hadn’t realised! And so you can use Applescript to get the OneDrive location of the file.

I’ve written a script to get the URL of the active document and then I URL-encode it. I can then take that URL, paste it into a browser like Chrome, and MS Word will open up the doc directly in the app.

This is pretty much perfect. I then take that same URL and hook it. That unfortunately does not work… Hook seems to encode the URL. So I may need some help with that bit! Here’s what I have so far:

use framework "Foundation"

tell application "Microsoft Word"
	set activeDoc to active document
	set activeDocName to name of activeDoc
	set activeDocPath to path of activeDoc
	set fullURL to posix full name of activeDoc
end tell

set fullURLEncoded to urlEncode(fullURL)

set appURL to "ms-word:ofe|u|" & fullURLEncoded

on urlEncode(input)
	tell current application's NSString to set rawUrl to stringWithString_(input)
	set theEncodedURL to rawUrl's stringByAddingPercentEscapesUsingEncoding:4 -- 4 is NSUTF8StringEncoding
	return theEncodedURL as Unicode text
end urlEncode

Are you Hooking to the URL on the clipboard or are you invoking Hook on the document using your script?

For a script that will return a hook://file/ or a MS specific URL see:

Using Hook in OneDrive – Hook

Paste that in Hook’s Get Address script for Microsoft Word. If the file is in OneDrive, OneDrive sync is on, and you invoke Hook in a MS Office app, you should get a MS specific URL.

Of course MS specific URLs are not useful if you move the file out of OneDrive (say into a different Finder folder ).

If you’d like to try it and let us know whether it works for you that would be grand.

Updated 2021-05-29 to reference Using Hook in OneDrive – Hook rather than including the script here.

Hi Luc

Should that script be placed in the Microsoft Word, Get Address script location. When I did this, Hook still says no linkable item found.

very sorry, that had a copy/paste error.

I’ve updated my prior comment to reference Using Hook in OneDrive – Hook so that we only have the script in one place (until it’s in Hook itself, and Hook’s integration server).

Thanks. That worked.

I amended the script to work with Excel as well. See below:

tell application “Microsoft Excel”
set activeDoc to active workbook
set activeDocName to name of active workbook
set activeDocPath to path of active workbook
set fullURL to full name of active workbook
if fullURL does not start with “http” then
return “file://” & POSIX path of fullURL
end if
end tell

set appURL to “ms-excel:ofe|u|” & fullURL

1 Like

Luc, I would like to amend your script to work with Powerpoint as well. What should the final line below be changed to for Powerpoint.

set appURL to “ms-excel:ofe|u|” & fullURL

We’ve updated Using Hook in Microsoft OneDrive with Microsoft Office Apps – Hook with a specific script you can use for Powerpoint.

That works. Thank you.

1 Like