Category: Uncategorised

This guide will walk you through turning a standard Google Sheet into a connected database for your mobile evaluation app.

Phase 1: Prepare Your Google Sheet

The app requires a specific sheet structure to read your class roster and save marks accurately.

1. Create a new Google Sheet for your module or cohort.
2. Double-click the tab name at the very bottom left of the screen (it usually defaults to “Sheet1”) and rename it to exactly Evaluations (Case-sensitive, no spaces).
3. Create your column headers in the first row. You must include Student ID, Good Name, Average, and Attempts.
4. Add your grading columns (e.g., Session 01, Session 02). Ensure there are no hidden spaces at the end of these names.
5. Populate the rows below the headers with your student data.

Phase 2: Add the Apps Script

This script acts as the bridge between your Google Sheet and the mobile app.

1. In your Google Sheet, click Extensions in the top menu and select Apps Script.
2. Delete any code currently in the editor.
3. Copy and paste the complete script below into the editor:

const TAB_NAME = "Evaluations"; 

function getTargetSheet() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(TAB_NAME);
  if (!sheet) {
    throw new Error("Target sheet not found. Looking for tab named: " + TAB_NAME);
  }
  return sheet;
}

function doGet(e) {
  try {
    const sheet = getTargetSheet();
    const data = sheet.getDataRange().getDisplayValues();
    if (!data || data.length === 0) throw new Error("Sheet is completely empty.");

    const headers = data[0].map(h => h.toString().trim());
    let students = [];
    
    for (let i = 1; i < data.length; i++) {
      let rowObj = {};
      for (let j = 0; j < headers.length; j++) {
        rowObj[headers[j]] = data[i][j];
      }
      rowObj["_rowIndex"] = i + 1; 
      students.push(rowObj);
    }
    
    return ContentService.createTextOutput(JSON.stringify({ status: "success", headers: headers, students: students }))
      .setMimeType(ContentService.MimeType.JSON);
  } catch (error) {
    return ContentService.createTextOutput(JSON.stringify({ status: "error", message: "Read Error: " + error.toString() }))
      .setMimeType(ContentService.MimeType.JSON);
  }
}

function doPost(e) {
  try {
    const sheet = getTargetSheet();
    if (!e || !e.postData || !e.postData.contents) {
      return ContentService.createTextOutput(JSON.stringify({ status: "error", message: "No data payload received." }))
        .setMimeType(ContentService.MimeType.JSON);
    }
    
    const payload = JSON.parse(e.postData.contents);
    const headers = sheet.getDataRange().getDisplayValues()[0].map(h => h.toString().trim());
    const targetSession = payload.sessionName ? payload.sessionName.toString().trim() : "";
    const targetColumn = headers.indexOf(targetSession) + 1;
    
    if (targetColumn > 0) {
      sheet.getRange(payload.rowIndex, targetColumn).setValue(payload.mark);
      SpreadsheetApp.flush();
      return ContentService.createTextOutput(JSON.stringify({ status: "success", message: "Mark saved successfully!" }))
        .setMimeType(ContentService.MimeType.JSON);
    } else {
      return ContentService.createTextOutput(JSON.stringify({ status: "error", message: "Column '" + targetSession + "' not found." }))
        .setMimeType(ContentService.MimeType.JSON);
    }
  } catch (error) {
    return ContentService.createTextOutput(JSON.stringify({ status: "error", message: "Write Error: " + error.toString() }))
      .setMimeType(ContentService.MimeType.JSON);
  }
}

4. Click the Save icon (the floppy disk) at the top of the editor.

Phase 3: Deploy as a Web App

You must deploy the script so the mobile app can access it securely.

1. Click the Deploy button at the top right of the Apps Script editor.
2. Select New deployment.
3. Click the gear icon next to “Select type” and choose Web app.
4. Add a description (e.g., “Student Evaluation Backend”).
5. Under Execute as, select Me.
6. Under Who has access, select Anyone.
7. Click Deploy.
8. Google will prompt you to authorize access. Click Authorize access, choose your Google account, click Advanced, and click Go to [Project Name] (unsafe) to allow the script to edit your spreadsheet.
9. Once authorized, copy the Web app URL provided.

Phase 4: Connect the Mobile App

Your backend is now ready to receive grades.

1. Open the Evaluation Mobile App on your device.
2. Tap the + button (Add New Sheet).
3. Give your sheet a recognizable name (e.g., “Term 1 Cohort”).
4. Paste the Web app URL you copied in Phase 3.
5. Tap Save.
6. Tap your newly added sheet from the list. The app will securely fetch your roster, and you are ready to begin grading.