Web Scraping and Automatic data refresh using Google Sheets and Tableau Public

-
Recently I have figured out how to do web scraping using google sheets and to automatically refresh a dashboard on tableau public. So here it is…just sharing what I have learned.

Tools I have used

  • Tableau Public Desktop.
  • Google Sheets.

At first, you have to find out the live data source and import that data to google sheets.
In my case, I have fetched the live air pollution data from https://data.gov.in/catalog/real-time-air-quality-index and next step is to import that data to google sheets. Input is an XML file and was available using API and I have got the access by registering and generating an API key.


Next step open your google sheets and import the data using IMPORTXML function available in google sheets.


 Format : IMPORTXML("URL","XPath")
URL : link to your data source
XPath : used to navigate through elements and attribute in XML (https://www.w3schools.com/xml/xpath_intro.asp).

Same way there are different functions available in google sheets to do web scraping.Check the link https://support.google.com/docs/answer/3093342 to know more about how to import data in google sheets from various sources. Once the data is imported in google sheet then next step is to enable the auto-refresh feature and to make sure that auto refresh works even in offline mode(data refresh work even without opening google sheets).

Go to File > Spread sheet settings and under Calculation tab change the Recalculation option to on change and every hour.
Next, go to Tools > Script editor and paste the below code and create a trigger to schedule the function once in every hour.

function myFunction() {

var ab = SpreadsheetApp.openById("1uU-Tru5eWh2AybPfxg0I268SAOscTjOWcDPhjGOQVVQ");
var sheet = ab.getSheetByName("Sheet1");
var cell = sheet.getRange('M1')

cell.setValue(1)
}

The above code first opens the google sheet by using ID, sheet id can be found out using the google sheet URL.
And then set the value of cells M1 to 1, we are doing this step to make sure that there is a calculation change and which will then trigger the IMPORTXML function to auto update even when the sheet is not opened.

Now it's time to open Tableau Public desktop and connect to the google sheets directly, create an awesome visualization 😃
and once it is ready to go to File >Save to Tableau Public and then tableau public login window opens and immediately you will get the below pop-up.

Don't forget to enable the Keep my data in sync option for Tableau to refresh the data from source once every day. You are all done now it's time for you to test it.👍

Comments

Post a Comment

Contact Form

Name

Email *

Message *

Popular posts from this blog

All you need to know about the Google Data Studio

-
Google data Studio(GDS) is based on SaaS(Software as a service, cloud-based) model and in my first look it is a neat and clean visualization tool primarily focused on Google data sources. So for businesses who are into google analytics or Google products can create great visualizations in minutes using DATA STUDIO. It is a free tool for now and is still in beta version. Other than google providers GDS can connect to CSV and databases like MySQL, PostgreSQL .For all other data sources, there are third-party community connectors available. URL: https://datastudio.google.com/u/0/navigation/reporting The look and feel are similar to other Google products like Google Sheets & Docs and all the reports created are saved in google drive. Here goes the list of features that I liked the most in GDS 😊. Easy to share the reports and works similar to file sharing in google drive(even edit permission is possible). Easy to copy the reports and any reports can be used as a template ...
Read more

How to fetch data from SnowFlake using Excel VBA?

-
Image
The easiest way to fetch data from databases to Excel is using the Get Data option available under the Data Tab, Excel creates a query using the ODBC connection and fetches the data without the hassle of coding. But coding makes the process more flexible and is very dynamic. Here goes the details... 1) Download and install the snowflake ODBC driver either 32 or 64 bit depending on your MS Office Installation & OS from the SnowFlake portal, available under the Help menu. 2) Create a DSN with the correct connection properties. 3) Add the below reference if required in the visual basic editor and also change the DSN name in the VBA code. VBA Code...  '----------------------------------------------------------------------------------------------------------------------------------------- Sub VBA_SnowFlake_Connect() Dim sSQLQry As String Dim ReturnArray Dim Conn As ADODB.Connection Dim rs As ADODB.Recordset Dim DBPath As String, sconne...
Read more

KPI's in Tableau with good or bad indicator.

-
Image
The nice way to create KPI's in Tableau with a good or bad indicator is to use the custom format option. Follow the below steps to create a cool looking KPI. At first, create a calculated field in Tableau based on your KPI definition. In my case, I am using the Profit Ratio calculated field in the Super Store sample dataset. Custom format used : ▲ +0"%"; ▼ -0"%"; ► 0"%" Happy Tableauing :)
Read more