Using Google Sheet API with Python

Here is the step-by-step guide to connecting Google Sheets to any Python program. In this tutorial, we connect it to Google Collab, but you can connect it to any application with the following the below points.

Requirement:

1. A Google account A Google Cloud Platform (GCP) project (https://developers.google.com/workspace/guides/create-project)
2. Python 3.x installed (https://www.python.org/downloads/)
3. A code editor (e.g., Visual Studio Code, PyCharm,Google Colab)

1. Create a New Google Cloud Platform (GCP) Project

• If you don’t have a GCP project already, you’ll need to create one. Head over to the GCP Console: https://console.cloud.google.com/.
• Click on the “Select a project” dropdown in the top navigation bar.
• If you don’t see any projects listed, choose “Create project.”
• Give your project a unique and descriptive name (e.g., “Google Sheets API Project”).
• Choose an organization (if applicable) and location for your project’s resources.
• Click “Create” to establish your new GCP project.

2. Enable Google Sheets API

• Now that you have a GCP project, you need to enable the specific API you want to use: Google Sheets API.
• In the GCP Console navigation bar, go to the “APIs & Services” section.
• Select “Library” from the submenu. This is where you’ll find all available Google Cloud APIs.
• Use the search bar to find “Google Sheets API.”
• Once you locate it, click on the API name to access its details.
• On the API details page, click the “Enable” button to activate the Google Sheets API for your project.

3. Create a New Service Account

• Service accounts are special Google accounts used by applications to access Google Cloud resources. For interacting with the Sheets API, you’ll need a service account.
• In the GCP Console navigation bar, go to “IAM & Admin” and then select “Service Accounts.”
• Click the “Create Service Account” button.
• Give your service account a descriptive name (e.g., “Sheets API Access”).
• Optionally, describe your service account.
• Click “Create” to establish the new service account.
• Click on the key to add a new key

4. Download the JSON File

• Once your service account is created, you need to download its credentials in JSON format. These credentials will be used to authenticate your application with Google when accessing the Sheets API.
• Click on the newly created service account name in the list.
• Go to the “Keys” tab.
• Click the “Add Key” button and select “Create new key.”
• Choose the JSON format for the key type.
• Click “Create” to download the JSON file.

Program side:

Installed the required libraries

!pip install google-cloud
!pip install gspread

Upload the JSON file, which you downloaded from the google API to the working directory

Once you uploaded, write the following program to connect to the google sheet.

import gspread
from google.oauth2.service_account import Credentials
import pandas as pd

# Authenticate using the uploaded JSON file
scope = ['https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive']
creds = Credentials.from_service_account_file('/content/your JSON file', scopes=scope)
gc = gspread.authorize(creds)

# Open the Google Sheet by its URL
sheet_url = 'your-url'
sheet = gc.open_by_url(sheet_url)

# Select the first worksheet
worksheet = sheet.get_worksheet(0)

# Read data from the worksheet into a Pandas DataFrame
data = worksheet.get_all_values()
df = pd.DataFrame(data[1:], columns=data[0])  # Assuming the first row contains column headers

# Display the DataFrame
print(df)

In the above program, replace the google sheet URL and json file names according to your project.