Custom Functions Google Spreadsheets

Working with URLs in Google Apps Script and Google Sheets

 

There is something so fitting about working with URLs in Google Apps Script and Google Spreadsheets. The cloud-based software works in the browser where, indeed, there are a lot of URLs to work with. Google Apps Script’s doPost, doGet, and UrlFetchApp make HTTP requests to URLs easy. Google Spreadsheets has similar functionality with IMPORTXML, IMPORTHTMLIMPORTFEED, and IMPORTDATA. If you work in web analytics, digital marketing, or especially SEO, chances are, you’ve stared at a big spreadsheet of URLs trying to figure out how to parse, check, or call the URLs quickly and easily. Luckily, there is a beautiful Javascript library that makes working with URLs just as quick and easy as you want it to be.

This post will show how to import the URI.js javascript library from a third-party source (in this case GitHub) and use it in Google Spreadsheet custom functions. The demo should also help you use the library if you are working with a script that involves constructing REST API URLs.

 

Introducing URI.js

URI.js is a very comprehensive javascript library for working with and manipulating URLs. It allows you to get specific parts of the URL, change them, and not have to worry if you’ve mashed up the original URL into a malformed mess. It even lets helps you determine if a URL qualifies as a URL or IP address. If you don’t know how handy this is, I’ve got some spreadsheets you can work on!

To get a javascript library from GitHub to Google Apps Script, you can use RawGit to serve the library’s source file from GitHub. Then use the eval() command to make the library and its methods accessible to the Google Apps Script environment.

The GitHub URL for URI.js is https://github.com/medialize/URI.js/blob/gh-pages/src/URI.js and when you enter that into RawGit, you get the usable source file at https://cdn.rawgit.com/medialize/URI.js/gh-pages/src/URI.js.

Now to get the URI object and all of its handy methods just call eval like so:

Then the URI object is in Google Apps Script’s global scope. To use it just do something like URI.methodName(). To see all the methods in the library check out the API reference.

 

URL Custom Functions for Google Apps Script

Here are a few wrapper functions to take advantage of the URI.js in Google Apps Script. There are other functionalities not included here so don’t be afraid to build on this! Just remember, you must use eval() (above) in order to load the library.

The output of the logs for the function testURI should look like this:

I hope this helps you clean up the internet. Leave a comment if you have any questions!

Leave a Reply

Your email address will not be published. Required fields are marked *