I love the clean and straightforward interface of Google Sheets, and I use it for everything from applying filters to adding heatmaps using conditional formatting. In addition, Google Sheets integrates well with other Google products and is a real timesaver.
As handy as Google Sheets is, I ran into a problem when importing data from Google Business profiles, and only the anchor text showed up. I had to manually find each website and copy the URLs from the address bar rather than just scraping them from the website all at once. It was incredibly frustrating, but I found some excellent solutions to share with you.
Extract by Copying
If you only have a few URLs to extract and won’t be doing it often, this is the easiest way as you don’t have to create a script or turn your links into hyperlink formulas. Instead, you manually obtain the URL. Please note that this only works if you place one link per cell. If you have copied multiple links into one cell, you must move each link into its cell before you can extract the URL using this method.
There are three symbols in the top right of the quick view box. The easiest and fastest way to extract the URL is to click on the first icon in the top left corner of the quick view box. This symbol is the copy icon that looks like two pages on top of each other. Click on this, and the URL will automatically be copied to your clipboard. Then you can paste it into the cell next to the anchor text or anywhere you wish. Either use the keyboard shortcut Ctrl + V, or you can also right-click and choose Paste from the menu.
2. Extract by Editing
Another way you can extract the URL is to click on the pencil icon in the top left of the quick view box, which will allow you to edit the URL. Then, when the window opens, click on the second box, which contains the URL, select and copy it, and then paste it where you wish.
3. Using a Custom Script
Google has hundreds of functions available that allow you to automate your workflow further, but there is no built-in function that will enable you to extract URLs. If you are comfortable with javascript, you can use a custom script to fit your requirements. Creating a script takes longer to set up, but you can save the script and then utilize it quickly in the future to extract multiple URLs.
Google created Apps Script to allow developers to write custom functions using Javascript code. After you write and save your script, use it as you would any other function in Google Sheets. Detailed instructions on using the custom functions in Google Sheets can be found in the Google Apps Script documentation. Only a Google account and a web browser are required to access the script editor.
You can find the script editor by going to Extensions in your Google Sheets Toolbar and selecting Apps Script. It has recently been moved from Tools.
In order to use custom scripts to extract URLs, you have to enter your links as hyperlink formulas. However, if you have not done so and have a massive list of links you copied over, you can first turn them into hyperlinks using a handy app from Google Workspace Marketplace. This free App for Google WorkSpace Marketplace will allow you to turn all the links on your Google Sheet into a Hyperlink formula from which you can then extract the Url.
There used to be many custom code scripts that you could copy and paste from the internet, but since the Google update of 2020, most of these no longer work. For now, I think the easiest way to extract Urls in bulk is to use the free app from Google Marketplace to turn them into hyperlinks in Google sheets and then extract them manually.
Go to the Google Marketplace link above and install this extension to your Google Sheets by clicking on the install now button. After you accept the download, you will be able to find the APP under extensions, and you can choose to turn all of your links into hyperlink formulas at once. You can see the options for this add-on here under Extract URLs.
Copy and Paste from the Hyperlink Window.
Once you have the URLs entered as hyperlinks, you can copy and paste the URLs into other cells or documents if you don’t wish to write your own script.
