Links to Google Spreadsheet with Textexpander
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.