{"id":3782,"date":"2024-04-07T12:43:28","date_gmt":"2024-04-07T17:43:28","guid":{"rendered":"https:\/\/osric.com\/chris\/accidental-developer\/?p=3782"},"modified":"2024-04-07T12:43:28","modified_gmt":"2024-04-07T17:43:28","slug":"extracting-links-from-google-sheets","status":"publish","type":"post","link":"https:\/\/osric.com\/chris\/accidental-developer\/2024\/04\/extracting-links-from-google-sheets\/","title":{"rendered":"Extracting links from Google Sheets"},"content":{"rendered":"

I was working with a shared Google Sheet at work and ran into this:<\/p>\n

\"An<\/a>
An excerpt of a Google Sheet. Each row contains a cell with a hyperlink labeled Link, but the actual URL is not displayed.<\/figcaption><\/figure>\n

I get it, URLs can be long and messy. We want narrow columns that look clean, not cluttered. But I wanted to analyze the URLs and search for certain content and patterns, which were hidden from me behind the link text.<\/p>\n

How can I extract all the URLs?
\n
\nBrute force?<\/strong>
\nI could get each link one at a time, by selecting each cell and using the link copy function in Google Sheets. But that would be extremely tedious and boring.<\/p>\n

Can paste-with-formatting actually help?<\/strong>
\nI tried copying-and-pasting the data into a variety of applications that I thought might be link-aware, but my results always ended up with just the link text and no URLs.<\/p>\n

StackOverflow to the rescue?<\/strong>
\nThe community provided plenty of custom formulae in How to extract URL from Link in Google Sheets using a formula?<\/a><\/p>\n

But I wasn’t interested in learning about creating a custom Google Sheets formula. Maybe that is something I should learn more about, but I just wanted a quick fix.<\/p>\n

Can AI tools help?<\/strong>
\nI admit, I logged into Microsoft Copilot to see if it could handle this. It confidently told me that it could, but then it failed every time with complaints about every file format I used to upload the data: Excel, HTML, PDF. (I was using test data, not actual work data, in case you are wondering how foolhardy or cautious I am about sharing data with LLM tools.)<\/p>\n

A sed and grep pipeline solution<\/strong><\/p>\n

1. Copy just one target column to a new Google Sheet
\n2. Download the new Google Sheet as HTML
\n3. Open the resulting ZIP file and extract just the HTML, e.g. Sheet1.html<\/code>
\n4. Run this slightly ridiculous pipeline:<\/p>\n

sed 's\/<td\/\\n<td\/g' Sheet1.html | # insert newlines before cells\r\ngrep '<td' | # select just the lines with data cells\r\nsed 's\/<td[^>]*>\/\/g' | # remove the data cell before the link\r\nsed 's\/[<]a target=\"_blank\" href=\"\/\/g' | # remove code before links\r\nsed 's\/\".\\+\/\/' | # remove everything after the link\r\nsed 's\/<.\\+\/\/' >raw_links.csv # replace non-link rows with blanks<\/code><\/pre>\n
5. Upload raw_links.csv<\/code> to Google Drive
\n6. Open raw_links.csv<\/code> in Google Sheets
\n7. Copy column A, and paste into a new empty column in your target Google Sheet
\n8. Replace the “Link” column with a formula, e.g.<\/p>\n
=IF(ISBLANK(L2), \"\", HYPERLINK(L2, \"Link\"))<\/code><\/pre>\n
9. Highlight the calculated “Link” column
\n10. Go to Data — Protect Sheets and Ranges<\/em> to help prevent your colleagues from hiding useful URLs behind link text!<\/p>\n
Obviously some of those steps are optional, depending on your goal. I think it is useful to preserve the URLs in an easy-to-access format, but at the same time I understand that it does make the spreadsheet more cluttered.<\/p>\n","protected":false},"excerpt":{"rendered":"
I was working with a shared Google Sheet at work and ran into this: I get it, URLs can be long and messy. We want narrow columns that look clean, not cluttered. But I wanted to analyze the URLs and search for certain content and patterns, which were hidden from me behind the link text. … Continue reading Extracting links from Google Sheets<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[232],"tags":[577,293],"class_list":["post-3782","post","type-post","status-publish","format-standard","hentry","category-tips-tricks","tag-google-sheets","tag-sed"],"_links":{"self":[{"href":"https:\/\/osric.com\/chris\/accidental-developer\/wp-json\/wp\/v2\/posts\/3782","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/osric.com\/chris\/accidental-developer\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/osric.com\/chris\/accidental-developer\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/osric.com\/chris\/accidental-developer\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/osric.com\/chris\/accidental-developer\/wp-json\/wp\/v2\/comments?post=3782"}],"version-history":[{"count":14,"href":"https:\/\/osric.com\/chris\/accidental-developer\/wp-json\/wp\/v2\/posts\/3782\/revisions"}],"predecessor-version":[{"id":3800,"href":"https:\/\/osric.com\/chris\/accidental-developer\/wp-json\/wp\/v2\/posts\/3782\/revisions\/3800"}],"wp:attachment":[{"href":"https:\/\/osric.com\/chris\/accidental-developer\/wp-json\/wp\/v2\/media?parent=3782"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/osric.com\/chris\/accidental-developer\/wp-json\/wp\/v2\/categories?post=3782"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/osric.com\/chris\/accidental-developer\/wp-json\/wp\/v2\/tags?post=3782"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}