/** @OnlyCurrentDoc */ // Evalúa LOMLOE para Google Sheets · https://evalua.facilo.es // Pega este archivo entero en Extensiones > Apps Script, guarda y vuelve a abrir tu hoja. // Solo puede leer y escribir la hoja donde lo instalas. Las notas de tus alumnos no salen de ella. /** * Evalúa LOMLOE: every text the teacher sees, word for word from the spec (section 5) and the approved design. * {placeholders} are filled in by the code. */ var Strings = { product: 'Eval\u00faa LOMLOE', supportUrl: 'https://evalua.facilo.es/soporte/', proUrl: 'https://evalua.facilo.es/#precios', email: 'info@facilo.es', menu: { title: 'Eval\u00faa LOMLOE', open: 'Abrir', calc: 'Calcular notas', help: 'Ayuda' }, ui: { back: '\u2039 Volver', loading: 'Cargando\u2026', calculating: 'Calculando\u2026', error: 'Algo ha fallado. Vuelve a intentarlo y, si se repite, escr\u00edbenos a info@facilo.es.', // New notebook newTitle: 'Prepara tu cuaderno de evaluaci\u00f3n', newLead: 'Elige tu materia y cargamos sus criterios de evaluaci\u00f3n, sus competencias espec\u00edficas y los descriptores con los que se relacionan.', fRegion: 'Comunidad', fStage: 'Etapa', fLevel: 'Curso', fCycle: 'Ciclo', fSubject: 'Materia', fGroup: 'Grupo', fGroupPh: 'p. ej. 3\u00ba B', fPeriods: 'Evaluaciones', newSummary: '{n} competencias espec\u00edficas \u00b7 {m} criterios de evaluaci\u00f3n', source: 'Fuente: {src}', baseNote: 'La base estatal es el Real Decreto que desarrollan todas las comunidades. Revisa los cambios de la tuya en la pesta\u00f1a Criterios.', create: 'Crear cuaderno', ownLink: 'Usar\u00e9 mis propios criterios', missingRegion: '\u00bfNo est\u00e1 tu comunidad? Empieza con la base estatal o con tus criterios, y escr\u00edbenos: a\u00f1adimos primero las comunidades que m\u00e1s se piden.', dataFailed: 'No hemos podido cargar la lista de materias. Revisa tu conexi\u00f3n y vuelve a intentarlo, o empieza con tus propios criterios.', retry: 'Reintentar', // Own criteria ownTitle: 'Tus criterios', ownHelp: 'Pega una l\u00ednea por criterio, con su c\u00f3digo y su texto: \u00ab1.1 Interpretar problemas\u2026\u00bb. Si quieres, a\u00f1ade con punto y coma la competencia espec\u00edfica y los descriptores: \u00ab1.1; Interpretar problemas\u2026; CE1; STEM1, CCL2\u00bb.', ownPh: '1.1; Interpretar problemas\u2026; CE1; STEM1, CCL2', errNoCode: 'L\u00ednea {n}: falta el c\u00f3digo del criterio.', errDup: 'L\u00ednea {n}: el c\u00f3digo {c} est\u00e1 repetido.', errNoText: 'L\u00ednea {n}: falta el texto del criterio {c}.', errNone: 'Pega al menos un criterio.', // Main view calc: 'Calcular notas', step1: 'Pega tus alumnos.', step2: 'A\u00f1ade actividades y marca los criterios que eval\u00faa cada una.', step3: 'Pulsa Calcular notas.', secActs: 'Actividades', addAct: 'A\u00f1adir actividad', actsLine: '{n} actividades \u00b7 {m} de {t} criterios con alguna nota', actsLine1: '1 actividad \u00b7 {m} de {t} criterios con alguna nota', secStudents: 'Alumnos', pasteStudents: 'Pegar lista de alumnos', studentsLine: '{n} alumnos', pastePh: 'Un alumno por l\u00ednea, o copia las columnas de S\u00e9neca, Ra\u00edces o \u00cdtaca', pasteBtn: 'Guardar lista', pasteDone: '{n} alumnos en la lista.', pasteGone: '{n} pasan al final como \u00ab(baja)\u00bb porque ya ten\u00edan notas.', pasteGone1: 'Un alumno pasa al final como \u00ab(baja)\u00bb porque ya ten\u00eda notas.', pasteHint: 'Pega la lista en el panel o directamente en Alumnos.', pasteNone: 'No hemos encontrado nombres en lo que has pegado. Pon un alumno por l\u00ednea.', secResults: 'Resultados', rCrit: 'Por criterio', rComp: 'Por competencia', rFinal: 'Notas finales', rPlatform: 'Para {name}', rReport: 'Informe para familias', rPdf: 'Descargar informes en PDF', pdfMaking: 'Preparando el PDF, una p\u00e1gina por alumno\u2026', pdfDone: 'PDF descargado: {n} informes, uno por p\u00e1gina.', pdfOpen: 'Descargar otra vez', secSettings: 'Ajustes', howCalc: 'C\u00f3mo se calcula', secMore: 'M\u00e1s', otherGroup: 'Preparar para otro grupo', footLicence: 'Licencia', footHelp: 'Ayuda', planFree: 'Plan gratuito', proUntil: 'Pro hasta el {fecha}', stale: 'Hay cambios sin calcular.', recalculated: 'Notas recalculadas.', pro: 'Pro', // Add activity addTitle: 'A\u00f1adir actividad', fName: 'Nombre', fNamePh: 'Examen tema 3', fPeriod: 'Evaluaci\u00f3n', fCrit: 'Criterios que eval\u00faa', search: 'Busca por c\u00f3digo o palabra', fWeight: 'Peso', fMax: 'Nota m\u00e1xima', fRec: 'Es una recuperaci\u00f3n', noneTicked: 'Ning\u00fan criterio marcado', add: 'A\u00f1adir', addDone: 'Columna \u00ab{x}\u00bb a\u00f1adida en Notas.', addNoCrit: 'Marca al menos un criterio para que la actividad cuente.', addTooMany: 'Ya hay 300 actividades en Notas, el m\u00e1ximo que calcula Eval\u00faa.', // After calculating calcDone: '{n} alumnos calculados.', wMissing: '{n} criterios de la {p} evaluaci\u00f3n a\u00fan sin notas: {codes}.', wBad: 'Nota no v\u00e1lida en Notas!{ref}: \u00ab{raw}\u00bb.', wUnknown: 'La actividad \u00ab{a}\u00bb usa un criterio que no existe: {c}.', wPct: 'Los porcentajes de Criterios suman {total} % y deber\u00edan sumar cien.', wTooMany: 'Notas tiene m\u00e1s de 300 actividades. Eval\u00faa calcula las 300 primeras.', allGood: 'Todo listo para la sesi\u00f3n de evaluaci\u00f3n.', goCell: 'Ir a la celda', noStudents: 'A\u00fan no hay alumnos. Pega la lista en el panel o directamente en Alumnos.', // Settings settingsTitle: 'C\u00f3mo se calcula', settingsSave: 'Guardar y recalcular', settingsSaved: 'Ajustes guardados.', // Pro card proTitle: 'Eval\u00faa todas tus clases', proBody: 'Con Pro tienes criterios cargados en todos tus grupos, la hoja lista para {platform}, informes para familias, recuperaciones y tus cuadernos listos para otro grupo en un clic.', proPrice: '9,90 \u20ac por curso escolar', proDept: 'Departamento: 39 \u20ac al a\u00f1o, hasta 10 docentes', proBuy: 'Pasar a Pro', proSoon: 'Muy pronto', proHave: 'Ya tengo una licencia', slotLine: 'Tu clase gratuita es {x}. Puedes cambiarla a este cuaderno una vez cada 30 d\u00edas.', slotUse: 'Usar aqu\u00ed mi clase gratuita', slotWait: 'Podr\u00e1s cambiar tu clase gratuita a este cuaderno a partir del {fecha}.', slotMoved: 'Este cuaderno es ahora tu clase gratuita.', proDeptMore: '\u00bfM\u00e1s compa\u00f1eros en tu departamento? Departamento: 39 \u20ac al a\u00f1o, hasta 10 docentes.', proOnly: 'Esta funci\u00f3n es de Pro.', copyLine: 'Este cuaderno es una copia de otro. Cada copia con criterios cargados cuenta como otra clase.', // Licence licenceTitle: 'Licencia', licenceField: 'Clave de licencia', licencePh: 'EVAL-XXXX-XXXX-XXXX-XXXX', licenceActivate: 'Activar', licenceDone: 'Licencia activa hasta el {fecha}.', licenceOffline: 'No hemos podido comprobar tu licencia. Revisa tu conexi\u00f3n y vuelve a intentarlo.', licenceLapsed: 'Tu licencia caduc\u00f3 el {fecha}. Tus cuadernos y tus notas siguen aqu\u00ed; las funciones Pro se desactivan.', licenceExpiredKey: 'Esa clave ya ha caducado. Puedes renovar tu plan en evalua.facilo.es.', licenceWrong: 'Esa clave no es v\u00e1lida. C\u00f3piala de nuevo del correo de compra.', licenceUsed: 'Esa clave ya se usa en otra cuenta. Si es tuya, escr\u00edbenos a info@facilo.es.', // Update updateLine: 'Hay criterios actualizados para {x} ({fecha}). Tus pesos y actividades se mantienen.', updateSee: 'Ver cambios', updateApply: 'Actualizar', updateAdded: 'Nuevos: {list}', updateRemoved: 'Quitados: {list}', updateChanged: 'Con texto nuevo: {list}', updateNone: 'Solo cambian detalles de la fuente.', updateDone: 'Criterios actualizados.', // Other group (Pro) otherTitle: 'Preparar para otro grupo', otherLead: 'Este cuaderno es una copia. Lo dejamos listo para un grupo nuevo: se quedan los criterios, los pesos y las actividades, y se vac\u00edan los alumnos y las notas.', otherGroupField: 'Grupo nuevo', otherGo: 'Preparar cuaderno', otherDone: 'Cuaderno listo para {g}. Pega la lista de alumnos.', otherKeep: 'Mantener como est\u00e1', // Help helpTitle: 'Ayuda', helpMore: 'M\u00e1s ayuda en evalua.facilo.es/soporte', faq: [ ['\u00bfD\u00f3nde est\u00e1n las notas de mis alumnos?', 'En tu hoja de Google. Eval\u00faa no tiene servidores que reciban notas ni nombres.'], ['\u00bfPuedo usarlo con la cuenta de mi centro?', 'S\u00ed, si tu centro permite complementos de Google Workspace. Si tu consejer\u00eda pide usar la cuenta del centro para datos de alumnos, \u00fasala. Tambi\u00e9n puedes poner iniciales o n\u00fameros de lista en lugar de nombres.'], ['\u00bfSon exactamente los criterios de mi comunidad?', 'Los copiamos de la norma publicada en el bolet\u00edn de tu comunidad, que enlazamos en cada cuaderno. Si ves alguna diferencia, escr\u00edbenos y la corregimos.'], ['\u00bfRellena S\u00e9neca, Ra\u00edces o \u00cdtaca por m\u00ed?', 'No. Te deja los datos en el orden en que esas plataformas los piden, para que los pases en minutos y sin errores.'], ['\u00bfQu\u00e9 pasa si dejo de pagar?', 'Tus cuadernos y tus notas siguen siendo tuyos. Vuelves al plan gratuito.'] ] }, // Settings: label, one-line explanation and options [value, text]. settings: [ { k: 'model', label: 'C\u00f3mo pesa cada criterio', ex: 'Cada competencia espec\u00edfica pesa lo mismo, o das un porcentaje a cada criterio.', opts: [['equal', 'Competencias espec\u00edficas por igual'], ['percent', 'Porcentaje por criterio']] }, { k: 'crit', label: 'Nota de cada criterio', ex: 'La media de las actividades que lo miden, o solo la \u00faltima.', opts: [['mean', 'Media de sus actividades'], ['last', 'La \u00faltima nota']] }, { k: 'fin', label: 'Nota final de cada criterio', ex: 'La media de todo el curso, o la de su \u00faltima evaluaci\u00f3n.', opts: [['year', 'Media de todo el curso'], ['last', 'Su \u00faltima evaluaci\u00f3n']] }, { k: 'trunc', label: 'Redondeo', ex: 'Un 4,50 pasa a 5 con redondeo normal; con truncar se queda en 4.', opts: [[false, 'Redondeo normal'], [true, 'Truncar']] }, { k: 'periods', label: 'Evaluaciones', ex: 'Las evaluaciones de tu curso, sin contar la final.', opts: [[3, '3'], [2, '2']] }, { k: 'npZero', label: 'NP cuenta como 0', ex: 'Si no, un NP no cuenta, igual que una celda vac\u00eda.', opts: [[true, 'S\u00ed'], [false, 'No']] }, { k: 'recAlways', label: 'Recuperaciones', ex: 'La nota de una recuperaci\u00f3n sustituye a la anterior solo si es mayor, o siempre.', pro: true, opts: [[false, 'Solo si es mayor'], [true, 'Siempre']] } ], // Tabs tabs: { inicio: 'Inicio', criterios: 'Criterios', alumnos: 'Alumnos', notas: 'Notas', crit: 'Por criterio', comp: 'Por competencia', fin: 'Notas finales', ajustes: 'Ajustes', informe: 'Informe familias', clash: ' (Eval\u00faa)' }, sheet: { notYet: 'Pulsa Calcular notas en el panel y esta pesta\u00f1a mostrar\u00e1 los resultados.', inicioTitle: 'Cuaderno de {subject} \u00b7 {group} \u00b7 curso {year}', inicioSteps: ['1. Pega tus alumnos.', '2. A\u00f1ade actividades y marca los criterios que eval\u00faa cada una.', '3. Pulsa Calcular notas.'], inicioCheck: 'Revisa siempre que los criterios y los pesos coinciden con la programaci\u00f3n de tu departamento.', inicioOpen: 'Abre el panel en Extensiones \u2192 Eval\u00faa LOMLOE \u2192 Abrir.', critTitle: 'Criterios de evaluaci\u00f3n \u00b7 {subject} \u00b7 {level} {stage} \u00b7 {region}', critTitleOwn: 'Criterios de evaluaci\u00f3n \u00b7 {subject} \u00b7 tus criterios', critOwnSource: 'Fuente: tus propios criterios', alumnosHead: ['N.\u00ba', 'Alumno'], notasLabels: ['Actividad', 'Evaluaci\u00f3n', 'Criterios', 'Peso', 'Nota m\u00e1xima', 'Alumno'], actNote: 'Mide {crit}. Peso {w}, sobre {max}.', critBlock: '{p} evaluaci\u00f3n', critFinal: 'Final del curso', extraBlock: 'Extraordinaria', critByTitle: 'Nota por criterio', critCodeNote: 'Media ponderada de las actividades que miden este criterio.', compTitle: 'Aportaci\u00f3n de esta materia al perfil competencial', compSub: 'El perfil de salida lo decide el equipo docente. Aqu\u00ed ves lo que aporta {subject}, con las notas finales de cada competencia espec\u00edfica.', compGroups: ['Competencias espec\u00edficas', 'Competencias clave', 'Descriptores'], compCeNote: 'Media de los criterios de {k}.', compKeyNote: 'Media de los descriptores de {k} a los que contribuye esta materia.', compDescNote: 'Media de las competencias espec\u00edficas de esta materia que contribuyen a {d}.', finTitle: 'Notas finales \u00b7 {group} \u00b7 {subject}', finNota: 'Nota', finTerm: 'T\u00e9rmino', finCover: 'Criterios evaluados', finFinal: 'Final', finNote: 'Media de las competencias espec\u00edficas evaluadas en este periodo, cada una con el mismo peso. Redondeo normal.', finNotePct: 'Media de los criterios evaluados en este periodo, con el porcentaje de cada uno. {round}.', finFinalNote: 'Nota final del curso, con los ajustes de C\u00f3mo se calcula.', finTermNote: 'IN Insuficiente, SU Suficiente, BI Bien, NT Notable, SB Sobresaliente.', finCoverNote: 'Criterios con alguna nota, de todos los del cuaderno.', partial: 'Calculada con {n} de {total} criterios', coverOf: '{n} de {total}', partialNote: 'Calculada con {n} de {total} criterios: menos de la mitad del peso tiene notas.', round: 'Redondeo normal', trunc: 'Truncada', ajTitle: 'Ajustes', ajSub: 'C\u00e1mbialos desde el panel, en C\u00f3mo se calcula.', ajHead: ['Ajuste', 'Valor'], ajData: 'Datos del cuaderno', platTitle: 'Para {name} \u00b7 {p} evaluaci\u00f3n \u00b7 {group} {subject}', platTitleFinal: 'Para {name} \u00b7 Final \u00b7 {group} {subject}', platHint: 'Para el CSV: Archivo \u2192 Descargar \u2192 Valores separados por comas, con esta pesta\u00f1a abierta.', repPick: 'Alumno', repAll: 'Preparar todos', repPickNote: 'Elige un alumno, o Preparar todos para una p\u00e1gina por alumno.', repMeta: '{group} \u00b7 {subject} \u00b7 {level} \u00b7 curso {year}', repGrades: ['Evaluaci\u00f3n', 'Nota', 'T\u00e9rmino'], repCes: ['Competencia espec\u00edfica', 'Nota', 'C\u00f3mo va'], repCesTitle: 'Competencias espec\u00edficas', repImprove: 'Aspectos a mejorar', repNone: 'Ninguno en esta evaluaci\u00f3n.', repMore: 'Y {n} m\u00e1s: {codes}.', repObs: 'Observaciones', repNoMarks: 'A\u00fan sin notas', repPrint: 'Para un PDF con una p\u00e1gina por alumno: panel \u2192 Descargar informes en PDF.', repFile: 'Informes familias {group} {subject}' }, familyWords: { IN: 'A\u00fan no lo consigue', SU: 'Lo consigue en lo b\u00e1sico', BI: 'Lo consigue', NT: 'Lo consigue con soltura', SB: 'Lo consigue con mucha soltura' } }; /** * Evalúa LOMLOE engine: pure logic, no Google calls. * Runs in Apps Script (V8) and in Node tests. Follows spec section 6 "Formulas" step by step. */ var Engine = (function () { var TERMS = [null, 'IN', 'IN', 'IN', 'IN', 'SU', 'BI', 'NT', 'NT', 'SB', 'SB']; var TERM_NAMES = { IN: 'Insuficiente', SU: 'Suficiente', BI: 'Bien', NT: 'Notable', SB: 'Sobresaliente' }; var TERMS_VA = { IN: 'IN', SU: 'SU', BI: 'BE', NT: 'NT', SB: 'EX' }; var KEY_ORDER = ['CCL', 'CP', 'STEM', 'CD', 'CPSAA', 'CC', 'CE', 'CCEC']; var KEY_NAMES = { CCL: 'Competencia en comunicaci\u00f3n ling\u00fc\u00edstica', CP: 'Competencia pluriling\u00fce', STEM: 'Competencia matem\u00e1tica y en ciencia, tecnolog\u00eda e ingenier\u00eda', CD: 'Competencia digital', CPSAA: 'Competencia personal, social y de aprender a aprender', CC: 'Competencia ciudadana', CE: 'Competencia emprendedora', CCEC: 'Competencia en conciencia y expresi\u00f3n culturales' }; var EXTRA = 'E'; // the Extraordinaria period var MAX_ACTIVITIES = 300; var NOTAS_FIRST_STUDENT_ROW = 7; // rows 1-5 activity details, row 6 header var DEFAULTS = { model: 'equal', crit: 'mean', fin: 'year', trunc: false, periods: 3, npZero: true, recAlways: false, pct: {} }; function r2(x) { return Math.round(x * 100) / 100; } function mean(a) { return a.length ? a.reduce(function (x, y) { return x + y; }, 0) / a.length : null; } // Step 7: fixed decimal step first, so 4.4999999 doesn't become 4. function whole(x, scale, trunc) { var v = r2(x); var n = trunc ? Math.floor(v) : Math.floor(v + 0.5); if (scale === 'bach') return Math.max(0, Math.min(10, n)); return Math.max(1, Math.min(10, n)); } function term(n) { return TERMS[Math.max(1, Math.min(10, n))]; } /** Decimal comma, as Spanish platforms and families read it. */ function dec(x, places) { if (x == null) return ''; var p = places == null ? 2 : places; var f = Math.pow(10, p); return (Math.round(x * f) / f).toFixed(p).replace('.', ','); } // A mark cell: number from 0 to max (comma or point), NP, or blank. function readMark(raw, max) { if (typeof raw === 'number') { if (isNaN(raw) || raw < 0 || raw > max) return { kind: 'bad' }; return { kind: 'num', v: raw }; } var s = String(raw == null ? '' : raw).trim(); if (s === '') return { kind: 'blank' }; if (/^np$/i.test(s)) return { kind: 'np' }; if (!/^\d+([.,]\d+)?$/.test(s)) return { kind: 'bad' }; var v = parseFloat(s.replace(',', '.')); if (v < 0 || v > max) return { kind: 'bad' }; return { kind: 'num', v: v }; } function parseCodes(s) { return String(s == null ? '' : s).split(/[,;\s]+/).filter(Boolean); } /** Row 2 of Notas: "1ª", "2ª · Recuperación", "Extraordinaria". */ function parsePeriod(raw) { var s = String(raw == null ? '' : raw).trim(); var rec = /recuperaci[o\u00f3]n/i.test(s); if (/^extra/i.test(s)) return { p: EXTRA, rec: rec }; var m = s.match(/^(\d)/); return m ? { p: +m[1], rec: rec } : null; } function periodLabel(p, rec) { var base = p === EXTRA ? 'Extraordinaria' : p + '\u00aa'; return rec ? base + ' \u00b7 Recuperaci\u00f3n' : base; } /** Column letters for warnings such as Notas!F12. */ function colLetter(n) { // 1-based var s = ''; while (n > 0) { var m = (n - 1) % 26; s = String.fromCharCode(65 + m) + s; n = Math.floor((n - 1) / 26); } return s; } // ---------- Reading the tabs ---------- /** * Notas values (getValues, row 1 first) to activities, students and marks. * Column A holds labels and names; each column from B is one activity. Empty columns are skipped. */ function readNotas(values) { var acts = [], students = [], marks = [], skipped = []; var width = values.length ? values[0].length : 0; for (var c = 1; c < width; c++) { var name = String(values[0][c] == null ? '' : values[0][c]).trim(); var per = parsePeriod(values[1] && values[1][c]); var crit = values[2] ? values[2][c] : ''; if (!name && !per && !String(crit || '').trim()) continue; if (!per) { skipped.push({ col: c + 1, name: name, why: 'period' }); continue; } var w = parseFloat(String(values[3] && values[3][c] != null && values[3][c] !== '' ? values[3][c] : 1).replace(',', '.')); var max = parseFloat(String(values[4] && values[4][c] != null && values[4][c] !== '' ? values[4][c] : 10).replace(',', '.')); acts.push({ name: name || colLetter(c + 1), period: per.p, rec: per.rec, crit: String(crit == null ? '' : crit), w: isNaN(w) || w <= 0 ? 1 : w, max: isNaN(max) || max <= 0 ? 10 : max, col: c + 1 }); } for (var r = NOTAS_FIRST_STUDENT_ROW - 1; r < values.length; r++) { var who = String(values[r][0] == null ? '' : values[r][0]).trim(); if (!who || / \(baja\)$/.test(who)) continue; students.push({ name: who, row: r + 1 }); marks.push(acts.map(function (a) { return values[r][a.col - 1]; })); } return { acts: acts, students: students, marks: marks, skipped: skipped, tooMany: acts.length > MAX_ACTIVITIES }; } /** Criterios values (header row first): Código, Criterio, Competencia específica, Texto de la competencia, Descriptores, Peso (%). */ function readCriterios(values, scale) { var set = { competencias: [], scale: scale || 'eso' }, byCE = {}, pct = {}; for (var r = 1; r < values.length; r++) { var row = values[r]; var code = String(row[0] == null ? '' : row[0]).trim(); if (!code) continue; var ce = String(row[2] == null ? '' : row[2]).trim() || 'CE'; if (!byCE[ce]) { byCE[ce] = { code: ce, text: String(row[3] || ''), descriptors: [], criteria: [] }; set.competencias.push(byCE[ce]); } parseCodes(row[4]).forEach(function (d) { if (byCE[ce].descriptors.indexOf(d) < 0) byCE[ce].descriptors.push(d); }); byCE[ce].criteria.push({ code: code, text: String(row[1] || '') }); var p = parseFloat(String(row[5] == null ? '' : row[5]).replace('%', '').replace(',', '.')); if (!isNaN(p)) pct[code] = p; } return { set: set, pct: pct }; } // ---------- The calculation ---------- /* * set: {competencias:[{code, descriptors, criteria:[{code}]}], scale: 'eso'|'bach'|'primaria'} * acts: [{name, period (1..3 or 'E'), crit:"1.1, 2.3", w, max, rec, col}] * marks[s][a]: raw cell values. students: names or {name,row}. o: see DEFAULTS. */ function compute(set, students, acts, marks, o) { o = Object.assign({}, DEFAULTS, o || {}); var critCE = {}, order = [], ceList = []; set.competencias.forEach(function (k) { ceList.push(k.code); k.criteria.forEach(function (c) { critCE[c.code] = k.code; order.push(c.code); }); }); var pctOf = function (c) { return o.pct[c] != null ? +o.pct[c] : 0; }; var warnings = [], unknown = {}; var A = acts.map(function (a, i) { var codes = parseCodes(a.crit), ok = []; codes.forEach(function (c) { if (critCE[c]) { if (ok.indexOf(c) < 0) ok.push(c); } else if (!unknown[a.name + '|' + c]) { unknown[a.name + '|' + c] = 1; warnings.push({ type: 'unknown', act: a.name, code: c, col: a.col || i + 2 }); } }); return { i: i, name: a.name, p: a.period, crit: ok, w: +a.w || 1, max: +a.max || 10, rec: !!a.rec, col: a.col || i + 2 }; }); var periods = []; for (var p = 1; p <= o.periods; p++) periods.push(p); var hasExtra = A.some(function (a) { return a.p === EXTRA; }); var activePeriods = periods.filter(function (p) { return A.some(function (a) { return a.p === p; }); }); if (o.model === 'percent') { var total = order.reduce(function (s, c) { return s + pctOf(c); }, 0); if (Math.abs(total - 100) > 0.01) warnings.push({ type: 'pct', total: r2(total) }); } // Criteria with no activity in a period that has activities. activePeriods.slice().reverse().forEach(function (p) { var missing = order.filter(function (c) { return !A.some(function (a) { return a.p === p && !a.rec && a.crit.indexOf(c) >= 0; }); }); if (missing.length) warnings.unshift({ type: 'missing', period: p, codes: missing }); }); function critMark(list) { // list of {w,m,col} if (!list.length) return null; if (o.crit === 'last') return list.reduce(function (x, y) { return y.col > x.col ? y : x; }).m; var sw = 0, s = 0; list.forEach(function (e) { sw += e.w; s += e.w * e.m; }); return s / sw; } function withRec(v, rec) { // step 3 if (!rec.length) return v; var rm = critMark(rec); return o.recAlways || v == null ? rm : Math.max(v, rm); } function weighted(codes, S) { var sw = 0, s = 0; codes.forEach(function (c) { if (S[c] != null) { var w = pctOf(c); sw += w; s += w * S[c]; } }); return sw ? s / sw : null; } function ceFrom(S) { var CE = {}; set.competencias.forEach(function (k) { var codes = k.criteria.map(function (c) { return c.code; }); CE[k.code] = o.model === 'percent' ? weighted(codes, S) : mean(codes.filter(function (c) { return S[c] != null; }).map(function (c) { return S[c]; })); }); return CE; } function gradeFrom(S, CE) { if (o.model === 'percent') return weighted(order, S); return mean(ceList.map(function (k) { return CE[k]; }).filter(function (v) { return v != null; })); } function coverage(S) { // step 6: share of the criteria weight assessed var done = order.filter(function (c) { return S[c] != null; }); var share = o.model === 'percent' ? (function () { var t = order.reduce(function (s, c) { return s + pctOf(c); }, 0); return t ? done.reduce(function (s, c) { return s + pctOf(c); }, 0) / t : 0; })() : (order.length ? done.length / order.length : 0); return { assessed: done.length, total: order.length, partial: share < 0.5 }; } function pack(raw) { if (raw == null) return null; var n = whole(raw, set.scale, o.trunc); return { raw: r2(raw), n: n, term: term(n) }; } var res = students.map(function (st, s) { var name = typeof st === 'string' ? st : st.name; var entries = {}; // code -> [{p,w,m,col,rec}] A.forEach(function (a) { var raw = (marks[s] || [])[a.i]; var mk = readMark(raw, a.max); if (mk.kind === 'bad') { warnings.push({ type: 'bad', s: s, row: st.row || s + 7, col: a.col, raw: raw }); return; } if (mk.kind === 'blank') return; if (mk.kind === 'np' && !o.npZero) return; var m = mk.kind === 'np' ? 0 : mk.v * 10 / a.max; // step 1 a.crit.forEach(function (c) { (entries[c] = entries[c] || []).push({ p: a.p, w: a.w, m: m, col: a.col, rec: a.rec }); }); }); var per = {}; periods.concat(hasExtra ? [EXTRA] : []).forEach(function (p) { var S = {}; order.forEach(function (c) { var e = (entries[c] || []).filter(function (x) { return x.p === p; }); S[c] = withRec(critMark(e.filter(function (x) { return !x.rec; })), e.filter(function (x) { return x.rec; })); }); var CE = ceFrom(S); per[p] = { S: S, CE: CE, grade: pack(gradeFrom(S, CE)), cover: coverage(S) }; }); // Step 4: final criterion mark over the ordinary periods. var SF = {}; order.forEach(function (c) { if (o.fin === 'last') { var last = null; periods.forEach(function (p) { if (per[p].S[c] != null) last = per[p].S[c]; }); SF[c] = last; } else { var e = (entries[c] || []).filter(function (x) { return x.p !== EXTRA; }); SF[c] = withRec(critMark(e.filter(function (x) { return !x.rec; })), e.filter(function (x) { return x.rec; })); } }); var CEF = ceFrom(SF); var out = { name: name, row: st.row, per: per, S: SF, CE: CEF, final: pack(gradeFrom(SF, CEF)), cover: coverage(SF) }; // Extraordinaria: works like a recuperación of the final mark, per criterion. if (hasExtra) { var SE = {}; order.forEach(function (c) { var x = per[EXTRA].S[c]; SE[c] = x == null ? SF[c] : (SF[c] == null || o.recAlways ? x : Math.max(SF[c], x)); }); var CEE = ceFrom(SE); out.extra = { S: SE, CE: CEE, grade: pack(gradeFrom(SE, CEE)) }; } // Step 8: descriptors and key competencies, from the final CE marks. var D = {}, K = {}; set.competencias.forEach(function (k) { (k.descriptors || []).forEach(function (d) { if (CEF[k.code] != null) (D[d] = D[d] || []).push(CEF[k.code]); }); }); Object.keys(D).forEach(function (d) { D[d] = mean(D[d]); }); var byKey = {}; Object.keys(D).forEach(function (d) { var key = d.replace(/\d+$/, ''); (byKey[key] = byKey[key] || []).push(D[d]); }); KEY_ORDER.forEach(function (key) { if (byKey[key]) { var v = Math.round(mean(byKey[key]) * 10) / 10; K[key] = { v: v, term: term(whole(v, 'eso')) }; } }); out.D = D; out.K = K; return out; }); return { students: res, warnings: warnings, order: order, critCE: critCE, ceList: ceList, periods: periods, activePeriods: activePeriods, hasExtra: hasExtra, scale: set.scale, options: o }; } /** Main-view numbers: activities and criteria with at least one mark. */ function stats(set, acts, marks) { var marked = {}; acts.forEach(function (a, i) { var any = marks.some(function (row) { var k = readMark(row[i], +a.max || 10).kind; return k === 'num' || k === 'np'; }); if (any) parseCodes(a.crit).forEach(function (c) { marked[c] = 1; }); }); var codes = []; set.competencias.forEach(function (k) { k.criteria.forEach(function (c) { codes.push(c.code); }); }); return { activities: acts.length, criteria: codes.length, marked: codes.filter(function (c) { return marked[c]; }).length }; } // ---------- Own criteria and students ---------- /** "código; texto; competencia específica; descriptores", one per line (F2). */ function parseOwnCriteria(text) { var rows = [], errors = [], seen = {}, heads = {}; String(text || '').split(/\r?\n/).forEach(function (line, i) { if (!line.trim()) return; var sep = line.indexOf('\t') >= 0 ? '\t' : line.indexOf(';') >= 0 ? ';' : null; // "1.1 Plantea el problema": a code, a space, then the text. var f = sep ? line.split(sep) : (function (m) { return m ? [m[1], m[2]] : [line]; })(/^\s*(\S+)\s+(.*)$/.exec(line)); f = f.map(function (x) { return x.trim(); }); var n = i + 1; // "CE1 Resolver problemas" names the competencia específica of the criteria under it. var ceHead = /^CE\s*(\d+)$/i.exec(f[0]); if (ceHead && f.length <= 2) { heads['CE' + ceHead[1]] = f[1] || ''; return; } if (!f[0]) return errors.push({ line: n, code: 'NO_CODE' }); if (seen[f[0]]) return errors.push({ line: n, code: 'DUP', value: f[0] }); if (!f[1]) return errors.push({ line: n, code: 'NO_TEXT', value: f[0] }); seen[f[0]] = 1; var ce = f[2] || ('CE' + f[0].split('.')[0]); rows.push({ code: f[0], text: f[1], ce: ce, descriptors: parseCodes(f[3]) }); }); rows.forEach(function (r) { if (heads[r.ce] != null && !r.ceText) r.ceText = heads[r.ce]; }); return { rows: rows, errors: errors }; } /** Criteria rows (own or loaded) into a set. */ function setFromRows(rows, scale) { var set = { competencias: [], scale: scale || 'eso' }, by = {}; rows.forEach(function (r) { if (!by[r.ce]) { by[r.ce] = { code: r.ce, text: r.ceText || '', descriptors: [], criteria: [] }; set.competencias.push(by[r.ce]); } r.descriptors.forEach(function (d) { if (by[r.ce].descriptors.indexOf(d) < 0) by[r.ce].descriptors.push(d); }); by[r.ce].criteria.push({ code: r.code, text: r.text }); }); return set; } /** * Pasted student list (F3): one per line, or tab-separated columns as copied from * Séneca, Raíces or Ítaca, where the first two text columns are used. */ function parseStudents(text) { var out = []; String(text || '').split(/\r?\n/).forEach(function (line) { if (!line.trim()) return; var name; if (line.indexOf('\t') >= 0) { var cols = line.split('\t').map(function (x) { return x.trim(); }) .filter(function (x) { return x && !/^[\d\s.,/-]+$/.test(x) && !/@/.test(x); }); if (!cols.length) return; name = cols.length > 1 && cols[0].indexOf(',') < 0 ? cols[0] + ', ' + cols[1] : cols[0]; } else name = line.trim().replace(/^\d+[.)-]?\s+(?=\D)/, ''); name = name.replace(/\s+/g, ' '); // A header row is only header words ("Apellidos, Nombre", "Alumno/a"); a name that starts with one is kept. if (/^((n\.?\u00ba|n\u00ba|n\.?o|alumnos?|alumnas?|alumno\/a|apellidos?|nombre|nombres)[\s,;:.]*)+$/i.test(name)) return; if (out.indexOf(name) < 0) out.push(name); }); return out; } /** * New student list against the rows already in Notas (F3). Existing names keep their marks, * new ones are added, and names no longer in the list go to the bottom with "(baja)". * old: [{name, marks}], names: new list. Returns rows [{name, marks}]. */ function mergeStudents(old, names, width) { var byName = {}, rows = [], gone = []; old.forEach(function (r) { byName[r.name.replace(/ \(baja\)$/, '')] = r; }); names.forEach(function (n) { var r = byName[n]; rows.push({ name: n, marks: r ? r.marks : blank(width) }); if (r) r.used = true; }); old.forEach(function (r) { if (r.used) return; var base = r.name.replace(/ \(baja\)$/, ''); if (r.marks.some(function (v) { return String(v == null ? '' : v).trim() !== ''; })) gone.push({ name: base + ' (baja)', marks: r.marks }); }); return rows.concat(gone); } function blank(n) { var a = []; for (var i = 0; i < n; i++) a.push(''); return a; } // ---------- Sorting ---------- /** Spanish order ignoring accents: ñ after n, like the platforms list students. */ function sortKey(s) { return String(s || '').toLowerCase().replace(/\u00f1/g, 'n{') .normalize('NFD').replace(/[\u0300-\u036f]/g, ''); } function byName(a, b) { var x = sortKey(a), y = sortKey(b); return x < y ? -1 : x > y ? 1 : 0; } // ---------- Platform layouts (Pro) ---------- var PLATFORMS = { 'Andaluc\u00eda': { tab: 'Para S\u00e9neca', name: 'S\u00e9neca', kind: 'criteria' }, 'Comunidad de Madrid': { tab: 'Para Ra\u00edces', name: 'Ra\u00edces', kind: 'raices' }, 'Madrid': { tab: 'Para Ra\u00edces', name: 'Ra\u00edces', kind: 'raices' }, 'Comunitat Valenciana': { tab: 'Para \u00cdtaca', name: '\u00cdtaca', kind: 'itaca' }, 'C. Valenciana': { tab: 'Para \u00cdtaca', name: '\u00cdtaca', kind: 'itaca' } // the region name the data sets use }; function platformFor(region) { return PLATFORMS[region] || { tab: 'Para tu plataforma', name: 'tu plataforma', kind: 'criteria' }; } /** Rows (header first) for one period, in the region's layout (spec section 6). p may be 'final'. */ function layout(result, region, p) { var pf = platformFor(region); var pick = function (st) { return p === 'final' ? { S: st.S, grade: st.final } : st.per[p] || { S: {}, grade: null }; }; var list = result.students.slice().sort(function (a, b) { return byName(a.name, b.name); }); // Primaria grades are terms alone (spec 5): the whole-number columns are left out var pri = result.scale === 'primaria'; if (pf.kind === 'criteria') { var codes = result.order.filter(function (c) { return list.some(function (st) { return pick(st).S[c] != null; }); }); var head = ['Alumno/a'].concat(codes, pri ? ['T\u00e9rmino'] : ['Nota del periodo', 'T\u00e9rmino']); return [head].concat(list.map(function (st) { var x = pick(st); return [st.name].concat(codes.map(function (c) { return dec(x.S[c]); }), pri ? [] : [x.grade ? x.grade.n : ''], [x.grade ? x.grade.term : '']); })); } if (pf.kind === 'raices') { if (pri) return [['Alumno/a', 'T\u00e9rmino']].concat(list.map(function (st) { var g = pick(st).grade; return [st.name, g ? g.term : '']; })); return [['Alumno/a', 'Calificaci\u00f3n', 'T\u00e9rmino', 'Calificaci\u00f3n con t\u00e9rmino']].concat(list.map(function (st) { var g = pick(st).grade; return [st.name, g ? g.n : '', g ? g.term : '', g ? g.term + ' ' + g.n : '']; })); } if (pri) return [['Alumno/a', 'T\u00e9rmino', 'Terme']].concat(list.map(function (st) { var g = pick(st).grade; return [st.name, g ? g.term : '', g ? TERMS_VA[g.term] : '']; })); return [['Alumno/a', 'Calificaci\u00f3n', 'T\u00e9rmino', 'Terme']].concat(list.map(function (st) { var g = pick(st).grade; return [st.name, g ? g.n : '', g ? g.term : '', g ? TERMS_VA[g.term] : '']; })); } // ---------- Family report (Pro) ---------- /** What one student's A4 page shows (F9). */ function report(result, set, s, meta) { var st = result.students[s]; var texts = {}, ceText = {}; set.competencias.forEach(function (k) { ceText[k.code] = k.text || ''; k.criteria.forEach(function (c) { texts[c.code] = c.text || ''; }); }); var periods = result.periods.filter(function (p) { return st.per[p] && st.per[p].grade; }).map(function (p) { var g = st.per[p].grade; return { label: periodLabel(p) + ' evaluaci\u00f3n', n: g.n, term: g.term, name: TERM_NAMES[g.term] }; }); if (st.final && result.activePeriods.length > 1) periods.push({ label: 'Final', n: st.final.n, term: st.final.term, name: TERM_NAMES[st.final.term] }); var ces = result.ceList.filter(function (k) { return st.CE[k] != null; }).map(function (k) { var t = term(whole(st.CE[k], 'eso')); return { code: k, text: shortText(ceText[k], 160), v: dec(st.CE[k], 1), term: t, name: TERM_NAMES[t] }; }); var improve = result.order.filter(function (c) { return st.S[c] != null && st.S[c] < 5; }) .map(function (c) { return { code: c, text: shortText(texts[c], 140) }; }); return { name: st.name, group: meta.group || '', subject: meta.subject || '', level: meta.level || '', year: meta.year || '', primaria: set.scale === 'primaria', periods: periods, ces: ces, improve: improve }; } function shortText(t, n) { t = String(t || '').replace(/\s+/g, ' ').trim(); if (t.length <= n) return t; var cut = t.slice(0, n), sp = cut.lastIndexOf(' '); return (sp > n * 0.6 ? cut.slice(0, sp) : cut).replace(/[,;:.]$/, '') + '\u2026'; } // ---------- Criteria updates (F12) ---------- /** Old and new sets matched by criterion code. */ function diffSets(oldSet, newSet) { var a = {}, b = {}; oldSet.competencias.forEach(function (k) { k.criteria.forEach(function (c) { a[c.code] = c.text; }); }); newSet.competencias.forEach(function (k) { k.criteria.forEach(function (c) { b[c.code] = c.text; }); }); return { added: Object.keys(b).filter(function (c) { return !(c in a); }), removed: Object.keys(a).filter(function (c) { return !(c in b); }), changed: Object.keys(b).filter(function (c) { return c in a && a[c] !== b[c]; }) }; } var CRITERIOS_HEAD = ['C\u00f3digo', 'Criterio de evaluaci\u00f3n', 'Competencia espec\u00edfica', 'Texto de la competencia espec\u00edfica', 'Descriptores', 'Peso (%)']; /** Rows for the Criterios tab (header first). pct keeps weights already typed, by code. */ function criteriosRows(set, pct) { var rows = [CRITERIOS_HEAD.slice()]; set.competencias.forEach(function (k) { k.criteria.forEach(function (c) { rows.push([c.code, c.text || '', k.code, k.text || '', (k.descriptors || []).join(', '), pct && pct[c.code] != null ? pct[c.code] : '']); }); }); return rows; } /** A criteria update: new texts, same weights by code; activity links live in Notas and are untouched. */ function updateCriterios(oldValues, newSet) { var old = readCriterios(oldValues); return { rows: criteriosRows(newSet, old.pct), diff: diffSets(old.set, newSet) }; } // ---------- Free slot (F1) ---------- /** * Can this spreadsheet use loaded criteria? slot: {ssId, label, moved (YYYY-MM-DD)} or null. * Returns {ok, canMove, nextMove}. */ function slotState(slot, ssId, pro, today) { if (pro || !slot || !slot.ssId || slot.ssId === ssId) return { ok: true, canMove: false }; var next = slot.moved ? addDays(slot.moved, 30) : today; return { ok: false, canMove: next <= today, nextMove: next, label: slot.label || '' }; } function addDays(iso, n) { var d = new Date(Date.UTC(+iso.slice(0, 4), +iso.slice(5, 7) - 1, +iso.slice(8, 10)) + n * 86400000); return d.toISOString().slice(0, 10); } return { compute: compute, stats: stats, whole: whole, term: term, readMark: readMark, parseCodes: parseCodes, parsePeriod: parsePeriod, periodLabel: periodLabel, colLetter: colLetter, readNotas: readNotas, readCriterios: readCriterios, parseOwnCriteria: parseOwnCriteria, setFromRows: setFromRows, parseStudents: parseStudents, mergeStudents: mergeStudents, sortKey: sortKey, byName: byName, platformFor: platformFor, layout: layout, report: report, diffSets: diffSets, slotState: slotState, criteriosRows: criteriosRows, updateCriterios: updateCriterios, CRITERIOS_HEAD: CRITERIOS_HEAD, addDays: addDays, dec: dec, r2: r2, mean: mean, TERM_NAMES: TERM_NAMES, TERMS_VA: TERMS_VA, KEY_ORDER: KEY_ORDER, KEY_NAMES: KEY_NAMES, EXTRA: EXTRA, MAX_ACTIVITIES: MAX_ACTIVITIES, NOTAS_FIRST_STUDENT_ROW: NOTAS_FIRST_STUDENT_ROW, DEFAULTS: DEFAULTS }; })(); if (typeof module !== 'undefined' && module.exports) module.exports = Engine; /** * Evalúa LOMLOE: tab layouts, formats and header notes (Apps Script only). * Each tab is written with one setValues() and formats are set per range, never per cell. * Colours follow the approved design: navy text on a pale navy band for headers, and a calm amber * (never red) for marks under 5. */ var LOOK = { ink: '#1f2a5a', band: '#eef0f7', band2: '#f7f8fc', line: '#d5d9e8', muted: '#5f6368', lowFont: '#8a5a00', lowFill: '#fff4dc', proFill: '#fff8e6', font: 'Arial' }; /** A tab by name, created (or cleared) for writing. clash: add " (Evalúa)" if a sheet the teacher made has that name. */ function tabFor_(ss, name, opts) { opts = opts || {}; var sh = ss.getSheetByName(name); if (sh && opts.fresh && !isOurs_(sh)) { name = name + Strings.tabs.clash; sh = ss.getSheetByName(name); } if (!sh) { sh = ss.insertSheet(name, opts.index != null ? Math.min(opts.index, ss.getNumSheets()) : ss.getNumSheets()); markOurs_(sh); } else if (opts.clear) { sh.clear(); sh.clearConditionalFormatRules(); sh.getDataRange().clearDataValidations(); var f = sh.getFrozenRows(); if (f) sh.setFrozenRows(0); if (sh.getFrozenColumns()) sh.setFrozenColumns(0); } return sh; } /** Our tabs carry a developer tag, so a teacher's own "Notas" is never overwritten. */ function markOurs_(sh) { sh.addDeveloperMetadata('evalua', 'tab'); } function isOurs_(sh) { return sh.getDeveloperMetadata().some(function (m) { return m.getKey() === 'evalua'; }); } /** Finds our tab even when it was created with the " (Evalúa)" suffix. */ function ourTab_(ss, name) { var a = ss.getSheetByName(name); if (a && isOurs_(a)) return a; var b = ss.getSheetByName(name + Strings.tabs.clash); if (b && isOurs_(b)) return b; return null; } /** A1 address for getRangeList(). */ function a1_(row, col, nr, nc) { var a = Engine.colLetter(col) + row; return nr ? a + ':' + Engine.colLetter(col + (nc || 1) - 1) + (row + nr - 1) : a; } function fit_(sh, rows, cols) { if (sh.getMaxRows() < rows) sh.insertRowsAfter(sh.getMaxRows(), rows - sh.getMaxRows()); if (sh.getMaxColumns() < cols) sh.insertColumnsAfter(sh.getMaxColumns(), cols - sh.getMaxColumns()); } /** Pads rows to the same width for setValues(). */ function square_(rows) { var w = rows.reduce(function (m, r) { return Math.max(m, r.length); }, 1); return rows.map(function (r) { var c = r.slice(); while (c.length < w) c.push(''); return c; }); } /** Styles the title row. Without text it keeps the title already written by setValues(). */ function titleRow_(sh, row, text) { var cell = sh.getRange(row, 1); if (text != null) cell.setValue(text); cell.setFontSize(14).setFontWeight('bold').setFontColor(LOOK.ink); sh.setRowHeight(row, 30); } function headerBand_(range) { range.setBackground(LOOK.band).setFontColor(LOOK.ink).setFontWeight('bold').setVerticalAlignment('middle') .setBorder(null, null, true, null, null, null, LOOK.line, SpreadsheetApp.BorderStyle.SOLID); } /** Calm amber for marks under 5 (no red for fail, per the spec). formula is relative to the range's top-left cell. * It must hold no argument separator: a rule's formula is read in the sheet's locale, and Spanish sheets use ';'. */ function lowRule_(range, formula) { return SpreadsheetApp.newConditionalFormatRule().whenFormulaSatisfied(formula) .setFontColor(LOOK.lowFont).setBackground(LOOK.lowFill).setRanges([range]).build(); } function fmt_(s, a) { return String(s).replace(/\{([^}]+)\}/g, function (m, k) { return a && a[k] != null ? a[k] : m; }); } // ---------- Inicio ---------- function writeInicio_(ss, meta) { var T = Strings.sheet; var sh = tabFor_(ss, Strings.tabs.inicio, { fresh: true, clear: true, index: 0 }); var rows = [ [fmt_(T.inicioTitle, { subject: meta.subject, group: meta.group || '\u2014', year: meta.year })], [''], [T.inicioSteps[0]], [T.inicioSteps[1]], [T.inicioSteps[2]], [''], [meta.sourceLabel ? fmt_(Strings.ui.source, { src: meta.sourceLabel }) : T.critOwnSource], [T.inicioCheck], [''], [T.inicioOpen] ]; fit_(sh, rows.length, 2); sh.getRange(1, 1, rows.length, 1).setValues(rows).setFontFamily(LOOK.font).setFontColor('#202124').setWrap(true); sh.setColumnWidth(1, 640); sh.getRange(1, 1).setFontSize(16).setFontWeight('bold').setFontColor(LOOK.ink); sh.setRowHeight(1, 36); sh.getRange(3, 1, 3, 1).setFontSize(12).setBackground(LOOK.band2); sh.getRange(3, 1, 3, 1).setBorder(true, true, true, true, null, null, LOOK.line, SpreadsheetApp.BorderStyle.SOLID); if (meta.sourceUrl) { var link = SpreadsheetApp.newRichTextValue().setText(rows[6][0]) .setLinkUrl(rows[6][0].indexOf(meta.sourceLabel), rows[6][0].length, meta.sourceUrl).build(); sh.getRange(7, 1).setRichTextValue(link); } sh.getRange(8, 1).setFontColor(LOOK.lowFont); sh.getRange(10, 1).setFontColor(LOOK.muted).setFontStyle('italic'); sh.setHiddenGridlines(true); return sh; } // ---------- Criterios ---------- function writeCriterios_(ss, meta, rows) { var T = Strings.sheet; var sh = tabFor_(ss, Strings.tabs.criterios, { fresh: true, clear: true }); var title = meta.own ? fmt_(T.critTitleOwn, meta) : fmt_(T.critTitle, meta); var src = meta.sourceLabel ? fmt_(Strings.ui.source, { src: meta.sourceLabel }) : T.critOwnSource; var all = [[title], [src]].concat(rows); all = square_(all); fit_(sh, all.length + 5, all[0].length); sh.getRange(1, 1, all.length, all[0].length).setValues(all).setFontFamily(LOOK.font).setVerticalAlignment('top'); titleRow_(sh, 1, title); sh.getRange(2, 1).setFontColor(LOOK.muted); if (meta.sourceUrl) { sh.getRange(2, 1).setRichTextValue(SpreadsheetApp.newRichTextValue().setText(src) .setLinkUrl(src.indexOf(meta.sourceLabel), src.length, meta.sourceUrl).build()); } headerBand_(sh.getRange(3, 1, 1, all[0].length)); sh.setFrozenRows(3); sh.setColumnWidth(1, 70); sh.setColumnWidth(2, 460); sh.setColumnWidth(3, 90); sh.setColumnWidth(4, 360); sh.setColumnWidth(5, 170); sh.setColumnWidth(6, 80); if (all.length > 3) { sh.getRange(4, 1, all.length - 3, all[0].length).setWrap(true); sh.getRange(4, 1, all.length - 3, 1).setNumberFormat('@').setFontWeight('bold').setFontColor(LOOK.ink); sh.getRange(4, 3, all.length - 3, 1).setFontColor(LOOK.ink); sh.getRange(4, 4, all.length - 3, 1).setFontColor(LOOK.muted).setFontSize(9); } sh.getRange(3, 6).setNote('Solo con el modelo de porcentaje por criterio (C\u00f3mo se calcula). Pon el porcentaje de cada criterio; deber\u00edan sumar 100.'); return sh; } /** Criterios values without the two title rows, header first, as Engine.readCriterios wants. */ function criteriosValues_(ss) { var sh = ourTab_(ss, Strings.tabs.criterios); if (!sh) return []; var v = sh.getDataRange().getValues(); var start = 0; for (var i = 0; i < Math.min(v.length, 6); i++) if (String(v[i][0]).trim() === 'C\u00f3digo') { start = i; break; } return v.slice(start); } // ---------- Alumnos and Notas ---------- function writeAlumnos_(ss, names) { var sh = tabFor_(ss, Strings.tabs.alumnos, { fresh: true, clear: true }); var rows = [Strings.sheet.alumnosHead].concat(names.map(function (n, i) { return [i + 1, n]; })); fit_(sh, rows.length + 20, 2); sh.getRange(1, 1, rows.length, 2).setValues(rows).setFontFamily(LOOK.font); headerBand_(sh.getRange(1, 1, 1, 2)); sh.setFrozenRows(1); sh.setColumnWidth(1, 50); sh.setColumnWidth(2, 300); if (!names.length) sh.getRange(2, 2).setValue(Strings.ui.pasteHint).setFontColor(LOOK.muted).setFontStyle('italic'); return sh; } /** A fresh Notas tab: the five header rows and the student names, no activities yet. */ function writeNotasEmpty_(ss, names) { var sh = tabFor_(ss, Strings.tabs.notas, { fresh: true, clear: true }); var L = Strings.sheet.notasLabels; var rows = L.map(function (l) { return [l, '']; }).concat(names.map(function (n) { return [n, '']; })); fit_(sh, rows.length + 40, 12); sh.getRange(1, 1, rows.length, 2).setValues(rows); formatNotas_(sh); return sh; } /** Shading, frozen rows, widths and the calm amber rule. Safe to run again after any change. */ function formatNotas_(sh) { var lastCol = Math.max(2, sh.getLastColumn()), maxRows = sh.getMaxRows(); sh.getRange(1, 1, maxRows, lastCol).setFontFamily(LOOK.font); sh.getRange(1, 1, 5, lastCol).setBackground(LOOK.band).setFontColor(LOOK.ink).setWrap(true).setVerticalAlignment('middle'); sh.getRange(1, 2, 1, lastCol - 1).setFontWeight('bold'); sh.getRange(1, 1, 6, 1).setFontWeight('bold').setFontColor(LOOK.ink); sh.getRange(6, 1, 1, lastCol).setBackground(LOOK.band2).setFontWeight('bold').setFontColor(LOOK.ink) .setBorder(null, null, true, null, null, null, LOOK.line, SpreadsheetApp.BorderStyle.SOLID); sh.getRange(3, 2, 1, lastCol - 1).setNumberFormat('@'); sh.getRange(1, 2, 6, lastCol - 1).setHorizontalAlignment('center'); sh.getRange(7, 2, Math.max(1, maxRows - 6), lastCol - 1).setHorizontalAlignment('center'); sh.setFrozenRows(6); sh.setFrozenColumns(1); sh.setColumnWidth(1, 240); if (lastCol > 1) sh.setColumnWidths(2, lastCol - 1, 110); sh.setRowHeight(1, 40); var body = sh.getRange(7, 2, Math.max(1, maxRows - 6), Math.max(1, lastCol - 1)); sh.setConditionalFormatRules([lowRule_(body, '=ISNUMBER(B7)*ISNUMBER(B$5)*(B$5>0)*(B7*10/B$5<5)>0')]); } /** Periods typed as "1" or "2 recuperacion" read back as "1ª", "2ª · Recuperación", like the ones the sidebar adds. */ function normalisePeriods_(sh) { var lastCol = sh.getLastColumn(); if (lastCol < 2) return 0; var row = sh.getRange(2, 2, 1, lastCol - 1).getValues()[0], n = 0; var out = row.map(function (v) { var per = Engine.parsePeriod(v); if (!per) return v; var label = Engine.periodLabel(per.p, per.rec); if (String(v).trim() !== label) n++; return label; }); if (n) sh.getRange(2, 2, 1, lastCol - 1).setNumberFormat('@').setValues([out]); return n; } /** Calcular reformats Notas only when a column the teacher added lacks our look: about 2 s saved on a full year. */ function formatNotasIfNew_(sh) { var lastCol = Math.max(2, sh.getLastColumn()); if (sh.getFrozenRows() === 6 && sh.getRange(1, lastCol).getBackground() === LOOK.band) return false; formatNotas_(sh); return true; } function notasNote_(act) { return fmt_(Strings.sheet.actNote, { crit: act.crit || '\u2014', w: String(act.w).replace('.', ','), max: String(act.max).replace('.', ',') }); } // ---------- Results ---------- /** Por criterio: a block of criterion columns per period, then the final. */ function writePorCriterio_(ss, R, meta) { var T = Strings.sheet; var sh = tabFor_(ss, Strings.tabs.crit, { fresh: true, clear: true }); var blocks = R.activePeriods.map(function (p) { return { label: fmt_(T.critBlock, { p: Engine.periodLabel(p) }), get: function (st) { return st.per[p] ? st.per[p].S : {}; } }; }); if (R.hasExtra) blocks.push({ label: T.extraBlock, get: function (st) { return st.per[Engine.EXTRA] ? st.per[Engine.EXTRA].S : {}; } }); blocks.push({ label: T.critFinal, get: function (st) { return st.S; } }); var n = R.order.length; var band = ['Alumno'], codes = ['']; blocks.forEach(function (b) { band.push(b.label); for (var i = 1; i < n; i++) band.push(''); codes = codes.concat(R.order); }); var body = R.students.map(function (st) { var row = [st.name]; blocks.forEach(function (b) { var S = b.get(st); R.order.forEach(function (c) { row.push(S[c] == null ? '' : Engine.r2(S[c])); }); }); return row; }); var rows = [[fmt_(T.critByTitle, {}) + ' \u00b7 ' + (meta.group || '') + ' \u00b7 ' + meta.subject], band, codes].concat(body); rows = square_(rows); var w = rows[0].length; fit_(sh, rows.length + 2, w); sh.getRange(1, 1, rows.length, w).setValues(rows).setFontFamily(LOOK.font); titleRow_(sh, 1); headerBand_(sh.getRange(2, 1, 2, w)); sh.getRange(2, 1, 2, 1).merge(); blocks.forEach(function (b, i) { if (n > 1) sh.getRange(2, 2 + i * n, 1, n).merge(); }); sh.getRangeList(blocks.map(function (b, i) { return a1_(2, 2 + i * n, rows.length - 1, 1); })) .setBorder(null, true, null, null, null, null, LOOK.line, SpreadsheetApp.BorderStyle.SOLID); // Block labels sit left in their wide merge so they show without scrolling; the codes under them stay centred. sh.getRange(2, 2, 1, w - 1).setHorizontalAlignment('left'); sh.getRange(3, 2, 1, w - 1).setHorizontalAlignment('center'); sh.getRange(3, 2, 1, w - 1).setNumberFormat('@'); sh.getRange(3, 2, 1, w - 1).setNotes([codes.slice(1).map(function () { return T.critCodeNote; })]); if (body.length) { var vals = sh.getRange(4, 2, body.length, w - 1); vals.setNumberFormat('0.00').setHorizontalAlignment('center'); sh.setConditionalFormatRules([lowRule_(vals, '=ISNUMBER(B4)*(B4<5)>0')]); } sh.setFrozenRows(3); sh.setFrozenColumns(1); sh.setColumnWidth(1, 240); sh.setColumnWidths(2, w - 1, 56); return sh; } /** Por competencia: CE scores, key competencies (value and term) and descriptors, from the final marks. */ function writePorCompetencia_(ss, R, set, meta) { var T = Strings.sheet; var sh = tabFor_(ss, Strings.tabs.comp, { fresh: true, clear: true }); var keys = Engine.KEY_ORDER.filter(function (k) { return R.students.some(function (st) { return st.K[k]; }); }); var descs = []; // a bare key code (Valencia's sets name key competences only) already has its own column above set.competencias.forEach(function (k) { (k.descriptors || []).forEach(function (d) { if (/\d$/.test(d) && descs.indexOf(d) < 0) descs.push(d); }); }); descs.sort(function (a, b) { var ka = Engine.KEY_ORDER.indexOf(a.replace(/\d+$/, '')), kb = Engine.KEY_ORDER.indexOf(b.replace(/\d+$/, '')); return ka - kb || (+a.replace(/\D/g, '')) - (+b.replace(/\D/g, '')); }); var keyLabel = function (k) { return k === 'CE' ? 'CE (emprendedora)' : k; }; var groups = ['Alumno'], head = [''], notes = ['']; R.ceList.forEach(function (k, i) { groups.push(i ? '' : T.compGroups[0]); head.push(k); notes.push(fmt_(T.compCeNote, { k: k })); }); keys.forEach(function (k, i) { groups.push(i ? '' : T.compGroups[1]); groups.push(''); head.push(keyLabel(k)); head.push('T\u00e9rmino'); notes.push(fmt_(T.compKeyNote, { k: Engine.KEY_NAMES[k] })); notes.push(T.finTermNote); }); descs.forEach(function (d, i) { groups.push(i ? '' : T.compGroups[2]); head.push(d); notes.push(fmt_(T.compDescNote, { d: d })); }); var body = R.students.map(function (st) { var row = [st.name]; R.ceList.forEach(function (k) { row.push(st.CE[k] == null ? '' : Engine.r2(st.CE[k])); }); keys.forEach(function (k) { var x = st.K[k]; row.push(x ? x.v : ''); row.push(x ? x.term : ''); }); descs.forEach(function (d) { row.push(st.D[d] == null ? '' : Engine.r2(st.D[d])); }); return row; }); var rows = square_([[T.compTitle], [fmt_(T.compSub, meta)], groups, head].concat(body)); var w = rows[0].length; fit_(sh, rows.length + 2, w); sh.getRange(1, 1, rows.length, w).setValues(rows).setFontFamily(LOOK.font); titleRow_(sh, 1); sh.getRange(2, 1).setFontColor(LOOK.muted).setFontStyle('italic'); headerBand_(sh.getRange(3, 1, 2, w)); sh.getRange(3, 1, 2, 1).merge(); var c = 2; [R.ceList.length, keys.length * 2, descs.length].forEach(function (n) { if (n > 1) sh.getRange(3, c, 1, n).merge(); if (n) sh.getRange(3, c, rows.length - 2, 1).setBorder(null, true, null, null, null, null, LOOK.line, SpreadsheetApp.BorderStyle.SOLID); c += n; }); sh.getRange(3, 2, 1, w - 1).setHorizontalAlignment('left'); sh.getRange(4, 2, 1, w - 1).setHorizontalAlignment('center'); sh.getRange(4, 2, 1, w - 1).setNotes([notes.slice(1)]); if (body.length) { var n = body.length; sh.getRange(5, 2, n, w - 1).setHorizontalAlignment('center'); if (R.ceList.length) sh.getRange(5, 2, n, R.ceList.length).setNumberFormat('0.00'); var rules = []; if (R.ceList.length) rules.push(lowRule_(sh.getRange(5, 2, n, R.ceList.length), '=ISNUMBER(B5)*(B5<5)>0')); keys.forEach(function (k, i) { var col = 2 + R.ceList.length + i * 2; sh.getRange(5, col, n, 1).setNumberFormat('0.0'); sh.getRange(5, col + 1, n, 1).setFontColor(LOOK.muted); }); if (descs.length) sh.getRange(5, 2 + R.ceList.length + keys.length * 2, n, descs.length).setNumberFormat('0.00'); sh.setConditionalFormatRules(rules); } sh.setFrozenRows(4); sh.setFrozenColumns(1); sh.setColumnWidth(1, 240); sh.setColumnWidths(2, w - 1, 64); return sh; } /** Notas finales: grade and term per period, the final, and coverage. Partial periods get a note. */ function writeNotasFinales_(ss, R, meta) { var T = Strings.sheet; var sh = tabFor_(ss, Strings.tabs.fin, { fresh: true, clear: true }); var cols = R.activePeriods.map(function (p) { return { label: Engine.periodLabel(p) + ' evaluaci\u00f3n', get: function (st) { return st.per[p] && st.per[p].grade; }, cover: function (st) { return st.per[p] && st.per[p].cover; } }; }); if (R.activePeriods.length > 1 || !cols.length) cols.push({ label: T.finFinal, get: function (st) { return st.final; }, cover: function (st) { return st.cover; }, fin: true }); if (R.hasExtra) cols.push({ label: T.extraBlock, get: function (st) { return st.extra && st.extra.grade; }, cover: function () { return null; } }); // Primaria shows the term alone (spec 5), so each period takes one column instead of two var pri = R.scale === 'primaria', step = pri ? 1 : 2; var band = ['Alumno'], head = ['']; cols.forEach(function (c) { if (pri) { band.push(c.label); head.push(T.finTerm); } else { band.push(c.label, ''); head.push(T.finNota, T.finTerm); } }); band.push(T.finCover); head.push(''); var notes = [], body = R.students.map(function (st, i) { var row = [st.name], nrow = ['']; cols.forEach(function (c) { var g = c.get(st), cv = c.cover(st); var note = cv && cv.partial && g ? fmt_(T.partialNote, { n: cv.assessed, total: cv.total }) : ''; if (pri) { row.push(g ? g.term : ''); nrow.push(note); } else { row.push(g ? g.n : '', g ? g.term : ''); nrow.push(note, ''); } }); row.push(fmt_(T.coverOf, { n: st.cover.assessed, total: st.cover.total })); nrow.push(''); notes.push(nrow); return row; }); var title = fmt_(T.finTitle, { group: meta.group || '\u2014', subject: meta.subject }); var rows = square_([[title], band, head].concat(body)); var w = rows[0].length; fit_(sh, rows.length + 2, w); sh.getRange(1, 1, rows.length, w).setValues(rows).setFontFamily(LOOK.font); titleRow_(sh, 1); headerBand_(sh.getRange(2, 1, 2, w)); sh.getRange(2, 1, 2, 1).merge(); sh.getRange(2, w, 2, 1).merge(); var gradeNote = R.options.model === 'percent' ? fmt_(T.finNotePct, { round: R.options.trunc ? T.trunc : T.round }) : T.finNote; var headNotes = []; cols.forEach(function (c, i) { var col = 2 + i * step; if (pri) { headNotes.push(T.finTermNote); return; } sh.getRange(2, col, 1, 2).merge(); headNotes.push(c.fin ? T.finFinalNote : gradeNote, T.finTermNote); }); sh.getRangeList(cols.map(function (c, i) { return a1_(2, 2 + i * step, rows.length - 1, 1); })) .setBorder(null, true, null, null, null, null, LOOK.line, SpreadsheetApp.BorderStyle.SOLID); while (headNotes.length < w - 2) headNotes.push(''); headNotes.push(T.finCoverNote); sh.getRange(3, 2, 1, w - 1).setNotes([headNotes]); sh.getRange(2, 2, rows.length - 1, w - 1).setHorizontalAlignment('center'); if (body.length) { sh.getRange(4, 1, body.length, w).setNotes(notes.map(function (r) { return r.concat(['']).slice(0, w); })); var rules = []; cols.forEach(function (c, i) { var col = 2 + i * step, L = Engine.colLetter(col); sh.getRange(4, col, body.length, 1).setFontWeight('bold').setFontSize(11); rules.push(lowRule_(sh.getRange(4, col, body.length, step), pri ? '=$' + L + '4="IN"' : '=ISNUMBER($' + L + '4)*($' + L + '4<5)>0')); }); sh.getRange(4, w, body.length, 1).setFontColor(LOOK.muted); sh.setConditionalFormatRules(rules); } sh.setFrozenRows(3); sh.setFrozenColumns(1); sh.setColumnWidth(1, 240); sh.setColumnWidths(2, w - 2, 74); sh.setColumnWidth(w, 120); return sh; } /** Tabs that wait for Calcular notas. */ function writeNotYet_(ss, name, title) { var sh = tabFor_(ss, name, { fresh: true, clear: true }); sh.getRange(1, 1, 2, 1).setValues([[title], [Strings.sheet.notYet]]).setFontFamily(LOOK.font); titleRow_(sh, 1); sh.getRange(2, 1).setFontColor(LOOK.muted).setFontStyle('italic'); sh.setColumnWidth(1, 520); return sh; } // ---------- Ajustes ---------- /** Settings first (label, value in words, one line of explanation), then the notebook's data. */ function writeAjustes_(ss, o, data) { var T = Strings.sheet; var sh = ourTab_(ss, Strings.tabs.ajustes) || tabFor_(ss, Strings.tabs.ajustes, { fresh: true }); sh.clear(); var rows = [[T.ajTitle, '', ''], [T.ajSub, '', ''], [T.ajHead[0], T.ajHead[1], '']]; Strings.settings.forEach(function (s) { var opt = s.opts.filter(function (x) { return String(x[0]) === String(o[s.k]); })[0] || s.opts[0]; rows.push([s.label, opt[1], s.ex]); }); rows.push(['', '', ''], [T.ajData, '', '']); DATA_KEYS.forEach(function (k) { rows.push([k, data[k] == null ? '' : data[k], '']); }); fit_(sh, rows.length + 2, 3); sh.getRange(1, 1, rows.length, 3).setValues(rows).setFontFamily(LOOK.font).setVerticalAlignment('top'); sh.getRange(1, 2, rows.length, 1).setNumberFormat('@'); titleRow_(sh, 1); sh.getRange(2, 1).setFontColor(LOOK.muted).setFontStyle('italic'); headerBand_(sh.getRange(3, 1, 1, 3)); var dataRow = 5 + Strings.settings.length; headerBand_(sh.getRange(dataRow, 1, 1, 3)); sh.getRange(4, 3, Strings.settings.length, 1).setFontColor(LOOK.muted).setWrap(true); sh.getRange(dataRow + 1, 1, DATA_KEYS.length, 2).setFontColor(LOOK.muted); sh.setColumnWidth(1, 220); sh.setColumnWidth(2, 260); sh.setColumnWidth(3, 420); return sh; } // ---------- Pro: platform layout ---------- function writePlatform_(ss, R, meta) { var T = Strings.sheet; var pf = Engine.platformFor(meta.own ? '' : meta.region); var sh = tabFor_(ss, pf.tab, { fresh: true, clear: true }); var periods = R.activePeriods.slice(); if (R.activePeriods.length > 1) periods.push('final'); var all = [], heads = []; periods.forEach(function (p) { var rows = Engine.layout(R, meta.own ? '' : meta.region, p); var title = p === 'final' ? fmt_(T.platTitleFinal, { name: pf.name, group: meta.group || '', subject: meta.subject }) : fmt_(T.platTitle, { name: pf.name, p: Engine.periodLabel(p), group: meta.group || '', subject: meta.subject }); all.push([title]); heads.push(all.length + 1); rows.forEach(function (r) { all.push(r); }); all.push(['']); }); all.unshift([T.platHint], ['']); heads = heads.map(function (h) { return h + 2; }); all = square_(all); var w = all[0].length; fit_(sh, all.length + 2, w); var rng = sh.getRange(1, 1, all.length, w); rng.setNumberFormat('@').setValues(all).setFontFamily(LOOK.font); sh.getRange(1, 1).setFontColor(LOOK.muted).setFontStyle('italic'); heads.forEach(function (h) { sh.getRange(h - 1, 1).setFontWeight('bold').setFontSize(12).setFontColor(LOOK.ink); headerBand_(sh.getRange(h, 1, 1, w)); }); sh.getRange(3, 2, all.length - 2, w - 1).setHorizontalAlignment('center'); sh.setColumnWidth(1, 240); // Each column wide enough for its longest header, e.g. "Nota del periodo"; equal neighbours share one call. var px = []; for (var c = 1; c < w; c++) { var longest = heads.reduce(function (m, h) { return Math.max(m, String(all[h - 1][c] || '').length); }, 0); px.push(Math.max(70, longest * 8 + 16)); } for (var i = 0, j; i < px.length; i = j) { for (j = i + 1; j < px.length && px[j] === px[i]; j++); sh.setColumnWidths(i + 2, j - i, px[i]); } return sh; } // ---------- Pro: family report ---------- var REPORT_PAGE_ROWS = 46; // rows per student page; each page is set to the same height so print breaks between students var REPORT_ROW_PX = 21; /** Rows for one student's page, plus where to put the formats. */ function reportBlock_(rep) { var T = Strings.sheet, F = Strings.familyWords; var rows = [], marks = { title: [], head: [], low: [], obs: null }; var add = function (r) { rows.push(r.concat(['', '', '']).slice(0, 4)); return rows.length; }; marks.name = add([rep.name]); add([fmt_(T.repMeta, rep)]); add(['']); marks.title.push(add([T.repGrades[0], T.repGrades[1], T.repGrades[2]])); marks.head.push(rows.length); if (!rep.periods.length) add([T.repNoMarks]); rep.periods.forEach(function (p) { var r = add([p.label, rep.primaria ? '' : p.n, p.name + ' (' + p.term + ')']); if (p.n < 5) marks.low.push(r); }); add(['']); marks.title.push(add([T.repCesTitle])); marks.head.push(add([T.repCes[0], T.repCes[1], T.repCes[2]])); if (!rep.ces.length) add([T.repNoMarks]); rep.ces.forEach(function (c) { add([c.code + '. ' + c.text, c.v, F[c.term] || c.name]); }); add(['']); marks.title.push(add([T.repImprove])); if (!rep.improve.length) add([T.repNone]); rep.improve.forEach(function (c) { add([c.code + ' \u00b7 ' + c.text]); }); add(['']); marks.title.push(add([T.repObs])); marks.obs = rows.length + 1; for (var i = 0; i < 5; i++) add(['']); while (rows.length < REPORT_PAGE_ROWS) add(['']); // A long report (many criteria) keeps every row and runs past one printed page; the PDF download still fits one page. if (rows.length > REPORT_PAGE_ROWS) marks.over = true; return { rows: rows, marks: marks }; } var REPORT_PDF_IMPROVE = 8; // criteria listed in full under Aspectos a mejorar; the rest by code only /** The same reports as one HTML document, one A4 page each, for the PDF download. reps: from Engine.report. */ function reportsHtml_(reps) { var T = Strings.sheet, F = Strings.familyWords; var e = function (v) { return String(v == null ? '' : v).replace(/&/g, '&').replace(//g, '>'); }; var row = function (cells, low) { return '
| ' + e(x) + ' | '; }).join('') + '
|---|
' + e(t) + '
'; }; // Shorter texts than the tab, so a full report fits one A4 page. var cut = function (t, n) { t = String(t || ''); if (t.length <= n) return t; t = t.slice(0, n); return t.slice(0, t.lastIndexOf(' ') > n * 0.6 ? t.lastIndexOf(' ') : n).replace(/[,;:.\u2026]$/, '') + '\u2026'; }; var list = function (items) { var shown = items.slice(0, REPORT_PDF_IMPROVE), rest = items.slice(REPORT_PDF_IMPROVE); return 'Gu\u00eda, preguntas y contacto: ' + 'evalua.facilo.es/soporte
') .setWidth(320).setHeight(90); SpreadsheetApp.getUi().showModalDialog(t, Strings.ui.helpTitle); } function include(name) { return HtmlService.createHtmlOutput(HTML_FILES_[name]).getContent(); } /** The student picker on Informe familias redraws that page. Simple trigger: current sheet only. */ function onEdit(e) { try { var sh = e.range.getSheet(); if (sh.getName().indexOf(Strings.tabs.informe) !== 0 || e.range.getRow() !== 1 || e.range.getColumn() !== 2) return; if (!isOurs_(sh)) return; writeAllReports(String(e.value || '')); } catch (err) { console.log('onEdit ' + (err && err.name)); } } // ---------- Ajustes ---------- /** {o: settings for the engine, data: notebook data by internal key}, or null if there's no Ajustes tab. */ function readAjustes_(ss) { var sh = ourTab_(ss, Strings.tabs.ajustes); if (!sh) return null; var v = sh.getDataRange().getValues(), byLabel = {}; v.forEach(function (r) { byLabel[String(r[0]).trim()] = r[1]; }); var o = {}; Strings.settings.forEach(function (s) { var text = String(byLabel[s.label] == null ? '' : byLabel[s.label]).trim(); var opt = s.opts.filter(function (x) { return x[1] === text; })[0] || s.opts[0]; o[s.k] = opt[0]; }); var data = {}; Object.keys(DATA).forEach(function (k) { data[k] = byLabel[DATA[k]] == null ? '' : String(byLabel[DATA[k]]); }); data.own = data.own === 'S\u00ed'; return { o: o, data: data }; } function writeAjustesFrom_(ss, o, data) { var byLabel = {}; Object.keys(DATA).forEach(function (k) { byLabel[DATA[k]] = k === 'own' ? (data.own ? 'S\u00ed' : 'No') : data[k]; }); writeAjustes_(ss, o, byLabel); } function today_() { return Utilities.formatDate(new Date(), 'Europe/Madrid', 'yyyy-MM-dd'); } function nowLocal_() { return Utilities.formatDate(new Date(), 'Europe/Madrid', 'yyyy-MM-dd HH:mm:ss'); } /** "2026-27" from September on, else "2025-26". */ function schoolYear_() { var d = today_(), y = +d.slice(0, 4), m = +d.slice(5, 7); var a = m >= 9 ? y : y - 1; return a + '-' + String(a + 1).slice(2); } // ---------- State for the sidebar ---------- function getState() { var ss = SpreadsheetApp.getActive(); var aj = readAjustes_(ss); var lic = licence_(); var st = { lic: lic, dataBase: DATA_BASE, dev: devOn_(), year: schoolYear_(), today: today_(), settings: null }; if (!aj || !ourTab_(ss, Strings.tabs.criterios)) { st.stage = 'new'; return st; } var d = aj.data; st.stage = 'main'; st.data = d; st.settings = aj.o; st.isCopy = !!d.ssId && d.ssId !== ss.getId(); st.slot = d.own ? { ok: true } : Engine.slotState(slot_(), ss.getId(), lic.pro, today_()); st.platform = Engine.platformFor(d.own ? '' : d.region); var crit = Engine.readCriterios(criteriosValues_(ss), d.scale); st.groups = crit.set.competencias.map(function (k) { return { code: k.code, criteria: k.criteria.map(function (c) { return { code: c.code, text: c.text }; }) }; }); var notas = notasValues_(ss); var N = Engine.readNotas(notas); st.stats = Engine.stats(crit.set, N.acts, N.marks); st.students = N.students.length; st.hasReport = !!ourTab_(ss, Strings.tabs.informe); st.calculated = !!ourTab_(ss, Strings.tabs.fin) && ourTab_(ss, Strings.tabs.fin).getLastRow() > 3; return st; } function notasValues_(ss) { var sh = ourTab_(ss, Strings.tabs.notas); return sh ? sh.getDataRange().getValues() : []; } // ---------- New notebook ---------- /** Keeps only the fields we use, as plain strings, so nothing odd from the network reaches the sheet. */ function cleanSet_(set) { var s = function (x, n) { return String(x == null ? '' : x).slice(0, n || 2000); }; if (!set || !Array.isArray(set.competencias) || !set.competencias.length) throw new Error('SET'); return { id: s(set.id, 80), region: s(set.region, 80), stage: s(set.stage, 40), level: s(set.level, 40), subject: s(set.subject, 120), scale: ['eso', 'bach', 'primaria'].indexOf(set.scale) >= 0 ? set.scale : 'eso', version: s(set.version, 20), source: { label: s(set.source && (set.source.label || [set.source.title, set.source.bulletin].filter(Boolean).join(' \u00b7 ')), 300), url: /^https:\/\//.test(set.source && set.source.url) ? s(set.source.url, 500) : '' }, competencias: set.competencias.slice(0, 40).map(function (k) { return { code: s(k.code, 20), text: s(k.text), descriptors: (k.descriptors || []).slice(0, 30).map(function (d) { return s(d, 12); }), criteria: (k.criteria || []).slice(0, 60).map(function (c) { return { code: s(c.code, 20), text: s(c.text) }; }) }; }) }; } /** Builds every tab from a loaded data set. opts: {group}. A free teacher's first one takes the free slot. */ function createNotebook(set, opts) { set = cleanSet_(set); opts = opts || {}; var ss = SpreadsheetApp.getActive(); var lic = licence_(); var label = set.subject + ' \u00b7 ' + (opts.group || set.level); var slot = Engine.slotState(slot_(), ss.getId(), lic.pro, today_()); if (!slot.ok) return { ok: false, code: 'SLOT', slot: slot }; var mine = slot_(); if (!lic.pro && (!mine || !mine.label)) saveSlot_({ ssId: ss.getId(), label: label, moved: mine ? mine.moved : '' }); var data = { setId: set.id, version: set.version, region: set.region, stage: set.stage, level: set.level, subject: set.subject, group: String(opts.group || '').slice(0, 40), year: schoolYear_(), source: set.source.label, sourceUrl: set.source.url, scale: set.scale, own: false, ssId: ss.getId() }; build_(ss, data, Engine.criteriosRows(set)); return { ok: true }; } /** Own criteria, typed or pasted (F2). Always free. */ function createOwn(text, opts) { var p = Engine.parseOwnCriteria(text); if (p.errors.length) return { ok: false, code: 'PARSE', errors: p.errors.slice(0, 8) }; if (!p.rows.length) return { ok: false, code: 'EMPTY' }; opts = opts || {}; var ss = SpreadsheetApp.getActive(); var set = Engine.setFromRows(p.rows, 'eso'); var data = { setId: '', version: '', region: '', stage: '', level: '', subject: String(opts.subject || 'Mi materia').slice(0, 120), group: String(opts.group || '').slice(0, 40), year: schoolYear_(), source: '', sourceUrl: '', scale: 'eso', own: true, ssId: ss.getId() }; build_(ss, data, Engine.criteriosRows(set)); return { ok: true }; } function build_(ss, data, critRows) { var meta = metaFrom_(data); // teachers type "4,5": in an English-locale sheet that stays text, so marks need a Spanish locale if (!/^es/.test(ss.getSpreadsheetLocale() || '')) ss.setSpreadsheetLocale('es_ES'); writeInicio_(ss, meta); writeCriterios_(ss, meta, critRows); writeAlumnos_(ss, []); writeNotasEmpty_(ss, []); writeNotYet_(ss, Strings.tabs.crit, Strings.sheet.critByTitle); writeNotYet_(ss, Strings.tabs.comp, Strings.sheet.compTitle); writeNotYet_(ss, Strings.tabs.fin, fmt_(Strings.sheet.finTitle, meta)); writeAjustesFrom_(ss, Engine.DEFAULTS, data); // a brand-new spreadsheet's empty "Sheet1" goes; any sheet the teacher actually used stays var theirs = ss.getSheets().filter(function (s) { return !isOurs_(s); }); if (theirs.length === 1 && theirs[0].getLastRow() === 0 && theirs[0].getLastColumn() === 0) { try { ss.deleteSheet(theirs[0]); } catch (e) { /* the teacher's own empty sheet stays if it can't go */ } } ss.setActiveSheet(ourTab_(ss, Strings.tabs.notas)); } function metaFrom_(d) { return { subject: d.subject, group: d.group, year: d.year, level: d.level, stage: d.stage, region: d.region, sourceLabel: d.source, sourceUrl: d.sourceUrl, own: !!d.own }; } // ---------- Students and activities ---------- /** Pasted list (F3): new names added, existing keep their marks, missing ones go to the bottom as "(baja)". */ function saveStudents(text) { var names = Engine.parseStudents(text); if (!names.length) return { ok: false, code: 'EMPTY' }; var ss = SpreadsheetApp.getActive(); var sh = ourTab_(ss, Strings.tabs.notas); var v = sh.getDataRange().getValues(); var width = Math.max(1, v[0].length - 1); var old = v.slice(Engine.NOTAS_FIRST_STUDENT_ROW - 1).filter(function (r) { return String(r[0]).trim(); }) .map(function (r) { return { name: String(r[0]).trim(), marks: r.slice(1) }; }); var rows = Engine.mergeStudents(old, names, width); var gone = rows.filter(function (r) { return / \(baja\)$/.test(r.name); }).length; var first = Engine.NOTAS_FIRST_STUDENT_ROW; var clearRows = Math.max(old.length, rows.length, 1) + 5; fit_(sh, first + clearRows, width + 1); sh.getRange(first, 1, clearRows, width + 1).clearContent(); if (rows.length) sh.getRange(first, 1, rows.length, width + 1).setValues(rows.map(function (r) { return [r.name].concat(r.marks); })); writeAlumnos_(ss, names); formatNotas_(sh); return { ok: true, n: names.length, gone: gone }; } /** One new column in Notas (F4). a: {name, period: 1|2|3|'E', rec, codes:[], w, max}. */ function addActivity(a) { var ss = SpreadsheetApp.getActive(); var lic = licence_(); if (a.rec && !lic.pro) return { ok: false, code: 'PRO' }; var codes = (a.codes || []).map(String); if (!codes.length) return { ok: false, code: 'NO_CRIT' }; var sh = ourTab_(ss, Strings.tabs.notas); var v = sh.getDataRange().getValues(); var N = Engine.readNotas(v); if (N.acts.length >= Engine.MAX_ACTIVITIES) return { ok: false, code: 'TOO_MANY' }; var col = 2; for (var c = v[0].length; c >= 2; c--) { if ([0, 1, 2].some(function (r) { return String(v[r] && v[r][c - 1] || '').trim(); })) { col = c + 1; break; } } var period = a.period === 'E' ? Engine.EXTRA : +a.period || 1; var name = String(a.name || '').trim().slice(0, 80) || 'Actividad ' + (N.acts.length + 1); var w = +String(a.w || 1).replace(',', '.') || 1, max = +String(a.max || 10).replace(',', '.') || 10; fit_(sh, sh.getMaxRows(), col); var act = { crit: codes.join(', '), w: w, max: max }; sh.getRange(1, col, 5, 1).setValues([[name], [Engine.periodLabel(period, !!a.rec)], [act.crit], [w], [max]]); sh.getRange(1, col).setNote(notasNote_(act)); formatNotas_(sh); sh.getRange(Engine.NOTAS_FIRST_STUDENT_ROW, col).activate(); return { ok: true, name: name, col: Engine.colLetter(col) }; } // ---------- Calculate ---------- /** Reads every source tab once and runs the engine. */ function run_(ss) { var aj = readAjustes_(ss); var crit = Engine.readCriterios(criteriosValues_(ss), aj.data.scale); var N = Engine.readNotas(notasValues_(ss)); var o = Object.assign({}, aj.o, { pct: crit.pct }); var acts = N.tooMany ? N.acts.slice(0, Engine.MAX_ACTIVITIES) : N.acts; var marks = N.tooMany ? N.marks.map(function (m) { return m.slice(0, Engine.MAX_ACTIVITIES); }) : N.marks; var R = Engine.compute(crit.set, N.students, acts, marks, o); return { R: R, set: crit.set, data: aj.data, meta: metaFrom_(aj.data), o: aj.o, N: N, acts: acts }; } function calculate() { var ss = SpreadsheetApp.getActive(); var aj = readAjustes_(ss); if (!aj) return { ok: false, code: 'NEW' }; var lic = licence_(); if (!aj.data.own) { var slot = Engine.slotState(slot_(), ss.getId(), lic.pro, today_()); if (!slot.ok) return { ok: false, code: 'SLOT', slot: slot }; } var was = ss.getActiveSheet(); // new result tabs open on insert; leave the teacher where they were var x = run_(ss); // Columns the teacher typed or pasted into Notas get the same look as ours. var notas = ourTab_(ss, Strings.tabs.notas); if (notas) { formatNotasIfNew_(notas); normalisePeriods_(notas); } writePorCriterio_(ss, x.R, x.meta); writePorCompetencia_(ss, x.R, x.set, x.meta); writeNotasFinales_(ss, x.R, x.meta); if (lic.pro) { writePlatform_(ss, x.R, x.meta); if (ourTab_(ss, Strings.tabs.informe)) writeAllReports_(ss, x, ''); } if (was) was.activate(); var warnings = warningsFor_(x); return { ok: true, n: x.R.students.length, warnings: warnings, pro: lic.pro, platform: Engine.platformFor(x.data.own ? '' : x.data.region) }; } /** Engine warnings as sidebar lines, each with the cell to jump to. */ function warningsFor_(x) { var U = Strings.ui, out = []; if (x.N.tooMany) out.push({ text: U.wTooMany, sheet: Strings.tabs.notas, row: 1, col: Engine.MAX_ACTIVITIES + 2 }); x.R.warnings.forEach(function (w) { if (w.type === 'missing') { out.push({ text: fmt_(U.wMissing, { n: w.codes.length, p: Engine.periodLabel(w.period), codes: w.codes.slice(0, 8).join(', ') + (w.codes.length > 8 ? '\u2026' : '') }), sheet: Strings.tabs.criterios, row: 4, col: 1 }); } else if (w.type === 'bad') { out.push({ text: fmt_(U.wBad, { ref: Engine.colLetter(w.col) + w.row, raw: String(w.raw).slice(0, 20) }), sheet: Strings.tabs.notas, row: w.row, col: w.col }); } else if (w.type === 'unknown') { out.push({ text: fmt_(U.wUnknown, { a: w.act, c: w.code }), sheet: Strings.tabs.notas, row: 3, col: w.col }); } else if (w.type === 'pct') { out.push({ text: fmt_(U.wPct, { total: String(w.total).replace('.', ',') }), sheet: Strings.tabs.criterios, row: 3, col: 6 }); } }); return out.slice(0, 30); } function goTo(sheet, row, col) { var ss = SpreadsheetApp.getActive(); var sh = ourTab_(ss, sheet); if (!sh) return false; ss.setActiveSheet(sh); sh.getRange(Math.max(1, row), Math.max(1, col)).activate(); return true; } function openTab(name) { var ss = SpreadsheetApp.getActive(); var sh = ourTab_(ss, name); if (sh) ss.setActiveSheet(sh); return !!sh; } /** Settings from the sidebar, written to Ajustes in words, then a fresh calculation. */ function saveSettings(o) { var ss = SpreadsheetApp.getActive(); var aj = readAjustes_(ss); var lic = licence_(); var next = {}; Strings.settings.forEach(function (s) { var ok = s.opts.some(function (x) { return String(x[0]) === String(o[s.k]); }); var val = ok ? s.opts.filter(function (x) { return String(x[0]) === String(o[s.k]); })[0][0] : aj.o[s.k]; next[s.k] = s.pro && !lic.pro ? s.opts[0][0] : val; }); writeAjustesFrom_(ss, next, aj.data); return calculate(); } // ---------- Pro ---------- function writeAllReports(picked) { var ss = SpreadsheetApp.getActive(); if (!licence_().pro) return { ok: false, code: 'PRO' }; var x = run_(ss); var over = writeAllReports_(ss, x, picked || ''); ss.setActiveSheet(ourTab_(ss, Strings.tabs.informe)); return { ok: true, over: over }; } /** Every family report as one PDF, one page per student. HTML page breaks are exact, unlike printing the tab. */ function familyPdf() { var ss = SpreadsheetApp.getActive(); if (!licence_().pro) return { ok: false, code: 'PRO' }; var x = run_(ss); var reps = reportOrder_(x).map(function (i) { return Engine.report(x.R, x.set, i, x.meta); }); var name = (fmt_(Strings.sheet.repFile, x.meta).replace(/[\\/:*?"<>|]+/g, ' ').replace(/\s+/g, ' ').trim()) + '.pdf'; var pdf = Utilities.newBlob(reportsHtml_(reps), 'text/html', 'informes.html').getAs('application/pdf'); return { ok: true, n: reps.length, name: name, b64: Utilities.base64Encode(pdf.getBytes()) }; } function reportOrder_(x) { return x.R.students.map(function (s, i) { return i; }) .sort(function (a, b) { return Engine.byName(x.R.students[a].name, x.R.students[b].name); }); } function writeAllReports_(ss, x, picked) { var sh = ourTab_(ss, Strings.tabs.informe) || tabFor_(ss, Strings.tabs.informe, { fresh: true }); var order = reportOrder_(x); var names = order.map(function (i) { return x.R.students[i].name; }); var pick = names.indexOf(picked) >= 0 ? [names.indexOf(picked)] : null; var reps = (pick ? pick.map(function (j) { return order[j]; }) : order).map(function (i) { return Engine.report(x.R, x.set, i, x.meta); }); return writeReports_(sh, reps, [Strings.sheet.repAll].concat(names), pick ? picked : Strings.sheet.repAll); } /** A copied notebook for a new group (F11, Pro): criteria, weights and activities stay; students and marks go. */ function prepareOtherGroup(group) { var ss = SpreadsheetApp.getActive(); if (!licence_().pro) return { ok: false, code: 'PRO' }; var aj = readAjustes_(ss); var sh = ourTab_(ss, Strings.tabs.notas); var first = Engine.NOTAS_FIRST_STUDENT_ROW; if (sh.getMaxRows() >= first) sh.getRange(first, 1, sh.getMaxRows() - first + 1, sh.getMaxColumns()).clearContent(); writeAlumnos_(ss, []); aj.data.group = String(group || '').slice(0, 40); aj.data.ssId = ss.getId(); aj.data.year = schoolYear_(); writeAjustesFrom_(ss, aj.o, aj.data); var meta = metaFrom_(aj.data); writeInicio_(ss, meta); writeNotYet_(ss, Strings.tabs.crit, Strings.sheet.critByTitle); writeNotYet_(ss, Strings.tabs.comp, Strings.sheet.compTitle); writeNotYet_(ss, Strings.tabs.fin, fmt_(Strings.sheet.finTitle, meta)); [Engine.platformFor(aj.data.own ? '' : aj.data.region).tab, Strings.tabs.informe].forEach(function (n) { var t = ourTab_(ss, n); if (t) ss.deleteSheet(t); }); ss.setActiveSheet(ourTab_(ss, Strings.tabs.alumnos)); return { ok: true, group: aj.data.group }; } /** The copy stays as it is: it becomes its own notebook (and, for a free teacher, needs the slot). */ function keepCopy() { var ss = SpreadsheetApp.getActive(); var aj = readAjustes_(ss); aj.data.ssId = ss.getId(); writeAjustesFrom_(ss, aj.o, aj.data); return { ok: true }; } // ---------- Free slot (F1) ---------- function slot_() { var raw = PropertiesService.getUserProperties().getProperty(UP.slot); try { return raw ? JSON.parse(raw) : null; } catch (e) { return null; } } function saveSlot_(s) { PropertiesService.getUserProperties().setProperty(UP.slot, JSON.stringify(s)); } /** Moves the free class to this notebook, at most once every 30 days. */ function useFreeSlot() { var ss = SpreadsheetApp.getActive(); var aj = readAjustes_(ss); var st = Engine.slotState(slot_(), ss.getId(), licence_().pro, today_()); if (st.ok) return { ok: true }; if (!st.canMove) return { ok: false, code: 'WAIT', slot: st }; // On a spreadsheet with no notebook yet, createNotebook fills in the label. saveSlot_({ ssId: ss.getId(), label: aj ? aj.data.subject + ' \u00b7 ' + (aj.data.group || aj.data.level) : '', moved: today_() }); if (aj && aj.data.ssId !== ss.getId()) { aj.data.ssId = ss.getId(); writeAjustesFrom_(ss, aj.o, aj.data); } return { ok: true, moved: true }; } // ---------- Criteria updates (F12) ---------- function diffPreview(set) { set = cleanSet_(set); return Engine.diffSets(Engine.readCriterios(criteriosValues_(SpreadsheetApp.getActive())).set, set); } function applyUpdate(set) { set = cleanSet_(set); var ss = SpreadsheetApp.getActive(); var aj = readAjustes_(ss); if (aj.data.own || set.id !== aj.data.setId) return { ok: false, code: 'SET' }; var u = Engine.updateCriterios(criteriosValues_(ss), set); aj.data.version = set.version; aj.data.source = set.source.label; aj.data.sourceUrl = set.source.url; writeCriterios_(ss, metaFrom_(aj.data), u.rows); writeAjustesFrom_(ss, aj.o, aj.data); return { ok: true, diff: u.diff }; } // ---------- Licence ---------- /** * The sidebar asks Facilo's licence API itself and checks the signature. The server keeps the result in user properties * and decides the plan from it. uid is random and never contains names. */ function licence_() { var p = PropertiesService.getUserProperties().getProperties(); var today = today_(); var plan = p[UP.plan] || '', expires = p[UP.expires] || '', last = p[UP.lastCheck] || ''; var active = !!plan && !!expires && expires >= today; var dev = devOn_() && DEV_.pro; return { plan: dev ? 'pro' : active ? plan : 'free', pro: dev || active, expires: dev ? '2027-08-31' : expires, lapsed: !dev && !!plan && !!expires && expires < today, hasKey: !!p[UP.key], // Lapsed keys are checked too, so a renewal paid in Stripe brings Pro back without typing the key again. needsCheck: !dev && !!plan && !!p[UP.key] && (!last || Engine.addDays(last.slice(0, 10), 1) <= today), graceLeft: last ? Math.max(0, GRACE_DAYS - daysBetween_(last.slice(0, 10), today)) : 0, live: LICENCE.sale }; } function daysBetween_(a, b) { return Math.round((Date.UTC(+b.slice(0, 4), +b.slice(5, 7) - 1, +b.slice(8, 10)) - Date.UTC(+a.slice(0, 4), +a.slice(5, 7) - 1, +a.slice(8, 10))) / 86400000); } /** What the sidebar sends to the licence API: a random uid and the key, never sheet data. */ function licenceRequest(key) { var props = PropertiesService.getUserProperties(); var uid = props.getProperty(UP.uid); if (!uid) { uid = hex32_(Utilities.getUuid() + ':' + Date.now()); props.setProperty(UP.uid, uid); } return { ok: !!LICENCE.publicKey, uid: uid, key: String(key || props.getProperty(UP.key) || '').trim(), api: LICENCE.api, pub: LICENCE.publicKey }; } function hex32_(text) { var bytes = Utilities.computeDigest(Utilities.DigestAlgorithm.SHA_256, text, Utilities.Charset.UTF_8); return bytes.map(function (b) { return ('0' + (b & 255).toString(16)).slice(-2); }).join('').slice(0, 32); } /** * result: {status: 'ok'|'wrong'|'used'|'offline'|'expired', key, plan ('pro'|'dept'), expires, sig}, already * signature-checked by the sidebar. 'wrong' on a stored key means it was revoked (a refund): back to Free. */ function recordLicence(result) { var props = PropertiesService.getUserProperties(); var today = today_(); if (result.status === 'ok') { var p = {}; p[UP.key] = String(result.key || props.getProperty(UP.key) || ''); p[UP.plan] = result.plan === 'dept' ? 'dept' : 'pro'; p[UP.expires] = String(result.expires || '').slice(0, 10); p[UP.sig] = String(result.sig || ''); p[UP.lastCheck] = nowLocal_(); props.setProperties(p); } else if (result.status === 'expired') { props.setProperty(UP.expires, String(result.expires || today).slice(0, 10)); props.setProperty(UP.lastCheck, nowLocal_()); } else if (result.status === 'wrong' && props.getProperty(UP.key)) { props.setProperty(UP.expires, Engine.addDays(today, -1)); props.setProperty(UP.lastCheck, nowLocal_()); } else if (result.status === 'offline') { // A key that was valid in the last 14 days keeps working; after that, back to Free. var st = licence_(); if (st.pro && st.graceLeft <= 0) props.setProperty(UP.expires, Engine.addDays(today, -1)); } return licence_(); } // ---------- Dev (the test sheet only; dev.gs is never in the public bundle) ---------- function devOn_() { return typeof DEV_ !== 'undefined'; } /** The data set from dev.gs, for the test sheet while evalua.facilo.es isn't live yet. */ function devData(id) { if (!devOn_()) return null; return id ? DEV_.sets[id] || null : DEV_.index; } // ---------- Small helpers ---------- /** Strings for the sidebar, as JSON safe to put inside a script tag. */ function stringsJson_() { return JSON.stringify(Strings).replace(/\n\n\n