# Connecting Python to Google Sheets

## Google Sheets

**Google Sheets is a free web-based spreadsheet application provided by Google that allows users to create, manage and format spreadsheets online. It also allows users to collaborate with other users.**


![Google Sheets](https://cdn.hashnode.com/res/hashnode/image/upload/v1654020199894/SjYHryrXn.png align="left")

## Why connect Python to Google Sheets?

**As the volume of data increases, Python proves much more powerful and practical to perform data analysis and machine learning on your Google sheet data to provide actionable intelligence. It does that through the help of powerful libraries and huge online support group.**

It is easy to setup and perform data analysis in Google Sheets. However, as the volume of data increases, you want more computational flexibility and power. 

Today, Python is the go-to programming language for data analysis and machine learning, thanks to its third party libraries like Pandas, Numpy, Scikit learn and Matplotlib. The presence of an active online support group also helps. 

It is easier to draw the power of those libraries and build actionable business intelligence around your data in Python.  

## How to connect Python to Google Sheets ?

**You can connect Python to Google Sheets by creating a service account in Google Cloud Console which allows you to make authorized API calls to the Google Sheets API.** 

Follow the steps below.

### Create a new project in Google Cloud Console

1. Log in to [Google Cloud Console](https://console.cloud.google.com) in your browser.
2. Click on the **Menu** > **IAM & Admin** > **Create a Project**.
   
   ![Google Cloud Console](https://cdn.hashnode.com/res/hashnode/image/upload/v1654132038852/-r7BdL5f8.png align="left")
3. Provide a **Project name** and click **Create**.
   
   ![New project in Google Cloud Console](https://cdn.hashnode.com/res/hashnode/image/upload/v1654132070538/aVGeur5E3.png align="left")

### Enable Google drive API

1.     Click on **Menu** > **APIs & Services** > **Enabled APIs & Services**.

   ![Google Cloud APIs & Services](https://cdn.hashnode.com/res/hashnode/image/upload/v1654133489663/AZoBlI20b.png align="left")
2. Click on **+ Enable APIS AND SERVICES** button in the top middle of the page.

   ![Enable an API in Google Cloud](https://cdn.hashnode.com/res/hashnode/image/upload/v1654133644955/em21T7LKC.png align="left")
3. Search for the **Google Drive API** and click on it.

   ![Google Drive API](https://cdn.hashnode.com/res/hashnode/image/upload/v1654134150935/IP1jPpEfT.png align="left")
4. Enable the Google Drive API.

   ![Enable the Google Drive API](https://cdn.hashnode.com/res/hashnode/image/upload/v1654134251867/_vix8a4hM.png align="left")
5. Search for the **Google Sheets API** and enable it.

![Screenshot from 2022-06-02 17-58-04.png](https://cdn.hashnode.com/res/hashnode/image/upload/v1654172949852/6lPE1m_Sr.png align="left")

Once you enable **Google Drive and Google Sheets API**, you will be redirected to its page. To start using this API, **you have to create credentials**.

### Create a Service account

1. Click on **Create Credentials** on the Google Drive API page.

   ![Create credentials for Google Drive API](https://cdn.hashnode.com/res/hashnode/image/upload/v1654134970330/AEQgSP52z.png align="left")
2. On the **Create Credentials** page, fill in the necessary details and click on **Done**. You will be directed to **Service accounts** page.

   ![Credentials Page Google Cloud Console](https://cdn.hashnode.com/res/hashnode/image/upload/v1654135437740/cHVnLLx07.png align="left")
3. Provide a Service account name and description. Click on **CREATE AND CONTINUE**.

   ![Service Account page in Google Cloud](https://cdn.hashnode.com/res/hashnode/image/upload/v1654136364943/EvkMeZUrv.png align="left")
4. You will receive an email on the screen. Copy that email for later use.

   ![Service account email for Google Drive API](https://cdn.hashnode.com/res/hashnode/image/upload/v1654137437905/v83-pEyqF.png align="left")

5. On the same page, click on **Keys** tab. **Add Key** >** Create a new key**. Click on JSON to create a private key.

![Private key in JSON for Google Drive access](https://cdn.hashnode.com/res/hashnode/image/upload/v1654138461239/_LHLYmrf6.png align="left")

Once the private key is downloaded, rename it to `credentials.json` for use later to perform OAuth2 authentication with Google APIs.

### Share the Google Sheet with client email 

Create a google sheet to experiment with. Here I have created a **To do list**.

![My to do list in Google Sheets](https://cdn.hashnode.com/res/hashnode/image/upload/v1654175889651/qCu82kWnf.png align="left")

Click on **Share** button and share the spreadsheet with the **client email** you have saved from Step 4 of [previous section](#create-a-service-account).

![lambda(11).png](https://cdn.hashnode.com/res/hashnode/image/upload/v1654177081665/86RbAmhSo.png align="left")

You can use the `gspread` third party library to interact with the sheet.

## Reading and writing to Google Sheet with Python

1. Install the necessary libraries.
   
   ```python3
   pip install gspread
   pip install --upgrade google-api-python-client oauth2client
   ```
2. Create a new Python file. Import the following libraries into the file.
   
   ```python3
   import gspread
   import pandas as pd
   from oauth2client.service_account import ServiceAccountCredentials
   ```
3. Copy the `credentials.json` into the same directory as your file and perform authorization.
   
   ```python3
   # defining the scope of the application
   scope = ['https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive'] 

   #credentials to the account
   cred = ServiceAccountCredentials.from_json_keyfile_name('credentials.json',scope) 

   # authorize the clientsheet 
   client = gspread.authorize(cred)
   ```
4. Read the data from the spreadsheet using gspread `get_all_values()` method.

   ```python3
   # Provide the Google Sheet Id
   gs1 = client.open_by_key('18ZG9iEJN4c2SRdhWxo3o05ch6_TF4r2-7joAGtOeKG0')
   ws1 = gs1.sheet1
   print(ws1.get_all_values())
   ```

   You should get the all the rows from the sheets.

   **Output**

   ```bash
   [['\n To Do', '', '0/3 completed  '], ['', '', ''], ['✓', 'Date', 'Task'], ['FALSE', '2/6', 'Finish blog on connecting Python to Google Sheets'], ['FALSE', '2/7', 'Go book shopping'], ['FALSE', '2/8', 'Conduct meeting with Jesus']]
   ```
5. You can also write to the Google sheet. For example - The below code create a new worksheet.

   ```python3
   # create a new spreadsheet
   new_ws = gs1.add_worksheet(title="Trial Worksheet", rows=10, cols=20)
   ```

   ![Creating new sheet with gspread in Python](https://cdn.hashnode.com/res/hashnode/image/upload/v1654179667856/W3fiACVJl.png align="left")

I hope this article provides a clear **step-by-step guide** to connect to Google Sheets from Python.
In the next article, we will leverage the power of Python and perform some data analysis on the spreadsheet data.