How to connect Google Data Studio with everything ?

Google Data Studio is one of the best free data visualization tool powered by Google.

Although it is been a while since Google Data Studio is up and running, but still there are limited connectors to fetch data from different data resources.

So, how do we connect and plot data from data resource whose connector is not present. Well there is a workaround to it, "Google Sheets". Yes, Google Sheets, Data Studio has direct integration with Google Sheets. So what ?

Well you can use any coding language like Python, JavaScript etc. which has infinitely large number of libraries which can connect and fetch and manipulate data from any data resource. Use Python scripts to write into Google Sheet and then plot Google Sheet data into Google Data Studio.

Following are the steps to connect GDS with the help of python. This will be divided into three steps, first to fetch data from data source by python, second to connect and write Google Spreadsheet by Python and third to connect Google Spreadsheet to Google Data Studio.

Step 1 : Connect Python to Data Sources

As I mentioned, there are libraries available for almost all the data source. Following are some examples:

1. MySQL : Library - MySQLdb

import MySQLdb
# Connecting to Database
conn = MySQLdb.connect("test_server","test_user","test_password","test_DB" )

# creating a cursor
cursor = conn.cursor()

# executing query
cursor.execute("SELECT * FROM Transaction limit 5")

# Fetching all the data
result = cursor.fetchall()

# closing the database connection
conn.close()

2. PostgreSQL : Library - Psycopg2 

import psycopg2
# Connecting to Database
conn=psycopg2.connect(host='test_server',dbname='test_DB',user='test_user',password='test_password')

# creating a cursor
cursor = conn.cursor()

# executing query
cursor.execute("SELECT * FROM Transaction limit 5")

# Fetching all the data
result = cursor.fetchall()

# closing the database connection
conn.close()

3. Microsoft SQL Server : Library - pyodbc

import pyodbc
# Connecting to Database
conn=pyodbc.connect('DRIVER={SQL Server};SERVER=test_server;DATABASE='+test_DB;UID=test_user;PWD=test_password')

# creating a cursor
cursor = conn.cursor()

# executing query
cursor.execute("SELECT * FROM Transaction limit 5")

# Fetching all the data
result = cursor.fetchall()

# closing the database connection
conn.close()

4. APIs : Library - requests

For any other data sources other than data bases such as various analytics tools, APIs will definitely be available to fetch data. Although, you will need other python libraries also, to modify and filter data of your need before writing into Google Spreadsheet, but that topic is beyond the scope of this blog post.

import requests
api_endpoint='https://www.googleapis/pagespeedonline/v2/runPagespeed'
input_url = 'www.test.com'
key='my_encrypted_key'
request_url = api_endpoint+'?key'+my_encrypted_key+'&url'+input_url+'&strategy=desktop'
response = requests.get(request_url)
data = json.loads(response.content)
*Do whatever you want with data*

Step 2 : Write data to Google Spreadsheet.

Library - gspread

You will need a client key to write on a Google Spreadsheet, consider it as substitute for your gmail password, as you will require permission to write on any Google Spreadsheet. You will get the key here, store the file.

import gspread
from oauth2client.service_account import ServiceAccountCredentials

Scopes=['https://spreadsheet.google.som/feeds']
credentials=ServiceAccountCredentials.from_json_keyfile_name(path_to_key,Scopes)
con=gspread.authorize(credentials)
ss = con.open(mygooglesheet)
ws=ss.worksheet(mysheet)

cell_range='A1:B3'
cell_list=ws.range(cell_range)
for i in range(0,6)
  cell_list[i]=i*2
ws.update_cells(cell_list)

You can find the details of gspread in the documentation.

Step 3 : Connect Google Spreadsheet to Google Data Studio

Direct connector is available to connect Google Spreadsheet to GDS.

In Google Data Studio, from the Edit Menu, just go to Resource->>Manage added data sources->> Add a Data Source, and you will find a Google Sheets there. Add your Google Sheet as a data source. Before migrating data, just check the data type of your columns as per GDS. Now you are good to go.

Conclusion:

In this blog post, we have learnt to connect Google Data Studio to many data sources with help of Python and Google Spreadsheet. You may encounter multiple errors along the way even after getting data into Google Sheet it may throw an error in GDS. This could be because some rows or columns in-between were set to NULL while using gspread to write into it. Go ahead and look for such anomalies, and find ways to fix them. That's how we learn!

Like this post, find more cool stuff here.