Skip to content

Instantly share code, notes, and snippets.

Created March 11, 2022 15:27
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
Star You must be signed in to star a gist
What would you like to do?
Example using the gspread python API for Google Sheets
"""Example using the gspread python API for Google Sheets
This code demonstrates how data can be acquired from a PosgreSQL database
and put into a Google Sheet.
* A project on GCP with access to the Google Sheets API and Google Drive API
* A service account in the project
* A spreadsheet in Drive, shared with the service account
import gspread
import psycopg2
from oauth2client.service_account import ServiceAccountCredentials
DSN = "host=localhost dbname=sdl user=ooo-data password=data123"
# Get data from postgres
with psycopg2.connect(DSN) as connection:
with connection.cursor() as cursor:
cursor.execute("SELECT * FROM reports.addressbook;")
rows = cursor.fetchall()
cursor.execute("SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'addressboo>
column_names = cursor.fetchall()
labels = [row[0] for row in column_names]
# Authorize using service account
gc = gspread.service_account('./pgtosheet-343713-0fb065bde239.json')
# Get an instance of the Spreadsheet
sh ='contacts')
# Get an instance of the first (only) worksheet
worksheet = sh.get_worksheet(0)
# Empty the worksheet
# Add the new data
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment