Skip to content

Instantly share code, notes, and snippets.

@steveblamey
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
Embed
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.
Prerequisites:
* 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 = gc.open('contacts')
# Get an instance of the first (only) worksheet
worksheet = sh.get_worksheet(0)
# Empty the worksheet
worksheet.clear()
# Add the new data
worksheet.append_row(labels)
worksheet.append_rows(rows)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment