The main benefit of connecting your Jupyter notebook with Tableau is that this allows for real-time analysis. To mean that, any changes made to the notebook will be reflected on Google Sheets and Tableau.
What are the steps involved?
1: Create a New Project on Google Developers Cloud
2: Enable Google Sheets and Google Drive APIs
3: Create credentials
4: Google Sheets
5: Code to add to your Jupyter Notebook
6: Tableau Public setup
Step 1: Create a New Project on Google Developers Cloud
Go to Google Cloud and select “My First Project”
This will open a pop-up window. Select “New Project” on the top right side of this pop-up window.
Choose a unique Project Name e.g. new-tutorial. You may leave the Organization field as it is and click on Create
Step 2: Enable Google Sheets and Google Drive APIs
On the Dashboard, click “Library”, on the Left Pane. This will redirect you to API Library.
Search for Google Sheets. This will redirect you to the Google sheets API window.
Proceed to click on “Enable API”.
Follow the above steps to also Enable the Google Drive API.
Note that the 2 APIs have to be enabled for each new project you create.
Step 3: Create credentials
Note: Always check that you are still working on the correct project
On the left pane, select “Credentials”
On the opened window select “+ Create Credentials”
Select “Service Account”. A service account is a special type of Google account intended to represent a non-human user that needs to authenticate and be authorized to access data in Google APIs [sic].
On Service Account Details - for the Service Account Name - you can choose a name similar to your project. E.g. new-tutorial. The Service Account ID will automatically update. You may choose to edit it. For the Service Account Description, you may choose to leave this blank as well.
Select “Create and Continue”
On “Grant this service account access to project”, select a role as Owner so that you may have full rights.
Click “Continue”
-
On “Grant users access to this service account”, you may add other users or groups. If you do not have other users, you may leave it blank. Below are some examples of users or groups.
a. Google Account email: [user@gmail.com](mailto:user@gmail.com), b. Google Group: admins@googlegroups.com, c. Service account: server@example.gserviceaccount.com d. Google Workspace domain: example.com Click “Done” to proceed
On the newly opened window, there is a Service Account section at the bottom of the page, select “Manage Service Accounts” on the left.
On the table, click on the 3 dots at the end, on the ”Actions sections”.
Select “Manage Keys”.
Click “Add Key” and select “Create New Key”.
Select JSON as the Key Type.
A JSON will be automatically downloaded. It should be like the below:-
Note: Ensure the JSON file is saved in the same folder as your project.
Step 4: Create a Google Sheet
Go to Google Sheets and create a new one.
Click the Share button and add the client-email that was on the JSON file e.g.
"new-tutorial@ new-tutorial-...iam.gserviceaccount.com"
Get the spreadsheet key from the URL. As highlighted below in black.
"://docs.google.com/spreadsheets/d/1l9yMNyoXt.....cZCYACyc/edit#gid=0 "
This Spreadsheet Key will be used in the notebook as shown below.
Step 5: Jupyter Notebook
Back to the notebook.
Import the necessary libraries
# import libraries
import gspread
from oauth2client.service_account import ServiceAccountCredentials
from df2gspread import df2gspread as d2g
Access Google Sheets and Google Drive APIs
# Access the Google Sheets and Google Drive APIs
scope = ['https://spreadsheets.google.com/feeds','https://www.googleapis.com/auth/drive']
credentials = ServiceAccountCredentials.from_json_keyfile_name('name_of_your_json_file.json',scopes=scope)
gc = gspread.authorize(credentials)
Code to upload the dataframe to Google Sheets.
spreadsheet_key is, what we copied earlier when we created the Google Sheet.
worksheet_name is, by default named Sheet_1. You can change this on the Google sheet as per your preference.
# Share the dataframe with Google Sheets
spreadsheet_key = '1XccW9EiPSYHRmmESyLTTUnmRdfMDiGlzqnG0_jOmA5Q'
worksheet_name = "Main"
result = d2g.upload(your_dataframe_name, spreadsheet_key, worksheet_name, credentials = credentials, row_names=True)
print(result)
Step 6: Tableau
To download Tableau click here
- On the left pane, select “Google Drive”
This will require authentication and it will redirect you to your browser.
Enter your correct details and select Allow.
A pop-up window will appear, where you will select the correct project Google Sheet, as you had saved it.
This will open your data in Tableau as shown below: -
Here is a sample of one of my visualizations created using this process.
Top comments (0)