def __init__(self):
# Initial _auth
start = perf_counter()
self._auth = gspread.service_account_from_dict(
json.loads(os.environ['GOOGLE_SA_JSON']))
end = perf_counter()
print(f'Time to load auth: {end - start}s')
# Open workbooks
start = perf_counter()
self.bpdia_workbook = self._auth.open_by_key(
os.environ['SPREADSHEET_ID'])
self.inv_workbook = self._auth.open_by_key(
os.environ['INV_SPREADSHEET_ID'])
end = perf_counter()
print(f'Time to load workbooks (2): {end - start}s')
# Open individual sheets
start = perf_counter()
self.char_sheet = self.bpdia_workbook.worksheet('Characters')
self.log_sheet = self.bpdia_workbook.worksheet('Log')
self.log_archive = self.bpdia_workbook.worksheet('Archive Log')
self.arenas_sheet = self.bpdia_workbook.worksheet('Arenas')
self.adventures_sheet = self.bpdia_workbook.worksheet('Adventures')
end = perf_counter()
print(f'Time to load sheets (5): {end - start}s')
def get_gspread_downloader():
# Get Google creds to use
google_creds_json = current_app.config.get(
'GOOGLE_SERVICE_ACCOUNT_CREDENTIALS_JSON')
google_creds_file = current_app.config.get(
'GOOGLE_SERVICE_ACCOUNT_CREDENTIALS_FILE')
if not google_creds_json and not google_creds_file:
current_app.logger.error(
"Google service account credentials or cred file are not set")
return
try:
if google_creds_json:
if not isinstance(google_creds_json, dict):
google_creds_json = json.loads(google_creds_json)
downloader = gspread.service_account_from_dict(google_creds_json)
elif google_creds_file:
downloader = gspread.service_account(filename=google_creds_file)
return downloader
except ValueError as e:
current_app.logger.error(e)
return
except TypeError:
current_app.logger.error(
"An error occured when trying to parse credentials")
return
except FileNotFoundError as e:
current_app.logger.error(f"{e}")
return
def __init__(self, bot: commands.Bot) -> None:
self.bot = bot
self.session = AO3.Session(Config.ao3Username, Config.ao3Password)
self.colour = Colour.green()
self.ignore = [line for line in open(ignorePath, "r").readlines()]
self.quoteSearcher = None
tempWorksheet = gspread.service_account_from_dict(Config.serviceAccount).open("Life Is Strange Read Fanfictions").worksheet("Life Is Strange Read Fanfictions").get_all_values()[2:]
self.worksheetArray = tempWorksheet[:tempWorksheet.index([""]*11)]
def __init__(self, bot):
self.bot = bot
self.records = []
self.check.start()
self.update.start()
self.gc = gspread.service_account_from_dict(credentials)
self.sheet = self.gc.open('Discord Announcements')
self.worksheet = self.sheet.get_worksheet(0)
def gspread_factory():
if Settings.DEV_ENVIRONMENT:
gs = gspread.service_account('gspread_key.json')
else:
GSPRED_JSON_KEY_DICT = json.loads(
base64.b64decode(Settings.GSPREAD_B64_KEY).decode())
gs = gspread.service_account_from_dict(GSPRED_JSON_KEY_DICT)
logger.info('Gspread have connected')
return gs
def insert_row(event, performer, parent_email, performer_email, piece_name,
composer, piece_length, questions):
with open('client_secret.json') as f:
credentials = json.load(f)
credentials.update({'private_key': os.environ.get('GOOGLE_API_KEY')})
client = gspread.service_account_from_dict(credentials)
sheet = client.open("Virtual Live Concert Signups - Website").sheet1
(days, hours, minutes, seconds) = days_hours_minutes_seconds(piece_length)
seconds = "{0:0=2d}".format(seconds)
values = [
event.get_date(), performer, "", "", parent_email, performer_email,
composer + " - " + piece_name,
str(minutes) + ":" + str(seconds), questions
]
sheet.append_row(values)
def connect_to_google_spreadsheets(auth_type='file', credentials=None):
"""
This function creates a connection to google spreadsheets using the service account. To access a certain
document the file must be explicitely shared with this service account via the mail under
client_email in the 'service_account.json'.
:return: connection: Returns the connection to google spreadsheets.
"""
logger.info('Start connect_to_google_spreadsheets()')
if auth_type == 'file':
conn = gspread.service_account(filename=PATH_GOOGLE_SERVICE_ACCOUNT)
elif auth_type == 'env':
conn = gspread.service_account_from_dict(credentials)
return conn
def setup_gsheet(
self,
configuration: Configuration,
gsheet_auth: Optional[str],
spreadsheet_test: bool,
no_spreadsheet: bool,
) -> Optional[str]:
"""Open the various Google spreadsheets. The Datasets with Issues spreadsheet
is for output. The HDX Data Partnerships Team Duty Roster and DataGrid Curation
filters spreadsheets are for input.
Args:
configuration (Configuration): Configuration object
gsheet_auth (Optional[str]): Google Sheets authorisation
spreadsheet_test (bool): Output to test Google spreadsheet
no_spreadsheet (bool): Don't output to Google spreadsheet
Returns:
Optional[str]: Error message or None
"""
if not gsheet_auth:
return "No GSheet Credentials!"
try:
info = json.loads(gsheet_auth)
scopes = ["https://www.googleapis.com/auth/spreadsheets"]
gc = gspread.service_account_from_dict(info, scopes=scopes)
if spreadsheet_test: # use test not prod spreadsheet
issues_spreadsheet = configuration[
"test_issues_spreadsheet_url"]
else:
issues_spreadsheet = configuration[
"prod_issues_spreadsheet_url"]
logger.info("Opening duty officers gsheet")
self.dutyofficers_spreadsheet = gc.open_by_url(
configuration["dutyofficers_url"])
logger.info("Opening datagrids gsheet")
self.datagrids_spreadsheet = gc.open_by_url(
configuration["datagrids_url"])
if not no_spreadsheet:
logger.info("Opening issues gsheet")
self.issues_spreadsheet = gc.open_by_url(issues_spreadsheet)
else:
self.issues_spreadsheet = None
except Exception as ex:
return str(ex)
return None
def __init__(self, bot):
self.bot = bot
self.weapons_map = dict()
self.armor_map = dict()
self.consumable_map = dict()
self.scroll_map = dict()
self.wondrous_map = dict()
try:
self.drive = gspread.service_account_from_dict(
json.loads(os.environ['GOOGLE_SA_JSON']))
self.inv_sheet = self.drive.open_by_key(
os.environ["INV_SPREADSHEET_ID"])
self.build_maps()
except Exception as E:
print(f'Exception: {type(E)} when trying to use service account')
print(f'Cog \'Items\' loaded')
def db():
try:
gc = gspread.service_account() # dev
except FileNotFoundError:
SERVICE_ACCOUNT_CREDENTIALS = os.getenv("SERVICE_ACCOUNT_CREDENTIALS")
if SERVICE_ACCOUNT_CREDENTIALS == None:
raise RuntimeError("Missing service account credentials")
credentials = json.loads(SERVICE_ACCOUNT_CREDENTIALS)
gc = gspread.service_account_from_dict(credentials)
sheet = gc.open("gspreaddict").sheet1
if sheet == None:
raise RuntimeError("Missing sheet")
db = gspreaddb.GspreadDB(sheet)
return db
def __init__(self, bot):
# Setting up some objects
self.bot = bot
try:
self.drive = gspread.service_account_from_dict(
json.loads(os.environ['GOOGLE_SA_JSON']))
self.bpdia_sheet = self.drive.open_by_key(
os.environ['SPREADSHEET_ID'])
self.char_sheet = self.bpdia_sheet.worksheet('Characters')
self.log_sheet = self.bpdia_sheet.worksheet('Log')
self.log_archive = self.bpdia_sheet.worksheet('Archive Log')
except Exception as E:
print(E)
print(f'Exception: {type(E)} when trying to use service account')
# self.user_map = self.build_user_map()
# self.get_asl()
print(f'Cog \'BPdia\' loaded')
def save_data_to_gspread(filename, data):
current_data = data
try:
credentials = json.loads(os.environ['GOOGLE_KEY'])
gc = gspread.service_account_from_dict(credentials)
sheet = gc.open(filename)
worksheet = sheet.worksheet('raw_data')
worksheed_header = worksheet.row_values(1)
new_row = []
for item in worksheed_header:
if item in current_data:
new_row.append(current_data[item])
else:
new_row.append('')
worksheet.append_row(new_row)
logger.info('Data added')
except gspread.exceptions.SpreadsheetNotFound:
logger.error(
'Spreadsheet not found on Google Drive or the file is not shared with client_email from JSON credentials file'
)
except FileNotFoundError:
logger.error('JSON file with credentials not found')
def load_dataset():
gc = gspread.service_account_from_dict(credentials)
ws = gc.open_by_key(sheetKey).worksheet(dbSheet)
return ws.get_all_records()
parser.add("--spotify-allowed-users", env_var="SPOTIFY_ALLOWED_USERS")
parser.add("--ops-chan-name", env_var="OPS_CHAN_NAME")
opts = parser.parse_args()
parser.print_values()
# init logs
logger_level = getattr(logging, opts.log_level.upper())
logger_format = "%(asctime)s %(name)-20s %(levelname)-8s %(message)s"
logging.basicConfig(level=logger_level, format=logger_format)
# init themes
if opts.gsheet_creds != None:
# get themes from Google Sheet column
gauth_key = json.loads(opts.gsheet_creds)
gauth = gspread.service_account_from_dict(gauth_key)
gsheet = gauth.open_by_key(opts.gsheet_key)
worksheet = gsheet.worksheet(opts.gsheet_worksheet)
worksheet_plist = gsheet.worksheet(opts.gsheet_plist_worksheet)
list_of_themes = gsheet_themes
logging.info("themes from GSheets")
else:
# get the themes from file
with open(opts.theme_file, "r") as f:
file_themes = f.read().splitlines()
def list_of_themes():
return file_themes
logging.info("themes from file {}".format(opts.theme_file))
import gspread # pip install gspread
import json
import os
from dotenv import load_dotenv
load_dotenv() # take environment variables from .env.
#with open('credentials1.json', 'w') as f:
# json.dump(json.loads(os.environ.get('credentials')), f, indent=2)
gc = gspread.service_account_from_dict(
json.loads(os.environ.get('credentials')))
sh = gc.open_by_key('1Y5YxgrpTuVgS_fkC4Te1ouMQbr7koCUIhVgJVBYsb-0')
worksheet = sh.sheet1
import discord
from discord.ext import commands
import numpy as np
import math
import string
alphabets = string.ascii_lowercase
bot = commands.Bot(command_prefix='$')
chars = ['/', ',']
def player_input_one_coord(coord):
error = False
for letter in alphabets:
import datetime
import string
import altair as alt
from altair.vegalite.v4.schema.channels import Color
import gspread
import pandas as pd
import streamlit as st
gc = gspread.service_account_from_dict(st.secrets["gcp_service_account"])
sh = gc.open_by_key("1bWv49xiZ4stjf8V6AnPWnDSyU9rbHe1R62Fw9eKyK98")
inventory_ws = sh.get_worksheet(0)
postpone_ws = sh.get_worksheet(1)
inventory_df = pd.DataFrame(inventory_ws.get_all_records())
inventory_df["row_id"] = inventory_df.index + 2
inventory_df["expiration_date"] = pd.to_datetime(
inventory_df["expiration_date"]).dt.date
inventory_df = inventory_df[inventory_df["servings"] > 0]
postpone_df = pd.DataFrame(postpone_ws.get_all_records())
postpone_df["expiration_date"] = pd.to_datetime(
postpone_df["expiration_date"]).dt.date
postpone_df["postpone_until"] = pd.to_datetime(
postpone_df["postpone_until"]).dt.date
postpone_df = postpone_df[
postpone_df["postpone_until"] > datetime.date.today()]
df = inventory_df.merge(postpone_df,
on=["expiration_date", "content"],
how="left")
def sheet(self):
gc = gspread.service_account_from_dict(self.auth_json)
return gc.open_by_key(self.sheet_key)
from bs4 import BeautifulSoup
credentials = {
'type': os.getenv('type'),
'project_id': os.getenv('project_id'),
'private_key_id': os.getenv('private_key_id'),
'private_key': os.getenv('private_key').replace('\\n', '\n'),
'client_email': os.getenv('client_email'),
'client_id': os.getenv('client_id'),
'auth_uri': os.getenv('auth_uri'),
'token_uri': os.getenv('token_uri'),
'auth_provider_x509_cert_url': os.getenv('auth_provider_x509_cert_url'),
'client_x509_cert_url': os.getenv('client_x509_cert_url')
}
gc = gspread.service_account_from_dict(credentials)
sheet = gc.open('kattis-stats')
stats_table = sheet.worksheet('Stats')
users_table = sheet.worksheet('Users')
Profile = namedtuple('Profile', [
'name', 'uid', 'rank', 'score', 'country', 'country_short', 'university',
'university_short'
])
def _scrape_profile(uid: str) -> Profile:
url = 'https://open.kattis.com/users/{}'.format(uid)
r = requests.get(
url,
headers={
creds = {
"type": "service_account",
"project_id": "pokemonspreadsheet",
"private_key_id": os.environ.get("GPKID"),
"private_key":
f"-----BEGIN PRIVATE KEY-----\n{p1}\n{p2}\n{p3}\n{p4}\n{p5}\nGcg5KJK9nQQSJS8pFuVFB8yaC5hJyuOMuDTgLt+r+hRM1Hih91Uo+bHRMHJlXFHY\nayia8VE3AgMBAAECggEASeqLdg7sf6glX2hTvdKO2QtC5ajO98CsXMFbsCoHsLzN\n4/7or39XV1RbcTMX6NvK03xYlbvef/oDMT44PAdklTrKG4mswuSxzTEN+0VK654H\nsLaUEH0C9PNC7iIQjKsjVIymF+c+/eYZWf5Tjbia/3oZqyLTaOFU+/5nBtWmkeA6\nT9o/zNT1F1N0bGP8803AJzBdcOdMTtwV40PIWefAOPAp6TnybMNiAzGifQ7YdFvM\n8b518oIzVYFcqG2smnFeNhoFtTDqWOXFs7MG1FvblCTawkyxNAOFT/Zrr3zabSDl\ngQ+9NyDQfPrp2Q5DaNlxTr2m3iUuNXGNS7xv5GONwQKBgQDPITNzgMmp/kuGq50/\nxPC9P71vtoa7Wh0OhDXTyFHGX4mUR2kPH4xPur3OGhbD4IfXNW8U2278J1MDkZ5t\napm0MjevAo3x6qY6CI0Xn3kfgyi1RFw6dZhCPcHICP/QWJwjfxTgzpNaURbWy1iL\nIdWHDX9LQ+6wa7BJSZarWIXO1QKBgQDMwrYI/NexGwuVavKWnn8JhpY6Iqa/zDV+\nB2jzDOqFGnwaPMnUHRWmAKBH0eS68h7jUD++ggrtv4jKk+neYcboCEuCvy/2BPK4\nUH9cIt9J53Ov41zIJhxJoCPyKgLMjCeDT4Au0R3w0Tor5AfmkwAqoUP/6I4Qd+NX\nhJbAWf5d2wKBgGZT17BTSs2xtKxGcjxD1k8Yg0UCXpOGVF6MkG8dQtEG99gKY2u2\nQUJOVmDQ4LhKX7HBUyxxr+Xgo5FaynLxvBeay4mpcGi4bQC0oset4E/iIyVSLWIw\nIFVxXx+s2nyamiCrMqxRQdjXwLnC4e5ye6Pp1h5f5DbCLOg387iY1ho1AoGBALKU\n5zqzelLGqbWpTuY6WcWvEfqmGhSRP94pDElebHOmA7LGbaiHrUfYp/G7h+fcH0BV\nzyKSPIHaWK2Fj6UaVeYsxd7EbEn/Ssmhu1JaP7AvIuC2RUdypZ05A+DC7yoC2rtV\nbRBn5dsQP9Hj0VgxwSVs/OeGpdCLH0ZHJ0ufsY+dAoGAHj8eSF9qvS9RzBDMHcqG\nLnYk2qxcCnowksbGYoMWmfPm8GwGPn9bFaEFdQM7QSxKqEIwg5Ln+jlipZ06l38e\nISToTC90DwhtOf7hNNQqufjka39xfAVYoCxseV1MTFfNyAsyKIOPgG9OLg0RJoYn\nAI98hrLde4Xl3oOPyGUY+pA=\n-----END PRIVATE KEY-----\n",
"client_email": os.environ.get("GSEMAIL"),
"client_id": os.environ.get("GSID"),
"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_x509_cert_url": os.environ.get("CERT_URI")
}
GSPREAD_CLIENT = gspread.service_account_from_dict(creds)
SHEET = GSPREAD_CLIENT.open('Pokemon Card Spreadsheet')
app = Flask(__name__)
app.config["MONGO_DBNAME"] = "pokemomCards"
app.config["MONGO_URI"] = os.environ.get("MONGO_URI", "")
app.secret_key = os.environ.get("SECRET_KEY", "")
app.templates = ""
mongo = PyMongo(app)
def from_service_account_dict(cls, service_account: Dict):
return GoogleSpreadsheetsClient(gspread.service_account_from_dict(service_account))
import os
from dotenv import load_dotenv
load_dotenv()
WEBAPP_PROJECT_ID = os.environ.get("WEBAPP_PROJECT_ID")
WEBAPP_PRIVATE_KEY_ID = os.environ.get("WEBAPP_PRIVATE_KEY_ID")
WEBAPP_PRIVATE_KEY = os.environ.get("WEBAPP_PRIVATE_KEY")
WEBAPP_CLIENT_EMAIL = os.environ.get("WEBAPP_CLIENT_EMAIL")
WEBAPP_CLIENT_ID = os.environ.get("WEBAPP_CLIENT_ID")
WEBAPP_CLIENT_X509_CERT_URL = os.environ.get("WEBAPP_CLIENT_X509_CERT_URL")
credentials = {
"type": "service_account",
"project_id": WEBAPP_PROJECT_ID,
"private_key_id": WEBAPP_PRIVATE_KEY_ID,
"private_key": WEBAPP_PRIVATE_KEY,
"client_email": WEBAPP_CLIENT_EMAIL,
"client_id": WEBAPP_CLIENT_ID,
"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_x509_cert_url": WEBAPP_CLIENT_X509_CERT_URL
}
webapp_google_spread = gspread.service_account_from_dict(credentials)
sh = webapp_google_spread.open('flask-website')
sh_profile = sh.get_worksheet(0)
sh_contacts = sh.get_worksheet(1)
def __init__(self):
self._account = gspread.service_account_from_dict(credintentials)
self._sheet = self._account.open_by_key(
"1R_l6bVt1Nv4qHsrjebW5CB9oWCTRUVKSWbtS2Apcp2A")
