How to Upload CSV Files Into Google Sheets Using Python

Uploading data programmatically from a CSV file to Google Sheets can be a powerful way to automate data ingestion processes. Python, with its versatile libraries, offers a seamless way to achieve this. Here’s a step-by-step guide:

Setting up the Google Sheets API:

• Go to Google Developers Console.
• Create a new project.
• Enable the Google Sheets API for your project.
• Create credentials (OAuth 2.0 client ID) for a Desktop App.
• Download the credentials.json file.

Installing Required Libraries:

• You will need the gspread and oauth2client libraries. You can install them using pip:

pip install gspread oauth2client

Authenticating and Uploading the CSV to Google Sheets:

• Use the gspread library to authenticate and interface with Google Sheets.
• The oauth2client library will help in handling the OAuth2 authentication.

Here’s a Python script to do this:

import gspread
from oauth2client.service_account import ServiceAccountCredentials
import csv

# Set up the Google Sheets API 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_credentials.json’, scope)
client = gspread.authorize(creds)

# Create a new Google Sheet (or you can open an existing one)
sheet = client.create(‘Name_Of_Your_Sheet’).sheet1

# Read the CSV file and upload data to the Google Sheet
with open(‘your_file.csv’, ‘r’, encoding=’utf-8′) as file_obj:
csv_reader = csv.reader(file_obj)
for row in csv_reader:
sheet.append_row(row)

Replace ‘path_to_credentials.json’ with the path to your downloaded credentials.json file, and ‘your_file.csv’ with the path to your CSV file. Adjust ‘Name_Of_Your_Sheet’ to whatever name you want for the Google Sheet.

Note: This script creates a new Google Sheet every time it runs. If you want to append to an existing sheet, you can replace the client.create(‘Name_Of_Your_Sheet’).sheet1 line with code to open an existing sheet.

Also, remember that Google Sheets has a limit on the number of cells, so ensure that your CSV does not exceed that limit.

Conclusion:

Uploading a CSV to Google Sheets using Python simplifies data management tasks. While the initial setup might take a few minutes, the ease of use and automation capabilities offered by this method make it a worthwhile investment. Whether it’s for personal projects, data backups, or business analytics, this approach can save time and reduce manual data entry errors.