The heart of tech

This article was published on April 7, 2021


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

Holy sheet: How to pull contact details from a website using only Google Sheets
Yaron Yitzhak
Story by

Yaron Yitzhak

Yaron is a Data Analyst & Audience Developer at TNW. He also enjoys writing stories on the intersection of finance and technology, and e Yaron is a Data Analyst & Audience Developer at TNW. He also enjoys writing stories on the intersection of finance and technology, and explainers on how to work with data even if you don’t have advanced technical skills.

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!

Also tagged with