Steve Grunwell

Open-source contributor, speaker, and coffee snob

SQL-generated Hyperlinks in your Spreadsheets

Update 3/4/15: The other day Nat Dunn, Founder and CIO of Webucator, reached out and asked if his company could turn this blog post into a free training video. Webucator did a bang-up job, so if video tutorials are your thing, check it out!

I was building a QA spreadsheet the other day, where I needed to generate a number of randomly-generated WordPress posts with the posts’ titles, URLs, admin URLs, and publication date to be shared with the client via Google Drive. This is a straight-forward enough task in SQL, with something like this:

This gives us a pretty simple output:

However, my PM wanted to be able to give the client a spreadsheet that looks more like this (where “Hello World!” and “Admin” are links to the public and admin URLs, respectively):

Unfortunately, CONCAT-ing together an HTML link did nothing, but then I found this post on CompuTips Blog outlining how to paste hyperlinked cells from CSV files. The expected format is:

To revisit our original query, we would rewrite it as:

The results from that query should paste nicely into Google Drive, Excel, Libre Office, and other popular spreadsheet applications. As a quick aside, if your post titles contain double-quotes you might get the infamous #ERROR in the post_title column once pasted; handling this is just a matter of quick manual clean-up after pasting fortunately, as pointed out in the Webucator video at the top of this post, it’s a simple matter of using MySQL’s REPLACE function:

See? Much better! Thanks again to Webucator’s instructor-led training services for putting together the video and improving upon the code!

Previous

Getting Started with Roasting Coffee at Home

Next

Adventures in Home Roasting: Bourbon-infused Coffee

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Be excellent to each other.