-
Notifications
You must be signed in to change notification settings - Fork 1
/
main.py
247 lines (199 loc) · 8.08 KB
/
main.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
# Google Spreadsheet Libraries
import gspread
from oauth2client.service_account import ServiceAccountCredentials
# GUI Libraries
from tkinter import *
from tkinter import ttk
from ttkthemes import ThemedStyle
# Regular Expressions Libary
import re
# File Flush
import os
# Other libraries
from datetime import datetime
# Global Variables
CELL_COL = "D"
CELL_COL_LETTER = "D"
CELL_CONTENT = "1"
GOOGLE_SHEETS_FILENAME = "Project Test 1"
# use creds to create a client to interact with the Google Drive API
scope = ['https://spreadsheets.google.com/feeds',
'https://www.googleapis.com/auth/drive']
creds = ServiceAccountCredentials.from_json_keyfile_name('client_secret.json', scope)
client = gspread.authorize(creds)
# Find a workbook by name and open the first sheet
# Make sure you use the right name here.
sheet = client.open(GOOGLE_SHEETS_FILENAME).sheet1
# Initialize Tkinter GUI Window
root = Tk()
root.title("UCI RCC Google Sheets Parser")
style = ThemedStyle(root)
style.set_theme("clearlooks")
# Clear/erase input.txt File
fp = open("input.txt", "w+")
fp.seek(0)
fp.truncate()
fp.close()
# Tkinter Command Functions
def retrieve_spreadsheet_name(self, *args):
global GOOGLE_SHEETS_FILENAME
GOOGLE_SHEETS_FILENAME = spreadsheet_name.get()
# New implementation: Inputting Letters
def retrieve_cell_col(self, *args):
global CELL_COL
global CELL_COL_LETTER # Audit Trail Implementation
CELL_COL_LETTER = cell_col.get()
CELL_COL_LETTER = CELL_COL_LETTER.upper()
result = 0
for i, T, in enumerate(CELL_COL_LETTER[::-1]):
letter_number = ord(T) - ord("A") + 1
result += letter_number * (26 ** i)
CELL_COL = result
def retrieve_cell_content(self, *args):
global CELL_CONTENT
CELL_CONTENT = cell_content.get()
# onButton
def retrieveInputForTextFile():
input_value = input_file_textbox.get("1.0", "end-1c")
# Open file i/o
try:
fp = open("input.txt", "w")
except:
# print("File to open does not exist!")
progress_message.set("Failed to open input.txt, file does not exist!")
fp.write(input_value) # Write to input.txt file
# Commands for real-time updating input.txt file
fp.flush()
os.fsync(fp.fileno())
# print(input_value) # Debugging/Printing to Console
input_file_textbox.delete(1.0, END) # Clear's TextBox Widget on Success
fp.close() # Close the file i/o
# Gspread Functions
def makeChangesToSpreadsheet():
# Edge cases
# Should not overwrite member names
if CELL_COL == 1:
progress_message.set("Invalid option, cannot update cells that contain member names.")
return
# Set Google Sheets
try:
sheet = client.open(GOOGLE_SHEETS_FILENAME).sheet1
except:
progress_message.set("Invalid Google Sheets Filename.")
return
# Attempt to open input.txt File
try:
fpr = open("input.txt", "r")
except:
print("File to open does not exist!")
# Attempt to open error.txt File
try:
fp_err = open("audit/error.txt", "a")
except:
print("Failed to open error.txt")
# Attempt to open audit_log.txt File
try:
fp_audit_log = open("audit/audit_log.txt", "a")
except:
print("Failed to open error.txt")
if os.stat("input.txt").st_size == 0:
progress_message.set("The input.txt file is empty! No changes were made.")
return
names = fpr.readlines()
# Variables to keep track of errors and progress
entries_changed = 0
errors = 0
# Retrieve current date
now = datetime.now()
current_time = now.strftime("%m/%d/%Y %H:%M:%S")
fp_audit_log.write("\n------------------------------------\n")
fp_audit_log.write(current_time)
fp_audit_log.write(f"\n[{CELL_CONTENT}] points assigned for these members")
fp_audit_log.write("\n------------------------------------\n")
for name in names:
try:
name = name.strip() # Remove trailing characters
name_regex = re.compile(name, re.IGNORECASE)
cell = sheet.find(name_regex)
# print("Found something at R%s C%s" % (cell.row, cell.col))
# print("Updating values B%s\n" % (cell.row))
# ex.) B1 -> (Column + Row)
sheet.update_cell(cell.row, CELL_COL, CELL_CONTENT) # Update the value of the current cell
entries_changed += 1
# Write to audit log
fp_audit_log.write(f"{CELL_COL_LETTER}{cell.row} --- {name}\n")
except:
# print("FAILED TO COMPUTE FOR: %s\n" % (name)) # Error Message for feedback
fp_err.write(f"FAILED TO COMPUTE FOR: {name}\n at {current_time}\n") # Error Message for feedback to error.txt
errors += 1
name = fpr.readline().strip() # Continue iterating through file
continue # Skip back to beginning of loop
# Updating entries_changed label
if (entries_changed == 1):
entries_changed_counter.set("%s cell has been updated." % (entries_changed))
else:
entries_changed_counter.set("%s cells have been updated." % (entries_changed))
# Updating errors_occurred label
if (errors == 0):
errors_occured_counter.set("%s errors have occured." % (errors))
elif (errors == 1):
errors_occured_counter.set("%s error has occured.\nCheck the error.txt file for more information." % (errors))
else:
errors_occured_counter.set("%s errors have occured.\nCheck the error.txt file for more information." % (errors))
# Updating progress_message label
if entries_changed > 0:
progress_message.set("Success!")
if errors > 0:
progress_message.set("Successfully inputted some cells, check error log.")
elif entries_changed == 0:
progress_message.set("None of the cells changed, check error.txt!")
# Close file i/o
fpr.close()
fp_err.close()
# GOOGLE_SHEETS_FILENAME
ttk.Label(root, text = "What is the spreadsheet name?:").pack()
spreadsheet_name = StringVar()
spreadsheet_name.trace_add("write", retrieve_spreadsheet_name)
spreadsheet_name_entry = Entry(root, width = 50, textvariable = spreadsheet_name)
spreadsheet_name_entry.pack()
# CELL_COL
ttk.Label(root, text = "Which column are we modifying? (Enter a letter):").pack()
cell_col = StringVar()
cell_col.trace_add("write", retrieve_cell_col)
cell_col_entry = Entry(root, width = 8, textvariable = cell_col)
cell_col_entry.pack()
# CELL_CONTENT
ttk.Label(root, text = "How many points do you want to input:").pack()
cell_content = StringVar()
cell_content.trace_add("write", retrieve_cell_content)
cell_content_entry = Entry(root, width = 8, textvariable = cell_content)
cell_content_entry.pack()
# FULL NAMES TO ADD TO INPUT FILE
ttk.Label(root, text = "Please input names of members who attended this meeting:").pack()
input_file_textbox = Text(root, height = 20, width = 20)
input_file_textbox.pack()
submit_button = ttk.Button(root, text = 'Submit Names', command = retrieveInputForTextFile)
submit_button.pack()
# entries_changed message/label
entries_changed_counter = StringVar()
entries_changed_counter.set("0 cells have been updated so far.")
entries_changed_label = Label(root, textvariable = entries_changed_counter)
entries_changed_label.pack()
# errors_occured message/label
errors_occured_counter = StringVar()
errors_occured_counter.set("0 errors have occured so far.")
errors_occured_label = Label(root, textvariable = errors_occured_counter)
errors_occured_label.pack()
# progress message
progress_message = StringVar()
progress_message.set("Thanks for using my application! -Dennis")
progress_message_label = Label(root, textvariable = progress_message)
progress_message_label.pack()
# SUBMIT CHANGES TO GSPREADSHEET
final_submission_button = ttk.Button(root, text = 'Submit Changes!', command = makeChangesToSpreadsheet)
final_submission_button.pack()
root.mainloop()
try:
root.destroy()
except:
pass