Google Sheets Demo

.gitignore

bin
include
lib
local
share
*.pyc
token.pickle

.GSHEETS_DEMO.md

howto

• https://developers.google.com/sheets/api/quickstart/python

• export BROWSER=google-chrome # avoid Ubuntu error

• create new Google Sheet with #{sheet_id} in url

https://docs.google.com/spreadsheets/d/1tXj5jCVBudPdqa3IyjX0pyfZk9s6WktC9zoiS8PEWFo/edit#gid=0

• [quickstart.py] SAMPLE_SPREADSHEET_ID=#{sheet_id}

• add Hello tab to sheet

• add random data in Hello!A1:B2

• [quickstart.py] SAMPLE_RANGE_NAME=Hello!A1:B2

run

justin@justin-XPS-13-9360:~/work$ python quickstart.py 
[['one', 'two'], ['three', 'four']]

[may be redirected via browser for auth]

notes

• auth seems to be a different scheme than used by translate API
• eg contents of credentials.json seem to be different (and simpler) than those used by translate API
• don't seem to need to get GOOGLE_APPLICATION_CREDENTIALS

credentials.json

{"installed":{"client_id":"157484129342-aukr2e8il4jb6rnv8hkkinhrp3a8ujun.apps.googleusercontent.com","project_id":"quickstart-1574781012278","auth_uri":"https://accounts.google.com/o/oauth2/auth","token_uri":"https://oauth2.googleapis.com/token","auth_provider_x509_cert_url":"https://www.googleapis.com/oauth2/v1/certs","client_secret":"xziXZApQS7gUoF4o2jHa8Kn6","redirect_uris":["urn:ietf:wg:oauth:2.0:oob","http://localhost"]}}

INSTALL.md

• virtualenv -p /usr/bin/python3.6 .
• source bin/activate
• pip install -r requirements.txt
• {...}
• deactivate

quickstart.py

from __future__ import print_function

import pickle
import os.path

from googleapiclient.discovery import build
from google_auth_oauthlib.flow import InstalledAppFlow
from google.auth.transport.requests import Request

SCOPES=['https://www.googleapis.com/auth/spreadsheets.readonly']

# https://docs.google.com/spreadsheets/d/1tXj5jCVBudPdqa3IyjX0pyfZk9s6WktC9zoiS8PEWFo/edit#gid=0

SAMPLE_SPREADSHEET_ID="1tXj5jCVBudPdqa3IyjX0pyfZk9s6WktC9zoiS8PEWFo"

SAMPLE_RANGE_NAME='Hello!A1:B2'

def main():
    creds=None
    if os.path.exists('token.pickle'):
        with open('token.pickle', 'rb') as token:
            creds=pickle.load(token)
    if not creds or not creds.valid:
        if creds and creds.expired and creds.refresh_token:
            creds.refresh(Request())
        else:
            flow=InstalledAppFlow.from_client_secrets_file('credentials.json',
                                                           SCOPES)
            creds=flow.run_local_server(port=0)
        with open('token.pickle', 'wb') as token:
            pickle.dump(creds, token)
    service=build('sheets', 'v4', credentials=creds)
    sheet=service.spreadsheets()
    result=sheet.values().get(spreadsheetId=SAMPLE_SPREADSHEET_ID,
                              range=SAMPLE_RANGE_NAME).execute()
    print (result.get('values', []))

if __name__ == '__main__':
    main()

requirements.txt

google-api-python-client
google-auth-httplib2
google-auth-oauthlib

setenv.sh

#!/usr/bin/env bash

export BROWSER=google-chrome
export PYTHONPATH=.