Google Site - Tutor Resource Hub
function doGet(e) {
const email = Session.getActiveUser().getEmail();
const adminEmails = [EMAILS];
const isAdmin = adminEmails.includes(email);
if (isAdmin) {
return HtmlService.createHtmlOutput(`<!DOCTYPE html>
<html>
<head>
<title>Admin Dashboard</title>
<style>
body {
font-family: 'Work Sans', sans-serif;
color: #224675;
margin: 0;
padding: 20px;
background-color: #F9F9F9;
text-align: center;
}
header {
background-color: #9A5870;
color: #EDECEA;
padding: 15px;
font-size: 20px;
font-weight: bold;
border-radius: 5px 5px 0 0;
}
.container {
max-width: 800px;
margin: 30px auto;
background: #fff;
padding: 20px;
border-radius: 5px;
box-shadow: 0 4px 8px rgba(0, 0, 0, 0.1);
}
.form-group {
margin: 20px 0;
}
input, button {
font-family: 'Work Sans', sans-serif;
font-size: 16px;
padding: 10px;
width: 100%;
margin-top: 10px;
border: 1px solid #ccc;
border-radius: 5px;
box-sizing: border-box;
}
button {
background-color: #9A5870;
color: #EDECEA;
border: none;
cursor: pointer;
transition: background-color 0.3s;
}
button:hover {
background-color: #7A4559;
}
#dashboardContainer {
margin-top: 30px;
text-align: left;
}
</style>
</head>
<body>
<header>Admin Dashboard</header>
<div class="container">
<p><strong>Search a tutor's dashboard by entering their Tutor ID:</strong></p>
<div class="form-group">
<input type="text" id="tutorIdInput" placeholder="Enter Tutor ID" />
<button onclick="searchTutor()">Search</button>
</div>
<div id="dashboardContainer"></div>
</div>
<script>
function searchTutor() {
const tutorId = document.getElementById("tutorIdInput").value.trim();
if (!tutorId) {
alert("Please enter a Tutor ID.");
return;
}
google.script.run.withSuccessHandler(function(html) {
document.getElementById("dashboardContainer").innerHTML = html;
}).getTutorDashboardHTML(tutorId);
}
</script>
</body>
</html>`);
}
// Standard tutor logic
const sheet1 = SpreadsheetApp.openById('SPREADSHEET ID').getSheetByName('SHEET NAME');
const data1 = sheet1.getDataRange().getValues();
const userRow = data1.find(row => row[4] === email); // Column E = email
if (!userRow) return HtmlService.createHtmlOutput("Access Denied: Email not recognized.");
const tutorID = userRow[0];
return HtmlService.createHtmlOutput(buildTutorDashboard(tutorID));
}
function getTutorDashboardHTML(tutorID) {
return buildTutorDashboard(tutorID);
}
function buildTutorDashboard(tutorID) {
// --- Sheet: tutor_master_sheet ---
const sheet1 = SpreadsheetApp.openById('SPREADSHEET ID').getSheetByName('SHEET NAME');
const data1 = sheet1.getDataRange().getValues();
const userRow = data1.find(row => row[0] === tutorID); // Column A = Tutor ID
if (!userRow) return "<p><strong>Error:</strong> Tutor ID not found.</p>";
const tutorName = `${userRow[2]} ${userRow[3]}`; // C & D
const tutorLevel = userRow[10]; // K
const tutorPrograms = userRow[21]; // V
const tutorAssignedPrograms = userRow[22]; // W
// --- Sheet: Tutor_QA ---
const sheet2 = SpreadsheetApp.openById('SPREADSHEET ID').getSheetByName('SHEET NAME');
const data2 = sheet2.getDataRange().getValues();
const qaRow = data2.find(row => row[0] === tutorID);
const avgObs = qaRow?.[5] || 'N/A';
const initialAudit = qaRow?.[11] || 'N/A';
const latestAudit = qaRow?.[12] || 'N/A';
const changeAudit = qaRow?.[14] || 'N/A';
const tutorPoints = qaRow?.[4] || 'N/A';
const auditAvg = (isFinite(initialAudit) && isFinite(latestAudit)) ? ((Number(initialAudit) + Number(latestAudit)) / 2).toFixed(2) : 'N/A';
// --- Sheet: Master_QA_Sheet ---
const sheet3 = SpreadsheetApp.openById('SPREADSHEET ID').getSheetByName('SHEET NAME');
const data3 = sheet3.getDataRange().getValues();
const studentRows = data3.filter(row => row[5] === tutorID); // Column F = Tutor ID
const completedSessionCount = studentRows.reduce((sum, row) => sum + (parseInt(row[14]) || 0), 0); // Column O
// --- Sheet: student_insights ---
const insightsSheet = SpreadsheetApp.openById('SPREADSHEET ID').getSheetByName('SHEET NAME');
const insightsData = insightsSheet.getDataRange().getValues();
const studentGradeMap = {};
insightsData.forEach(row => {
const studentId = row[0];
const grade = row[4]; // Column E
if (studentId) studentGradeMap[studentId] = grade;
});
// Generate student rows HTML
const studentHTML = studentRows.map(row => {
const studentId = row[0];
const grade = studentGradeMap[studentId] || 'N/A';
return `
<tr>
<td>${studentId}</td>
<td>${row[3]} ${row[4]}</td>
<td>${grade}</td>
<td>${row[2]}</td>
<td>${row[8]}</td>
<td>${row[15]}</td>
<td>${row[16]}</td>
<td>${row[17]}</td>
<td>${row[12]}</td>
<td>${row[13]}</td>
</tr>
`;
}).join('');
return `
<style>
h2, h3 {
color: #224675;
font-family: 'Work Sans', sans-serif;
}
table {
border-collapse: collapse;
width: fit-content;
min-width: 900px;
margin: auto;
font-family: 'Work Sans', sans-serif;
color: #333;
}
th {
background-color: #9A5870;
color: #EDECEA;
padding: 8px;
text-align: left;
}
td {
padding: 8px;
border: 1px solid #ccc;
}
.section {
margin: 40px auto;
max-width: 1000px;
background: #fff;
padding: 20px;
border-radius: 6px;
box-shadow: 0 4px 8px rgba(0,0,0,0.08);
}
.scroll-x {
overflow-x: auto;
padding-bottom: 10px;
}
.logo {
display: block;
margin: 30px auto 10px;
max-width: 250px;
height: auto;
}
</style>
<h2 style="text-align:center;">${tutorName}</h2>
<div class="section">
<h3>Tutor Summary</h3>
<div class="scroll-x">
<table>
<tr>
<th>Programs Certified In</th>
<th>Programs Tutoring In</th>
<th>Tutor Level</th>
<th>Completed Sessions</th>
</tr>
<tr>
<td style="word-break: break-word;">${tutorPrograms}</td>
<td style="word-break: break-word;">${tutorAssignedPrograms}</td>
<td>${tutorLevel}</td>
<td>${completedSessionCount}</td>
</tr>
</table>
</div>
</div>
<div class="section">
<h3>Active Students</h3>
<div class="scroll-x">
<table>
<thead>
<tr>
<th>Student ID</th>
<th>Name</th>
<th>Grade</th>
<th>Contract</th>
<th>Program</th>
<th>Pre Assessment</th>
<th>Post Surveys?</th>
<th># Post Surveys</th>
<th>Contract Coach</th>
<th>Primary Coach</th>
</tr>
</thead>
<tbody>
${studentHTML || '<tr><td colspan="10">No students found</td></tr>'}
</tbody>
</table>
</div>
</div>
<div class="section">
<h3>QA Metrics</h3>
<div class="scroll-x">
<table>
<tr>
<th>Avg Obs Score</th>
<th>Initial Audit</th>
<th>Latest Audit</th>
<th>Audit Avg</th>
<th>Change</th>
<th>Rating</th>
<th>Annual Points</th>
</tr>
<tr>
<td>${avgObs}</td>
<td>${initialAudit}</td>
<td>${latestAudit}</td>
<td>${auditAvg}</td>
<td>${changeAudit}</td>
<td>N/A</td>
<td>${tutorPoints}</td>
</tr>
</table>
</div>
</div>
<img class="logo" src="https://xfcfight.com/wp-content/uploads/2024/05/EQPD_EmailSig_Logo.png" alt="Logo">
`;
}
