async def gsheet_qualifier_start(race, date):
agcm = gspread_asyncio.AsyncioGspreadClientManager(get_creds)
agc = await agcm.authorize()
wb = await agc.open_by_key(c.TournamentQualifierSheet)
raceid = race['id']
try:
wks = await wb.worksheet(f'Qualifier - {date} - {raceid}')
await wks.clear()
except gspread.exceptions.WorksheetNotFound:
wks = await wb.add_worksheet(title=f'Qualifier - {date} - {raceid}',
rows=50,
cols=10)
await wks.append_row([
'Place', 'Nickname', 'Twitch Stream', 'Finish Time', 'Score', 'Notes'
])
for entrant in race['entrants']:
if entrant == "JOPEBUSTER":
continue
twitch = race['entrants'][entrant]['twitch']
await wks.append_row([
9999, entrant, twitch, '',
'=IF(ROUND((2-(INDIRECT(\"R[0]C[-1]\", false)/AVERAGE($D$2:$D$6)))*100,2)>105,105,IF(ROUND((2-(INDIRECT(\"R[0]C[-1]\", false)/AVERAGE($D$2:$D$6)))*100,2)<0,0,ROUND((2-(INDIRECT(\"R[0]C[-1]\", false)/AVERAGE($D$2:$D$6)))*100,2)))'
],
value_input_option='USER_ENTERED')
async def loadnicks(ctx):
agcm = gspread_asyncio.AsyncioGspreadClientManager(get_creds)
agc = await agcm.authorize()
wb = await agc.open_by_key(c.Tournament[ctx.guild.id]['registration_sheet']
)
wks = await wb.get_worksheet(0)
bad_discord_names = []
for idx, row in enumerate(await wks.get_all_records()):
if not row['Nick Loaded'] in ['Y', 'ignore']:
try:
member = await commands.MemberConverter().convert(
ctx, row['Discord Name'])
player_role = await commands.RoleConverter().convert(
ctx, c.Tournament[ctx.guild.id]['player_role'])
await srlnick.insert_srl_nick(member.id, row['SRL Name'])
await srlnick.insert_twitch_name(member.id, row['Twitch Name'])
await member.add_roles(player_role)
await wks.update_cell(idx + 2, 5, 'Y')
except discord.ext.commands.errors.BadArgument:
bad_discord_names.append(row['Discord Name'])
await wks.update_cell(idx + 2, 5, 'Error')
if len(bad_discord_names) > 0:
await ctx.send(
"Bad discord names. These names were not processed.\n\n`{names}`".
format(names='\n'.join(bad_discord_names)))
def init_agcm(json_secret, loop=None):
"""
Initialize the global AGCM, share with all sheets.
Has internal rate limitting but we should do batch updates still to prevent hitting them.
Args:
json_secret: The *absolute* path to the secret json file for a service account.
loop: The loop to attach the agcm to, by default with get_event_loop()
"""
def get_creds():
# To obtain a service account JSON file, follow these steps:
# https://gspread.readthedocs.io/en/latest/oauth2.html#for-bots-using-service-account
creds = Credentials.from_service_account_file(json_secret)
scoped = creds.with_scopes([
"https://spreadsheets.google.com/feeds",
"https://www.googleapis.com/auth/spreadsheets",
"https://www.googleapis.com/auth/drive",
])
return scoped
if not loop:
loop = asyncio.get_event_loop()
return gspread_asyncio.AsyncioGspreadClientManager(get_creds, loop=loop)
async def get_worksheet(sheet_name, worksheet_name):
agcm = gspread_asyncio.AsyncioGspreadClientManager(get_creds)
agc = await agcm.authorize()
sh = await agc.open(sheet_name)
if worksheet_name is None:
return await sh.get_worksheet(0)
return await sh.worksheet(worksheet_name)
async def main():
google_client = gaio.AsyncioGspreadClientManager(get_creds)
sheets = await get_sheet(google_client)
sheet = sheets[0]
sheet2 = sheets[1]
names = utilities.get_rank_categories(flatten=True)
print(names)
all_time = pair_data(sheet.range("J2:K" + str(sheet.row_count)), 2,
"all_time")
df_all_time = pd.DataFrame(all_time[1:], columns=all_time[0])
monthly = pair_data(sheet.range("C2:D" + str(sheet.row_count)), 2,
"monthly")
df_monthly = pd.DataFrame(monthly[1:], columns=monthly[0])
weekly = pair_data(sheet.range("Q2:R" + str(sheet.row_count)), 2, "weekly")
df_weekly = pd.DataFrame(weekly[1:], columns=weekly[0])
daily = pair_data(sheet.range("X2:Y" + str(sheet.row_count)), 2, "daily")
df_daily = pd.DataFrame(daily[1:], columns=daily[0])
streaks = pair_data(sheet2.range("A3:C" + str(sheet2.row_count)), 3,
"current_streak", "longest_streak")
df_streaks = pd.DataFrame(streaks[1:], columns=streaks[0])
return [df_all_time, df_monthly, df_weekly, df_daily, df_streaks]
async def get_settings(episodeid, guildid):
agcm = gspread_asyncio.AsyncioGspreadClientManager(get_creds)
agc = await agcm.authorize()
wb = await agc.open_by_key(c.Tournament[guildid]['schedule_sheet'])
wks = await wb.worksheet('Schedule')
for row in await wks.get_all_records():
if row['Game ID'] == episodeid:
return row
return None
def __init__(self, bot) -> None:
log.debug("Initializing database...")
super().__init__()
self._bot: "perlink.bot.PerlinkBot" = bot
self._gspread_manager = gspread_asyncio.AsyncioGspreadClientManager(
self._authenticate)
self._cached_messages: typing.Mapping[str, DbEntry] = {}
self._database_file_id = bot.config.db_file
self._max_vote_hours = bot.config.max_vote_hours
self.save_messages.start()
self.collect_votes.start()
def init():
# Google Sheets authentication manager
global agcm
agcm = gspread_asyncio.AsyncioGspreadClientManager(__get_creds)
# Postgresql connection manager
global connpool
connpool = asyncpg.create_pool(database=dbname,
user=dbuser,
password=dbpass,
host=dbhost,
port=dbport)
def __init__(self):
super().__init__(command_prefix=_prefix_callable,
case_insensitive=True,
activity=discord.Game(name="Blaseball"))
self.logger = init_loggers()
custom_error_handling(self, self.logger)
self.guild_dict = {'configure_dict': {}}
self._load_data()
self._load_config()
self.db_path = 'data/watcher.db'
self.success_react = '✅'
self.failed_react = '❌'
self.thumbsup_react = '👍'
self.empty_str = '\u200b'
self.initial_start = True
self.watch_servers = [738107179294523402, 671866672562307091]
self.off_topic_channels = [756667935728074754, 815745893315772426]
self.SPREADSHEET_IDS = {}
self.favor_rankings = self.config.setdefault('favor_rankings', {})
self.daily_watch_message = self.config.setdefault(
'daily_watch_message', 'Go Bet!')
self.check_for_games_complete = self.config.setdefault(
'check_for_games_complete', False)
self.check_for_new_schedules = self.config.setdefault(
'check_for_new_schedules', False)
self.current_day = 0
self.tasks = []
self.agcm = gspread_asyncio.AsyncioGspreadClientManager(self.get_creds)
self.team_cache = {}
self.player_cache = {}
self.player_names = {}
self.player_id_to_name = {}
self.team_names = {}
self.player_team_map = {}
self.deceased_players = {}
self.team_cache_updated = False
self.divisions = None
self.load_defaults()
self.playoff_teams = []
self.session = None
for ext in default_exts:
try:
self.load_extension(f"watcher.exts.{ext}")
except Exception as e:
print(
f'**Error when loading extension {ext}:**\n{type(e).__name__}: {e}'
)
else:
if 'debug' in sys.argv[1:]:
print(f'Loaded {ext} extension.')
async def workbook_refresh(self):
while True:
self.creds = ServiceAccountCredentials.from_json_keyfile_name(
'resources/client_secret.json', scope)
self.manager = gspread_asyncio.AsyncioGspreadClientManager(
lambda *args: self.creds)
self.client = await self.manager.authorize()
self.workbook = await self.client.open_by_url(workbook_url)
self.sheet = await self.workbook.get_worksheet(0)
self.offerworkbook = await self.client.open_by_url(offerbook_url)
self.offersheet1 = await self.offerworkbook.get_worksheet(0)
self.offersheet2 = await self.offerworkbook.get_worksheet(1)
self.sheets = (self.sheet, self.offersheet1, self.offersheet2)
await asyncio.sleep(60 * 30)
async def _test_smoke(self, creds_fn):
agcm = gspread_asyncio.AsyncioGspreadClientManager(creds_fn,
gspread_delay=3.1)
agc = await agcm.authorize()
self.assertIsInstance(agc, gspread_asyncio.AsyncioGspreadClient)
ss = await agc.create("Smoketest Spreadsheet")
print("Spreadsheet URL: https://docs.google.com/spreadsheets/d/{0}".
format(ss.id))
self.assertIsInstance(ss, gspread_asyncio.AsyncioGspreadSpreadsheet)
self.assertEqual(1, len(agc._ss_cache_key))
self.assertEqual(1, len(agc._ss_cache_title))
self.assertEqual(ss, agc._ss_cache_key[ss.id])
self.assertEqual(ss, agc._ss_cache_title[await ss.get_title()])
await agc.insert_permission(ss.id,
None,
perm_type="anyone",
role="writer")
ws = await ss.add_worksheet("My Test Worksheet", 2, 2)
self.assertIsInstance(ws, gspread_asyncio.AsyncioGspreadWorksheet)
self.assertEqual(1, len(ss._ws_cache_idx))
self.assertEqual(1, len(ss._ws_cache_title))
self.assertEqual(ws, ss._ws_cache_idx[1])
self.assertEqual(ws, ss._ws_cache_title[ws.title])
for row in range(1, 3):
for col in range(1, 3):
val = "{0}/{1}".format(row, col)
await ws.update_cell(row, col, val)
cell = await ws.cell(row, col)
self.assertEqual(cell.value, val)
self.assertEqual(await ws.get_values(),
[["1/1", "1/2"], ["2/1", "2/2"]])
await ss.del_worksheet(ws)
self.assertEqual(0, len(ss._ws_cache_idx))
self.assertEqual(0, len(ss._ws_cache_title))
ss_id = ss.ss.id
await agc.del_spreadsheet(ss_id)
self.assertEqual(0, len(agc._ss_cache_key))
self.assertEqual(0, len(agc._ss_cache_title))
def __init__(self, bot):
# MongoDB
client = motor.motor_asyncio.AsyncIOMotorClient(config.mongodb)
bot.db = client["ArcaeaBot"]
# Google Cloud (and related products)
credentials = service_account.Credentials.from_service_account_file(
config.gc_json)
scoped_credentials = credentials.with_scopes(config.gc_scopes)
bot.gc_client = gspread_asyncio.AsyncioGspreadClientManager(
lambda: scoped_credentials)
bot.songs = None
bot.loop.create_task(self.async_init())
self.bot = bot
self.gc_client = bot.gc_client
async def gsheet_qualifier_finish(race, date):
agcm = gspread_asyncio.AsyncioGspreadClientManager(get_creds)
agc = await agcm.authorize()
wb = await agc.open_by_key(c.TournamentQualifierSheet)
raceid = race['id']
try:
wks = await wb.worksheet(f'Qualifier - {date} - {raceid}')
except gspread.exceptions.WorksheetNotFound:
await gsheet_qualifier_start(race, date)
wks = await wb.worksheet(f'Qualifier - {date} - {raceid}')
for idx, row in enumerate(await wks.get_all_records()):
try:
entrant = race['entrants'][row['Nickname']]
except KeyError:
continue
await wks.update_cell(idx + 2, 4, entrant['time'])
await wks.update_cell(idx + 2, 1, entrant['place'])
async def loadnicks(ctx):
agcm = gspread_asyncio.AsyncioGspreadClientManager(get_creds)
agc = await agcm.authorize()
wb = await agc.open_by_key(c.TournamentQualifierSheet)
wks = await wb.get_worksheet(0)
bad_discord_names = []
converter = commands.MemberConverter()
for idx, row in enumerate(await wks.get_all_records()):
if not row['Nick Loaded'] in ['Y', 'ignore']:
try:
member = await converter.convert(ctx, row['Discord Name'])
await srlnick.insert_srl_nick(member.id, row['SRL Name'])
await wks.update_cell(idx + 2, 6, 'Y')
except discord.ext.commands.errors.BadArgument as e:
bad_discord_names.append(row['Discord Name'])
await wks.update_cell(idx + 2, 6, 'Error')
if len(bad_discord_names) > 0:
await ctx.send(
"Bad discord names. These names were not processed.\n\n`{names}`".
format(names='\n'.join(bad_discord_names)))
async def race_recording_task():
agcm = gspread_asyncio.AsyncioGspreadClientManager(gsheet.get_creds)
agc = await agcm.authorize()
for event in TOURNAMENT_DATA.keys():
event_data = await TOURNAMENT_DATA[event].get_config()
if event_data.data.gsheet_id is None:
continue
wb = await agc.open_by_key(event_data.data.gsheet_id)
races = await models.TournamentResults.filter(written_to_gsheet=None,
event=event)
if not races:
continue
try:
wks = await wb.worksheet(event)
except gspread.exceptions.WorksheetNotFound:
wks = await wb.add_worksheet(event, 50, 10)
await wks.append_row(values=[
'episode',
'start time (eastern)',
'racetime',
'winner',
'loser',
'winner finish',
'loser finish',
'permalink',
'spoiler',
])
for race in races:
logging.info(f"Recording {race.episode_id}")
try:
async with aiohttp.request(
method='get',
url=f"{RACETIME_URL}/{race.srl_id}/data",
raise_for_status=True) as resp:
race_data = json.loads(await resp.read())
if race_data['status']['value'] == 'finished':
winner = [
e for e in race_data['entrants'] if e['place'] == 1
][0] # pylint: disable=used-before-assignment
runnerup = [
e for e in race_data['entrants']
if e['place'] in [2, None]
][0] # pylint: disable=used-before-assignment
started_at = isodate.parse_datetime(
race_data['started_at']).astimezone(
pytz.timezone('US/Eastern'))
ended_at = isodate.parse_datetime(race_data['ended_at'])
record_at = ended_at + datetime.timedelta(
minutes=event_data.data.stream_delay)
if record_at > datetime.datetime.now(
tz=datetime.timezone.utc):
continue
await wks.append_row(values=[
race.episode_id,
started_at.strftime("%Y-%m-%d %H:%M:%S"),
f"{RACETIME_URL}/{race.srl_id}", winner['user']
['name'], runnerup['user']['name'],
str(isodate.parse_duration(winner['finish_time']))
if isinstance(winner['finish_time'], str) else None,
str(isodate.parse_duration(runnerup['finish_time']))
if isinstance(runnerup['finish_time'], str) else None,
race.permalink, race.spoiler
])
race.status = "RECORDED"
race.written_to_gsheet = 1
await race.save()
if event_data.data.auto_record:
await racetime_auto_record(race_data)
elif race_data['status']['value'] == 'cancelled':
await race.delete()
else:
continue
except Exception as e:
logging.exception(
"Encountered a problem when attempting to record a race.")
logging.debug('done')
async def qualifier_cmd(ctx, arg1, logger, loop):
tz = timezone('US/Eastern')
logger.info(
'Qualifier Requested - {servername} - {channelname} - {player} - {seednum}'
.format(
servername=ctx.guild.name,
channelname=ctx.channel.name,
player=ctx.author,
seednum=arg1,
))
# if helpers.check_cmd_filter(ctx.guild.id,ctx.channel.name,'qualifier',config):
# return
try:
seednum = int(arg1)
except ValueError:
await ctx.message.add_reaction('👎')
await ctx.send(
'{author}, that is not a number.'.format(author=ctx.author.mention)
)
return
spdb = db.SpoilerBotDatabase(loop)
await spdb.connect()
qualifier_seed = await spdb.get_qualifier_seed(seednum)
await spdb.close()
if qualifier_seed == None:
await ctx.message.add_reaction('👎')
await ctx.send('{author}, that seed does not exist.'.format(
author=ctx.author.mention))
return
seed = await pyz3r_asyncio.create_seed(
randomizer='item',
baseurl=config['alttpr_website']['baseurl'],
seed_baseurl=config['alttpr_website']['baseurl_seed'],
hash=qualifier_seed['hash'])
spoilerlog = qualifier_seed['spoilerlog']
verificationkey = await generate_verification_key(loop)
permalink = await seed.url()
fscode = ' | '.join(await seed.code())
timestamp = str(datetime.now(tz).replace(microsecond=0))
logger.info(
'Qualifier Generated - {servername} - {channelname} - {player} - {seednum} - {verificationkey}'
.format(servername=ctx.guild.name,
channelname=ctx.channel.name,
player=ctx.author,
seednum=seednum,
verificationkey=verificationkey))
dm = ctx.author.dm_channel
if dm == None:
dm = await ctx.author.create_dm()
await dm.send(
'This is the verification key that is required to be in the first four characters of the filename of your run:\n`{verificationkey}`\n\n' \
'Seed number: {seednum}\n' \
'Timestamp: {timestamp}\n' \
'Permalink: {permalink}\n' \
'File select code: [{fscode}]\n' \
'Spoiler log: {spoilerlog}\n\n' \
'Submit your run here once completed: <{submiturl}>\n\n' \
'You have 15 minutes from the receipt of this message to start your run!\n' \
'**Please DM an admin immediately if this was requested in error**, otherwise it may be counted as a forfeit (slowest time of all runners of the seed plus 30 minutes).\n\n' \
'Good luck <:mudora:536293302689857567>'.format(
verificationkey=verificationkey,
seednum=seednum,
timestamp=timestamp,
fscode=fscode,
permalink=permalink,
spoilerlog=spoilerlog,
submiturl=config['qualifier_form_prefill'].format(
discordtag=urllib.parse.quote_plus(ctx.author.name + '#' + ctx.author.discriminator),
verificationkey=verificationkey,
seednum=seednum
)
)
)
modlogchannel = ctx.guild.get_channel(config['log_channel'][ctx.guild.id])
msg = 'Qualifier request for {player}:\n\n' \
'Verification Key: {verificationkey}\n' \
'Seed Number: {seednum}\n' \
'Timestamp: {timestamp}'.format(
player = ctx.author.name + '#' + ctx.author.discriminator,
verificationkey=verificationkey,
seednum=seednum,
timestamp=timestamp,
)
await modlogchannel.send(msg)
logger.info(
'Qualifier DM Sent - {servername} - {channelname} - {player} - {seednum} - {verificationkey}'
.format(servername=ctx.guild.name,
channelname=ctx.channel.name,
player=ctx.author,
seednum=seednum,
verificationkey=verificationkey))
agcm = gspread_asyncio.AsyncioGspreadClientManager(helpers.get_creds)
agc = await agcm.authorize()
wb = await agc.open_by_key(config['gsheet_id'])
wks = await wb.get_worksheet(0)
await wks.append_row([
timestamp,
str(ctx.author), seednum, verificationkey,
"=INDEX('Submitted Runs'!E:G,MATCH(INDIRECT(\"R[0]C[-1]\", false),'Submitted Runs'!C:C,0), 1)*3600+INDEX('Submitted Runs'!E:G,MATCH(INDIRECT(\"R[0]C[-1]\", false),'Submitted Runs'!C:C,0), 2)*60+INDEX('Submitted Runs'!E:G,MATCH(INDIRECT(\"R[0]C[-1]\", false),'Submitted Runs'!C:C,0), 3)",
"=INDEX('Submitted Runs'!H:H,MATCH(INDIRECT(\"R[0]C[-2]\", false),'Submitted Runs'!C:C,0), 1)",
"=INDEX('Submitted Runs'!I:I,MATCH(INDIRECT(\"R[0]C[-3]\", false),'Submitted Runs'!C:C,0), 1)",
"=INDEX('Submitted Runs'!J:J,MATCH(INDIRECT(\"R[0]C[-4]\", false),'Submitted Runs'!C:C,0), 1)"
],
value_input_option='USER_ENTERED')
logger.info(
'Qualifier Recorded in Gsheet - {servername} - {channelname} - {player} - {seednum} - {verificationkey}'
.format(servername=ctx.guild.name,
channelname=ctx.channel.name,
player=ctx.author,
seednum=seednum,
verificationkey=verificationkey))
await ctx.message.add_reaction('👍')
# https://gspread.readthedocs.io/en/latest/oauth2.html#for-bots-using-service-account
creds = Credentials.from_service_account_file(
"/Users/nawakatsushou/Documents/コード/my_app_Movieist/movieist-b7c9917e1dde.json"
)
scoped = creds.with_scopes([
"https://spreadsheets.google.com/feeds",
"https://www.googleapis.com/auth/spreadsheets",
"https://www.googleapis.com/auth/drive",
])
return scoped
# Create an AsyncioGspreadClientManager object which
# will give us access to the Spreadsheet API.
agcm = gspread_asyncio.AsyncioGspreadClientManager(get_creds)
# Here's an example of how you use the API:
async def example(agcm):
N = 500 # ページネートの数
BT = 2
add_num = 0
agc = await agcm.authorize()
ss = await agc.open('スクレイピング_映画')
for i in range(390, N + 1):
sleep(BT)
zero_ws = await ss.get_worksheet(0)
async def update_helper_sheet(self, data):
agcm = gspread_asyncio.AsyncioGspreadClientManager(self.get_creds)
agc = await agcm.authorize()
ss = await agc.open_by_key(helper_sheet)
ws = await ss.worksheet('Sheet1')
await ws.append_row(data)
def _make_gsheets_client_manager(gsheets_creds):
def get_creds():
return ServiceAccountCredentials.from_json_keyfile_name(
gsheets_creds, ['https://www.googleapis.com/auth/spreadsheets.readonly'])
return gspread_asyncio.AsyncioGspreadClientManager(get_creds)
def __init__(self, key):
self.key = key
self.client_manager = gspread_asyncio.AsyncioGspreadClientManager(
self.get_credentials)
self.worksheet = None
from types import SimpleNamespace
import gspread_asyncio
from google.oauth2 import service_account
def get_credentials():
scopes = [
'https://www.googleapis.com/auth/drive',
'https://www.googleapis.com/auth/drive.file',
'https://www.googleapis.com/auth/spreadsheets',
'https://spreadsheets.google.com/feeds'
]
return service_account.Credentials.from_service_account_file('ticketron.json', scopes=scopes)
gspread_manager = gspread_asyncio.AsyncioGspreadClientManager(get_credentials)
TicketStatus = SimpleNamespace(
NEW="0 - New", OPEN="1 - Open", PINNED="8 - Pinned", CLOSED="9 - Closed"
)
async def get_tickets():
tickets = set()
gspread = await gspread_manager.authorize()
ss = await gspread.open_by_key("1-XlENZVrZ9oYx6UqIUi5V3eq0l3RPDCfeXIyB6TC2NA")
tickets_sheet = await ss.get_worksheet(0)
for row in await tickets_sheet.get_all_records():
def __init__(self, bot, *args, **kwargs): super().__init__(*args, **kwargs) self.bot: blueonblue.BlueOnBlueBot = bot self.agcm = gspread_asyncio.AsyncioGspreadClientManager(self._get_google_credentials) # Authorization manager for gspread
async def make_client():
agcm = gspread_asyncio.AsyncioGspreadClientManager(get_creds)
agc = await agcm.authorize()
return agc
async def event_sglive2020smm2(self):
loop = asyncio.get_event_loop()
copy_workbook = gsheet.drive_service.files().copy(
fileId=SMM2_GDRIVE_TEMPLATE,
body={
'name': f"SMM2 - {self.episode_id} - {self.versus}",
'parents': [SMM2_GDRIVE_FOLDER]
}
)
results = await loop.run_in_executor(None, copy_workbook.execute)
self.permalink = f"https://docs.google.com/spreadsheets/d/{results['id']}/edit#gid=0"
self.seed_id = results['id']
set_owner = gsheet.drive_service.permissions().create(
fileId=self.seed_id,
transferOwnership=True,
body={
'type': 'user',
'role': 'owner',
'emailAddress': SMM2_SHEET_OWNER
}
)
await loop.run_in_executor(None, set_owner.execute)
set_anyone = gsheet.drive_service.permissions().create(
fileId=self.seed_id,
body={
'type': 'anyone',
'role': 'writer',
'allowFileDiscovery': False
}
)
await loop.run_in_executor(None, set_anyone.execute)
agcm = gspread_asyncio.AsyncioGspreadClientManager(gsheet.get_creds)
agc = await agcm.authorize()
wb = await agc.open_by_key(self.seed_id)
wks = await wb.get_worksheet(0)
await wks.batch_update(data=[
{
'range': 'B3:B5',
'values': [[self.players[0].display_name], [self.players[1].display_name], ['']]
},
{
'range': 'E2:G4',
'values': [['', '', ''], ['', '', ''], ['', '', '']]
},
{
'range': 'E2:G4',
'values': [['', '', ''], ['', '', ''], ['', '', '']]
},
{
'range': 'B10:B11',
'values': [[''], ['']]
},
{
'range': 'B14:D15',
'values': [['', '', ''], ['', '', '']]
},
{
'range': 'B18:D19',
'values': [['', '', ''], ['', '', '']]
},
], value_input_option="RAW")
async def record_episode(race, bo3=False):
# do a bunch of stuff to write the race to the spreadsheet
if race['status'] == "RECORDED":
return
sheet_name = EVENTS[race['event']].get('sheet')
agcm = gspread_asyncio.AsyncioGspreadClientManager(gsheet.get_creds)
agc = await agcm.authorize()
wb = await agc.open_by_key(SGL_RESULTS_SHEET)
wks = await wb.worksheet(sheet_name)
if race['platform'] == 'racetime':
async with aiohttp.request(
method='get',
url=f"https://racetime.gg/{race['room_name']}/data",
raise_for_status=True) as resp:
race_data = json.loads(await resp.read())
if race_data['status']['value'] == 'finished':
winner = [e for e in race_data['entrants'] if e['place'] == 1][0]
runnerup = [e for e in race_data['entrants']
if e['place'] in [2, None]][0]
await wks.append_row(values=[
race['episode_id'],
f"https://racetime.gg/{race['room_name']}",
winner['user']['name'],
runnerup['user']['name'],
str(isodate.parse_duration(winner['finish_time'])) if isinstance(
winner['finish_time'], str) else None,
str(isodate.parse_duration(runnerup['finish_time'])) if isinstance(
runnerup['finish_time'], str) else None,
race['permalink'],
race['password'],
str(race['created']),
str(race['updated'])
])
elif race_data['status']['value'] == 'cancelled':
if bo3:
await sgl2020_tournament_bo3.delete_active_tournament_race(race['room_name'])
else:
await sgl2020_tournament.delete_active_tournament_race(race['room_name'])
else:
return
else:
episode_data = await speedgaming.get_episode(race['episode_id'])
await wks.append_row(values=[
race['episode_id'],
None,
episode_data['match1']['players'][0]['displayName'],
episode_data['match1']['players'][1]['displayName'],
None,
None,
race['permalink'],
race['password'],
str(race['created']),
str(race['updated'])
])
if bo3:
await sgl2020_tournament_bo3.update_tournament_race_status(race['room_name'], "RECORDED")
else:
await sgl2020_tournament.update_tournament_race_status(race['room_name'], "RECORDED")
def init():
if not service_account_file:
log.warning("missing service account file; ignoring...")
return
return gspread_asyncio.AsyncioGspreadClientManager(get_creds)
async def get_gspread_client(access_key=None):
agcm = gspread_asyncio.AsyncioGspreadClientManager(get_service_account_creds)
agc = await agcm.authorize()
return agc
def get_manager() -> gspread_asyncio.AsyncioGspreadClientManager:
return gspread_asyncio.AsyncioGspreadClientManager(get_credentials)
def __init__(self, episodeid):
self.agcm = gspread_asyncio.AsyncioGspreadClientManager(get_creds)
self.episodeid = episodeid
def setUp(self):
self.agcm = gspread_asyncio.AsyncioGspreadClientManager(get_creds,
gspread_delay=3.1)
