Use Google Sheets as a Screen Scraper (solution example not a problem)

Options
NewsomSolutions
NewsomSolutions Contributor
in Connectors

I wanted to pass along some Google Sheets knowledge that I’ve found helpful with my instance of Domo.  I’m hoping this will either help you directly or maybe help you generate some ideas of your own.


As you know you can connect to a Google Sheets dataset VERY easily in Domo.  What most people think about when they see that is a spreadsheet that they’re collaborating on or some results from a Google Form.  But Google Sheets is a powerful tool and if you do a little snippet copying/pasting you can do some cool stuff with it.

 

For my example, I use it to do some screen scrapes for me.  Why do I do this? I thought it would be helpful to look at websites (that don’t have a good RSS feed already that I could import directly) for mentions of important information.  Like a specific client name or a trend, or something that could impact numbers, even slightly.

 

So let’s say you have retail stores as your client, you could use this to review web pages that talk about Retail trends or Distribution issues, or even Customer Privacy at the point of sale..your scope could be huge.

 

To do this, create a Google Sheet, name the tab something like ‘website 1’.

 

  1. In cell 1A name it “Data Header”.
  2. In cell 2A put in something like =query(IMPORTHTML("https://www.retaildive.com/","list",5))
    1. There are tons of different customizations you can do here, but for this example I’m using IMPORTHTML (more info: https://support.google.com/docs/answer/3093339?hl=en) , I then designate the URL I want to query, then I select that I want to look at the list on the webpage, and the 5 designates where it is on the page.  
    2. You will have to play around w/ that because your website may be designed differently or it may change.
  3. The cells below 2A the data will begin to flow in.  I believe google runs the query every 7 minutes (but please correct that if I’m wrong)
  4. Now go to Domo and create a Google Sheets connector (https://liveops.domo.com/appstore/connectors/com.domo.connector.googlesheets?origin=dc&classification=API)
  5. Once you’ve created your Google Sheets connector you’ll want to create an ETL for it.  In that ETL you may want to filter on things like Store Name in your data or some other term you’re looking for.  Then output that data. You may want to add in a date to run this as well.
    1. Another benefit of an ETL is you can combine this with RSS feeds that you may be doing similar work with and be able to have one huge bucket of sources where you can search for mentions.
  6. In your card, you now have one pane of glass where you can see your company’s name being mentioned (or whatever you need this for) without having to scour the interweblinks yourself.

You could use that same Google sheet to scrape mutliple sites/indexes.  Just create a new tab and put in different logic for the query in 2A.  Then create a different Domo Google Sheets connector to bring that in.

 

Good Luck and I hope this helps.  There are lots of youtube videos and sites about Google Sheets as a screen scrape so you may want to expand on this some.



Categories