5

function doGet(e) {
   Logger.log( Utilities.jsonStringify(e) );

  if (!e.parameter.page) {

    return HtmlService.createTemplateFromFile('index2').evaluate();
  }

  var template = HtmlService.createTemplateFromFile(e.parameter.page)
  template.action = ScriptApp.getService().getUrl();
  return template.evaluate();
}

function addUser(id,name,nota1,nota2,nota3) {
  var url = "https://docs.google.com/spreadsheets/d/1Yn-z7X-mb9TWaxjWWps79qb_v68h9KHFAWDMn7jLfU0/edit#gid=0";
  var ss = SpreadsheetApp.openByUrl(url);
  var ws = ss.getSheetByName("notas");

  ws.appendRow([id,name,nota1,nota2,nota3]);


}



//Called from the client with form data, basic validation for blank values
function formSubmit(formData){
  for(var field in formData){
    if(formData[field] == ''){
      return {success: false, message: field + 'No se pueden enviar campos vacios'}
    }
  }
  return {success: true, message: 'Mensaje enviado con exito'};
}

function menu(){
  var ui = SpreadsheetApp.getUi();
  ui.createMenu("Procesos")
    .addItem('Calcular Notas', 'CalculoNotas')
    .addItem('Enviar resultados', 'spreadsheetToPdf')
    .addToUi();
}


function CalculoNotas() {

 const sps = SpreadsheetApp.getActive();
  var sheet =  sps.getSheets();
  var data =  sheet[0].getDataRange().getValues();
var contacts = sps.getSheetByName("percent");
var numRows = contacts.getLastRow();
const percent1 = contacts.getRange(1, 1, numRows, 1).getValues();
const percent2 = contacts.getRange(1, 2, numRows, 1).getValues();
const percent3 = contacts.getRange(1, 3, numRows, 1).getValues();




    for (i in data)
  {
    var row = data[i];
    var Nota1 = row[2];
    var Nota2 = row[3];
    var Nota3 = row[4];
    var Apro =  row[8];
    var Total = (Nota1 * percent1) + (Nota2 * percent2) + (Nota3 * percent3);


   if (Total >= 6 )
    {

     var StarRow = 1 + +i;
     sheet[0].getRange(StarRow, 7).setValue("Aprobado");
     }
      else
      {

     var StarRow = 1 + +i;
     sheet[0].getRange(StarRow, 7).setValue("Reprobado");
      }

    var StarRow = 1 + +i;
     sheet[0].getRange(1, 6).setValue("Total"); //Agrega el  texto: Total en la columna 5
     sheet[0].getRange(1, 7).setValue("Estado"); //Agrega el texto:Estado en la columna 6
     sheet[0].getRange(1, 8).setValue("Aprobados").setBackground('Green'); //Agrega el texto:Estado en la columna 7
     sheet[0].getRange(2, 8).setValue("Reprobados").setBackground('Red'); //Agrega el texto:Estado en la columna 8
    sheet[0].getRange(StarRow, 6).setValue(Total); //6

    //conteo aprobados vrs reprobados
  sheet[0].getRange("I1").setFormula('=COUNTIF(G:G;"Aprobado")');
  sheet[0].getRange("I2").setFormula('=COUNTIF(G:G;"Reprobado")');


  }

}



function spreadsheetToPdf() {

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const subject = `PDF Generado por ${ss.getName()}`;
  const body = "Enviado con [Email SpreadSheet] Reporte de Notas";
  const url = 'https://docs.google.com/spreadsheets/d/SS_ID/export?'.replace('SS_ID', ss.getId());
  var contacts = ss.getSheetByName("contacto");
  var numRows = contacts.getLastRow();
  const email = contacts.getRange(1, 1, numRows, 1).getValues();
  const exportOptions =
    'exportFormat=pdf&format=pdf' + // export as pdf / csv / xls / xlsx
    '&size=letter' + // paper size legal / letter / A4
    '&portrait=false' + // orientation, false for landscape
    '&fitw=true&source=tuto' + // fit to page width, false for actual size
    '&sheetnames=false&printtitle=false' + // hide optional headers and footers
    '&pagenumbers=false&gridlines=false' + // hide page numbers and gridlines
    '&fzr=false' + // do not repeat row headers (frozen rows) on each page
    '&gid='; // the sheet's Id

  const token = ScriptApp.getOAuthToken();
  const sheets = ss.getSheets();
  const blobs = [];

  for (let i = 0; i < sheets.length; i += 1) {
    const response = UrlFetchApp.fetch(url + exportOptions + sheets[i].getSheetId(), {
      headers: {
        Authorization: `Bearer ${token}`
      }
    });


    blobs[i] = response.getBlob().setName(`${sheets[i].getName()}.pdf`);
  }


  const zipBlob = Utilities.zip(blobs).setName(`${ss.getName()}.zip`);


  DriveApp.createFile(zipBlob);

  Logger.log(`Storage Space used: ${DriveApp.getStorageUsed()}`);

  if (MailApp.getRemainingDailyQuota() > 0)
    GmailApp.sendEmail(email, subject, body, {
      htmlBody: body,
      attachments: [zipBlob]
    });
}