Don't Just Get
a License. Build
Road Confidence.

// ═══ PMDS ATTENDANCE SHEET  (Apps Script) ════════════════
// Rows = Students | Columns = Days 1-31 | Cells = P / A / L
// Each month gets its own tab: "Att Mar 2026", etc.
//
// SETUP (do once):
// 1. Create a NEW Google Sheet (attendance only)
// 2. Extensions → Apps Script → paste this code
// 3. Replace ATT_SHEET_ID_PLACEHOLDER with your sheet ID
// 4. Run setupAttendanceSheet() once
// 5. Run setupTriggers() once (auto-creates monthly tabs)
// 6. Deploy → Web App → Execute as Me → Anyone → Copy URL
// ════════════════════════════════════════════════════════

function getSS() {
  return SpreadsheetApp.openById('ATT_SHEET_ID_PLACEHOLDER');
}
function monthTabName(y, m) {
  return 'Att ' + new Date(y, m-1, 1).toLocaleDateString('en-IN',{month:'short',year:'numeric'});
}
function columnLetter(n) {
  let s='';
  while(n>0){const r=(n-1)%26;s=String.fromCharCode(65+r)+s;n=Math.floor((n-1)/26);}
  return s;
}
function createMonthlyTab(year, month) {
  const ss=getSS(); const tabName=monthTabName(year,month);
  if(ss.getSheetByName(tabName)) return ss.getSheetByName(tabName);
  const days=new Date(year,month,0).getDate();
  const sheet=ss.insertSheet(tabName);
  const dayHdrs=[];
  for(let d=1;d<=days;d++){const wd=new Date(year,month-1,d).toLocaleDateString('en-IN',{weekday:'short'});dayHdrs.push(d+'
'+wd);}
  const hdrs=['S.No','Student Name','Session',...dayHdrs,'Present','Absent','Late','% Att'];
  const hRange=sheet.getRange(1,1,1,hdrs.length);
  hRange.setValues([hdrs]).setFontWeight('bold').setBackground('#0F172A').setFontColor('#ff7c01')
        .setHorizontalAlignment('center').setVerticalAlignment('middle').setWrap(true);
  sheet.setRowHeight(1,45);sheet.setFrozenRows(1);sheet.setFrozenColumns(3);
  for(let d=1;d<=days;d++){if(new Date(year,month-1,d).getDay()===0)sheet.getRange(1,d+3,200,1).setBackground('#1a0a0a');}
  sheet.setColumnWidth(1,45);sheet.setColumnWidth(2,170);sheet.setColumnWidth(3,130);
  for(let d=1;d<=days;d++)sheet.setColumnWidth(d+3,38);
  SpreadsheetApp.flush();Logger.log('Created: '+tabName);return sheet;
}
function _addFormulas(sheet,row,days){
  const ds=columnLetter(4),de=columnLetter(days+3),pc=columnLetter(days+4),ac=columnLetter(days+5);
  sheet.getRange(row,days+4).setFormula(`=COUNTIF(${ds}${row}:${de}${row},"P")`);
  sheet.getRange(row,days+5).setFormula(`=COUNTIF(${ds}${row}:${de}${row},"A")`);
  sheet.getRange(row,days+6).setFormula(`=COUNTIF(${ds}${row}:${de}${row},"L")`);
  sheet.getRange(row,days+7).setFormula(`=IF(${pc}${row}+${ac}${row}=0,"",ROUND(${pc}${row}/(${pc}${row}+${ac}${row})*100,1)&"%")`);
}
function doGet(){ return out({ok:true,service:'PMDS Attendance',v:'1.0'}); }
function doPost(e){
  try{
    const body=JSON.parse(e.postData.contents);
    switch(body.action){
      case 'saveAttendance': return saveAttendance(body.data);
      case 'createMonthlyTab': createMonthlyTab(body.data.year||new Date().getFullYear(),body.data.month||new Date().getMonth()+1);return out({ok:true});
      case 'getStatus': return out({ok:true});
      default: return out({ok:false,error:'Unknown action'});
    }
  }catch(err){return out({ok:false,error:err.message});}
}
function saveAttendance(d){
  const dt=new Date(d.date),year=dt.getFullYear(),month=dt.getMonth()+1,day=dt.getDate();
  const ss=getSS();let sheet=ss.getSheetByName(monthTabName(year,month));
  if(!sheet)sheet=createMonthlyTab(year,month);
  const days=new Date(year,month,0).getDate(),col=day+3;
  const map={present:'P',absent:'A',late:'L'};
  let data=sheet.getDataRange().getValues();
  const nameToRow={};
  for(let r=1;r<data.length;r++){const n=String(data[r][1]).trim().toLowerCase();if(n)nameToRow[n]=r+1;}
  if(d.studentMap){
    let added=0;
    Object.values(d.studentMap).forEach(info=>{
      const key=(info.name||'').toLowerCase().trim();
      if(!key||nameToRow[key])return;
      const nr=sheet.getLastRow()+1;
      sheet.getRange(nr,1).setValue(nr-1);
      sheet.getRange(nr,2).setValue(info.name||'');
      sheet.getRange(nr,3).setValue(info.batch||info.grade||'');
      sheet.getRange(nr,1,1,3).setBackground('#0F172A').setFontColor('#ffffff');
      _addFormulas(sheet,nr,days);nameToRow[key]=nr;added++;
    });
    if(added)data=sheet.getDataRange().getValues();
  }
  Object.entries(d.attendance||{}).forEach(([name,status])=>{
    const cell=map[status]||'';if(!cell)return;
    const sRow=nameToRow[name.toLowerCase().trim()];if(!sRow)return;
    const range=sheet.getRange(sRow,col);range.setValue(cell);
    if(cell==='P')range.setBackground('#0D2B1A').setFontColor('#6EE7B7').setFontWeight('bold');
    else if(cell==='A')range.setBackground('#2D0D0D').setFontColor('#FCA5A5').setFontWeight('bold');
    else if(cell==='L')range.setBackground('#2D2700').setFontColor('#FCD34D').setFontWeight('bold');
  });
  SpreadsheetApp.flush();return out({ok:true});
}
function setupAttendanceSheet(){
  const ss=getSS();
  const def=ss.getSheetByName('Sheet1');if(def&&ss.getSheets().length>1)ss.deleteSheet(def);
  const now=new Date();createMonthlyTab(now.getFullYear(),now.getMonth()+1);
  SpreadsheetApp.flush();Logger.log('Done! Run setupTriggers() next.');
}
function setupTriggers(){
  ScriptApp.getProjectTriggers().forEach(t=>{if(t.getHandlerFunction()==='autoMonthlyTab')ScriptApp.deleteTrigger(t);});
  ScriptApp.newTrigger('autoMonthlyTab').timeBased().onMonthDay(1).atHour(1).create();
  Logger.log('Trigger set!');
}
function autoMonthlyTab(){const n=new Date();createMonthlyTab(n.getFullYear(),n.getMonth()+1);}
function out(data){return ContentService.createTextOutput(JSON.stringify(data)).setMimeType(ContentService.MimeType.JSON);}

/* Please click the orange "Copy Code" button above to dynamically generate your completely up-to-date and customized Apps Script based on your current fields. */