How to Append Data in a Google Sheet with Python

by
5/5 - (1 vote)

Appending data to a Google Sheet using Python can be achieved using the gspread library. Here’s a step-by-step guide to help you append data to a Google Sheet:

Step 1: Set up Google Sheets API

  • Go to the Google Developers Console.
  • Create a new project.
  • Enable the Google Sheets API for the project.
  • Create service account credentials.
  • Download the JSON key for these credentials.
  • Share your Google Sheet with the email address associated with the service account (you can find this in the JSON key).

Step 2: Install GSpread Library and Authentication

Run the following in your environment (e.g., Windows, Linux, macOS terminal/console/shell):

pip install gspread oauth2client
  1. gspread: A Python library that provides a simple interface to interact with Google Sheets, allowing for reading, writing, and modifying spreadsheet data.
  2. oauth2client: A library for OAuth 2.0 client-side authentication, which is commonly used to grant access to Google APIs.

💡 Note: For newer projects, you might want to consider using the google-auth and google-auth-oauthlib libraries instead.

Step 3: Python Script

The following script is designed to add data to a Google Sheet using Python.

It first sets up the necessary tools and permissions by importing specific libraries and defining the scope of access it requires, such as reading and writing to Google Sheets and accessing Google Drive.

The script then authenticates itself using a service account’s credentials, which are loaded from a provided JSON file. Once authenticated, it accesses a specified Google Sheet and its first tab.

The script then defines a sample set of data, which it subsequently appends to the bottom of the accessed sheet. After successfully adding the data, the script confirms the action by displaying a success message.

import gspread
from oauth2client.service_account import ServiceAccountCredentials

# Set up the credentials
scope = ["https://spreadsheets.google.com/feeds", "https://www.googleapis.com/auth/spreadsheets", "https://www.googleapis.com/auth/drive.file", "https://www.googleapis.com/auth/drive"]
creds = ServiceAccountCredentials.from_json_keyfile_name('path_to_service_account_key.json', scope)
client = gspread.authorize(creds)

# Open the Google Sheet using its name
sheet = client.open("Your Google Sheet Name").sheet1

# Data to append
data = ["John", "Doe", 25]

# Append the data
sheet.append_row(data)

print("Data appended successfully!")

Replace 'path_to_service_account_key.json' with the path to your downloaded JSON key and "Your Google Sheet Name" with the name of your Google Sheet.

Step 4: Run the script

Execute the Python script. It will append the data to the Google Sheet.

💡 Recommended: How to Run a Python Script?

Remember, every time you want to append new data, you can modify the data list in the script and run it again.