Updated October 25, 2023

Introduction to Google Sheets with Python

Google Sheets is a popular cloud-based spreadsheet application that allows users to create, edit, and collaborate on spreadsheets online. You can interact with Google Sheets programmatically using the Google Sheets API, automate tasks, or perform data analysis with Python.

Introduction to Google Sheets with Python

Key Takeaways

The Google Sheets API allows programmatic access to and manipulation of Google Sheets.
Start by setting up the API in the Google Cloud Console and obtaining credentials.
Install the gspread and oauth2client Python libraries for interaction.
Using the credentials provided, authenticate with the API.
Access spreadsheets and worksheets by title or URL.
Read and write data to cells in the worksheet using gspread methods.

Installing Required Libraries

Install necessary libraries such as google-api-python-client, gspread, pygsheets, and pandas using package managers like pip or conda.

Install essential libraries using package managers like pip or conda.
google-api-python-client: For Google API interaction.
gspread: To access Google Sheets,
pygsheets: An alternative library for Google Sheets interaction.
pandas: Useful for data manipulation and analysis in Python.

pip command:

pip install google-api-python-client gspread pygsheets pandas

conda command:

conda install -c conda-forge google-api-python-client gspread pygsheets pandas

Ensure these libraries are installed before working with Google Sheets in Python.

Authenticate with Google Sheets API

Generate the Google Cloud Console API credentials and authenticate your Python script to access Google Sheets.

Step 1: Go to the Google Cloud Console.

Step 2: Create a new project or pick one from the list.

Step 3: Allow Google Sheets API access for your project.

Step 4: Create credentials for the API by configuring the OAuth consent screen.

Add a service account as an editor

You can create a new Google Sheet or use an existing one in your Google account. Add the service account as one of the editors in that sheet by just doing so.

Your keys file’s “client_email” entry contains the service account email.

Users will find it easy to combine their data with data analysis tools like NumPy or Pandas or data visualization frameworks like Matplotlib or Seaborn if they continue utilizing Python in Google Spreadsheets. Google Sheets provides a user-friendly interface with built-in functions and formulas, while Python’s Panda library offers extensive capabilities for programmatically processing data.

While Python’s Panda library offers significant capabilities for programmatic data processing, Google Sheets offers an intuitive interface with built-in functions and formulas.

Essential need for using Google Sheets with Python

Let’s see some of the essentials for reading and writing data to Python sheets.

1. Create a Service Account

A service account is a special type of Google account used for server-to-server authentication. It allows applications to access Google services and APIs on behalf of users or the application itself without human intervention.

Now, how can we create the service account?

Step 1: Go to the Google Cloud Console and select your project.

Step 2: Select “New Project”

Step 3: Fill in the necessary fields and click the “Create” button. You will be taken to the project home page after creating the project.

Step 4: From the left panel, navigate to “API and Services”. Select “+ Enable API and Services”.

Step 5: After clicking “Enable API and services,” the API library will be displayed. Locate the Google Drive API from this location, then select “Enable.” For the Google Sheets API, repeat this.

Step 6: Next, select “Create credentials”.

Step 7: Select “Service Account” from the Create credentials tab.

Step 8: Provide a name for the service account and click the “Create and Continue” button.

Step 9: Please grant the service account “Editor” access.

Step 10: Now click on the Done button.

Step 11: The next step is adding the keys. To add a new key, click on “Click new key”.

Step 12: Select JSON as a key and click Create.

Step 13: Service account credentials are created and downloaded as a JSON file in your personal file.

2. Creating Python Environment Variables for Google Sheets

Step 1: You must create environment variables after creating the service account.

Step 2: Open the Command Prompt Terminal on your system. Type CMD in the Windows search bar, and then press Enter.

Step 3: To create a new project directory called “python-to-google-sheets”, use the following command on your system’s terminal or command line:

mkdir pythontogooglesheets

Step 4: Navigate to the directory using the command

cd pythontogooglesheets

Step 5: Next, To build a virtual Python environment for the project, please use the following command:

python -m venv env

Step 6: After setting up the environment, you can use a code editor to access the pythontogooglesheets directory.

Step 7: The contents of the JSON file for the service account should be copied and pasted into a new file called “credentials.json.”

{
"type": "service_account",
"project_id": "educba63294",
"private_key_id": "795e9bbfd1f6778e49c6703450e78ca284b5a3a5",
"private_key": "-----BEGIN PRIVATE KEY-----\nMIIEvgIBADANBgkqhkiG9w0BAQEFAASCBKgwggSkAgEAAoIBAQDOf7oOpYYjoFtD\n+qDiBXrRozVeD8+to5YbzCoBpB7y0xBBy3zNvWBbgZ8aGf0JtVctNkKibjAGsqCa\nyoOtS3jnp4TZN74nYXZnNa8E51lHM27z/9haBVLWXsGxUJDPSCLyChJbzgreylh9\nDAQ/86NKiUFhYr1gJXJxS/nhoOgy7Mv4oRiIV69c4Pt+FBAcM7/yxwIFJ1XvTouA\n1g3eHPfdAn7irE/Dnr0sKDl37IjGJ+tASPsSvvdQG0hCOCdshbBu0hOqEulB8Nly\nX9XeB8ngfdDvfZoB4pt3Ut93AOtjJd0s92ejb7HpO/qKIW0PQFvWqXZEzSc5/1tX\np+YFx/phAgMBAAECggEAE13qUsZWas/2z0zZuuj9qFywvCYqeaQOh4Ph6q58UpTW\nAP23MkqRF3P0Oov2besqhmyA2mAQNAibxHi62c1qxPsym/a86E+dPbfMtt6FwoZB\n4TN9FJBMQRu4+crPEgm3cVDdgOu1nEiI/Q1b9q2AgpSV467RiQtqDNMt1+nx7Ria\nLI5EdP8GjakyT5x1tZrPVToxBn9FR9ahhxdtB1ktaxysj7rs+7f8EK40TMPDnqko\npeWIzmwiHPumbrwPiVp1uPRUytUBFkYM5jSJhFRG+VEyrLgjun9EqVILSd2Xb1vs\nWVZpJVm3PPEkaRoNF+UH/MaRr6Yzsa5Z1wd4/nsN4QKBgQDvoGMO2J19CUmTyv1J\n6bLxbV0CjX7sSSoeSQrQOk2C0l/YZzK4pOcaDi8iqsFMvu8YdNTd5Ta+53Ipduqu\nUwcXvYbDyIjPgxBTDWCMHwjGCacmVP8cSdKYiw78sB7197bT2CvAWRYqRnOscSTp\nqRfPgYQj1pv1XGZmDC6rZR0ZyQKBgQDcm9zzqeBTKNkxJX1NbmUHacHznDbH2yqD\nWHMAN0iQP2GAwG02IfJPT5u2Ej4F+FISNEHq6JAMi/1MQnZ3RUS96P+aeAdC56Al\n7FHexjsfrZkhGbM4yHOsLwVoH0oHX2x3YTeW4/BW+4BXKsW8W3hVR5t2iBpbP949\n2ob+R3On2QKBgBBp0v8sVTVAUprd95V9fXVOAWXwXTZAGLN0W1pB9Bc247vF7dJ5\n8R52xEHConToSyZIsl/0mFhhMA+pBpAkPcMhhz6X2M8l8hNOQkO2yHoQJTPZ9JNy\ndwtd0wv54FUGv64pC24kbAE/TCMOqPGcVHYxL+ToiUw4JQduZC/3QBQJAoGBANY9\nc2Uam2SHLe2hh79Trz90wEsxcK3ENNQbaItUJN8Y/jqIJUdFzkiheglmS9+GWUWe\nhEl6ErJsnM+bV+raVTjM9rVvTTX3NTq+9n8PKcnI0AYqLDe/yz3y6CBHCqCAVhjV\nS6P4tqXyVp/QRPl4JzNCeEKzSE1HiC+vchutDi95AoGBAIBqp2Ix2oLAnFoUnm0+\nD0vT/MvYmc9Jwp+mGZYcBd6D2XeP5Y61+8tg11yTsni3f+pX48EeM0eeAN6fvF+2\n5GZjWPeZv6Cz88ztFohmaY05ufT1xAZUo48nf0WpliUmFjHJPBYD0IdkGNV7pS4y\nm8hr+PBClKukjw0Tjal9MitQ\n-----END PRIVATE KEY-----\n",
"client_email": "[email protected]",
"client_id": "101897058609373210525",
"auth_uri": "https://accounts.google.com/o/oauth2/auth",
"token_uri": "https://oauth2.googleapis.com/token",
"auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
"client_x509_cert_url": "https://www.googleapis.com/robot/v1/metadata/x509/educba-python-google-sheet%40educba63294.iam.gserviceaccount.com",
"universe_domain": "googleapis.com"
}

Step 8: Next, to grant this Python project access to your Google Cloud service account, create an auth.py file and insert the following script inside of it:

