import json raw_data = """{ "App":"yohBLAHBsCN51LcZZhsn", "Table":"native-table-4ZzEgTrklqd0GHwcHspu", "RowID":"L4rsG0iYwvZrJvxLrzgj", "Centre":{ "App":"yohBLAHBsCN51LcZZhsn", "Table":"native-table-laBA6Lxho5caOvjXqbZQ", "RowID":"9rcMq7RfQIizQjMVXa0AjA", "CentreID":"ICHILD", "CentreName":"Happy Chinese Mastery", "CentreAddress":" S", "CentrePostalCode":"", "CentreWebsite":"", "CentreLoginURL":"https://happychinese.ichild.app/", "PICName":"Happy Admin", "PICEmail":"ichildmarketing@gmail.com", "PICWA":"+6591999633", "SupportWA":"", "HQWA":"", "TotalStudent":"4", "ActiveStudent":"4", "HQ":"IChild", "Report":{ "Folder":"1Mx7jaHjksMBA1zLTLHa4eivrXUVmIJKK", "MthActiveGSID":"1QaCDY57ln2SeUrCTHC9inzLEdmRqg7elLMBK1Cr75bo" } }, "Report":{ "Report":"Active", "ReportMonth":"Mar 2026", "ReportStudentNo":"4", "ReportDate":"30 Mar 2026", "ReportDocID":"1Uzqo8rQYROKxZPLuelf0fOQqhDY_uAheQxsaVSlg6B0", "ReportLink":"https://docs.google.com/spreadsheets/d/1Uzqo8rQYROKxZPLuelf0fOQqhDY_uAheQxsaVSlg6B0", "ReportLevelBreakdown":"PG: 0 PN: 0 N: 0 K1: 0 K2: 4", "PGCount":"0", "PNCount":"0", "NCount":"0", "N1Count":"0", "N2Count":"0", "K1Count":"0", "K2Count":"4" }, "Students": [{ "StudentID":"ICS1", "StudentName":"Peter", "JoinedSince":"30 Jul 24", "GroupName":"Nursery III", "GroupLevel":"K2", "JoinedGroupDate":"26 Dec 24", "Remark":"", "LatestMaterial":"PG", "IssueDate":"11 Mar 26" }, { "StudentID":"ICS1", "StudentName":"Peter", "JoinedSince":"30 Jul 24", "GroupName":"K2", "GroupLevel":"K2", "JoinedGroupDate":"30 Nov 24", "Remark":"", "LatestMaterial":"PG", "IssueDate":"11 Mar 26" }, { "StudentID":"ICS2", "StudentName":"Janice", "JoinedSince":"30 Jul 24", "GroupName":"Nursery III", "GroupLevel":"K2", "JoinedGroupDate":"01 Sep 24", "Remark":"", "LatestMaterial":"", "IssueDate":"" }, { "StudentID":"ICS3", "StudentName":"Richard", "JoinedSince":"27 Aug 24", "GroupName":"Nursery III", "GroupLevel":"K2", "JoinedGroupDate":"31 Aug 24", "Remark":"", "LatestMaterial":"", "IssueDate":"" }], "User": { "RowID":"dJBy3nyvQwWQFaRxBBudcA", "UserName":"Happy Admin", "UserWA":"+6591999633", "UserEmail":"ichildmarketing@gmail.com", "UserCentreID":"ICHILD", "UserCentreName":"ICHILD", "UserRole":"" }, "Route": "Create PDF Only" }""" report_month = "Mar 2026" # 1) Slice JSON safely start = raw_data.find("{") end = raw_data.rfind("}") + 1 cleaned = raw_data[start:end] # 2) FIX: escape newline characters for JSON cleaned = cleaned.replace(" ", " ") # 3) Parse JSON try: data = json.loads(cleaned, strict=False) except Exception as e: raise Exception("JSON parsing failed: " + str(e)) # 4) Get students students = data.get("Students", []) if not students: raise Exception("No students found in data") # 5) Build result result = {key: [] for key in students[0].keys()} for student in students: for key in result: result[key].append(str(student.get(key, ""))) # 6) Add MonthReport result["MonthReport"] = ", ".join([report_month] * len(students)) # 7) Convert lists to strings for key in result: if isinstance(result[key], list): result[key] = ", ".join(result[key]) print(json.dumps(result, indent=2))