Skip to content

Instantly share code, notes, and snippets.

@steveblamey
Created March 11, 2022 15:27
Show Gist options
  • Save steveblamey/02dcc8526328734d6d4a3ce10da4c7c2 to your computer and use it in GitHub Desktop.
Save steveblamey/02dcc8526328734d6d4a3ce10da4c7c2 to your computer and use it in GitHub Desktop.
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