class Allocation: def __init__(self): self.sheet = Sheet() def generate_allocation(self, candidates): allocation = logics.get_random_allocation(candidates) return allocation def create_allocation(self, match, allocations): if not self.sheet.if_exists(configs.ALLOCATIONS_SHEET_ID, match['no']): response = self.sheet.create_sheet(configs.ALLOCATIONS_SHEET_ID, match['no']) values = [["Match Number", match['no'], '', '', 'Created Date Time']] values.append([ "Teams", match["team1"] + ' - vs - ' + match["team2"], '', '', str(datetime.datetime.now()) ]) values.append(["No", "Email", "Name", "Player 1", "Player 2"]) fdic = [] for i in range(1, len(allocations) + 1): fdic.append([ data for data in allocations if int(data['candidate']['id']) == i ][0]) allocations = fdic for alloc in allocations: values.append([ alloc['candidate']['id'], alloc['candidate']['email'], alloc['candidate']['name'], alloc['players'][0], alloc['players'][1] ]) self.sheet.update_values(configs.ALLOCATIONS_SHEET_ID, match['no'] + "!A1", 'USER_ENTERED', values) return values def get_allocation(self, match): rows = self.sheet.get_rows( configs.ALLOCATIONS_SHEET_ID, "'" + match + "'!" + configs.ALLOCATIONS_GET_SHEET_RANGE_POSTFIX) allocations = [] for row in rows: allocations.append({ 'no': row[0], 'name': row[1], 'email': row[2], 'player1': row[3], 'player2': row[4] }) return allocations
class CandidateResult: def __init__(self): self.sheet = Sheet() def add_results(self, match, results): if not self.sheet.if_exists(configs.CANDIDATE_RESULTS_SHEET_ID, match['no']): response = self.sheet.create_sheet( configs.CANDIDATE_RESULTS_SHEET_ID, match['no']) values = [[ "Match Number", match['no'], '', '', '', '', '', '', '', 'Created Date Time' ]] values.append([ "Teams", match["team1"] + ' - vs - ' + match["team2"], '', '', '', '', '', '', '', str(datetime.datetime.now()) ]) values.append([ "No", "Name", "Email", "Player 1", "Player 1 runs, wickets", "Player 1 Points", "Player 2", "Player 2 runs, wickets", "Player 2 Points", "Total Points" ]) point_results = results['results'] for res in point_results: values.append([ res['no'], res['email'], res['name'], res['player1'] + '(' + res['player1_name'] + ')', res['player1_run_wicket'], res['player1_points'], res['player2'] + '(' + res['player2_name'] + ')', res['player2_run_wicket'], res['player2_points'], res['total'] ]) values.append(["Winners", '', '', '', '', '', '', '', '', '']) winners = results['winners'] for winner in winners: values.append([ winner['no'], winner['email'], winner['name'], winner['player1'] + '(' + winner['player1_name'] + ')', winner['player1_run_wicket'], winner['player1_points'], winner['player2'] + '(' + winner['player2_name'] + ')', winner['player2_run_wicket'], winner['player2_points'], winner['total'] ]) self.sheet.update_values(configs.CANDIDATE_RESULTS_SHEET_ID, match['no'] + "!A1", 'USER_ENTERED', values) return values
def pin_added(body, client, context, logger): context.ack() event = body["event"] channel_id = event["channel_id"] channel_name = client.conversations_info(channel=channel_id)["channel"]["name"] pinned_at = datetime.datetime.fromtimestamp(event["item"]["created"]) pinned_at = pinned_at.strftime("%d/%m/%Y %H:%M:%S") message = event["item"]["message"]["text"] message_by = client.users_info(user=event["item"]["message"]["user"]) message_username = message_by["user"]["profile"]["display_name"] permalink = event["item"]["message"]["permalink"] row = [pinned_at, channel_name, message_username, message, permalink] pins_sheet.append(row)
class Candidate(CandidateBase): def __init__(self): self.sheet = Sheet() def get_all(self): cands = self.sheet.get_rows(configs.CANDIDATES_SHEET_ID, configs.CANDIDATES_SHEET_RANGE) return [{'id': cand[0], 'name': cand[1], 'email': cand[2]} for cand in cands]
def message_saver(mention_event, thread_content): thread_start_msg = thread_content.data["messages"][0] txt = thread_start_msg["text"] channel = mention_event["channel"] thread_ts = mention_event["thread_ts"] try: client.reactions_add(channel=channel, timestamp=thread_ts, name="floppy_disk") except SlackApiError: pass username = client.users_info( user=thread_start_msg["user"])["user"]["profile"]["display_name"] dt = datetime.fromtimestamp(float(thread_ts)).strftime("%d/%m/%Y %H:%M:%S") pins_sheet.append([channel, dt, username, txt], subsheet=MESSAGE_SAVER_SHEET) client.chat_postMessage(text=f"```{txt}``` Aldim bunu.", thread_ts=thread_ts, channel=channel)
def add_links_to_sheet(links, channel_name): today_start = datetime.today().replace(minute=0, hour=0, second=0, microsecond=0) inserted_link_count = 0 user_map = {} links.sort(key=lambda tup: tup[1]) for user_id, msg_ts, title, link in links: pinned_at = datetime.fromtimestamp(msg_ts) if pinned_at > today_start: username = user_map.get(user_id) if not username: username = user_map[user_id] = client.users_info( user=user_id)["user"]["profile"]["display_name"] pinned_at = pinned_at.strftime("%d/%m/%Y %H:%M:%S") pins_sheet.append([pinned_at, title, link, username], subsheet=channel_name) inserted_link_count += 1 return inserted_link_count
def main(): ''' This program will be able to execute multiple tasks to fetch data and put it in Google Sheets Task: A task is a operation of fetching data from a MySql DataBase and uploads it to a Google Sheet How to make a new Task: 1: Make a new directory in the 'tasks' directory 2: Create a 'credentials.json' file - Read doc's in 'configuration.py' for layout 3: Create a 'query.sql' file ''' tasks_dir = "tasks/" for task in os.listdir(tasks_dir): if os.path.isdir('{}/{}'.format(tasks_dir, task)): config = Configuration(task_path=task) try: # Get data from database dbData = DataBase(config=config).getTableData() columns = dbData['column_names'] data = dbData['table_data'] # Upload data to sheet sheet = Sheet(config=config) sheet.clear_sheet() sheet.upload_data(columns=columns, data=data, date_time=True) printMessage("Completed task: " + task) except Exception as error: printMessage("{} => {}".format(task, error)) traceback.print_exc() return 0
class Match: def __init__(self): self.sheet = Sheet() def get_all(self): vals = self.sheet.get_rows(configs.MATCHES_SHEET_ID, configs.MATCHES_SHEET_RANGE) matches = [] for val in vals: matches.append({'no': val[0], 'team1': val[1], 'team2': val[2]}) return matches def get(self, match_no): matches = self.get_all() return [match for match in matches if match['no'] == match_no][0]
class MatchResults: def __init__(self): self.sheet = Sheet() def get(self, match_no): vals = self.sheet.get_rows( configs.MATCH_RESULTS_SHEET_ID, "'" + match_no + "'" + configs.MATCH_RESULTS_SHEET_RANGE_POSTFIX) results = [] for val in vals: results.append({ 'player': val[0], 'player_name': val[1], 'runs': val[2], 'wickets': val[3] }) return results
from flask import Flask, request, session, g, redirect, url_for, abort, \ render_template, flash from sheets import Sheet from local_settings import II_SHEET_ID, II_SHEET_RANGE app = Flask(__name__) app.config.from_object(__name__) innovating_instruction = Sheet( II_SHEET_ID, II_SHEET_RANGE) @app.route('/') def hello_world(): return render_template('map.html', locations=innovating_instruction.get_locations())
def __init__(self): self.sheet = Sheet()
from lights import Light, LightSet from sheets import Sheet from time import sleep from datetime import datetime, timedelta from people import Person import os datasource = f'{os.environ["GOOGLE_SHEET_KEY"]}' # dictionary to store current status person_status = {'Zaks-iPhone': None, 'Louiseās iPhone': None} sheet = Sheet(datasource) lights = LightSet() for i in range(1, 13): lights.add_light(i, Light(i)) # louise = Person('Louise') # zak = Person('Zak') previous_index = None async def poll_event(loop, prev_datetime): global previous_index records_since = sheet.get_records_since_index(previous_index)