The HYPERLINK function in Google Sheets does not support the file (file://) protocol. This means that any link to a local file that you enter will get rejected as an Invalid URL. If you really must link to local files from a sheet, there are ways to work around that limitation, such as installing a web server to serve local files from your computer over http.
file:// urlsWe’ve found a quick and easy way to do this that should work on any platform, ChromeOS, Windows, Mac etc using the Chrome browser and a handy little app called Web Server for Chrome. I’ll detail this in a minute.
First though, before we get to the workaround. Google Sheets are highly shareable and designed for collaborative work. Every time I try to help someone out on a forum, it makes life so easy that I can just get a link to their sheet! Linking to local files from your sheet will take away a lot of these benefits. If you don’t have all the same local files downloaded, and the web server app installed wherever you open the sheet, the links will not work.
So, that said, here’s how to do it.
HYPERLINK – Supported link types
If you look at the HYPERLINK documentation, you can see that only certain types of link protocol are allowed:
HYPERLINK –
urlparameter
http://https://mailto:aim:ftp://gopher://telnet://news://Only the above are permitted; others are explicitly forbidden. If another protocol is specified, link_label will be displayed in the cell, but will not be hyperlinked.
HYPERLINK – Online help
Manually entering links into cells in a Google Sheets follows the same rules. If you try to enter a file:// URL into a hyperlink cell in sheets it will get rejected.
So the answer to the problem is to make your files available over one of the above supported protocols. If you have experience setting up an FTP server or a web server on your local machine, then do this yourself using your preferred server software like IIS or FileZilla. However, we found a really simple method that anyone can set up in a few minutes.
Using ‘Web Server for Chrome’ to link to local files
Web Server for Chrome is available on the Chrome Web Store. It’s a nifty app that will turn your Chrome browser into a web server. It will run anywhere Chrome runs, so you can use it on Windows, Mac, ChromeOS, maybe even your phone or tablet (we’ve not tried that).
Whilst I’m sure the icon could do with some work, this little app has lots of 5 star reviews and 400,000 users…. and uses open source MIT code to make it work. Grab it and install it.
Once that’s done, the app window will load and you’ll see a settings window like this:
Click on ‘CHOOSE FOLDER‘ and select the folder containing the local files you want to link to (I used my Downloads folder), then make sure you click the slider so that the web server shows as STARTED. And take a note of your Web Server URL(s), which should be like this, http://127.0.0.1:8887.
If I visit that URL, I can see the contents of my Downloads folder listed in Chrome.
All I need to do to add links to local files in my Google Sheet is to use http://127.0.0.1:8887/ and append the filename or path to a file in my downloads folder and it will open in Chrome whenever I click the link in Google Sheets.
For example, I hit Ctrl+k, enter a name for the link and http://127.0.0.1:8887/Screenshot 2022-11-18 17.56.44.png in the URL box to link to that screenshot in my downloads folder!
One thing to note, if Web Server for Chrome is not running on your computer, your links will not work. So you’ll either need to remember to start it every time you want to use local files links, or change its settings so that it will ‘run in the background‘, and ‘start on login‘.
Other solutions – REDIRECTOR
Our goal for this article was to use a Google Sheet to link to a local file on a Chromebook running ChromeOS. As that’s what we’re currently working on! One of the first solutions we came across was using the REDIRECTOR chrome add-on. This add-on allows you to set your own redirection rules within your browser.
It is possible to access local files on a Chromebook from within the Chrome browser. If you drag a file from your Files app into Chrome you’ll see it displayed and see it’s URL which will be similar to this:
file:///home/chronos/u-abc23c1a0d13425436546d424e/MyFiles/Downloads/Somefile.png
So now we had the file URL we decided add a rule within REDIRECTOR that would redirect any http://localhost/ request to file:///.
I won’t go into details about how that works, because whilst the redirect worked, Chrome blocked it returned ‘Aww snap – Error code: SIGIL‘ which basically means you’re doing something you shouldn’t!
Whilst this method doesn’t work on ChromeOS, it might work on Windows or Mac. We didn’t try that because our Web Server for Chrome solution above worked so well; we didn’t need to.