Links to Google Spreadsheet with Textexpander

6. september, 2011 — 2 Comments

This is an annoying one. Google Spreadsheet doesn't allow you to make links with linktexts in the fields in the spreadsheet.

This is highly annoying, because sometimes you'd like to have some text link somewhere else. For instance... I sometimes do simple leadlists in Google Spreadsheets and share them with my team.

But to create the inline links, you have to type something like this

=hyperlink("url_goes_here";"linktekst_goes_here")

Annoying! Tedious! Boring!

Well... luckily we have Textexpander. The little app that expands snippets.

This wonderful piece of software will run shell scripts when expanding snippets. So to solve my problem, I hacked out this little piece of code, based on an original snippet by Brett Terpstra


!/usr/bin/env ruby -wKU

def entity_escape(text) text.gsub(/&(?!([a-zA-Z0-9]+|#[0-9]+|#x[0-9a-fA-F]+);)/, '&') end

def make_link(text) case text when %r{\A(mailto:)?(.?@...)\z}: "mailto:#{$2.gsub(/./) {sprintf("&#x%02X;", $&.unpack("U")[0])}}" when %r{\Ahttps?://.?.\w{2,4}.?\z}: entity_escape(text) when %r{\A(www..|..\w{2,4})\z}: "http://#{entity_escape text}" when %r{\A.?.\w{2,4}\/?.*\z}: "http://#{entity_escape text}" else nil end end

url = make_link %x{__CF_USER_TEXT_ENCODING=$UID:0x8000100:0x8000100 pbpaste}.strip print %Q{=hyperlink("#{url}";"%fill:Link Text%")}


So the way to use this is.

  • Put it into your textexpander - remember to change content of snippet to shell-script
  • Copy the link you want to paste to your clipboard
  • Hit your snippet keyboard combo - mine is ,,GL
  • The script prompts you for a linktekst, enter it at hit return

Voila! A google spreadsheet hyperlink, with almost no fuss.

Download the snippet here!

Jens Poder

Posts Twitter Facebook

2 responses to Links to Google Spreadsheet with Textexpander

  1. Thanks so much for taking the time to do this. You just saved me an hour of my life!

Trackbacks and Pingbacks:

  1. Web excursions: September 6, 2011 - September 12, 2011 - Brett Terpstra - 13. september, 2011

    [...] on Lion yet, but Dr. Drang covered the bases for me. Looks like I’m switching back to NerdTool.Links to Google Spreadsheet with TextexpanderJens Poder modified one of my Markdown snippets to create links in Google Spreadsheets (with linked [...]

Skriv et svar

*

Text formatting is available via select HTML. <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>