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)