def _get_records(
spreadsheet: gspread.models.Spreadsheet,
spreadsheet_name: str,
sheet_index: int = None,
) -> pd.DataFrame or None:
"""
Collect data from specified spreadsheet and return a sheet as
pandas DataFrame object.
Args:
spreadsheet (:class:`~gspread.models.Spreadsheet`):
Opened spreadsheet.
spreadsheet_name (str):
Spreadsheet name from spreadsheets.json file.
sheet_index (int):
Sheet index. Optional.
If None - function returns data from the last sheet.
Returns:
A :class:`~pandas.frame.DataFrame` instance.
"""
if sheet_index is None:
sheet_index = len(spreadsheet.worksheets()) - 1
try:
df = pd.DataFrame(
spreadsheet.get_worksheet(sheet_index).get_all_records())
except IndexError:
return None
df_id = _cfg[spreadsheet_name]['id-col']
if df_id is not None:
df.set_index(df_id, inplace=True)
return df.drop(columns='', errors='ignore')
def update_voting_results_sheet(sheet: gspread.models.Spreadsheet, votes: VotingSummaryPerYear, years: List[str]):
data = votes_to_sheets_format(votes, years)
sheet.sheet1.clear()
sheet.values_update(
range='A:' + gspread.utils.rowcol_to_a1(1, 3 + 5 * len(years))[:-1],
params={
'valueInputOption': 'USER_ENTERED'
},
body={
'values': data
}
)
def update_plan_proposal_sheet(sheet: gspread.models.Spreadsheet, proposal: ProposalSummary):
data = proposal_to_sheets_format(proposal)
sheet.sheet1.clear()
sheet.values_update(
range='A:N',
params={
'valueInputOption': 'USER_ENTERED'
},
body={
'values': data
}
)
def get_client_list(sheet: gspread.models.Spreadsheet, tab: str) -> Set[str]:
"""
Retrieve a list of Client IDs to query Snowflake with
Parameters
----------
sheet : gspread.models.Spreadsheet
tab : str
The name of the tab to open on the Needs Review Tracker spreadsheet
Returns
-------
client_list: Set[str]
A set of all Client IDs labeled 'Active' or 'Active - OCR to Send to
Client First' found in the 'Client List' tab of the Needs Review
Tracker. These are returned as integers converted to strings with a
comma after each ID. This is done to provide an easily readable string
of Client IDs to query Snowflake with.
Ex. {'294,', '305,', '442,'}
"""
cursheet = sheet.worksheet(tab)
curclients = cursheet.get_all_records()
curclients = pd.DataFrame(curclients)
active_clients = curclients[(curclients.Status == 'Active') |
(curclients.Status ==
'Active - OCR to Send to Client First')]
client_list = active_clients['Client ID']
client_list = {str(x)+',' for x in client_list}
print('Found ' + str(len(client_list)) + ' active clients in Client List')
return client_list
def overwrite_sheet(df_to_write: pd.DataFrame, google_sheet: gspread.models.Spreadsheet, worksheet_index: int):
"""
:param df_to_write: whcih df should be written to google sheets
:param google_sheet: the sheet to write to
:param worksheet_index: the index of the worksheet e {0,1,2,...,n}
"""
worksheet = google_sheet.get_worksheet(worksheet_index)
worksheet.clear()
set_with_dataframe(worksheet, df_to_write)
def get_worksheet(workbook: gspread.models.Spreadsheet,
sheet_name: str) -> gspread.models.Worksheet:
"""
指定した名前のワークシートを取得
@param workbook: ワークブック
@param sheet_name: ワークシート名
@return worksheet: ワークシートオブジェクト
"""
worksheet = workbook.worksheet(sheet_name)
return worksheet
def getmetadata(cls, spreadsheet: gspread.models.Spreadsheet):
metadata = None
try:
metadata = spreadsheet.fetch_sheet_metadata()
except PermissionError:
flash(
"Erreur : Accès refusé (absence du mail du credential dans les destinataires du partage ?)"
)
except Exception as ex:
flash(f"Erreur : {ex}")
return metadata
def get_unreviewed_posts(sheet: gspread.models.Spreadsheet):
"""Get all unreviewed posts.
:param sheet: Google spreadsheet
:return: generator object of not yet reviewed posts
"""
week = get_review_week_start_end(date.today())
title = f"Unreviewed - {week[0]:%b} {week[0].day} - {week[1]:%b} {week[1].day}"
unreviewed = sheet.worksheet(title)
return (contribution(row) for row in unreviewed.get_all_values()[1:]
if row)
def get_watched_users(sheet: gspread.models.Spreadsheet, bannedOnly=True):
"""Get all banned users from Banned users worksheet.
:param bannedOnly:
:param sheet: utopian review sheet
:return: generator object of watched users
"""
banned_sheet = sheet.worksheet('Banned users')
return (user(row) for row in banned_sheet.get_all_values()[1:]
if row and row[0] and (
row[3].lower() == 'yes' if bannedOnly else True))
def get_reviewed_posts_in_week(sheet: gspread.models.Spreadsheet,
week_date: date):
"""Get reviewed contributions in one week.
The week is decided by the given date.
Review week starts on Thursday.
:param sheet: Google spreadsheet
:param week_date: date of the week
:return: generator object of the contributions
"""
week = get_review_week_start_end(week_date)
title = f"Reviewed - {week[0]:%b} {week[0].day} - {week[1]:%b} {week[1].day}"
reviewed = sheet.worksheet(title)
return (contribution(row) for row in reviewed.get_all_values()[1:] if row)