Skip to content

Instantly share code, notes, and snippets.

@leomrocha
Created September 10, 2020 17:36
Show Gist options
  • Save leomrocha/0dceaa4b21da36096697b02617a8d99a to your computer and use it in GitHub Desktop.
Save leomrocha/0dceaa4b21da36096697b02617a8d99a to your computer and use it in GitHub Desktop.
Google Sheet example for credential encryption modification
# Modified to add cryptography from the spreadsheet example:
# https://developers.google.com/sheets/api/quickstart/python
# https://github.com/gsuitedevs/python-samples/blob/master/sheets/quickstart/quickstart.py
#
# Copyright 2018 Google LLC
#
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
#
# http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.
# [START sheets_quickstart]
from __future__ import print_function
import pickle
from cryptography.fernet import Fernet
import os.path
from googleapiclient.discovery import build
from google_auth_oauthlib.flow import InstalledAppFlow
from google.auth.transport.requests import Request
# If modifying these scopes, delete the file token.pickle.
SCOPES = ['https://www.googleapis.com/auth/spreadsheets.readonly']
# The ID and range of a sample spreadsheet.
SAMPLE_SPREADSHEET_ID = '1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms'
SAMPLE_RANGE_NAME = 'Class Data!A2:E'
def load_decrypt_unpkl(fname, encrypt_key, protocol=3):
"""
:param fname: file to load and decode
:param encrypt_key: encryption key
:param protocol: default=3 pickle protocol version, using 3 as the version 4 is default in python3.8 and 3 previous to py3.8 while GitLab
uses 3.7 for the build
:returns: Google Credentials object
"""
decryptor = Fernet(encrypt_key)
with open(fname, 'rb') as enc_token:
pkl_token = decryptor.decrypt(enc_token.read())
token = pickle.loads(pkl_token)
return token
def pkl_encrypt_save(gcreds, fname, encrypt_key,protocol=3):
"""
As the Google Credentials object is not easy to handle, the easiest way to do it is using a pickled version
Encryption is used to save
:param gcreds: google credentials
:param encrypt_key: encryption key
:param protocol: default=3 pickle protocol version, using 3 as the version 4 is default in python3.8 and 3 previous to py3.8 while GitLab
uses 3.7 for the build
:returns: pickled and encrypted
"""
encoder = Fernet(encrypt_key)
pkl_creds = pickle.dumps(gcreds, protocol=protocol)
enc_pkl_creds = encoder.encrypt(pkl_creds)
with open(fname, 'wb') as enc_token:
enc_token.write(enc_pkl_creds)
return enc_pkl_creds
# THIS SHOULD BE DONE SECURELY, here is just as an example:
ENC_KEY=b'6SyMleooZS64_qb9dnhH63cGDoFl2-Yc0z0otmCBabQ='
def main():
"""Shows basic usage of the Sheets API.
Prints values from a sample spreadsheet.
"""
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'):
creds = load_decrypt_unpkl('token.pickle', ENC_KEY)
# 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
pkl_encrypt_save(creds, 'token.pickle', ENC_KEY)
service = build('sheets', 'v4', credentials=creds)
# Call the Sheets API
sheet = service.spreadsheets()
result = sheet.values().get(spreadsheetId=SAMPLE_SPREADSHEET_ID,
range=SAMPLE_RANGE_NAME).execute()
values = result.get('values', [])
if not values:
print('No data found.')
else:
print('Name, Major:')
for row in values:
# Print columns A and E, which correspond to indices 0 and 4.
print('%s, %s' % (row[0], row[4]))
if __name__ == '__main__':
main()
# [END sheets_quickstart]
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment