Import XML In A Google Docs Spreadsheet

Google Cloud Spreadsheets

If you want to learn Excel, keep reading or Get 10 days of free unlimited access to Lynda.com. for professional help and Excel Tutorials
***************

I have to admit, I did not have much understanding of XML up until a few hours ago. But I had read about the ability to use the importXML function in Google Docs spreadsheets and it looked promising so I decided to give it a try. I did use a few resources which I’ll be sure to link to at the end of this post. So what is ImportXML? It’s a function made available in Google Docs that makes it easy to make different types of web queries

How Does It Work?

The one downside is that using it requires some understanding of the html. But it can be extremely powerful. You can see 2 examples here:

#1-Imagine that I’m trying to get access to the list of all links in this page on a blog that I visit:

It’s certainly possible for me to get them all one by one or to download them in one way. Another option though is to do a simple ImportXML after seeing that these links were located inside of this code:

I did the following query:

=importxml(“http://www.smartpassiveincome.com/archives/”,”//div[@class=’format_text’]//li/a/@href”)

And you can see the result here:

How about another use? I have a few dividend-related sites and imagine that I’d need to know the top 20 sites for the keyword: “dividend investing”

I added the keyword in cell A1 to make it easy to change and then used the following function in B1:

=”http://www.google.com/search?hl=en&gl=US&num=20&q=”&A1

This game me a list of 20 websites. Ideally, I wanted to have clickable links so I used the “find” function which I’ve discussed in Excel and then the “mid” function, you can see the result here:

Resources: ImportXML Guide for Google Docs (http://www.distilled.net/blog/distilled/guide-to-google-docs-importxml/#chapter1)

***************************************************

Look Good at Work and Become Indispensable Become an Excel Pro and Impress Your Boss


***************************************************

Comments

Please Leave a Comment!





Please note: Comments may be moderated. It may take a while for them to show on the page.