from __future__ import print_function
from googleapiclient.discovery import build
from google.oauth2 import service_account
SCOPES = [
'https://www.googleapis.com/auth/spreadsheets',
'https://www.googleapis.com/auth/drive'
]
credentials = service_account.Credentials.from_service_account_file('credentials.json', scopes=SCOPES)
spreadsheet_service = build('sheets', 'v4', credentials=credentials)
drive_service = build('drive', 'v3', credentials=credentials)

3. Activate Environmental Variables

Step 1: Execute this command. The path-to-env command. cd pythontogooglesheets\env\Scripts is the command to use in this instance.

Step 2: Once within the Scripts directory, use the Windows command to execute this: activate.bat

Step 3: After this step, you must install the necessary Google client libraries.

1. In the env folder, create one text file named: require.txt and paste the following dependencies.

google-api-python-client==1.7.9

google-auth-httplib2==0.0.3

google-auth-oauthlib==0.4.0

2. To install the packages, open the env directory of your project in the terminal, then type the following command:

pip install -r require.txt

3. Create a createsheets.py and add the following code to it.

This script creates a new spreadsheet named demo Test sheet.

from __future__ import print_function
from auth import spreadsheet_service
from auth import drive_service
def create():
    spreadsheet_details = {
        'properties': {
            'title': 'Demo Test Sheet'
        }
    }
    sheet = spreadsheet_service.spreadsheets().create(body=spreadsheet_details, fields='spreadsheetId').execute()
    sheetId = sheet.get('spreadsheetId')
    print('Spreadsheet ID: {0}'.format(sheetId))
    permission1 = {
        'type': 'user',
        'role': 'writer',
        'emailAddress': '[email protected]'
    }
    drive_service.permissions().create(fileId=sheetId, body=permission1).execute()
    return sheetId
create()

4. Open the command Prompt terminal and execute the createsheet.py command.

Access Google Sheets Using Python

Use the appropriate methods from the gspread library to open the desired Google Sheet by providing the required credentials.

Step 1: Install the gspread library using the pip command.

Pip install gspread

Specify the URL or title of the sheet to be accessed.

Import the necessary libraries and authenticate as described in the previous steps.

Use gspread to open the desired Google Sheet. You can do this by specifying the URL or the sheet’s title.

If you want to access the sheet by its title, you can use the following code.

import gspread
from oauth2client.service_account import ServiceAccountCredentials
# Authenticate with OAuth2.0 credentials
scope = ['https://spreadsheets.google.com/feeds', '1sXj6uSnhyptZjt-gVe4eTMIpnIuGfOyGLI_yjK1MbXc/edit#gid=0']
credentials = ServiceAccountCredentials.from_json_keyfile_name('Crediationals.json', scope)
gc = gspread.authorize(credentials)
# Open the Google Sheet by title
sheet_title = "demosheeteducba"
worksheet = gc.open(sheet_title).sheet1
# Alternatively, open the Google Sheet by URL
sheet_url = "https://docs.google.com/spreadsheets/d/your_spreadsheet_id/edit"
worksheet = gc.open_by_url(sheet_url).sheet1

Specify the URL of the sheet to be accessed.

sheet_url = "https://docs.google.com/spreadsheets/d/your_spreadsheet_id/edit"
worksheet = gc.open_by_url(sheet_url).sheet1

In the above code, we have mentioned the URL of the Google sheet.

Read Data from Google Sheet

Use gspread to retrieve data from the Google Sheet using the get_all_values() method

Follow the following steps to retrieve data from the Google Sheet:

Step 1: Authenticate and Access Google Sheets (if not already done)

Ensure you have already authenticated and accessed your Google Sheet as described in the previous article steps.

Step 2: Read Data and Convert to DataFrame:

You can use the get_all_values() method to retrieve data from a Google Sheet and convert it into a Pandas DataFrame. Here’s how you can do it:

import gspread
import pandas as pd
from oauth2client.service_account import ServiceAccountCredentials
scope = ['https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive']
credentials = ServiceAccountCredentials.from_json_keyfile_name('Crediationals.json', scope)
gc = gspread.authorize(credentials)
)
sheet_title = "demosheeteducba"
worksheet = gc.open(sheet_title).sheet1
# Retrieve data from the Google Sheet
data = worksheet.get_all_values()
# Convert the data into a pandas DataFrame
df = pd.DataFrame(data[1:], columns=data[0])

Convert the retrieved data into a suitable data structure, like a Pandas DataFrame, for further analysis.

