The in-class participation app is basically a web-based artefact designed and developed to ease me in assigning reflective feedback to my students. It is capable of recording notes for my students while also recording the score for each in-class participation.

Develop Your Own Class Participation App

Dibutuhkan 2 script untuk menjalankan aplikasi ini - code.gs dan form. Code.gs akan diletakkan di Apps Script, sementara form akan diletakkan di website.

Scripts Deployment

1. Buat Google Spreadsheet baru. Anda dapat menamainya untuk memudahkan Anda dalam mengidentifikasi spreadsheet yang Anda gunakan.
2. Pada baris menu, klik Extensions.
3. Pilih Apps Scripts.
4. Paste code.gs berikut ini ke Apps Script.

const SPREADSHEET_ID = 'ABCDE';

// Function to handle GET requests (fetches student name and class names)
function doGet(e) {
  const sheet = SpreadsheetApp.openById(SPREADSHEET_ID);
  const className = e.parameter.className;
  const studentId = e.parameter.studentId;
  const assessorId = e.parameter.assessorId;

  if (className && studentId) {
    const classSheet = sheet.getSheetByName(className);
    if (!classSheet) {
      return ContentService.createTextOutput(JSON.stringify({ error: 'Class not found' }))
        .setMimeType(ContentService.MimeType.JSON);
    }

    const studentData = classSheet.getDataRange().getValues();

    // Find student row by studentId (column C, index 2)
    const studentRow = studentData.find(row => String(row[2]) === String(studentId));
    if (!studentRow) {
      return ContentService.createTextOutput(JSON.stringify({ error: 'Student not found' }))
        .setMimeType(ContentService.MimeType.JSON);
    }

    const studentName = studentRow[1]; // Column B

    // If assessorId provided, return score options for assessor UI
    if (assessorId) {
      return ContentService.createTextOutput(JSON.stringify({
        studentName: studentName,
        scoreOptions: Array.from({ length: 101 }, (_, i) => i) // 0 to 100
      })).setMimeType(ContentService.MimeType.JSON);
    }

    // Extract notes from column D (index 3)
    const notesRaw = studentRow[3] || '';
    const notesArray = notesRaw.split('\n').map(note => note.trim());

    // Extract scores from column F (index 5) onward
    const firstScoreColIndex = 5;
    const lastCol = classSheet.getLastColumn();
    const scoresArray = studentRow.slice(firstScoreColIndex, lastCol);

    // Combine scores and notes into array of objects
    const combined = scoresArray.map((score, i) => ({
      no: i + 1,
      score: score !== '' ? score : 'N/A',
      notes: notesArray[i] || ''
    }));

    // Return detailed student data (name + combined scores & notes)
    return ContentService.createTextOutput(JSON.stringify({
      studentName: studentName,
      data: combined
    })).setMimeType(ContentService.MimeType.JSON);

  } else {
    // Return list of class names if no parameters
    const classNames = sheet.getSheets().map(s => s.getName());
    return ContentService.createTextOutput(JSON.stringify(classNames))
      .setMimeType(ContentService.MimeType.JSON);
  }
}


