How to Integrate Retool with Google Classroom

Navigate to https://console.cloud.google.com and create a new project (or use an existing Google Workspace project). In APIs & Services → Library, search for 'Google Classroom API' and click Enable. Next, configure the OAuth consent screen: APIs & Services → OAuth consent screen. Select 'Internal' if using with Google Workspace accounts within your domain (recommended for school use — this skips external review). Fill in app name, support email, and add the following scopes: - https://www.googleapis.com/auth/classroom.courses.readonly (list courses) - https://www.googleapis.com/auth/classroom.coursework.students.readonly (read assignments and grades) - https://www.googleapis.com/auth/classroom.rosters.readonly (read enrollments) - https://www.googleapis.com/auth/classroom.announcements.readonly (read announcements) - Add write scopes (without .readonly) if you need to create/update resources Go to APIs & Services → Credentials → Create Credentials → OAuth 2.0 Client ID. Select Web Application. Add your Retool URL as an authorized redirect URI. Copy the client ID and client secret. To obtain a refresh token, use Google OAuth Playground (https://developers.google.com/oauthplayground): authorize the above scopes for a teacher or admin account, and copy the refresh_token from Step 2. Store in Retool Configuration Variables: CLASSROOM_CLIENT_ID, CLASSROOM_CLIENT_SECRET (secret), CLASSROOM_REFRESH_TOKEN (secret).

Google OAuth 2.0 access tokens expire after 1 hour. Create a token refresh mechanism in Retool. Create a refreshClassroomToken JavaScript query (set to run on page load): POST https://oauth2.googleapis.com/token with a URL-encoded body: grant_type=refresh_token&client_id=CLASSROOM_CLIENT_ID&client_secret=CLASSROOM_CLIENT_SECRET&refresh_token=CLASSROOM_REFRESH_TOKEN. Store the returned access_token in a Retool app variable called classroomToken. Now create the REST API Resource: Resources tab → Add Resource → REST API. Name: Google Classroom API Base URL: https://classroom.googleapis.com/v1 Authentication: Bearer Token → {{ classroomToken.value }} Add a default header: Content-Type: application/json. Click Create Resource. Test immediately with GET /courses?pageSize=5. A successful response returns a JSON object with a courses array containing course objects. If you get 401, the refresh token authorization may not have included all required Classroom API scopes — re-authorize using Google OAuth Playground with the complete scope list from the previous step.

1// JavaScript query: refreshClassroomToken
2// Run on page load to get fresh OAuth token
3const response = await fetch('https://oauth2.googleapis.com/token', {
4  method: 'POST',
5  headers: { 'Content-Type': 'application/x-www-form-urlencoded' },
6  body: [
7    'grant_type=refresh_token',
8    `client_id=${retoolContext.configVars.CLASSROOM_CLIENT_ID}`,
9    `client_secret=${retoolContext.configVars.CLASSROOM_CLIENT_SECRET}`,
10    `refresh_token=${retoolContext.configVars.CLASSROOM_REFRESH_TOKEN}`
11  ].join('&')
12});
13const data = await response.json();
14if (data.error) throw new Error(data.error_description);
15return data.access_token;

Create a getCourses query: GET /courses with parameters: - courseStates: ACTIVE (also: ARCHIVED, PROVISIONED, DECLINED — use to filter course status) - pageSize: 50 - pageToken: {{ getCourses.data.nextPageToken || '' }} (for pagination through large course lists) Google Classroom API uses cursor-based pagination with nextPageToken. For school-wide dashboards, repeatedly call getCourses with the nextPageToken from the previous response until nextPageToken is empty — collecting all pages into a combined dataset. Create a getCourseStudents query: GET /courses/{{ select_course.value }}/students?pageSize=100 — returns all enrolled students for the selected course. Each student object has: userId, profile.name.fullName, profile.emailAddress, and profile.photoUrl. Create a getCourseTeachers query: GET /courses/{{ select_course.value }}/teachers?pageSize=20 — returns all teachers (a course can have multiple co-teachers). For the enrollment report, fetch all courses and then all students for each course. Because this requires multiple sequential API calls, build a JavaScript query using async/await to fetch students for all courses in series and aggregate the results into a single student-course mapping dataset.

1// Transformer for Google Classroom courses
2const courses = data?.courses || [];
3return courses.map(course => ({
4  id: course.id,
5  name: course.name,
6  section: course.section || '',
7  description: (course.description || '').substring(0, 100),
8  room: course.room || '',
9  owner_id: course.ownerId,
10  state: course.courseState,
11  created_at: course.creationTime ? new Date(course.creationTime).toLocaleDateString() : '',
12  alternate_link: course.alternateLink || '',
13  enrollment_code: course.enrollmentCode || '',
14  teacher_group_email: course.teacherGroupEmail || ''
15}));

