meghalaya.js

const getAttendanceData = (sheet) => {
  const lastColumn = sheet.getLastColumn();
  const heading = sheet.getRange("A1:" + sheet.getRange(1, lastColumn).getA1Notation()).getValues()[0];
  const data = sheet.getRange("A3:" + sheet.getRange(1, lastColumn).getA1Notation() + sheet.getLastRow()).getValues();

  // Find the start date for the last 12 weeks
  const today = new Date();
  const twelveWeeksAgo = new Date(today.setDate(today.getDate() - 84));

  // Filter the headings to get the columns within the last 12 weeks
  const columnsWithinLast12Weeks = heading
    .map((date, index) => ({ date: new Date(date), index }))
    .filter(({ date }) => date >= twelveWeeksAgo)
    .map(({ index }) => index);

  // Create groups of dates based on 4-week periods, starting from the last date available
  const dateGroups = [];
  let currentGroup = [];
  let groupEndDate = columnsWithinLast12Weeks.length > 0 ? heading[columnsWithinLast12Weeks[columnsWithinLast12Weeks.length - 1]] : null;

  columnsWithinLast12Weeks.slice().reverse().forEach((colIndex) => {
    const sessionDate = heading[colIndex];
    if (groupEndDate && (groupEndDate - sessionDate) / (1000 * 60 * 60 * 24) > 28) {
      // Start a new group if more than 4 weeks have passed since the end of the current group
      dateGroups.unshift(currentGroup); // Add the current group to the beginning of the dateGroups array
      currentGroup = [];
      groupEndDate = sessionDate;
    }
    currentGroup.push(colIndex);
  });

  // Add the last group if it's not empty
  if (currentGroup.length > 0) {
    dateGroups.unshift(currentGroup); // Add the last group to the beginning of the dateGroups array
  }

  const processedData = data.map((row) => {

    // Use the date groups to extract and process the attendance data
    const attendanceGroups = dateGroups.map((group) => group.map((colIndex) => row[colIndex] == '1' ? 1 : 0));
    const lastThreeGroups = attendanceGroups.slice(-3);
    const useLastTwoGroups = lastThreeGroups[0].length < 10;
    const groupsToUse = useLastTwoGroups ? lastThreeGroups.slice(1) : lastThreeGroups;
    const averageAttendance = groupsToUse.reduce((sum, group) => sum + group.reduce((groupSum, attendance) => groupSum + attendance, 0), 0) / (lastThreeGroups.length);

    // Assign the level based on the average attendance
    let level = "L0";
    if (averageAttendance >= 8) {
      level = "L3";
    } else if (averageAttendance >= 4) {
      level = "L2";
    } else if (averageAttendance >= 2) {
      level = "L1";
    } else {
      level = "L0";
    }

    return row.concat(level);
  });

  return processedData;
};

const doGet = () => {
  // Replace these with the actual IDs of your spreadsheets
  const spreadsheetIds = ['1DuEEfO0-OuMabB4tXer52r7NXScAykiVuY3kq3TedVk', '1fRr6efbAHw4NQz4b3VHqNS6H992nrcW-OMWJC5ZyADo'];

  const htmls = spreadsheetIds.map(ssID => {
    const ss = SpreadsheetApp.openById(ssID);
    const title = ss.getName();
    const sheet = ss.getSheetByName("Attendance");
    const data = getAttendanceData(sheet)
    let tableHtml = '<table border="1"><tr><th>Name</th><th>Level</th></tr>';

    data.forEach(row => {
      if (row[1].trim() !== '') { // Check if the name is not empty
        let color = '';
        switch (row[row.length - 1]) {
          case 'L3':
            color = 'green'; // Good
            break;
          case 'L2':
            color = 'lightgreen'; // Fair
            break;
          case 'L1':
            color = 'red'; // Bad
            break;
          case 'L0':
            color = 'lightgrey'; // Inactive
            break;
        }
        tableHtml += `<tr><td>${row[1]}</td><td style="background-color: ${color};">${row[row.length - 1]}</td></tr>`;
      }
    });

    tableHtml += '</table>';
    return `<h1>${title}</h1>\n${tableHtml}`

  });

  const output = HtmlService.createHtmlOutput(htmls.join('\n'));
  return output;
};