// Code.
gs - Full backend for tab-per-grade layout, with new bulk subject entry
save function.
function doGet() {
return [Link]('index')
.setTitle('Marks Entry App - TrMbelo Primary & JSS Application')
.setXFrameOptionsMode([Link]);
}
// Load all sheet names (grades)
function getGrades() {
return [Link]().getSheets().map(s => [Link]());
}
// Get subjects from header row starting at column 3
function getSubjectsForGrade(gradeName) {
const ss = [Link]();
const sheet = [Link](gradeName);
if (!sheet) return [];
const lastCol = [Link]();
if (lastCol <= 2) return [];
const headers = [Link](1, 3, 1, lastCol - 2).getValues()[0];
return [Link](h => String(h));
}
// Get students (ADM, Name) from columns A and B
function getStudentsForGrade(gradeName) {
const ss = [Link]();
const sheet = [Link](gradeName);
if (!sheet) return [];
const lastRow = [Link]();
if (lastRow <= 1) return [];
const data = [Link](2, 1, lastRow - 1, 2).getValues();
return [Link](r => r[0] !== "");
}
// Save marks via the existing per-grade sheet model (same as earlier saveAllMarks)
function saveAllMarks(marksData) {
const ss = [Link]();
let result = [];
for (const gradeName in marksData) {
const sheet = [Link](gradeName);
if (!sheet) {
[Link](`❌ Sheet ${gradeName} not found`);
continue;
}
const headers = [Link](1, 1, 1, [Link]()).getValues()[0];
const admValues = [Link](2, 1, [Link]() - 1, 1).getValues();
for (const adm in marksData[gradeName]) {
const studentMarks = marksData[gradeName][adm];
let rowIndex = -1;
for (let i = 0; i < [Link]; i++) {
if (String(admValues[i][0]) === String(adm)) {
rowIndex = i + 2;
break;
}
}
if (rowIndex === -1) {
[Link](`❌ ADM ${adm} not found in ${gradeName}`);
continue;
}
for (const subject in studentMarks) {
let colIndex = [Link](subject) + 1;
// If subject column doesn't exist, append it at the end
if (colIndex === 0) {
const newCol = [Link]() + 1;
[Link](1, newCol).setValue(subject);
colIndex = newCol;
}
[Link](rowIndex, colIndex).setValue(studentMarks[subject]);
[Link](`✅ Saved ${studentMarks[subject]} for ${adm} (${subject})`);
}
}
}
return result;
}
// Download CSV for single grade (clean version: no quotes, with empty first
column)
function downloadCSV(gradeName) {
const ss = [Link]();
const sheet = [Link](gradeName);
if (!sheet) return "";
const data = [Link]().getValues();
// Add an empty first column before ADM and remove punctuation marks
const csvData = [Link](row => {
// prepend empty column, strip punctuation marks
const cleanRow = ["", ...[Link](cell =>
String(cell)
.replace(/["',;]/g, "") // remove quotes, commas, semicolons
.replace(/\r?\n|\r/g, " ") // remove line breaks
)];
return [Link](",");
});
return [Link]("\r\n");
}
// Download all grades as ZIP (base64)
function downloadAllGrades() {
const ss = [Link]();
const grades = getGrades();
const blobs = [];
[Link](gradeName => {
const sheet = [Link](gradeName);
if (sheet) {
const data = [Link]().getValues();
const csv = [Link](row => [Link](cell => {
return `"${String(cell).replace(/"/g,'""')}"`;
}).join(",")).join("\r\n");
const blob = [Link](csv, 'text/csv', gradeName + '.csv');
[Link](blob);
}
});
if ([Link] === 0) return "";
const zipBlob = [Link](blobs).setName('All_Grades.zip');
return Utilities.base64Encode([Link]());
}
// Return full sheet data (header+rows) for viewing marksheet
function getMarksheet(gradeName) {
const ss = [Link]();
const sheet = [Link](gradeName);
if (!sheet) return [];
return [Link]().getValues();
}
// Clear marks only (keep ADM/Name/headers)
function clearMarksheetData(gradeName) {
const ss = [Link]();
const sheet = [Link](gradeName);
if (!sheet) return `Sheet ${gradeName} not found.`;
const lastRow = [Link]();
const lastCol = [Link]();
if (lastRow <= 1 || lastCol <= 2) return `No marks to clear in ${gradeName}.`;
[Link](2, 3, lastRow - 1, lastCol - 2).clearContent();
return `✅ Cleared marks for ${gradeName}`;
}
/**
* New function:
* Saves marks for a single subject in bulk for the given grade.
* marksArray: [{adm: '1', mark: '78'}, {adm:'2', mark:'AB'}, ...]
* Overwrites existing marks for that subject (i.e., updates the column).
*
* Behavior:
* - If subject column doesn't exist, it will be appended to the right.
* - Only ADMs found in column A will be written; ADMs not found are reported.
*
* Returns: summary object { saved: n, skipped: [...], createdColumn: boolean }
*/
function saveMarksForSubject(gradeName, subject, marksArray) {
const ss = [Link]();
const sheet = [Link](gradeName);
if (!sheet) return { error: `Sheet ${gradeName} not found.` };
// Ensure header row exists
const lastCol = [Link]();
let headers = lastCol >= 1 ? [Link](1,1,1,lastCol).getValues()[0] : [];
// Find subject column (search across all columns)
let colIndex = [Link](subject) + 1;
let createdColumn = false;
if (colIndex === 0) {
// append subject at end
colIndex = lastCol + 1;
[Link](1, colIndex).setValue(subject);
createdColumn = true;
// refresh headers
headers = [Link](1,1,1,[Link]()).getValues()[0];
}
// Read ADM column to build adm->rowIndex map
const lastRow = [Link]();
const admValues = lastRow >= 2 ? [Link](2, 1, lastRow - 1, 1).getValues()
: [];
const admToRow = {};
for (let i = 0; i < [Link]; i++) {
const adm = String(admValues[i][0]);
if (adm !== '') admToRow[adm] = i + 2; // sheet row index
}
// Prepare write array (we'll write cell-by-cell; could be batched)
let saved = 0;
const skipped = [];
for (let i = 0; i < [Link]; i++) {
const entry = marksArray[i];
const adm = String([Link]);
const mark = [Link];
const rowIndex = admToRow[adm];
if (!rowIndex) {
[Link](adm);
continue;
}
[Link](rowIndex, colIndex).setValue(mark);
saved++;
}
return { saved: saved, skipped: skipped, createdColumn: createdColumn };
}