5 Best Ways to Import CSV into Google Sheets with Python

by

💡 Problem Formulation: Transferring data from local CSV files to Google Sheets is a common task for individuals looking to collaborate, share, or simply leverage Google Sheets’ capabilities. The typical input would be a CSV file with structured data that users want to see represented in Google Sheets’ tabular format as the desired output. This article outlines five methods to achieve this using Python.

Method 1: Using Google Sheets API v4

One robust method involves using the Google Sheets API v4 to read CSV files with Python and write the data to a Google Sheet. The API permits granular control and comes with the benefit of direct integration with the whole Google ecosystem.

Here’s an example:

from google.oauth2.service_account import Credentials
from googleapiclient.discovery import build
import csv

# Authenticate and build service
creds = Credentials.from_service_account_file('service_account.json')
service = build('sheets', 'v4', credentials=creds)

# Read CSV data
with open('file.csv', 'r') as file:
    csv_reader = csv.reader(file)
    rows = list(csv_reader)

# Write data to Google Sheet
spreadsheet_id = 'YOUR_SPREADSHEET_ID'
range_name = 'Sheet1!A1'
body = {'values': rows}
request = service.spreadsheets().values().update(spreadsheetId=spreadsheet_id, range=range_name, valueInputOption='RAW', body=body)
response = request.execute()

The data from file.csv is inserted into the specified Google Sheet.

This code snippet uses the Google Sheets API to update the contents of a Google Sheet with the data from a CSV file. The update method is used within the API to insert the CSV data, and it requires proper authentication and permissions, which are handled by the service account credentials.

Method 2: Using gspread Library

The gspread library is a Python API for Google Sheets that simplifies the process of reading from and writing to Google Sheets. It handles authentication neatly and provides a more straightforward API compared to the native Google Sheets API.

Here’s an example:

import gspread
from oauth2client.service_account import ServiceAccountCredentials
import csv

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

# Open Google Sheet
sheet = client.open("MySpreadsheet").sheet1

# Read CSV and update
with open('file.csv', 'r') as file:
    csv_reader = csv.reader(file)
    for row in csv_reader:
        sheet.append_row(row)

CSV data from file.csv is appended to the Google Sheet named ‘MySpreadsheet’.

This snippet reads the CSV file and appends each row to the end of the Google Sheet. The gspread library handles the process of connecting to the Google Sheets API and provides easy methods for manipulating sheets like append_row.

Method 3: Using Pandas and Google Auth

Pandas, a powerful data manipulation library, can read CSV files and handle data frames effectively. In conjunction with Google auth libraries, it’s possible to authenticate and write the data frame directly to Google Sheets.

Here’s an example:

import pandas as pd
from google.oauth2.service_account import Credentials
from gspread_pandas import Spread, Client

# Authenticate
creds = Credentials.from_service_account_file('service_account.json')
client = Client(scope=['https://spreadsheets.google.com/feeds'], creds=creds)

spread = Spread('MySpreadsheet', client=client)

# Read CSV into DataFrame
df = pd.read_csv('file.csv')

# Update Google Sheet with DataFrame
spread.df_to_sheet(df, index=False)

The DataFrame created from file.csv is now reflected in the ‘MySpreadsheet’ Google Sheet.

In this code, the df_to_sheet method from the gspread_pandas library transfers the data from a Pandas DataFrame to a Google Sheet. This method is very powerful as it combines the fluidity of Pandas’ data manipulation with the accessibility of Google Sheets.

Method 4: Using csv and pygsheets Library

The pygsheets library offers a more Pythonic way to work with Google Sheets, providing objects and methods that feel more native to Python developers. It blends straightforward authentication with ease of use.

Here’s an example:

import pygsheets
import csv

# Authenticate
gc = pygsheets.authorize(service_file='service_account.json')

# Open Google Sheet and the first worksheet
sh = gc.open('MySpreadsheet')
wks = sh[0]

# Read CSV and update
with open('file.csv', 'r') as file:
    csv_reader = csv.reader(file)
    wks.set_values('A1', list(csv_reader))

Data from file.csv is updated starting from cell ‘A1’ of the first worksheet in ‘MySpreadsheet’.

By reading the CSV into a list and using the set_values method of pygsheets, users can quickly upload entire chunks of data to a Google Sheet efficiently.

Bonus One-Liner Method 5: Using CLI with gsheet-cli

For command-line aficionados, gsheet-cli offers a quick one-liner to update Google Sheets with CSV contents, assuming gsheet-cli is already installed and configured on your system.

Here’s an example:

gsheet-cli update MySpreadsheet --file file.csv

This command updates ‘MySpreadsheet’ with the data from ‘file.csv’.

This method is very minimalistic but requires the user to have the gsheet-cli tool set up in advance. However, it’s a quick and straightforward way to achieve the task without writing a script.

Summary/Discussion

  • Method 1: Google Sheets API v4. Highly customizable. Can be complex to set up.
  • Method 2: gspread Library. Simplifies authentication and sheet manipulation. Less granular control than the native API.
  • Method 3: Pandas and Google Auth. Powerful data manipulation. Requires knowledge of Pandas library.
  • Method 4: csv and pygsheets Library. Pythonic and easy to use. More limited functionality compared to Google Sheets API.
  • Bonus Method 5: CLI with gsheet-cli. Fast and minimalistic. Requires prior setup and comfort with the command line.