Create a getCoursework query: GET /courses/{{ select_course.value }}/courseWork?pageSize=50&orderBy=updateTime+desc. Each coursework item includes: id, title, description, dueDate (object with year, month, day), dueTime, maxPoints, workType (ASSIGNMENT, SHORT_ANSWER_QUESTION, MULTIPLE_CHOICE_QUESTION), and state (PUBLISHED, DRAFT, DELETED). Note: The dueDate field in Google Classroom API is an object with separate year, month, day properties — not an ISO timestamp. Build a transformer that reconstructs the date: const due = cw.dueDate ? new Date(cw.dueDate.year, cw.dueDate.month - 1, cw.dueDate.day) : null. Create a getStudentSubmissions query: GET /courses/{{ select_course.value }}/courseWork/{{ select_assignment.value }}/studentSubmissions?pageSize=100. This is the most data-rich endpoint — each submission includes: id, userId, courseWorkId, state (NEW, CREATED, TURNED_IN, RETURNED, RECLAIMED_BY_STUDENT), late (boolean), draftGrade, assignedGrade, updateTime, and submissionHistory (array of submission state changes with timestamps). The state field is critical for tracking: NEW means the student hasn't started, CREATED means the student opened it but hasn't submitted, TURNED_IN means submitted, RETURNED means the teacher returned it (with or without grade), RECLAIMED_BY_STUDENT means the student unsubmitted.

1// Transformer for student submissions with status summary
2const submissions = data?.studentSubmissions || [];
3const studentMap = {};
4
5// Build student lookup from course students data
6(getCourseStudents.data?.students || []).forEach(s => {
7  studentMap[s.userId] = {
8    name: s.profile?.name?.fullName || 'Unknown',
9    email: s.profile?.emailAddress || ''
10  };
11});
12
13return submissions.map(sub => {
14  const student = studentMap[sub.userId] || { name: 'Unknown', email: '' };
15  const turnedInAt = sub.submissionHistory?.slice().reverse()
16    .find(h => h.stateHistory?.state === 'TURNED_IN')?.stateHistory?.stateTimestamp;
17
18  return {
19    id: sub.id,
20    student_name: student.name,
21    student_email: student.email,
22    state: sub.state,
23    is_late: sub.late ? 'Late' : 'On Time',
24    draft_grade: sub.draftGrade ?? '',
25    assigned_grade: sub.assignedGrade ?? '',
26    submitted_at: turnedInAt ? new Date(turnedInAt).toLocaleString() : '',
27    last_updated: new Date(sub.updateTime).toLocaleString(),
28    needs_grading: sub.state === 'TURNED_IN' && sub.assignedGrade === undefined
29  };
30});

Build the multi-course reporting view that aggregates data across courses. Create a JavaScript query (allCoursesSummary) that fetches coursework and submissions for each course in getCourses.data and computes summary statistics. For each course, compute: - submission_rate: (TURNED_IN count + RETURNED count) / total students × 100 - on_time_rate: non-late submissions / total submitted × 100 - average_grade: mean of assignedGrade values for returned submissions - missing_count: students with state === 'NEW' or state === 'CREATED' Display these per-course metrics in an aggregate Table sortable by submission rate. Add a drill-down pattern: clicking a course row shows the individual assignment-level breakdown for that course. For the student missing work view, build a transformer that inverts the data structure: instead of course → students, produce student → courses with missing work. Group submission records by student email and count MISSING states per student to generate the at-risk student list. For returning grades to students, create an updateGrade query: PATCH /courses/{courseId}/courseWork/{courseWorkId}/studentSubmissions/{submissionId} with body: { "assignedGrade": {{ input_grade.value }}, "draftGrade": {{ input_grade.value }} }. Follow this with a return query: POST /courses/{courseId}/courseWork/{courseWorkId}/studentSubmissions/{submissionId}:return to make the grade visible to the student. For school districts building comprehensive academic monitoring dashboards combining Google Classroom, SIS grade book data, and attendance records across hundreds of teachers, RapidDev's team can help architect the complete Retool solution.

1// JavaScript query: compute per-course submission summary
2const courses = getCourses.data || [];
3const results = [];
4
5for (const course of courses.slice(0, 20)) { // limit to first 20 courses
6  const cwResp = await getCourseWorkForCourse.trigger({ course_id: course.id });
7  const assignments = cwResp?.courseWork || [];
8
9  let totalSubmissions = 0, turnedIn = 0, lateCount = 0, gradeSum = 0, gradedCount = 0;
10
11  for (const assignment of assignments.slice(0, 5)) { // last 5 assignments per course
12    const subResp = await getSubmissionsForAssignment.trigger({
13      course_id: course.id,
14      assignment_id: assignment.id
15    });
16    const subs = subResp?.studentSubmissions || [];
17    totalSubmissions += subs.length;
18    turnedIn += subs.filter(s => ['TURNED_IN', 'RETURNED'].includes(s.state)).length;
19    lateCount += subs.filter(s => s.late).length;
20    subs.filter(s => s.assignedGrade != null).forEach(s => {
21      gradeSum += s.assignedGrade;
22      gradedCount++;
23    });
24  }
25
26  results.push({
27    course_id: course.id,
28    course_name: course.name,
29    section: course.section || '',
30    submission_rate: totalSubmissions > 0 ? ((turnedIn / totalSubmissions) * 100).toFixed(1) : '0.0',
31    late_rate: turnedIn > 0 ? ((lateCount / turnedIn) * 100).toFixed(1) : '0.0',
32    avg_grade: gradedCount > 0 ? (gradeSum / gradedCount).toFixed(1) : 'N/A'
33  });
34}
35
36return results;