// Function to handle POST requests (writes scores and notes to the corresponding cells)
function doPost(e) {
  const sheet = SpreadsheetApp.openById(SPREADSHEET_ID);
  const className = e.parameter.className;
  const studentId = e.parameter.studentId;
  const score = parseInt(e.parameter.score, 10);
  const notes = e.parameter.notes;
  const submittedAssessorId = e.parameter.assessorId;

  if (className && studentId && !isNaN(score)) {
    const classSheet = sheet.getSheetByName(className);
    const studentData = classSheet.getDataRange().getValues();
    const studentRow = studentData.findIndex(row => row[2] == studentId); // Assume Student ID is in column C
    const storedAssessorId = classSheet.getRange('A1').getValue();

    if (submittedAssessorId !== storedAssessorId) {
      return ContentService.createTextOutput('Invalid Assessor ID.')
        .setMimeType(ContentService.MimeType.TEXT);
    }

    if (studentRow !== -1) {
      const row = studentRow + 1;
      const timestamp = new Date();

      if (notes && notes.trim() !== '') {
        const existingNotes = classSheet.getRange(row, 4).getValue(); // Assume notes are in column D
        const notesArray = existingNotes ? existingNotes.split('\n').filter(note => note.trim() !== '') : [];
        const nextNoteNumber = notesArray.length + 1;
        const numberedNote = `${nextNoteNumber}. ${notes}`;
        const updatedNotes = existingNotes ? `${existingNotes}\n${numberedNote}` : numberedNote;
        classSheet.getRange(row, 4).setValue(updatedNotes);
      }

      classSheet.getRange(row, 1).setValue(timestamp); // Assume timestamp is in column A

      const firstScoreColumn = 6; // Column F
      let col = firstScoreColumn;
      while (classSheet.getRange(row, col).getValue() !== '' && col <= classSheet.getLastColumn()) {
        col++;
      }
      classSheet.getRange(row, col).setValue(score);

      updateCumulativeScore(classSheet, row, firstScoreColumn, col);

      const dataRange = classSheet.getDataRange();
      dataRange.setHorizontalAlignment("left")
        .setVerticalAlignment("top")
        .setWrap(false);

      classSheet.autoResizeColumns(firstScoreColumn, classSheet.getLastColumn() - firstScoreColumn + 1);

      const lastRow = classSheet.getLastRow();
      const fixedHeight = 20; // Adjust this value as needed
      classSheet.setRowHeightsForced(1, lastRow, fixedHeight);

      return ContentService.createTextOutput('Score successfully submitted!')
        .setMimeType(ContentService.MimeType.TEXT);
    } else {
      return ContentService.createTextOutput('Student not found.')
        .setMimeType(ContentService.MimeType.TEXT);
    }
  }

  return ContentService.createTextOutput('Error processing request.')
    .setMimeType(ContentService.MimeType.TEXT);
}

function updateCumulativeScore(sheet, row, firstScoreColumn, lastFilledColumn) {
  const numCols = lastFilledColumn - firstScoreColumn + 1;
  if (numCols < 1) {
    // No scores to sum, clear cumulative score and exit
    sheet.getRange(row, 5).setValue('');
    return;
  }

  const range = sheet.getRange(row, firstScoreColumn, 1, numCols);
  const scores = range.getValues()[0].filter(score => score !== '' && !isNaN(score));
  const divider = parseFloat(sheet.getRange('E1').getValue());

  if (scores.length > 0 && !isNaN(divider) && divider > 0) {
    const sumOfScores = scores.reduce((sum, score) => sum + score, 0);
    const cumulativeScore = Math.min(sumOfScores / divider, 100);
    const formattedScore = parseFloat(cumulativeScore.toFixed(2));

    sheet.getRange(row, 5).setValue(formattedScore);
  } else {
    sheet.getRange(row, 5).setValue('');
  }
}


// Called when E1 is edited or from the menu button
function refreshAllScores() {
  const ss = SpreadsheetApp.openById(SPREADSHEET_ID);
  const sheet = ss.getActiveSheet();
  const studentData = sheet.getDataRange().getValues();
  const firstScoreColumn = 6; // Column F

  for (let i = 1; i < studentData.length; i++) {
    let lastFilledColumn = firstScoreColumn - 1;
    for (let col = firstScoreColumn; col <= sheet.getLastColumn(); col++) {
      if (sheet.getRange(i + 1, col).getValue() !== '') {
        lastFilledColumn = col;
      }
    }
    updateCumulativeScore(sheet, i + 1, firstScoreColumn, lastFilledColumn);
  }
}

// Called when E1 is edited or from the menu button
function refreshAllScores() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getActiveSheet();
  const studentData = sheet.getDataRange().getValues();
  const firstScoreColumn = 6;

  for (let i = 1; i < studentData.length; i++) {
    let lastFilledColumn = firstScoreColumn - 1;
    for (let col = firstScoreColumn; col <= sheet.getLastColumn(); col++) {
      if (sheet.getRange(i + 1, col).getValue() !== '') {
        lastFilledColumn = col;
      }
    }
    updateCumulativeScore(sheet, i + 1, firstScoreColumn, lastFilledColumn);
  }
}

// Triggered automatically when any edit happens
function onEdit(e) {
  const range = e.range;
  if (range.getA1Notation() === 'E1') {
    refreshAllScores();
  }
}

