def update_symbols_price(self, symbol_value_dict):
"""
update the last value of a list of symbols
:param dict symbol_value_dict:
:return:
"""
value_cell_list = []
status_cell_list = []
symbol_list = self.get_list_of_current_symbol().get("symbols", [])
value_list = self.get_list_of_current_symbol().get("values", [])
for i, symbol in enumerate(symbol_list):
# skip header
if i == 0:
continue
symbol_row = i + 1
val = symbol_value_dict.get(symbol)
if type(val) is int: # Skip stopped symbols
val = val / 10 # Convert ot Toman
status_cell_list.append(Cell(symbol_row, 9, "مجاز"))
else:
val = value_list[i]
status_cell_list.append(Cell(symbol_row, 9, "متوقف"))
value_cell_list.append(Cell(symbol_row, 2, val))
if value_cell_list:
self.spreadsheet.sheet1.update_cells(value_cell_list)
if status_cell_list:
self.spreadsheet.sheet1.update_cells(status_cell_list)
async def attendance(ctx, *args):
#refreshes drive info
scope = [
'https://www.googleapis.com/auth/spreadsheets',
"https://www.googleapis.com/auth/drive.file",
"https://www.googleapis.com/auth/drive"
]
creds = ServiceAccountCredentials.from_json_keyfile_name(
'apikey.json', scope)
client = gspread.authorize(creds)
sheet = client.open("bruh").sheet1
#runs only if someone is in voice channel
if ctx.author.voice and ctx.author.voice.channel:
#gets voice channel of author
channel = ctx.author.voice.channel
members = channel.members
#gets members in voice channel
connected = [] #(list)
for member in members:
#connected.append(str(bot.get_user(member.id)))
connected.append(str(
member.display_name)) #IMPORTANT DISPLAYS AS DISPLAY NAME
#puts members into string
connected_string = ""
for i in connected:
connected_string = connected_string + i + ", "
connected_string = connected_string[:-2]
#displays list of connected people
await ctx.send("**Current list of connected people:**\n{}".format(
connected_string))
#gets event name
if len(args) == 1:
event_name = str(args[0]) + ", "
else:
event_name = ""
#creates valuedata
value_data = str(event_name + get_date())
#adds values to sheets
column = len(sheet.row_values(1)) + 1
cells = []
cells.append(Cell(row=1, col=column, value=value_data))
for q in range(2, len(connected) + 2):
cells.append(Cell(row=q, col=column, value=connected[q - 2]))
sheet.update_cells(cells)
print("adding\n\n{}\n\nto sheets".format(connected))
else:
await ctx.send(
"Could be wrong, but \n*You are not connected to a voice channel*")
def write_cells(video, url, genre, index):
cells = []
try:
cells.append(Cell(index, 1, video))
cells.append(Cell(index, 2, url))
cells.append(Cell(index, 3, genre))
sheet.update_cells(cells)
except:
print('**FAILED**')
def writing(self,event):
temp =[]
table = gc.open("Name").sheet1
rs = nAvailable(table)
temp.append(Cell(rs,1,self.ent_name.get()))
temp.append(Cell(rs,2,self.ent_id.get()))
self.ent_name.delete(0,tk.END)
self.ent_id.delete(0,tk.END)
return table.update_cells(temp)
def reset():
for index_rank in range(0, len(review_list_rank)):
cells = []
cells.append(Cell(row=index_rank + 2, col=2, value=0))
cells.append(Cell(row=index_rank + 2, col=3, value=0))
cells.append(Cell(row=index_rank + 2, col=4, value=0))
review_sheet_rank.update_cells(cells)
return redirect('/')
def createDoc(self):
print('Loading original...')
self.gspreadWrapper.loadAssessmentsFile()
print('Make a new copy...')
spreadsheet = self.gspreadWrapper.gc.copy(
self.options.originalExportFromIdeascale,
title=self.options.proposerDocumentName
)
spreadsheet.share(
self.options.accountEmail,
perm_type='user',
role='writer'
)
worksheet = spreadsheet.worksheet(self.options.assessmentsSheet)
print('Setting headings for report...')
# Add columns for y/r cards criteria
currentColsCount = worksheet.col_count
cellsToAdd = []
# Set headings
headings = [
self.options.blankColumn, self.options.topQualityColumn,
self.options.profanityColumn, self.options.scoreColumn,
self.options.copyColumn, self.options.wrongChallengeColumn,
self.options.wrongCriteriaColumn, self.options.otherColumn,
self.options.otherRationaleColumn
]
worksheet.add_cols(len(headings))
print('Set column width...')
set_column_widths(worksheet, [
('J:Q', 40), ('R', 200)
])
for i, value in enumerate(headings):
cellsToAdd.append(
Cell(row=1, col=(currentColsCount + i + 1), value=value)
)
print('Mark blank assessments...')
# Autofill blank assessments
assessments = self.gspreadWrapper.getAssessmentsData()
for note in assessments:
assessment = note[self.options.assessmentColumn].strip()
if (assessment == ''):
col = (currentColsCount + 1)
cellsToAdd.append(
Cell(row=note[self.options.assessmentsIdColumn], col=col, value='x')
)
worksheet.update_cells(cellsToAdd, value_input_option='USER_ENTERED')
print('Document for proposers created')
print('Link: {}'.format(spreadsheet.url))
def write_sheet(videos, urls, genres):
i = 2
n = 0
cells = []
for video in videos:
try:
cells.append(Cell(i, 1, video))
cells.append(Cell(i, 2, urls[n]))
cells.append(Cell(i, 3, genres[n]))
i += 1
n += 1
except:
print('**FAILED**')
sheet.update_cells(cells)
def checking(self,event):
temp=[]
data = gc.open("Name").sheet1
record = gc.open("Record").sheet1
vals = data.get_all_values()
df = pd.DataFrame(vals)
rs = nAvailable(record)
result = list(df[df[1].astype(str)==self.ent_input.get()][0])[0]
self.lbl_res["text"] = result
now = datetime.now()
curT = now.strftime("%Y-%m-%d, %H:%M:%S")
temp.append(Cell(rs,1,result))
temp.append(Cell(rs,2,curT))
self.ent_input.delete(0,tk.END)
return record.update_cells(temp)
def summery():
for index_rank in range(0, len(review_list_rank)):
cells = []
cells.append(
Cell(row=index_rank + 2,
col=2,
value=review_list_rank[index_rank]['Rating'] /
review_list_rank[index_rank]['Count']))
cells.append(
Cell(row=index_rank + 2,
col=3,
value=review_list_rank[index_rank]['Confidence'] /
review_list_rank[index_rank]['Count']))
review_sheet_rank.update_cells(cells)
return redirect('/')
def addKeywordsToWorksheet(worksheetName, keywords):
tempWorksheet = masterSheet.worksheet(worksheetName)
newCells = []
for i, keyword in enumerate(keywords, start=1):
newCells.append(Cell(row=i, col=1, value=keyword))
tempWorksheet.update_cells(newCells)
def data(self, x, y, values):
super().permissionCheck("w")
cells = []
for j, line in enumerate(values, y):
for i, val in enumerate(line, x):
cells.append(Cell(row=j, col=i, value=val))
self.table.update_cells(cells)
def update_uploaded_status(sheet: gspread.models.Worksheet,
uploaded_rows: List[int]):
if len(uploaded_rows) == 0:
return
cells = [
Cell(row=row, col=UPLOADED_COLUMN, value="TRUE")
for row in uploaded_rows
]
sheet.update_cells(cells, value_input_option="USER_ENTERED")
def tuples_to_cells(tuples, row_offset=0, col_offset=0):
cells = []
for row in range(0, len(tuples)):
for col in range(0, len(tuples[row])):
cells.append(
Cell(row + row_offset + 1, col + col_offset + 1,
tuples[row][col]))
return cells
def cal():
for index in range(0, len(review_list)):
for index_rank in range(0, len(review_list_rank)):
if review_list[index]['StoreName'] == review_list_rank[index_rank][
'StoreName']:
print('store :' + review_list[index]['StoreName'] + ' rate :' +
str(review_list[index]['Rate']) + ': count :' +
str(review_list_rank[index_rank]['Count']))
review_list_rank[index_rank]['Rating'] += review_list[index][
'Rate']
if float(review_list[index]['Confidence']) > 0.55555:
review_list_rank[index_rank][
'Confidence'] = review_list_rank[index_rank][
'Confidence'] + (review_list[index]['Confidence'] -
0.55555)
else:
review_list_rank[index_rank][
'Confidence'] = review_list_rank[index_rank][
'Confidence'] - review_list[index]['Confidence']
review_list_rank[index_rank]['Count'] += 1
for index_rank in range(0, len(review_list_rank)):
cells_rank = []
cells_rank.append(
Cell(row=index_rank + 2,
col=2,
value=review_list_rank[index_rank]['Rating']))
cells_rank.append(
Cell(row=index_rank + 2,
col=3,
value=review_list_rank[index_rank]['Confidence']))
cells_rank.append(
Cell(row=index_rank + 2,
col=4,
value=review_list_rank[index_rank]['Count']))
review_sheet_rank.update_cells(cells_rank)
return redirect('/')
def main():
compare_list = get_compare_list()
sheet_list = sheet.get_all_records()
oems = []
for i in range(len(sheet_list)):
seller_names = sheet_list[i]['SELLER_NAME'].lower().split(' ')
inventory = sheet_list[i]['INVENTORY_URL'].lower()
tmp_flag = True
empty_flag = True
for cmp_name in compare_list:
if cmp_name in inventory:
oems.append(Origin_Makes[compare_list.index(cmp_name)])
tmp_flag = False
empty_flag = False
break
elif 'benz' in inventory:
oems.append('Mercedes Benze')
tmp_flag = False
empty_flag = False
break
elif inventory[:2] == 'mb':
oems.append('Mercedes Benze')
tmp_flag = False
empty_flag = False
break
elif 'cdjr' in inventory:
oems.append('Chrysler Dodge Jeep Ram')
tmp_flag = False
empty_flag = False
break
elif 'cdj' in inventory:
oems.append('Chrysler Dodge Jeep')
tmp_flag = False
empty_flag = False
break
elif 'vw' in inventory:
oems.append('Volkswagen')
tmp_flag = False
empty_flag = False
break
if tmp_flag:
for seller_name in seller_names:
if seller_name in compare_list:
oems.append(Origin_Makes[compare_list.index(seller_name)])
empty_flag = False
break
if empty_flag:
oems.append('')
cells = []
for i, val in enumerate(oems):
print(val)
cells.append(Cell(row=i+2, col=15, value=val))
sheet.update_cells(cells)
def writeSheet(self,grades,sheet):
dates_to_fill = {date: sheet.find(date) for date in set(grades['dates']) if date != 'No Date'}
waster_sample_mapping = {value:key+4 for key, value in grades['SampleID'].to_dict().items()}
cells = []
for i in range(len(grades)):
sample_row = waster_sample_mapping[grades.iloc[i]['SampleID']]
if dates_to_fill.get(grades.iloc[i]['dates'],None):
print(grades.iloc[i]['grade'],grades.iloc[i]['dates'],grades.iloc[i]['SampleID'])
cells.append(Cell(row=sample_row, col=dates_to_fill[grades.iloc[i]['dates']].col, value=grades.iloc[i]['grade']))
res = sheet.update_cells(cells) if len(cells) > 0 else None
return res
def update_ct_meta(new_data, client, sheet_name='SARCOV2-Metadata'):
messages = []
old = None
if os.path.exists('old_samples'):
old = [x.strip() for x in open('old_samples').readlines()]
sheet = client.open(sheet_name).sheet1
all_values = sheet.get_all_records()
column_position = sheet.row_values(1)
row_position = sheet.col_values(1)
no_ct_meta_data = set(row_position) - set(new_data.keys())
if old:
no_ct_meta_data = set(no_ct_meta_data) - set(old)
logging.info('No CT META found for ' + ','.join(no_ct_meta_data))
messages.append('No metadata in input sheet found for ' +
', '.join(no_ct_meta_data))
cells_to_update = []
duplicate_sheet = ','.join([
item for item, count in collections.Counter(row_position).items()
if count > 1
])
logging.info(f'Following records are duplicated in master sheet: \
{duplicate_sheet}')
messages.append(
f'Following records are duplicated in master sheet: {duplicate_sheet}\n'
)
missing_in_master = '\n'.join(
list(set(new_data.keys()) - set(row_position)))
messages.append(
'Following records appear in the CT sheet, but NOT in the master sheet:\n'
+ missing_in_master)
logging.info('PLEASE ADD THESE ROWS\n' + missing_in_master)
for x in all_values:
ct_metadata = new_data.get(x['central_sample_id'])
if ct_metadata:
for k, v in ct_metadata.items():
if k in column_position:
cells_to_update.append(
Cell(row=row_position.index(x['central_sample_id']) +
1,
col=column_position.index(k) + 1,
value=v))
if cells_to_update:
logging.info('Updating values')
sheet.update_cells(cells_to_update)
else:
logging.info('All values sync. Nothing to update')
return messages
def write_func(self, tweets_row):
new_row = 0
cells = []
for row in range(len(tweets_row)):
new_row = self.end_row + row + 1
#print(tweets_row[0][1])
cells.append(Cell(row=new_row, col = self.timestamp, value = tweets_row[row][0]))
cells.append(Cell(row=new_row, col = self.goal, value = tweets_row[row][1]))
cells.append(Cell(row=new_row, col = self.assist1, value = tweets_row[row][2]))
cells.append(Cell(row=new_row, col = self.assist2, value = tweets_row[row][3]))
cells.append(Cell(row=new_row, col = self.team1, value = tweets_row[row][4]))
cells.append(Cell(row=new_row, col = self.score1, value = tweets_row[row][5]))
cells.append(Cell(row=new_row, col = self.team2, value = tweets_row[row][6]))
cells.append(Cell(row=new_row, col = self.score2, value = tweets_row[row][7]))
cells.append(Cell(row=new_row, col = self.gametime, value = tweets_row[row][8]))
self.sheet.update_cells(cells)
self.end_row = new_row
return 0
def simulate_update_symbols_price(self, symbol_value_dict):
"""
update the last value of a list of symbols
:param dict symbol_value_dict:
:return:
"""
cell_list = []
symbol_list = self.get_list_of_current_symbol()
for i, symbol in enumerate(symbol_list):
# skip header
if i == 0:
continue
symbol_row = i + 1
val = symbol_value_dict.get(symbol, 0) / 10 # Convert to Toman
print(symbol_row, ":", symbol, val)
cell_list.append(Cell(symbol_row, 2, val))
return cell_list
def _get_all_values(worksheet, evaluate_formulas):
data = worksheet.spreadsheet.values_get(
worksheet.title,
params={
"valueRenderOption": (
"UNFORMATTED_VALUE" if evaluate_formulas else "FORMULA"
),
"dateTimeRenderOption": "FORMATTED_STRING",
},
)
(row_offset, column_offset) = (1, 1)
(last_row, last_column) = (worksheet.row_count, worksheet.col_count)
values = data.get("values", [])
rect_values = fill_gaps(
values,
rows=last_row - row_offset + 1,
cols=last_column - column_offset + 1,
)
cells = [
Cell(row=i + row_offset, col=j + column_offset, value=value)
for i, row in enumerate(rect_values)
for j, value in enumerate(row)
]
# defaultdicts fill in gaps for empty rows/cells not returned by gdocs
rows = defaultdict(lambda: defaultdict(str))
for cell in cells:
row = rows.setdefault(int(cell.row), defaultdict(str))
row[cell.col] = cell.value
if not rows:
return []
all_row_keys = chain.from_iterable(row.keys() for row in rows.values())
rect_cols = range(1, max(all_row_keys) + 1)
rect_rows = range(1, max(rows.keys()) + 1)
return [[rows[i][j] for j in rect_cols] for i in rect_rows]
def update_civet_meta(new_dict, client, sheet_name='SARCOV2-Metadata'):
sheet = client.open(sheet_name).sheet1
all_values = sheet.get_all_records()
column_position = sheet.row_values(1)
row_position = sheet.col_values(1)
cells_to_update = []
for x in all_values:
civet_metadata = new_dict.get(x['central_sample_id'])
if civet_metadata:
for k, v in civet_metadata.items():
if k in column_position:
cells_to_update.append(
Cell(row=row_position.index(x['central_sample_id']) +
1,
col=column_position.index(k) + 1,
value=v))
if cells_to_update:
logging.info('Updating values')
sheet.update_cells(cells_to_update)
else:
logging.info('All values sync. Nothing to update')
def read_row_values(sheet_name, column_name, symbol, row_data):
ws = get_sheet().worksheet(sheet_name)
first_row_vals = ws.row_values(1)
for col in first_row_vals:
if col.lower() == column_name.lower():
col_index = first_row_vals.index(col)
break
# print(col_values[len(col_values) - 1]) # last value from column
row_index = int(next_available_row(ws)) - 1
row_values = ws.row_values(row_index) # Last row values
# print(f"Column value: {row_values[col_index]}")
if row_values[col_index] == row_data[0]:
if symbol == 'NIFTY' and row_values[col_index + 1] == "":
cells = [
Cell(row=row_index, col=2, value=row_data[1]),
Cell(row=row_index, col=3, value=row_data[2]),
Cell(row=row_index, col=4, value=row_data[3])
]
ws.update_cells(cells)
return -1
elif symbol == 'BANKNIFTY' and len(row_values) < 5:
cells = [
Cell(row=row_index, col=5, value=row_data[1]),
Cell(row=row_index, col=6, value=row_data[2]),
Cell(row=row_index, col=7, value=row_data[3])
]
ws.update_cells(cells)
return -1
return row_values
else:
if symbol == 'BANKNIFTY':
row_data.insert(1, "")
row_data.insert(2, "")
row_data.insert(3, "")
ws.append_row(row_data)
return -1
def updatefiles():
global lastupdate
try:
jobs = ss.worksheet("_Jobs")
jobsmap = ss.worksheet("_JobMap")
except gspread.WorksheetNotFound:
jobs = ss.add_worksheet("_Jobs", 101, 4)
jobs.append_row(
["Created", "Updated", "Completed", "Progress", "FP", "Name"])
jobsmap = ss.add_worksheet("_JobMap", MAX_JOBS + 1, 5)
jobsmap.append_row(
["SheetID", "JobName", "Completed", "Started", "Last Updated"])
for i in range(MAX_JOBS):
jobsmap.append_row([
"Sheet%d" % (i + 1), ".", "No", "0",
"=INDIRECT(A2&\"!A2\", TRUE)"
])
files = [f for f in os.listdir(LOGS_DIR) if f.endswith(LOGS_EXTENSION)]
for f in files:
currtime = time.time()
epochtime = currtime / (60 * 60 * 24) + 25569
name = f[:-len(LOGS_EXTENSION)]
if name == ".":
print("Invalid job name '.'")
continue
f = os.path.join(LOGS_DIR, f)
last_modified = os.stat(f).st_mtime
if last_modified < lastupdate:
continue
print("Updating %s" % name)
# Find sheet for job
currjobs = jobsmap.col_values(2)
try:
idx = currjobs.index(name)
jobsheet = ss.worksheet(idx2sheet(idx))
jobsmap.update_cell(idx + 1, 3, 'No')
except ValueError:
try:
idx = currjobs.index('.')
except ValueError:
currcompletion = jobsmap.col_values(3)
try:
idx = currcompletion.index('Yes')
except ValueError:
continue
jobsmap.update_cells([
Cell(idx + 1, 2, name),
Cell(idx + 1, 3, 'No'),
Cell(idx + 1, 4, epochtime)
])
currjobs[idx] = name
sheetid = idx2sheet(idx)
try:
jobsheet = ss.worksheet(sheetid)
jobsheet.clear()
except gspread.WorksheetNotFound:
jobsheet = ss.add_worksheet(sheetid, 50, 5)
jobsheet.append_row(
['Timestamp', 'Line', 'Progress', 'Status', 'Task'])
jobs.append_row([epochtime, epochtime, '', 0, 0, name])
# Update job info
rowidx = jobs.find(name).row
row = jobs.row_values(rowidx)
completed = False
fp = int(row[FP_COL - 1])
fd = open(f, "r")
fd.seek(0, 2)
filelen = fd.tell()
# TODO: Check previous N characters match previous line
if filelen < fp:
# This job got restarted; update status
jobsmap.update_cells([
Cell(idx + 1, 2, name),
Cell(idx + 1, 3, 'No'),
Cell(idx + 1, 4, epochtime)
])
jobsheet.clear()
jobsheet.append_row([
'Timestamp', 'Line', 'Progress', 'Status', 'Task', "Rate",
"Median Rate", "Rate Ratio", "Smoothed Rate", "ETA"
])
jobs.update_cells([
Cell(rowidx, 1, epochtime),
Cell(rowidx, UPDATED_COL, epochtime),
Cell(rowidx, COMPLETED_COL, ''),
Cell(rowidx, PROGRESS_COL, 0),
Cell(rowidx, FP_COL, 0)
])
fp = 0
percentage = 0
else:
percentage = float(row[PROGRESS_COL - 1])
fd.seek(fp)
linesbuffer = RingBuffer(MAX_LINES)
status = ""
task = ""
for line in fd.readlines():
if line.strip():
if line.startswith("status:") or line.startswith("Status:"):
status = line[len("status:"):].strip()
if line.startswith("task:") or line.startswith("Task:"):
task = line[len("task:"):].strip()
line_progress = findprogress(line)
if line_progress > 0:
percentage = line_progress
line = [
epochtime, line[:-1], percentage,
status if status else "=D3", task if task else "=E3"
]
line.append(
"=if(ISBLANK(C3),0,if(OR(A2=A3,C2=C3),F3,(A2-A3)/(C2-C3)))"
) # Rate
line.append("=MEDIAN(F2:F11)") # Median Rate
line.append("=if(F2=0,0,G2/F2)") # Rate Ratio
cond = "H2:H11,\">0.95\",H2:H11, \"<1.05\""
line.append("=if(COUNTIFS(%s)=0,H2,AVERAGEIFS(F2:F11,%s))" %
(cond, cond)) # Smoothed Rate
line.append("=if(I2=0,\"inf\",A2+(100-C2)*I2)") # ETA
linesbuffer.push(line)
for line in linesbuffer:
jobsheet.insert_row(line, 2, value_input_option='USER_ENTERED')
if status.startswith("Error") or status.startswith("error"):
jobsmap.update_cell(currjobs.index(name) + 1, 3, 'Error')
if percentage == 100:
completed = True
# FIXME: Other completion criteria
jobsmap.update_cell(currjobs.index(name) + 1, 3, 'Yes')
fp = fd.tell()
updated_cells = [
Cell(rowidx, UPDATED_COL, epochtime),
Cell(rowidx, PROGRESS_COL, percentage),
Cell(rowidx, FP_COL, fp)
]
if completed:
updated_cells.append(Cell(rowidx, COMPLETED_COL, epochtime))
jobs.update_cells(updated_cells)
lastupdate = currtime
import os.path
import json
import re
from gspread.models import Cell
from gspread_dataframe import _cellrepr
def contents_of_file(filename, et_parse=True):
with open(os.path.join(os.path.dirname(__file__), filename), "r") as f:
return json.load(f)
SHEET_CONTENTS_FORMULAS = contents_of_file("sheet_contents_formulas.json")
SHEET_CONTENTS_EVALUATED = contents_of_file("sheet_contents_evaluated.json")
CELL_LIST = [
Cell(row=i + 1, col=j + 1, value=value)
for i, row in enumerate(contents_of_file("cell_list.json"))
for j, value in enumerate(row)
]
CELL_LIST_STRINGIFIED = [
Cell(
row=i + 1,
col=j + 1,
value=_cellrepr(
value,
allow_formulas=True,
string_escaping=re.compile(r"3e50").match,
),
) for i, row in enumerate(contents_of_file("cell_list.json"))
for j, value in enumerate(row)
def set_with_dataframe(worksheet,
dataframe,
row=1,
col=1,
include_index=False,
include_column_header=True,
resize=False,
allow_formulas=True):
"""
Sets the values of a given DataFrame, anchoring its upper-left corner
at (row, col). (Default is row 1, column 1.)
:param worksheet: the gspread worksheet to set with content of DataFrame.
:param dataframe: the DataFrame.
:param include_index: if True, include the DataFrame's index as an
additional column. Defaults to False.
:param include_column_header: if True, add a header row before data with
column names. (If include_index is True, the index's name will be
used as its column's header.) Defaults to True.
:param resize: if True, changes the worksheet's size to match the shape
of the provided DataFrame. If False, worksheet will only be
resized as necessary to contain the DataFrame contents.
Defaults to False.
:param allow_formulas: if True, interprets `=foo` as a formula in
cell values; otherwise all text beginning with `=` is escaped
to avoid its interpretation as a formula. Defaults to True.
"""
# x_pos, y_pos refers to the position of data rows only,
# excluding any header rows in the google sheet.
# If header-related params are True, the values are adjusted
# to allow space for the headers.
y, x = dataframe.shape
if include_index:
x += 1
if include_column_header:
y += 1
if resize:
worksheet.resize(y, x)
updates = []
if include_column_header:
elts = list(dataframe.columns)
if include_index:
elts = [dataframe.index.name] + elts
for idx, val in enumerate(elts):
updates.append((row, col + idx, _cellrepr(val, allow_formulas)))
row += 1
values = []
for value_row, index_value in zip_longest(dataframe.values,
dataframe.index):
if include_index:
value_row = [index_value] + list(value_row)
values.append(value_row)
for y_idx, value_row in enumerate(values):
for x_idx, cell_value in enumerate(value_row):
updates.append(
(y_idx + row, x_idx + col, _cellrepr(cell_value,
allow_formulas)))
if not updates:
logging.debug("No updates to perform on worksheet.")
return
cells_to_update = [Cell(row, col, value) for row, col, value in updates]
logging.debug("%d cell updates to send", len(cells_to_update))
resp = worksheet.update_cells(cells_to_update,
value_input_option='USER_ENTERED')
logging.debug("Cell update response: %s", resp)
def createDoc(self):
print('Create new document...')
spreadsheet = self.gspreadWrapper.gc.create(
self.options.VCAMasterFileName)
spreadsheet.share(self.options.accountEmail,
perm_type='user',
role='writer')
print('Create sheet...')
worksheet = spreadsheet.get_worksheet(0)
worksheet.update_title("Assessments")
cellsToAdd = []
# Set headings
print('Set headings...')
headings = [
self.options.assessmentsIdColumn, self.options.ideaURLColumn,
self.options.questionColumn, self.options.ratingColumn,
self.options.assessorColumn, self.options.assessmentColumn,
self.options.proposerMarkColumn, self.options.fairColumn,
self.options.topQualityColumn, self.options.profanityColumn,
self.options.scoreColumn, self.options.copyColumn,
self.options.wrongChallengeColumn,
self.options.wrongCriteriaColumn, self.options.otherColumn,
self.options.otherRationaleColumn
]
for i, value in enumerate(headings):
cellsToAdd.append(Cell(row=1, col=(i + 1), value=value))
print('Set column width...')
set_column_widths(worksheet, [('A', 40), ('B:C', 200), ('D', 40),
('E', 120), ('F', 400), ('G:O', 30),
('P', 300)])
print('Format columns')
noteFormat = cellFormat(wrapStrategy='CLIP')
flagFormat = cellFormat(textFormat=textFormat(bold=True),
horizontalAlignment='CENTER')
format_cell_ranges(worksheet,
[('D:D', flagFormat), ('F:F', noteFormat),
('G:O', flagFormat)])
print('Load proposers flagged reviews...')
assessments = self.gspreadWrapper.getProposersData()
# extract Assessors
assessors = self.gspreadWrapper.groupByAssessor(assessments)
# filter assessors with more than allowed blank reviews.
excludedAssessors = [
k for k in assessors if (assessors[k]['blankPercentage'] >=
self.options.allowedBlankPerAssessor)
]
includedAssessors = [
k for k in assessors if (assessors[k]['blankPercentage'] <
self.options.allowedBlankPerAssessor)
]
proposersDoc = self.gspreadWrapper.gc.open_by_key(
self.options.proposersFile)
self.gspreadWrapper.createSheetFromGroup(proposersDoc, 'Excluded CAs',
assessors, excludedAssessors,
['assessments'])
self.gspreadWrapper.createSheetFromGroup(proposersDoc, 'Included CAs',
assessors, includedAssessors,
['assessments'])
# Add sheet for excluded/included assessors
index = 2
print('Cloning flagged reviews...')
for assessment in assessments:
if (assessment[self.options.assessorColumn]
not in excludedAssessors):
marked = 'x' if (
(assessment[self.options.profanityColumn] == 'x') or
(assessment[self.options.scoreColumn] == 'x') or
(assessment[self.options.copyColumn] == 'x') or
(assessment[self.options.wrongChallengeColumn] == 'x') or
(assessment[self.options.wrongCriteriaColumn] == 'x') or
(assessment[self.options.otherColumn] == 'x')) else ''
cellsToAdd.extend([
Cell(row=index,
col=1,
value=assessment[self.options.assessmentsIdColumn]),
Cell(row=index,
col=2,
value=assessment[self.options.ideaURLColumn]),
Cell(row=index,
col=3,
value=assessment[self.options.questionColumn]),
Cell(row=index,
col=4,
value=assessment[self.options.ratingColumn]),
Cell(row=index,
col=5,
value=assessment[self.options.assessorColumn]),
Cell(row=index,
col=6,
value=assessment[self.options.assessmentColumn]),
Cell(row=index, col=7, value=marked)
])
index = index + 1
worksheet.update_cells(cellsToAdd, value_input_option='USER_ENTERED')
print('Master Document for vCAs created')
print('Link: {}'.format(spreadsheet.url))
def _fetch_cells(self):
return [Cell(self, elem) for elem in CELL_FEED.findall(_ns('entry'))]
# team name to standing (from bball ref)
# convert to team code to standing to avoid
# string comparisons, after retrieving standings
# sort alphabetically to relate to codes
eastNameToStanding = {}
westNameToStanding = {}
eastCodeToStanding = {}
westCodeToStanding = {}
westRows = tree.xpath(
'//table[@id="confs_standings_W"]/tbody/tr[contains(@class, "full_table")]'
)
for i in range(len(westRows)):
westrow = westRows[i]
standingsCells.append(Cell(i + 3, 3, westrow[0].text_content()))
westNameToStanding.update({westrow[0].text_content(): i})
eastRows = tree.xpath(
'//table[@id="confs_standings_E"]/tbody/tr[contains(@class, "full_table")]'
)
for i in range(len(eastRows)):
eastrow = eastRows[i]
standingsCells.append(Cell(i + 3, 5, eastrow[0].text_content()))
eastNameToStanding.update({eastrow[0].text_content(): i})
codeIndex = 0
for key in sorted(westNameToStanding.keys()):
westCodeToStanding.update(
{westTeamCodeAlphabetical[codeIndex]: westNameToStanding.get(key)})
codeIndex += 1
def save_logs_to_sheet(spreadsheet, sheetname, logs):
worksheet = spreadsheet.worksheet(sheetname)
lines = logs.split('\n')
cells = [Cell(1+number,1, line) for line, number in zip(lines, range(len(lines)))]
worksheet.update_cells(cells)
def set_with_dataframe(worksheet,
dataframe,
row=1,
col=1,
include_index=False,
include_column_header=True,
resize=False,
allow_formulas=True):
"""
Sets the values of a given DataFrame, anchoring its upper-left corner
at (row, col). (Default is row 1, column 1.)
:param worksheet: the gspread worksheet to set with content of DataFrame.
:param dataframe: the DataFrame.
:param include_index: if True, include the DataFrame's index as an
additional column. Defaults to False.
:param include_column_header: if True, add a header row or rows before data with
column names. (If include_index is True, the index's name(s) will be
used as its columns' headers.) Defaults to True.
:param resize: if True, changes the worksheet's size to match the shape
of the provided DataFrame. If False, worksheet will only be
resized as necessary to contain the DataFrame contents.
Defaults to False.
:param allow_formulas: if True, interprets `=foo` as a formula in
cell values; otherwise all text beginning with `=` is escaped
to avoid its interpretation as a formula. Defaults to True.
"""
# x_pos, y_pos refers to the position of data rows only,
# excluding any header rows in the google sheet.
# If header-related params are True, the values are adjusted
# to allow space for the headers.
y, x = dataframe.shape
index_col_size = 0
column_header_size = 0
if include_index:
index_col_size = _determine_index_column_size(dataframe.index)
x += index_col_size
if include_column_header:
column_header_size = _determine_column_header_size(dataframe.columns)
y += column_header_size
if resize:
worksheet.resize(y, x)
else:
_resize_to_minimum(worksheet, y, x)
updates = []
if include_column_header:
elts = list(dataframe.columns)
# if columns object is hierarchical multi-index, it will span multiple rows
if column_header_size > 1:
elts = list(dataframe.columns)
if include_index:
if hasattr(dataframe.index, 'names'):
index_elts = dataframe.index.names
else:
index_elts = dataframe.index.name
if not isinstance(index_elts, (list, tuple)):
index_elts = [index_elts]
elts = [((None, ) * (column_header_size - 1)) + (e, )
for e in index_elts] + elts
for level in range(0, column_header_size):
for idx, tup in enumerate(elts):
updates.append(
(row, col + idx, _cellrepr(tup[level],
allow_formulas)))
row += 1
else:
elts = list(dataframe.columns)
if include_index:
if hasattr(dataframe.index, 'names'):
index_elts = dataframe.index.names
else:
index_elts = dataframe.index.name
if not isinstance(index_elts, (list, tuple)):
index_elts = [index_elts]
elts = list(index_elts) + elts
for idx, val in enumerate(elts):
updates.append((row, col + idx, _cellrepr(val,
allow_formulas)))
row += 1
values = []
for value_row, index_value in zip_longest(dataframe.values,
dataframe.index):
if include_index:
if not isinstance(index_value, (list, tuple)):
index_value = [index_value]
value_row = list(index_value) + list(value_row)
values.append(value_row)
for y_idx, value_row in enumerate(values):
for x_idx, cell_value in enumerate(value_row):
updates.append(
(y_idx + row, x_idx + col, _cellrepr(cell_value,
allow_formulas)))
if not updates:
logger.debug("No updates to perform on worksheet.")
return
cells_to_update = [Cell(row, col, value) for row, col, value in updates]
logger.debug("%d cell updates to send", len(cells_to_update))
resp = worksheet.update_cells(cells_to_update,
value_input_option='USER_ENTERED')
logger.debug("Cell update response: %s", resp)
