Connecting Jupyter Notebook with Tableau
Tags: tableau, python, tutorial, datascience
Published: 2022-08-05 17:52:00

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**

1. Go to [Google Cloud](https://console.developers.google.com/)  and select **_“My First Project”_**

2. This will open a pop-up window. Select **_“New Project_**” on the top right side of this pop-up window.

4. 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**

1. On the Dashboard, click “**_Library_**”, on the Left Pane. This will redirect you to API Library.

2. Search for Google Sheets. This will redirect you to the Google sheets API window.

3. 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_*

1. On the left pane, select “**_Credentials”_**

2. On the opened window select **_“+ Create Credentials”_**

3. 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].

4. 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.

5. Select **_“Create and Continue”_**

6. On **_“Grant this service account access to project”_**, select a role as Owner so that you may have full rights.

7. Click **_“Continue”_**

8. 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

9. Click **_“Done”_** to proceed

10. On the newly opened window, there is a Service Account section at the bottom of the page, select **_“Manage Service Accounts”_** on the left.

11. On the table, click on the 3 dots at the end, on the **_”Actions sections”._**

12.	Select **_“Manage Keys”._**

13. Click **_“Add Key”_** and select **_“Create New Key”._**

14. Select JSON as the Key Type.

15. A JSON will be automatically downloaded. It should be like the below:-

![image](https://user-images.githubusercontent.com/40733583/183126353-ce4c5fd8-2841-4594-9091-5b357a6de56e.png)

*_Note: Ensure the JSON file is saved in the same folder as your project._*

**Step 4:  Create a Google Sheet**

1. Go to Google Sheets and create a new one.

2. 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](https://public.tableau.com/en-us/s/download)

1. On the left pane, select **_“Google Drive”_**

![image](https://user-images.githubusercontent.com/40733583/183126738-2361bd89-b6ae-4dad-a4b1-4185831bcbe0.png)

2. This will require authentication and it will redirect you to your browser.

3. Enter your correct details and select Allow.

4. A pop-up window will appear, where you will select the correct project Google Sheet, as you had saved it.

5. This will open your data in Tableau as shown below: -

![image](https://user-images.githubusercontent.com/40733583/183126891-07b46074-adf8-4dc5-848c-bd2e152b5b36.png)

[Here](https://public.tableau.com/app/profile/claire.maina/viz/MavenChurnChallenge_16585980395970/MavenDashboard) is a sample of one of my visualizations created using this process.