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