Example using the gspread python API for Google Sheets

gsheet-contacts.py

"""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)