Holy sheet: How to pull contact details from a website using only Google Sheets

Welcome to TNW Basics, a collection of tips, guides, and advice on how to easily get the most out of your gadgets, apps, and other stuff.

Are you in a job where you regularly need to collect contact information from companies? And do you find it an annoying and unnecessarily time-consuming task to sort through ever-changing web designs, to find that one button or bit of information you’re actually looking for? Don’t look any further… help is on the way!

Google Sheets contains some neat functions that can sort through a website’s content, or HTML in technical terms. To obtain very specific bits of that HTML, Google Sheets uses a structured data format called XML and a related query language called XPath.

Such a formula in Google Sheets is built up as follows: =IMPORTXML(url, xpath_query).

You could for instance have an XPath query that points at all links on a certain web page (=IMPORTXML(url, “//a/@href”), but also at one specific item such as the title of a web page (=IMPORTXML(url, “//title”). There are many XPath cheat sheets and tutorials available on the web that can help you craft the right queries.

To show you what’s possible, we’ve used these (and some other) formulas to create a nifty little Google Sheets template. It takes a url as input, and then automatically gathers the unique email addresses, phone numbers, Skype names, LinkedIn pages, Twitter accounts, Facebook pages, and Instagram accounts that can be found on that page.

The template can be found here. To use or tweak it, make a copy of the document first using the File menu. There you go, hope this makes your work life a little bit more efficient!