def updateScore(sheet, student, problem, score, submitTime):
# Log to file
f = open("contest_log.txt", "a")
f.write("Name: {}, Problems: {}, Score: {}, Time Stamp: {}\n".format(
student, problem, score, submitTime))
f.close()
SHEET_OUTPUT_ID = Config.infomationTaker("SHEET_OUTPUT_ID")
CONTEST_MODE = Config.infomationTaker("CONTEST_MODE")
RANGE_NAME = getRangeName(sheet, student, problem)
isUpdatePenaltyNeeded = 0
try:
result = sheet.values().get(spreadsheetId=SHEET_OUTPUT_ID,
range=RANGE_NAME).execute()
currScore = float(result.get('values', [])[0][0])
except:
currScore = 0
if CONTEST_MODE == "ACM":
if currScore <= 0: currScore -= 1 # one more submission
if score >= 10 and currScore < 0: # not yet AC and now AC
score = -currScore
isUpdatePenaltyNeeded = 1
else:
score = currScore
else:
score = max(currScore, score)
body = {'values': [[score]]}
result = sheet.values().update(spreadsheetId=SHEET_OUTPUT_ID,
range=RANGE_NAME,
valueInputOption="RAW",
body=body).execute()
if isUpdatePenaltyNeeded:
RANGE_NAME_PENALTY = getRangeName(sheet, student, "PENALTY")
updatePenalty(sheet, RANGE_NAME_PENALTY, score, submitTime)
print(" {} - {} - {}, score is {} at timestamp {}".format(
student, problem, RANGE_NAME, score, submitTime))
def getRow(sheet, row):
SHEET_INPUT_ID = Config.infomationTaker("SHEET_INPUT_ID")
SHEET_INPUT_NAME = Config.infomationTaker("SHEET_INPUT_NAME")
RANGE_NAME = SHEET_INPUT_NAME + "!" + str(row) + ":" + str(row)
result = sheet.values().get(spreadsheetId=SHEET_INPUT_ID,
range=RANGE_NAME).execute()
values = result.get('values', [])
return values
def getRangeName(sheet, student, problem):
SHEET_OUTPUT_ID = Config.infomationTaker("SHEET_OUTPUT_ID")
SHEET_OUTPUT_NAME = Config.infomationTaker("SHEET_OUTPUT_NAME")
# GET ROW
FIRST_COL_RANGE_NAME = SHEET_OUTPUT_NAME + "!A:A"
result = sheet.values().get(spreadsheetId=SHEET_OUTPUT_ID,
range=FIRST_COL_RANGE_NAME).execute()
values = result.get('values', [])
found = 0
writeRow = 1
for value in values:
if (value[0].strip().upper() == student):
found = 1
break
writeRow += 1
if found == 0:
writeRow = len(values) + 1
# REWRITE
if found == 0:
body = {'values': [[student]]}
RANGE_NAME = "{}!A{}:A{}".format(SHEET_OUTPUT_NAME, writeRow, writeRow)
result = sheet.values().update(spreadsheetId=SHEET_OUTPUT_ID,
range=RANGE_NAME,
valueInputOption="RAW",
body=body).execute()
# GET COL
FIRST_ROW_RANGE_NAME = SHEET_OUTPUT_NAME + "!1:1"
result = sheet.values().get(spreadsheetId=SHEET_OUTPUT_ID,
range=FIRST_ROW_RANGE_NAME).execute()
values = result.get('values', [])
found = 0
writeCol = 1
for value in values[0]:
if (value.strip().upper() == problem):
found = 1
break
writeCol += 1
if found == 0:
writeCol = len(values[0]) + 1
writeCol = colNumToColString(writeCol)
# REWRITE
if found == 0:
body = {'values': [[problem]]}
RANGE_NAME = "{}!{}1:{}1".format(SHEET_OUTPUT_NAME, writeCol, writeCol)
result = sheet.values().update(spreadsheetId=SHEET_OUTPUT_ID,
range=RANGE_NAME,
valueInputOption="RAW",
body=body).execute()
# Get RANGE_NAME
RANGE_NAME = "{}!{}{}:{}{}".format(SHEET_OUTPUT_NAME, writeCol, writeRow,
writeCol, writeRow)
return RANGE_NAME
def markDone(sheet, dateAndTime, row):
SHEET_INPUT_ID = Config.infomationTaker("SHEET_INPUT_ID")
SHEET_INPUT_NAME = Config.infomationTaker("SHEET_INPUT_NAME")
RANGE_NAME = SHEET_INPUT_NAME + "!A" + str(row) + ":A" + str(row)
print(RANGE_NAME)
body = {'values': [[dateAndTime]]}
result = sheet.values().update(spreadsheetId=SHEET_INPUT_ID,
range=RANGE_NAME,
valueInputOption="RAW",
body=body).execute()
print('{0} cells updated.'.format(result.get('updatedCells')))
def updateStatus(sheet, newStatus):
# GET VALUE
SHEET_OUTPUT_ID = Config.infomationTaker("SHEET_OUTPUT_ID")
SHEET_UPDATE_NAME = Config.infomationTaker("SHEET_UPDATE_NAME")
RANGE_NAME = SHEET_UPDATE_NAME + "!A2:A99"
result = sheet.values().get(spreadsheetId=SHEET_OUTPUT_ID,
range=RANGE_NAME).execute()
values = result.get('values', [])
values = [[newStatus]] + values
# RE-WRITE
RANGE_NAME = SHEET_UPDATE_NAME + "!A2:A100"
body = {'values': values}
sheet.values().update(spreadsheetId=SHEET_OUTPUT_ID,
range=RANGE_NAME,
valueInputOption="RAW",
body=body).execute()
def updatePenalty(sheet, RANGE_NAME, score, submitTime):
SHEET_OUTPUT_ID = Config.infomationTaker("SHEET_OUTPUT_ID")
WRONG_SUBMISSION_PENALTY = Config.infomationTaker(
"WRONG_SUBMISSION_PENALTY")
START_TIME = Config.infomationTaker("START_TIME")
timestamp = time.mktime(time.strptime(START_TIME, '%m/%d/%Y %H:%M:%S'))
START_TIMESTAMP = int(timestamp)
try:
result = sheet.values().get(spreadsheetId=SHEET_OUTPUT_ID,
range=RANGE_NAME).execute()
currPenalty = float(result.get('values', [])[0][0])
except:
currPenalty = 0
currPenalty += (score - 1) * WRONG_SUBMISSION_PENALTY + int(
(submitTime - START_TIMESTAMP) / 60)
body = {'values': [[currPenalty]]}
result = sheet.values().update(spreadsheetId=SHEET_OUTPUT_ID,
range=RANGE_NAME,
valueInputOption="RAW",
body=body).execute()
print(" RANGE_NAME: {} - Score: {} - Penalty: {}".format(
RANGE_NAME, score, currPenalty))