// Creates custom menu with "REFRESH SCORE" button
function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('Custom')
    .addItem('REFRESH SCORE', 'refreshAllScores')
    .addToUi();
}

function createTextOutput(content) {
  return ContentService.createTextOutput(JSON.stringify(content))
    .setMimeType(ContentService.MimeType.JSON);
}

5. Pada baris pertama code.gs Anda, terdapat baris const SPREADSHEET_ID = 'ABCDE';. Ganti ABCDE dengan Spreadsheet ID Anda. Spreadsheet ID Anda bisa Anda dapatkan di URL spreadsheet.
6. Deploy script Anda.
7. Klik ikon "⚙" pada jenjela po-up yang terbuka, pilih Webapp.
8. Tuliskan deskripsi pada kolom Description. Pada kolom Who has access, pilih Anyone.
9. Jendela baru akan terbuka. Pilih Authorize access.
10. Pilih email Anda. Disarankan untuk menggunakan email pribadi dengan ekstensi gmail.com.
11. Akan muncul pemberitahuan Google hasn't verified this app, klik Advanced, lalu klik Go to [your project] (unsafe). Notifikasi ini muncul karena skrip yang Anda deploy merupakan skrip yang Anda kembangkan sendiri, bukan berarti tidak aman (unsafe).
12. Klik Allow.
13. Jendela baru akan terbuka yang berisi Deployment ID dan Web app URL. Biarkan jendela ini tetap terbuka.

Membuat Google Sites

Untuk menjalankan aplikasi ini, idealnya Anda membutuhkan website. Jika Anda belum memiliki website, Google menyediakan pembuatan website interaktif yang dapat digunakan secara gratis tanpa perlu hosting dan domain.

1. Pertama-tama, buat Google Sites Anda. Akses laman https://sites.google.com/.
2. Klik Blank Sites.
3. Klik dua kali pada bagian kosong, klik icon < > (embed).
4. Jendela pop-up akan terbuka, klik Embed Code.
5. Anda akan meluhat placeholder code goes here. Pada placeholder tersebut, tempelkan script berikut:

