Fetch questions and answers from google sheets

ice_breaking_challenge/google_sheets.py

@@ -5,6 +5,7 @@ from google.oauth2.credentials import Credentials
 from google_auth_oauthlib.flow import InstalledAppFlow
 from googleapiclient.discovery import build
 from googleapiclient.errors import HttpError
+from flask import session
 
 # If modifying these scopes, delete the file token.json.
 SCOPES = ["https://www.googleapis.com/auth/spreadsheets"]
@@ -12,10 +13,11 @@ SCOPES = ["https://www.googleapis.com/auth/spreadsheets"]
 # The ID and range of a sample spreadsheet.
 # SAMPLE_SPREADSHEET_ID = "1Z3doUDpoUmXs7p4-g7f8q3XZIBhH_Lz2w51bnpUFigw"
 SAMPLE_SPREADSHEET_ID = "1NJGhpMsfUySHJcMBNpLTEnt-r5Co6qwRahenIa9D55k"
-SAMPLE_RANGE_NAME = "Responses!I2:I30"
 
 SERVICE = None
 
+MAX_ROW = 100
+
 
 def load_google_sheets():
   global SERVICE
@@ -43,20 +45,37 @@ def load_google_sheets():
 
   SERVICE = build("sheets", "v4", credentials=creds)
 
-def get_team_numbers() -> list[str]:
+
+def get_range(range_name: str) -> list[list[str]]:
     sheet = SERVICE.spreadsheets()
     result = (
         sheet.values()
         .get(
             spreadsheetId=SAMPLE_SPREADSHEET_ID,
-            range=SAMPLE_RANGE_NAME
+            range=range_name
         )
         .execute()
     )
     values = result.get("values", [])
 
-    if not values:
-        print("No data found.")
-        return
+    return values
+
+
+def get_team_numbers() -> list[str]:
+    values = get_range(f"Responses!I2:I{MAX_ROW}")
 
     return [value[0] for value in values]
+
+def get_questions_and_answers() -> list[list[str]]:
+    question_values = get_range("Responses!B1:F1")
+
+    questions = question_values[0]
+
+    answers_values = get_range(f"Responses!B2:F{MAX_ROW}")
+
+    questions_and_answers: list[list[str]] = []
+    for answers in answers_values:
+        for question, answer in zip(questions, answers):
+            questions_and_answers.append([question, answer])
+
+    session['questions_and_answers'] = questions_and_answers