Google Sheets ImportFEED, Import HTML & ImportXML

Further to my previous post Google Sheets “GOOGLEFINANCE()” for free Stock data that started with referencing 5 things that Excel couldn’t do but Google Sheets can, I wanted to explore a few of the features mentioned in that video.

It mentioned the following Google Sheet Functions:

ImportFEED(), Import HTML() &ImportXML()

1. ImportFeed()

Well, I followed the steps for importing a Feed and got the same results. I tried to bring in the image of the feed but had no great success. I will need to explore finding the RSS links for sites, Feedly doesn’t show them on my mobile so I’ll look elsewhere.

2.ImportHTML()

I tried getting a table to work on the front page of NZX, and even though there is are tables in the page source, it would not display them, yet……

When I went onto pages with the actual market data, I was able to extract information and put it in a Google Sheet

So that was pleasing

I have been trying to get tables from Weather from metService but that seems to be like the front page of NZX and although tables are  shown in the source code the data will not come across. I had this issue the last time I was doing web scraping.

3. ImportXML()

There is also the ImportXML() function that can be used for web scraping as shown in this video:

I had difficulty getting this to work on a chosen website, and found the following tutorial video regarding Xpath really really helpful, I used his example and it worked fine.

I then used this coding for NZX market, as it worked with the importHTML() function above and it brought it in just fine. I needed to maker 2 importXML() calls, one for the column header and one for the data in the body.

I suppose I need a bit more practice to become more confident with using the importXML() function, but its pretty powerful. I am happy with the results so far, but need to do more to be effective with it on other sites I want to scrape.

While looking on YouTube I also came across a couple of other interesting videos.

4. Automation process for attendance

I really like this practical and simple automation process for attendance. Using Google Forms & Sheets with the addition of putting a QR code up on screen for people to read using a barcode scanner on their Tablet or Phone and filling out the details. Quick & easy.

5. Dropdown list and dependent list in Google Sheets

I thought this was a wow tutorial, no current use for it at the moment, but it looks as if it could be a useful method for filtering data and having a clean and simple interface on the front sheet.

End comment

Some nice tools to play with in google sheets and the last two processes are interesting too.

I think importXML(0 is the most potent ones of the 3 functions and I should explore it more to become more effective with it. Its a lot simpler than using Get Requests with Postman and getting connected to API’s. My weather App using Yahoo weather now doesn’t work as they want you to authenticate before getting the data. I enjoyed the exercise but I haven’t been able to fix it so far.