<script>
  const WEB_APP_URL = 'FGHIJ'; // Replace with your WebApp URL

  function showLoadingModal(message) {
    const modal = document.getElementById('loadingModal');
    const loadingMessage = document.getElementById('loadingMessage');
    const okBtn = document.getElementById('loadingOkBtn');
    modal.style.display = 'flex';
    loadingMessage.innerText = message || 'Loading...';
    okBtn.style.display = 'none';
  }

  function hideLoadingModal() {
    document.getElementById('loadingModal').style.display = 'none';
  }

  function fetchStudentDetails() {
    const studentId = document.getElementById('studentId').value.trim();
    const className = document.getElementById('className').value.trim();
    const assessorId = document.getElementById('assessorId').value.trim();

    if (!studentId || !className) {
      alert('Please enter Student ID and Class Name.');
      return;
    }

    showLoadingModal('Fetching student details...');

    const url = `${WEB_APP_URL}?studentId=${encodeURIComponent(studentId)}&className=${encodeURIComponent(className)}&assessorId=${encodeURIComponent(assessorId)}`;

    fetch(url)
      .then(res => res.json())
      .then(data => {
        if (data.studentName) {
          document.getElementById('studentName').innerHTML = `<p><strong>Student Name: ${data.studentName}</strong></p>`;

          // Display Assessor Name if available
          if (data.assessorName) {
            document.getElementById('assessorName').innerHTML = `<p><strong>Assessor: ${data.assessorName}</strong></p>`;
          } else {
            document.getElementById('assessorName').innerHTML = ''; // Clear if no assessor name
          }

          if (data.scoreOptions) {
            document.getElementById('displayNotes').innerHTML = '';
            createScoreInput();
            createNotesInput();
          } else {
            // Display formatted table data (score and notes)
            displayStudentData(data);
          }
          document.getElementById('message').innerText = '';
        } else {
          document.getElementById('studentName').innerText = data.error || 'Student not found';
          document.getElementById('assessorName').innerText = ''; // Clear if student not found
          document.getElementById('displayNotes').innerText = '';
          document.getElementById('scoreButtons').innerHTML = '';
          document.getElementById('notesInputContainer').innerHTML = '';
        }
      })
      .catch(() => {
        document.getElementById('studentName').innerText = 'Error fetching student details';
        document.getElementById('assessorName').innerText = ''; // Clear on error
        document.getElementById('displayNotes').innerText = '';
        document.getElementById('scoreButtons').innerHTML = '';
        document.getElementById('notesInputContainer').innerHTML = '';
      })
      .finally(() => {
        hideLoadingModal();
      });
  }

  // Display student data in a table format
  function displayStudentData(data) {
    let tableHtml = '<table border="1" style="width: 100%; border-collapse: collapse;">';
    tableHtml += '<tr><th>NO.</th><th>SCORE</th><th>NOTES</th></tr>';

    data.data.forEach((item) => {
      tableHtml += `<tr>
                      <td>${item.no}</td>
                      <td>${item.score}</td>
                      <td>${item.notes}</td>
                    </tr>`;
    });

    tableHtml += '</table>';
    document.getElementById('displayNotes').innerHTML = tableHtml;
    document.getElementById('scoreButtons').innerHTML = '';
    document.getElementById('notesInputContainer').innerHTML = '';
  }

  function createScoreInput() {
    const container = document.getElementById('scoreButtons');
    container.innerHTML = '';

    const input = document.createElement('input');
    input.type = 'number';
    input.id = 'scoreInput';
    input.placeholder = 'Enter score (0-100)';
    input.min = 0;
    input.max = 100;
    input.required = true;

    const button = document.createElement('button');
    button.innerText = 'Submit Score';
    button.onclick = submitScore;

    container.appendChild(input);
    container.appendChild(button);
  }

  function createNotesInput() {
    const container = document.getElementById('notesInputContainer');
    container.innerHTML = '';

    const textarea = document.createElement('textarea');
    textarea.id = 'notes';
    textarea.placeholder = 'Enter notes';

    // Prevent line breaks by disabling the Enter key
    textarea.addEventListener('keydown', function(event) {
      if (event.key === 'Enter') {
        event.preventDefault();
      }
    });

    container.appendChild(textarea);
  }

  function submitScore() {
    const studentId = document.getElementById('studentId').value.trim();
    const className = document.getElementById('className').value.trim();
    const assessorId = document.getElementById('assessorId').value.trim();
    const scoreInput = document.getElementById('scoreInput');
    const notes = document.getElementById('notes').value.trim();

    if (!studentId || !className || !assessorId) {
      alert('Please fill Student ID, Class Name, and Assessor ID.');
      return;
    }

    const score = parseInt(scoreInput.value, 10);
    if (isNaN(score) || score < 0 || score > 100) {
      alert('Please enter a valid score between 0 and 100.');
      return;
    }

    showLoadingModal('Saving the score into the database...');

    const submitBtn = document.querySelector('#scoreButtons button');
    submitBtn.disabled = true;

    const formData = new FormData();
    formData.append('assessorId', assessorId);
    formData.append('studentId', studentId);
    formData.append('className', className);
    formData.append('score', score);
    formData.append('notes', notes);

    fetch(WEB_APP_URL, {
      method: 'POST',
      body: formData
    })
      .then(res => res.text())
      .then(message => {
        const messageEl = document.getElementById('message');
        messageEl.innerText = message;
        messageEl.classList.remove('error-message');
        messageEl.classList.add('success-message');

        document.getElementById('notes').value = '';
        scoreInput.value = '';

        const loadingMessage = document.getElementById('loadingMessage');
        const okBtn = document.getElementById('loadingOkBtn');

        loadingMessage.innerText = 'Score submitted successfully.';
        okBtn.style.display = 'inline-block';
        submitBtn.disabled = false;

        fetchStudentDetails();
      })
      .catch(() => {
        const messageEl = document.getElementById('message');
        messageEl.innerText = 'Error submitting score';
        messageEl.classList.remove('success-message');
        messageEl.classList.add('error-message');

        const loadingMessage = document.getElementById('loadingMessage');
        const okBtn = document.getElementById('loadingOkBtn');

        loadingMessage.innerText = 'Error submitting score.';
        okBtn.style.display = 'inline-block';
        submitBtn.disabled = false;
      });
  }

  document.getElementById('loadingOkBtn').addEventListener('click', () => {
    hideLoadingModal();
  });

  document.getElementById('fetchStudentBtn').addEventListener('click', fetchStudentDetails);
