-
Notifications
You must be signed in to change notification settings - Fork 4
/
Sheets.py
195 lines (158 loc) · 5.99 KB
/
Sheets.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
import gspread
import pygsheets
from oauth2client.service_account import ServiceAccountCredentials
from gspread_formatting import *
import datetime
from init import fullStudentNames
from datetime import datetime
def loadLists(textFile):
with open(textFile) as file:
list = file.readlines()
file.close()
list = [x[:-1] for x in list]
return list
def absentCell(cell):
# Add Red Color Cell Format
format = CellFormat(backgroundColor=Color(.96, .80, .80))
# Update a Cell as Absent
format_cell_range(sheet, cell, format)
def presentCell(cell):
# Add Green Color Cell Format
format = CellFormat(backgroundColor=Color(.85, .93, .82))
# Update a Cell as Present
format_cell_range(sheet, cell, format)
def lateCell(cell):
# Add Yellow Color Cell Format
format = CellFormat(backgroundColor=Color(1.00, .95, .80))
# Update a Cell as Late
format_cell_range(sheet, cell, format)
def resetCell(cell):
# Add White Color Cell Format
format = CellFormat(backgroundColor=Color(1, 1, 1))
# Reset a Cell
format_cell_range(sheet, cell, format)
sheet.update_acell(cell, '')
def addKey():
# Reset Top Cells
for n in range(1, 5):
cellLocation = 'A' + str(n)
resetCell(cellLocation)
# Add Key Colors and Labels
presentCell('A2')
absentCell('A3')
lateCell('A4')
format = CellFormat(textFormat=TextFormat(bold=True))
format_cell_range(sheet, 'A1', format)
sheet.update_acell('A1', 'KEY')
sheet.update_acell('A2', 'Present')
sheet.update_acell('A3', 'Absent')
sheet.update_acell('A4', 'Late')
def addStudentNames():
# Format and write Student Name subtitle
format = CellFormat(textFormat=TextFormat(bold=True))
format_cell_range(sheet, 'A8', format)
sheet.update_acell('A8', 'Student Names')
# Write student names from init list
for n in range(0, len(fullStudentNames)):
cellLocation = 'A' + str(9 + n)
sheet.update_acell(cellLocation, fullStudentNames[n])
def addDate():
# Get and format date
date = datetime.today().strftime('X%m/X%d')
date = date.replace('X0', 'X').replace('X', '')
# character number for "B"
cellStartNum = ord('B')
# Flag boolean to exit loop
emptyDateCell = False
# Format Date Subtitles
format = CellFormat(textFormat=TextFormat(bold=True), horizontalAlignment='RIGHT')
while not emptyDateCell:
# Get Current cell location
currentCell = str(chr(cellStartNum)) + '8'
# If the date is already there, then you do not need to add another column
if sheet.acell(currentCell).value == date:
break
else:
# # If cell is not empty, move over one cell horizontally
if sheet.acell(currentCell).value != '':
cellStartNum = cellStartNum + 1
else:
# If cell is empty, write the date
format_cell_range(sheet, currentCell, format)
sheet.update_acell(currentCell, date)
emptyDateCell = True
def formatPage():
# Adds key, student names, and current date
if sheet.acell('A1').value != 'KEY':
addKey()
addStudentNames()
addDate()
def getRowNum(personToFind):
startCellNum = 9
for x in range(0, len(fullStudentNames)):
# Find how many to go down from row 9 by comparing names + arrays
if fullStudentNames[x].strip() == personToFind.strip():
# Update row to go to
startCellNum += x
return startCellNum
def getColumnLetter():
# Start column is B
cellStartNum = ord('B')
# Get date because column will correspond
date = datetime.today().strftime('X%m/X%d')
date = date.replace('X0', 'X').replace('X', '')
columnFound = False
# Compare current date to column date
while not columnFound:
currentCell = str(chr(cellStartNum)) + '8'
# If found, return cell column Letter
if sheet.acell(currentCell).value == date:
return cellStartNum
else:
cellStartNum += 1
def updatePresentPerson(personToFind):
# Change numerical values to cell value
cellToPresent = chr(getColumnLetter()) + str(getRowNum(personToFind))
# Mark present
presentCell(cellToPresent)
def updateAbsentPerson(personToFind):
# Change numerical values to cell value
cellToAbsent = chr(getColumnLetter()) + str(getRowNum(personToFind))
# Mark Absent
absentCell(cellToAbsent)
def updateLatePerson(personToFind):
# Change numerical values to cell value
cellToAbsent = chr(getColumnLetter()) + str(getRowNum(personToFind))
# Mark Late
lateCell(cellToAbsent)
def markOnce(name):
# Change numerical values to cell value
cellToCheck = str(chr(getColumnLetter())) + str(getRowNum(name))
# Return False if cell is not white or red
return worksheet.cell(cellToCheck).color != (None, None, None, None) or worksheet.cell(cellToCheck).color != (
.96, .80, .80, 1.00)
def markAbsentUnmarked():
rowStart = 9
for x in range(0, len(fullStudentNames)):
cellToCheck = str(chr(getColumnLetter())) + str(rowStart)
if worksheet.cell(cellToCheck).color == (None, None, None, None):
absentCell(cellToCheck)
rowStart += 1
else:
rowStart += 1
try:
fullStudentNames = loadLists("List Information/Full Student Names")
# Gets scope of sheet
scope = ["https://spreadsheets.google.com/feeds", "https://www.googleapis.com/auth/spreadsheets",
"https://www.googleapis.com/auth/drive.file", "https://www.googleapis.com/auth/drive"]
# Gets sheet credentials and authorizes it
creds = ServiceAccountCredentials.from_json_keyfile_name("creds.json", scope)
client = gspread.authorize(creds)
# Opens sheet based on sheet name
sheet = client.open("19/20 Attendance").sheet1
# Authorize Pygsheets library
gc = pygsheets.authorize()
worksheet = gc.open('19/20 Attendance').sheet1
formatPage()
except Exception as e:
print(e)