Untitled

function recommendTeachers() {
  const profilingSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Teacher Profiling");
  const statsSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Teacher Moodle Stats");

  // Read course names (current and future courses)
  const courseInputRange = profilingSheet.getRange("C6:C9").getValues().flat().filter(course => course);

  if (courseInputRange.length === 0) {
    Logger.log("No courses provided for recommendation.");
    return;
  }

  // Read headers and teacher data from Teacher Moodle Stats
  const headers = statsSheet.getRange("E2:BB2").getValues()[0];
  const teacherData = statsSheet.getRange(3, 1, statsSheet.getLastRow() - 2, statsSheet.getLastColumn()).getValues();

  // Create a dictionary to store recommendations for each teacher
  let teacherRecommendations = {};

  // Iterate over each course
  courseInputRange.forEach((course, courseIndex) => {
    const courseHeaderIndex = headers.indexOf(course); // Find the column index for the course

    if (courseHeaderIndex === -1) {
      Logger.log(`Course "${course}" not found in Teacher Moodle Stats headers.`);
      return;
    }

    // Column to search is offset by E2
    const columnIndex = courseHeaderIndex + 4;

    // Check each teacher for compliance
    teacherData.forEach(row => {
      const teacherName = row[0];
      const percentage = row[columnIndex];

      if (percentage && !percentage.includes("Not onboarded")) {
        // Parse percentage (e.g., "51-60%" -> numeric range)
        const match = percentage.match(/^(\d+)-(\d+)%|^(\d+)%$/);
        if (match) {
          const lowerBound = parseInt(match[1] || match[3], 10);

          if (lowerBound >= 50) {
            // Initialize teacher record if not already present
            if (!teacherRecommendations[teacherName]) {
              teacherRecommendations[teacherName] = Array(courseInputRange.length).fill("-");
            }

            // Update the percentage for this course
            teacherRecommendations[teacherName][courseIndex] = percentage;
          }
        }
      }
    });
  });

  // Prepare data for writing to the sheet
  const outputData = Object.entries(teacherRecommendations).map(([teacherName, percentages]) => {
    return [teacherName, ...percentages];
  });

  // Write the header row
  const headerRow = ["Recommend Teacher", ...courseInputRange.map((course, index) => `Future Course ${index + 1} %`)];
  profilingSheet.getRange("C13").setValue("Recommend Teacher"); // Set the first column header
  profilingSheet.getRange(13, 4, 1, courseInputRange.length).setValues([headerRow.slice(1)]); // Set future course headers

  // Write teacher recommendations starting from C14
  profilingSheet.getRange(14, 3, outputData.length, outputData[0].length).setValues(outputData);

  Logger.log("Recommendations have been updated.");
}