</script>



<h2>Class Participation</h2>

<input type="text" id="assessorId" placeholder="Enter Assessor ID (For assessor only - Case Sensitive)" />
<input type="text" id="studentId" placeholder="Enter Student ID (Required)" />
<input type="text" id="className" placeholder="Enter Class Name (Required)" />

<button id="fetchStudentBtn">Fetch Student Details</button>

<div id="assessorName"></div>
<div id="studentName" class="result-container"></div>
<div id="displayNotes" class="result-container"></div>
<div id="notesInputContainer" class="notesInputContainer"></div>
<div id="scoreButtons" class="scoreContainer"></div>
<div class="message" id="message"></div>

<!-- Loading Modal -->
<div id="loadingModal">
  <div>
    <p id="loadingMessage">Loading...</p>
    <button id="loadingOkBtn" style="display:none; margin-top:20px; padding:8px 20px; font-size:16px;">OK</button>
  </div>
</div>


<style>
  input, textarea, button {
    display: block;
    width: 95%;
    max-width: 100%;
    margin: 15px 0;
    font-size: 1rem;
    min-height: 1.5em;
    padding: 12px 10px;
    border: 1px solid TEAL;
    border-radius: 8px;
    background: #f9f9f9;
    box-shadow: inset 1px 1px 3px rgba(0, 0, 0, 0.05);
    transition: all 0.2s ease;
  }

  input:focus, textarea:focus {
    border-color: #4a90e2;
    background: #ffffff;
    outline: none;
    box-shadow: 0 0 0 3px rgba(74, 144, 226, 0.2);
  }

  button {
    background-color: #4a90e2;
    color: #fff;
    font-weight: bold;
    cursor: pointer;
    border: none;
    transition: background-color 0.3s ease;
    width: 50%;
  }

  button:hover {
    background-color: #357ab7;
  }

  .result-container {
    margin-top: 10px;
    background: #eef3f9;
    padding: 15px;
    border-radius: 8px;
    box-shadow: 0 2px 6px rgba(0, 0, 0, 0.05);
    font-size: 0.98rem;
  }

  .scoreContainer,
  .notesInputContainer {
    margin-top: 12px;
  }

  .message {
    margin-top: 20px;
    font-weight: 400;
    font-size: 1rem;
    padding: 10px;
    border-radius: 6px;
    text-align: center;
  }

  .success-message {
    color: #2ecc71;
    background-color: #eafaf1;
    border: 1px solid #b2f2c9;
  }

  .error-message {
    color: #e74c3c;
    background-color: #fdecea;
    border: 1px solid #f5c6cb;
  }

  #loadingModal {
    display: none;
    position: fixed;
    top: 0;
    left: 0;
    width: 100vw;
    height: 100vh;
    background: rgba(0, 0, 0, 0.5);
    justify-content: center;
    align-items: center;
    z-index: 9999;
  }

  #loadingModal > div {
    background: #fff;
    padding: 25px;
    border-radius: 10px;
    width: 85%;
    max-width: 320px;
    text-align: center;
    box-shadow: 0 4px 12px rgba(0, 0, 0, 0.15);
  }

  .result-container:empty {
    display: none;
  }

  @media screen and (max-width: 480px) {
    input, textarea, button {
      font-size: 1rem;
      padding: 10px 10px;
    }

    .message {
      font-size: 0.95rem;
    }

    #loadingModal > div {
      padding: 10px;
    }
  }
</style>

6. Selanjutnya, pada baris paling Anda, pada baris const WEB_APP_URL = 'FGHIJ'; // GANTI DENGAN WEB APP URL ANDA, ganti FGHIJ dengan Web App url Anda. Web App URL ada pada Apps Script yang sebelumnya telah Anda deploy.
7. Simpan script.
8. Klik Publish pada bagian kanan atas.
9. Lakukan pengujian.