Tuesday , 25 September 2018
Home >> E >> Enterprise Applications >> How to create an automatically updating Google spreadsheet

How to create an automatically updating Google spreadsheet

Ideally, it would be best to use the Twitter API to pull in this data. However, to use the Twitter API, or any other API that requires authorization for use, you’d need to set up OAuth2 authorization for your spreadsheet. That’s a bit outside the scope of this tutorial; but if you’re interested, Google has an Oauth2 library for Google Apps Script.

Since I don’t have space to include these instructions, I’ll take an easier way out and extract number of followers from the Web Intent page. Google Sheet’s IMPORTXML function lets you extract specific HTML using XPath queries.

XPath is a fairly complex language to learn, but SelectorGadget makes it easy to point-and-click your way to finding XPath for specific data on an HTML page.

This is what SelectorGadget shows for an XPath query: //*[contains(concat( " ", @class, " " ), concat( " ", "count", " " ))]//*[contains(concat( " ", @class, " " ), concat( " ", "alternate-context", " " ))]. However, because the Google Sheets function uses double quotation marks to enclose arguments, you’ll first need to change all of the XPath double quotes to single quotes, and then enter a formula like this for your followers column:

=ImportXML(B2, "//dl[(((count(preceding-sibling::*) + 1) = 1) and parent::*)]//*[contains(concat( ' ', @class, ' ' ), concat( ' ', 'alternate-context', ' ' ))]")

Again, click-and-drag that down the rest of your column.

close
==[ Click Here 1X ] [ Close ]==