import gspread
import pandas as pd
from oauth2client.service_account import ServiceAccountCredentials
# Authenticate with OAuth2.0 credentials
scope = ['https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive']
credentials = ServiceAccountCredentials.from_json_keyfile_name('Crediationals.json', scope)
gc = gspread.authorize(credentials)
# Open the Google Sheet by title or URL (as shown in previous responses)
sheet_title = "demosheeteducba"
worksheet = gc.open(sheet_title).sheet1
# Retrieve data from the Google Sheet
data = worksheet.get_all_values()
# Convert the data into a pandas DataFrame
df = pd.DataFrame(data[1:], columns=data[0])

Write Data to Google Sheet

Open the Google Sheets website and look for a new sheet.

Append the list to a Google Sheet by Python

Now, we have a newly created Google sheet with us. Let us see how to update the sheet and append data to it.

Step 1: First of all, create the new Python file. Update.py and paste the following code into it.

from __future__ import print_function
from auth import spreadsheet_service  # Import the spreadsheet service.
from auth import drive_service  # Import the drive service.
def read_range():
    range_name = 'Sheet1!A1:H8'  # Correct the range name.
    spreadsheet_id = 'your_spreadsheetID'  # Replace with your actual spreadsheet ID.
    result = spreadsheet_service.spreadsheets().values().get(
        spreadsheetId=spreadsheet_id, range=range_name).execute()
    rows = result.get('values', [])    
    print('{0} rows retrieved.'.format(len(rows))
    print('{0} rows retrieved.'.format(rows))
    return rows
def write_range():
    spreadsheet_id = 'your_spreadsheetID'  # Replace with your actual spreadsheet ID.
    range_name = 'Sheet1!A1:H3'  # Correct the range name.
    values = [
        ['John', 'Dubai', '20', 'Male', 'oxford', 'Britain', '7757858957', '[email protected]'],
        ['Alexa', 'Paul', '30', 'Female', 'London', 'Britain', '7875825624', '[email protected]'],
        ['Shane, 'Phillipse', '25', 'Female', 'Cambridge', 'Britain', '9420664141', '[email protected]'],
    ]
    value_input_option = 'USER_ENTERED'
    body = {
        'values': values
    }
    result = spreadsheet_service.spreadsheets().values().update(
        spreadsheetId=spreadsheet_id, range=range_name,
        valueInputOption=value_input_option, body=body).execute()
    print('{0} cells updated.'.format(result.get('updatedCells'))
if __name__ == '__main__':
    write_range()
    read_range()

Step 2: Here are the changes in the Google sheet.

Provide the range or row where the data needs to be updated and the values to be written

To write data to a specific cell or row in a Google Sheet using the gspread library in Python, you can use either the update() method or the append_row() method, depending on your specific use case.

Using update() Method:

The update() method lets you update a specific cell or range in a worksheet. Here’s how you can use it:

import gspread
from oauth2client.service_account import ServiceAccountCredentials
# Authenticate with OAuth2.0 credentials (as discussed in previous responses)
scope = ['https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive']
credentials = ServiceAccountCredentials.from_json_keyfile_name(educba63294-10d6f0746b07.json', scope)
gc = gspread.authorize(credentials)
# Open the Google Sheet by title or URL (as shown in previous responses)
sheet_title = "educba_spreadsheet_demo"
worksheet = gc.open(sheet_title).sheet1
# Specify the range to update (e.g., cell A1)
cell_range = 'A1'
# Define the value you want to write
value_to_write = 'Lvan'
# Update the cell with the new value
worksheet.update_acell(cell_range, value_to_write)

You can see the changed values in the Google sheet.

Filtering and Sorting Data

You can filter data in a DataFrame to extract specific rows or subsets of the data based on certain conditions.

Here is the syntax for filtering the data.

Assuming you have a DataFrame named ‘df’

# Filter rows based on a condition (e.g., column 'Age' greater than 30)
filtered_data = df[df['Age'] > 30]
# You can apply multiple conditions using '&' for 'and' and '|' for 'or'
filtered_data = df[(df['Age'] > 30) & (df['Gender'] == 'Male')]
# Assuming you have a DataFrame named 'df'
# Sort by a single column in ascending order
sorted_data = df.sort_values(by='Column_Name')
# Sort by a single column in descending order
sorted_data = df.sort_values(by='Column_Name', ascending=False)
# Sort by multiple columns in a specific order
sorted_data = df.sort_values(by=['Column1', 'Column2'], ascending=[True, False])
# Reset the index after sorting (optional)
sorted_data = sorted_data.reset_index(drop=True)

Data Visualization

Data visualization is crucial to data analysis, and Python offers several powerful libraries for creating visualizations. Two commonly used libraries for data visualization are Matplotlib and Seaborn.

1. Matplotlib

Matplotlib is a versatile library for creating a wide range of static, animated, and interactive visualizations.

pip install matplotlib

Example:

import matplotlib.pyplot as plt
# Sample data
x = [1, 2, 3, 4, 5]
y = [10, 15, 13, 18, 16]
# Create a simple line plot
plt.plot(x, y)
# Add labels and a title
plt.xlabel('X-axis')
plt.ylabel('Y-axis')
plt.title('Simple Line Plot')
# Show the plot
plt.show()

Output:

2. Seaborn

Simple syntax for seaborn:

pip install seaborn

To run Seaborn, first install the Seaborn library

Example:

import matplotlib.pyplot as plt
import seaborn as sns
# x axis values
x =['Jan', 'Feb', 'Mar', 'April', 'May', 'June', 'July','Aug','Sept','Oct','Nov','Dec']
# y axis values
y =[5,8,7,4,9,6, 2, 4.5, 1,7,3,2.5]
# plotting strip plot with seaborn
ax = sns.stripplot(x, y);
# giving labels to x-axis and y-axis
ax.set(xlabel ='Month', ylabel ='Amount_spend in Thousands')
# giving title to the plot
plt.title('2023 Expense chart');
# function to show plot
plt.show()

Output:

These are basic examples of creating visualizations using Matplotlib and Seaborn. You can explore and customize various plot types, styles, and features these libraries offer to create informative and visually appealing data visualizations for your specific data analysis needs.

Performing Calculations and Data Analysis

Utilize Google Sheets built-in functions and formulas: worksheet.update(‘C1’, ‘=A1+B1’)

import gspread
from oauth2client.service_account import ServiceAccountCredentials
# Authenticate and open the Google Sheet as shown in previous responses
scope = ['https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive']
credentials = ServiceAccountCredentials.from_json_keyfile_name('educba63294-10d6f0746b07.json', scope)
gc = gspread.authorize(credentials)
# Open the Google Sheet by title or URL (as shown in previous responses)
sheet_title = "educba_demoadd"
worksheet = gc.open(sheet_title).sheet1
# Apply a Google Sheets formula to calculate the sum of values from columns A and B and update cell C1
formula = "=SUM(A1:B1)"
worksheet.update('C1', formula)

Let’s see:

Use pandas DataFrame methods for calculations and analysis: mean_value = df[‘column_name’].mean()

import pandas as pd
# Assuming you have a DataFrame named 'df'
# Calculate the mean of a specific column
mean_value = df['column_name'].mean()
# Other data analysis operations you can perform with pandas include sum, median, standard deviation, and more.
# Example: sum_value = df['column_name'].sum()

Replace ‘column_name’ with the column name for which you want to calculate the mean.

Using Pandas, you can perform various data analysis tasks, including descriptive statistics, filtering, grouping, and much more, depending on your specific data analysis needs.

Conclusion – Google Sheets with Python

Performing calculations and data analysis is a fundamental aspect of data manipulation. Google Sheets provides a user-friendly interface with built-in functions and formulas, while Python’s Panda library offers extensive capabilities for programmatically processing data. Choosing the right tool depends on the specific needs and preferences of the analysis, with both options enabling effective data exploration and insight generation.

FAQs

Q1. What libraries are commonly used for Google Sheets integration in Python?

Ans: Common libraries include gspread, pygsheets, and the Google API client library.

Q2. How can I authenticate my Python script to access Google Sheets?

Ans: You can authenticate by creating OAuth 2.0 credentials in the Google Cloud Console and using them in your script.

Q3. How do I read data from a Google Sheet into a Python data frame?

Ans: Use the chosen library (e.g., gspread) to fetch data from the sheet and convert it to a DataFrame.

Q4. How can I perform calculations or data analysis with the data from Google Sheets in Python?

Ans: After loading data into a DataFrame, you can use Pandas methods for calculations, analysis, and data manipulation.

Q5. Is it possible to visualize data from Google Sheets in Python?

Ans: Yes, you can use libraries like Matplotlib and Seaborn to create data visualizations based on your Google Sheets data in Python.

Q6. How can I handle authentication securely when working with Google Sheets in Python?

Ans: Protect your credentials file and consider using environment variables to store sensitive information.

Quiz Result
Total Questions	Correct Answers	Wrong Answers	Percentage