Last active
January 6, 2020 03:26
-
-
Save cunla/ecd20504acd8e2cd26e4317d34966aec to your computer and use it in GitHub Desktop.
Connect to google sheets and read data from spreadsheet
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
from __future__ import print_function | |
import pickle | |
import os.path | |
from io import StringIO | |
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'] | |
SPREADSHEET_ID = 'xxxx' | |
SPREADSHEET_RANGE = 'Sheet!A:A' | |
class NoDataFoundError(Exception): | |
pass | |
def _build_google_client(): | |
""" | |
Build a google api client | |
:return: a google api client | |
""" | |
creds = None | |
# The file token.pickle stores the user's access and refresh tokens, and is | |
# created automatically when the authorization flow completes for the first | |
# time. | |
if os.path.exists('token.pickle'): | |
with open('token.pickle', 'rb') as token: | |
creds = pickle.load(token) | |
# If there are no (valid) credentials available, let the user log in. | |
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) | |
# Save the credentials for the next run | |
with open('token.pickle', 'wb') as token: | |
pickle.dump(creds, token) | |
return build('sheets', 'v4', credentials=creds) | |
def read_spreadsheet_as_csv(spreadsheet_id: str, range: str) -> StringIO: | |
""" | |
Read a range from google spreadsheet and return it as csv | |
:param spreadsheet_id spreadsheet ID | |
:param range range to read | |
:return csv of the range | |
""" | |
service = _build_google_client() | |
sheet = service.spreadsheets() | |
result = sheet.values().get(spreadsheetId=spreadsheet_id, range=range).execute() | |
values = result.get('values', []) | |
if not values: | |
raise NoDataFoundError() | |
io_str = StringIO() | |
for row in values: | |
io_str.write(','.join(row) + '\n') | |
io_str.flush() | |
return io_str | |
if __name__ == '__main__': | |
file_str = read_spreadsheet_as_csv(SPREADSHEET_ID, SPREADSHEET_RANGE) | |
print(file_str.getvalue()) |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment