Skip to content

Instantly share code, notes, and snippets.

@alejandrolechuga

alejandrolechuga/index.html

Created June 18, 2025 21:45
Show Gist options
  • Save alejandrolechuga/dce50deb1f1a6b1a946efbff2d2e1993 to your computer and use it in GitHub Desktop.
Save alejandrolechuga/dce50deb1f1a6b1a946efbff2d2e1993 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
index.html
<style>
body {
font-family: Arial, sans-serif;
font-size: 14px;
color: #333;
margin: 0;
display: flex;
flex-direction: column;
height: 100vh;
}
#app {
padding: 15px;
flex-grow: 1;
overflow-y: auto;
}
.control-group {
margin-bottom: 15px;
}
.date-controls {
display: flex;
align-items: center;
gap: 5px;
margin-bottom: 15px;
}
.date-controls button {
width: 30px;
height: 30px;
padding: 0;
margin: 0;
flex-shrink: 0;
line-height: 1;
font-size: 20px;
}
#month-select {
flex-grow: 1;
}
#day-input {
width: 60px;
flex-shrink: 0;
}
select,
input[type="number"],
input[type="text"] {
padding: 8px;
border-radius: 4px;
border: 1px solid #ccc;
box-sizing: border-box;
width: 100%;
}
#people-table {
width: 100%;
border-collapse: collapse;
}
#people-table td {
padding: 8px 4px;
border-bottom: 1px solid #e2e8f0;
vertical-align: middle;
}
#people-table td:last-child {
text-align: right;
}
.actions-container {
display: flex;
gap: 5px;
align-items: center;
justify-content: flex-end;
}
.apply-single-btn {
padding: 5px 10px;
font-size: 12px;
margin: 0;
width: auto;
flex-shrink: 0;
background-color: #60a5fa;
/* A nice blue */
}
/* New style for the clickable name */
.person-name-link {
color: #0078d4;
text-decoration: none;
cursor: pointer;
}
.person-name-link:hover {
text-decoration: underline;
}
button {
width: 100%;
padding: 10px;
color: white;
border: none;
border-radius: 4px;
font-size: 16px;
cursor: pointer;
margin-top: 15px;
}
#process-button {
background-color: #2c5282;
}
#update-button {
background-color: #38a169;
}
button:disabled {
background-color: #a0aec0;
cursor: not-allowed;
}
.message {
padding: 10px;
border-radius: 4px;
margin-bottom: 15px;
text-align: center;
font-weight: bold;
}
.error {
background-color: #fff0f0;
color: #c53030;
}
.success {
background-color: #c6f6d5;
color: #2f855a;
}
</style>
<div id="app">
<button id="process-button">Leer y Procesar Hoja</button>
<div id="message-container"></div>
<div id="attendance-ui" style="display: none;">
<div class="date-controls">
<button id="prev-day-btn">&lt;</button>
<select id="month-select"></select>
<input type="number" id="day-input" min="1" max="31">
<button id="next-day-btn">&gt;</button>
</div>
<div class="control-group">
<select id="coordinator-select"></select>
</div>
<div class="control-group">
<input type="text" id="name-filter" placeholder="Filtrar por nombre...">
</div>
<table id="people-table">
<tbody id="people-list"></tbody>
</table>
<button id="update-button">Actualizar Asistencias (Todo)</button>
</div>
</div>
Raw
script.js
Office.onReady(() => {
// This function runs when the Office host is ready.
// --- GLOBAL STATE & CONSTANTS ---
let tableData = {};
const MONTH_NAMES = ["Enero", "Febrero", "Marzo", "Abril", "Mayo", "Junio", "Julio", "Agosto", "Septiembre", "Octubre", "Noviembre", "Diciembre"];
const ATTENDANCE_VALUES = ["A", "D", "V", "I", "P", "E", "F"];
const COLOR_MAP = {
"E": "#c55812",
"A": "#a8ce8c",
"V": "#fbfb00",
"D": "#04aeef",
"I": "#f8cbad",
"P": "#c8c8c8",
"F": "#808080"
};
// --- DOM ELEMENTS ---
const processButton = document.getElementById("process-button");
const updateButton = document.getElementById("update-button");
const monthSelect = document.getElementById("month-select");
const dayInput = document.getElementById("day-input");
const prevDayBtn = document.getElementById("prev-day-btn");
const nextDayBtn = document.getElementById("next-day-btn");
const nameFilterInput = document.getElementById("name-filter");
// --- INITIALIZE ---
initializeDateControls();
// --- EVENT LISTENERS ---
processButton.addEventListener("click", processSheet);
updateButton.addEventListener("click", updateAllAttendance);
monthSelect.addEventListener("change", handleDateChange);
dayInput.addEventListener("change", handleDateChange);
prevDayBtn.addEventListener("click", () => handleDayArrowClick(-1));
nextDayBtn.addEventListener("click", () => handleDayArrowClick(1));
nameFilterInput.addEventListener("input", renderPeopleList);
function initializeDateControls() {
MONTH_NAMES.forEach((name, index) => {
monthSelect.add(new Option(name, index));
});
const today = new Date();
monthSelect.value = today.getMonth();
dayInput.value = today.getDate();
}
function handleDateChange() {
processSheet();
}
function handleDayArrowClick(direction) {
const currentMonth = parseInt(monthSelect.value, 10);
const currentDay = parseInt(dayInput.value, 10);
const currentDate = new Date(new Date().getFullYear(), currentMonth, currentDay);
currentDate.setDate(currentDate.getDate() + direction);
monthSelect.value = currentDate.getMonth();
dayInput.value = currentDate.getDate();
handleDateChange();
}
async function processSheet() {
const messageContainer = document.getElementById("message-container");
messageContainer.innerHTML = '';
processButton.disabled = true;
let allValues = [];
const CHUNK_SIZE = 1000;
let currentRow = 0;
// --- CHUNK 1: Read data from the sheet ---
try {
console.clear();
while (true) {
processButton.textContent = `Leyendo Fila ${currentRow + 1}...`;
let chunkValues;
await Excel.run(async (context) => {
const sheet = context.workbook.worksheets.getActiveWorksheet();
const range = sheet.getRangeByIndexes(currentRow, 0, CHUNK_SIZE, 370);
range.load("values");
await context.sync();
chunkValues = range.values;
});
const isChunkEmpty = chunkValues.every(row => row.every(cell => cell === "" || cell === null));
if (isChunkEmpty && allValues.length > 0) break;
allValues.push(...chunkValues);
currentRow += CHUNK_SIZE;
if (currentRow > 20000) throw new Error("Se detuvo la lectura en la fila 20,000.");
}
} catch (error) {
handleError(error, "CHUNK 1: Reading Sheet Data");
processButton.disabled = false;
processButton.textContent = "Leer y Procesar Hoja";
return;
}
processButton.textContent = "Procesando Datos...";
const values = allValues;
// --- CHUNK 2: Find headers and key columns ---
try {
document.getElementById("attendance-ui").style.display = 'none';
tableData = {
coordinators: {},
dateColumnIndex: -1,
headerRowIndex: -1,
nameColIndex: -1,
coordColIndex: -1,
rawRows: values
};
const COORDINATOR_HEADER_TEXT = "COORDINADOR DE PERSONAL";
const NAME_HEADER_TEXT = "NOMBRE";
const DATE_HEADER_ROW_INDEX = 5;
const mainHeaderRowIndex = values.findIndex((row) =>
row.some((cell) => typeof cell === "string" && cell.toUpperCase().includes(COORDINATOR_HEADER_TEXT))
);
if (mainHeaderRowIndex === -1) throw new Error("No se pudo encontrar la fila de encabezado de 'COORDINADOR'.");
tableData.headerRowIndex = mainHeaderRowIndex;
const mainHeaderRow = values[mainHeaderRowIndex];
if (values.length <= DATE_HEADER_ROW_INDEX) throw new Error("La hoja no tiene suficientes filas para encontrar la fila de fecha (Fila 6).");
const dateHeaderRow = values[DATE_HEADER_ROW_INDEX];
tableData.coordColIndex = mainHeaderRow.findIndex(
(cell) => typeof cell === "string" && cell.toUpperCase().includes(COORDINATOR_HEADER_TEXT)
);
const nombreIndices = [];
mainHeaderRow.forEach((cell, index) => {
if (typeof cell === "string" && cell.toUpperCase() === NAME_HEADER_TEXT) {
nombreIndices.push(index);
}
});
if (nombreIndices.length === 0) throw new Error('No se encontró ninguna columna "Nombre".');
tableData.nameColIndex = nombreIndices.length === 1 ? nombreIndices[0] : (() => {
let bestIndex = -1, maxAvgLength = -1;
nombreIndices.forEach(index => {
let totalLength = 0, dataRowCount = 0;
for (let i = mainHeaderRowIndex + 1; i < Math.min(values.length, mainHeaderRowIndex + 21); i++) {
const cellContent = values[i][index] ?.toString().trim();
if (cellContent) totalLength += cellContent.length;
dataRowCount++;
}
const avgLength = dataRowCount > 0 ? totalLength / dataRowCount : 0;
if (avgLength > maxAvgLength) {
maxAvgLength = avgLength;
bestIndex = index;
}
});
return bestIndex;
})();
if (tableData.coordColIndex === -1 || tableData.nameColIndex === -1) {
throw new Error("No se encontraron las columnas de Coordinador o Nombre.");
}
const selected_month = parseInt(monthSelect.value, 10);
const selected_day = parseInt(dayInput.value, 10);
tableData.dateColumnIndex = dateHeaderRow.findIndex((cell) => {
let cellDate;
if (typeof cell === "number" && cell > 25569) {
const utcMilliseconds = (cell - 25569) * 86400 * 1000;
cellDate = new Date(utcMilliseconds);
return cellDate.getUTCMonth() === selected_month && cellDate.getUTCDate() === selected_day;
}
if (typeof cell === 'string' && cell.trim().length > 4) {
try {
const dateParts = cell.split('/');
if (dateParts.length === 3) {
const month = parseInt(dateParts[0], 10) - 1;
const day = parseInt(dateParts[1], 10);
return month === selected_month && day === selected_day;
}
} catch (e) { }
}
return false;
});
if (tableData.dateColumnIndex === -1) {
messageContainer.innerHTML = `<div class="message error">ATENCIÓN: La columna para la fecha seleccionada no existe en la Fila 6.</div>`;
document.getElementById("update-button").disabled = true;
} else {
document.getElementById("update-button").disabled = false;
}
} catch (error) {
handleError(error, "CHUNK 2: Finding Headers");
processButton.disabled = false;
processButton.textContent = "Leer y Procesar Hoja";
return;
}
// --- CHUNK 3: Parse People Data (with corrected logic) ---
try {
for (let i = tableData.headerRowIndex + 1; i < values.length; i++) {
const row = values[i];
if (!row) continue;
const nameInRow = row[tableData.nameColIndex] ?.toString().trim();
// If there's no name, it's a blank row, so skip it.
if (!nameInRow) {
continue;
}
// Check for a coordinator in the current row. If blank, use the default group.
let coordInRow = row[tableData.coordColIndex] ?.toString().trim() || "Sin Coordinador";
if (!tableData.coordinators[coordInRow]) {
tableData.coordinators[coordInRow] = [];
}
let currentState = "";
if (tableData.dateColumnIndex !== -1) {
currentState = row[tableData.dateColumnIndex] ?.toString().trim() || "";
}
tableData.coordinators[coordInRow].push({ name: nameInRow, currentState, rowIndex: i });
}
} catch (error) {
handleError(error, "CHUNK 3: Parsing People Data");
return;
}
// --- CHUNK 4: Build the UI ---
try {
buildUI();
} catch (error) {
handleError(error, "CHUNK 4: Building UI");
} finally {
processButton.disabled = false;
processButton.textContent = "Leer y Procesar Hoja";
}
}
function buildUI() {
const coordinatorSelect = document.getElementById("coordinator-select");
coordinatorSelect.innerHTML = '';
const DEFAULT_COORDINATOR = "Nelly";
const sortedCoordinators = Object.keys(tableData.coordinators).sort((a, b) => {
if (a === "Sin Coordinador") return 1;
if (b === "Sin Coordinador") return -1;
return a.localeCompare(b);
});
sortedCoordinators.forEach((name) => {
coordinatorSelect.add(new Option(name, name));
});
if (tableData.coordinators[DEFAULT_COORDINATOR]) {
coordinatorSelect.value = DEFAULT_COORDINATOR;
}
coordinatorSelect.addEventListener("change", () => {
document.getElementById('name-filter').value = '';
renderPeopleList();
});
renderPeopleList();
document.getElementById("attendance-ui").style.display = 'block';
}
function renderPeopleList() {
const peopleList = document.getElementById("people-list");
peopleList.innerHTML = '';
const selectedCoordinator = document.getElementById("coordinator-select").value;
const filterText = document.getElementById("name-filter").value.toLowerCase();
let people = tableData.coordinators[selectedCoordinator] || [];
if (filterText) {
people = people.filter(person => person.name.toLowerCase().includes(filterText));
}
people.forEach((person) => {
const personRow = document.createElement("tr");
const currentState = person.currentState.toUpperCase();
const defaultValue = ATTENDANCE_VALUES.includes(currentState) ? currentState : "A";
const optionsHTML = ATTENDANCE_VALUES.map(
(s) => `<option value="${s}" ${s === defaultValue ? "selected" : ""}>${s}</option>`
).join("");
personRow.innerHTML = `
<td>
<a href="#" class="person-name-link" data-row-index="${person.rowIndex}">${person.name}</a>
<span style="color:#718096;font-size:12px;margin-left:8px;">(Valor actual: ${person.currentState || "N/A"})</span>
</td>
<td>
<div class="actions-container">
<select data-row-index="${person.rowIndex}" style="padding:5px;border-radius:4px;border:1px solid #ccc;">
${optionsHTML}
</select>
<button class="apply-single-btn" title="Aplicar asistencia seleccionada">Aplicar</button>
</div>
</td>
`;
personRow.querySelector(".person-name-link").addEventListener("click", goToCell);
personRow.querySelector(".apply-single-btn").addEventListener("click", applySingleUpdate);
peopleList.appendChild(personRow);
});
}
async function goToCell(event) {
event.preventDefault();
const rowIndex = parseInt(event.target.dataset.rowIndex, 10);
try {
await Excel.run(async (context) => {
const sheet = context.workbook.worksheets.getActiveWorksheet();
const cell = sheet.getCell(rowIndex, tableData.nameColIndex);
cell.select();
await context.sync();
});
} catch (error) {
handleError(error, `Navigating to row ${rowIndex + 1}`);
}
}
async function applySingleUpdate(event) {
const button = event.target;
const select = button.previousElementSibling;
const rowIndex = parseInt(select.dataset.rowIndex, 10);
const newState = select.value;
const originalText = button.textContent;
button.textContent = '...';
button.disabled = true;
try {
await Excel.run(async (context) => {
const sheet = context.workbook.worksheets.getActiveWorksheet();
const cell = sheet.getCell(rowIndex, tableData.dateColumnIndex);
cell.select();
await context.sync();
cell.values = [[newState]];
cell.numberFormat = [["General"]];
if (COLOR_MAP[newState]) {
cell.format.fill.color = COLOR_MAP[newState];
} else {
cell.format.fill.clear();
}
await context.sync();
});
const personData = tableData.coordinators[document.getElementById("coordinator-select").value].find(p => p.rowIndex === rowIndex);
if (personData) personData.currentState = newState;
renderPeopleList();
} catch (error) {
handleError(error, `Updating row ${rowIndex + 1}`);
} finally {
const rowElement = document.querySelector(`select[data-row-index='${rowIndex}']`);
if (rowElement && rowElement.nextElementSibling) {
const newButton = rowElement.nextElementSibling;
newButton.textContent = originalText;
newButton.disabled = false;
}
}
}
async function updateAllAttendance() {
const messageContainer = document.getElementById("message-container");
const updateButton = document.getElementById("update-button");
messageContainer.innerHTML = '';
updateButton.disabled = true;
updateButton.textContent = "Actualizando...";
try {
const selects = Array.from(document.querySelectorAll("#people-list select"));
let updatesCount = 0;
for (const select of selects) {
const rowIndex = parseInt(select.dataset.rowIndex, 10);
const newState = select.value;
await Excel.run(async (context) => {
const sheet = context.workbook.worksheets.getActiveWorksheet();
const cell = sheet.getCell(rowIndex, tableData.dateColumnIndex);
cell.select();
await context.sync();
cell.values = [[newState]];
cell.numberFormat = [["General"]];
if (COLOR_MAP[newState]) {
cell.format.fill.color = COLOR_MAP[newState];
} else {
cell.format.fill.clear();
}
await context.sync();
});
updatesCount++;
updateButton.textContent = `Actualizando... (${updatesCount}/${selects.length})`;
await new Promise(resolve => setTimeout(resolve, 50));
}
messageContainer.innerHTML = `<div class="message success">${updatesCount} registros actualizados directamente en la hoja.</div>`;
updateButton.textContent = "Actualizar Asistencias (Todo)";
updateButton.disabled = false;
await processSheet();
} catch (error) {
handleError(error, 'CHUNK 5: Updating Sheet');
updateButton.disabled = false;
updateButton.textContent = "Actualizar Asistencias (Todo)";
}
}
function handleError(error, stage = "Unknown Stage") {
console.error(`Error during ${stage}:`, error);
const messageContainer = document.getElementById("message-container");
if (error instanceof OfficeExtension.Error) {
console.log("Debug Info:", error.debugInfo);
messageContainer.innerHTML = `<div class="message error"><b>Error en: ${stage}</b><br>Office.js: ${error.message} (Code: ${error.code})</div>`;
} else {
messageContainer.innerHTML = `<div class="message error"><b>Error en: ${stage}</b><br>Unexpected: ${error.message}</div>`;
}
}
});
Raw
style.css
body {
font-family: Arial, sans-serif;
font-size: 14px;
color: #333;
margin: 0;
display: flex;
flex-direction: column;
height: 100vh;
}
#app {
padding: 15px;
flex-grow: 1;
overflow-y: auto;
}
.control-group {
margin-bottom: 15px;
}
.date-controls {
display: flex;
align-items: center;
gap: 5px;
margin-bottom: 15px;
}
.date-controls button {
width: 30px;
height: 30px;
padding: 0;
margin: 0;
flex-shrink: 0;
line-height: 1;
font-size: 20px;
}
#month-select {
flex-grow: 1;
}
#day-input {
width: 60px;
flex-shrink: 0;
}
select,
input[type="number"],
input[type="text"] {
padding: 8px;
border-radius: 4px;
border: 1px solid #ccc;
box-sizing: border-box;
width: 100%;
}
#people-table {
width: 100%;
border-collapse: collapse;
}
#people-table td {
padding: 8px 4px;
border-bottom: 1px solid #e2e8f0;
vertical-align: middle;
}
#people-table td:last-child {
text-align: right;
}
.actions-container {
display: flex;
gap: 5px;
align-items: center;
justify-content: flex-end;
}
.apply-single-btn {
padding: 5px 10px;
font-size: 12px;
margin: 0;
width: auto;
flex-shrink: 0;
background-color: #60a5fa;
/* A nice blue */
}
/* New style for the clickable name */
.person-name-link {
color: #0078d4;
text-decoration: none;
cursor: pointer;
}
.person-name-link:hover {
text-decoration: underline;
}
button {
width: 100%;
padding: 10px;
color: white;
border: none;
border-radius: 4px;
font-size: 16px;
cursor: pointer;
margin-top: 15px;
}
#process-button {
background-color: #2c5282;
}
#update-button {
background-color: #38a169;
}
button:disabled {
background-color: #a0aec0;
cursor: not-allowed;
}
.message {
padding: 10px;
border-radius: 4px;
margin-bottom: 15px;
text-align: center;
font-weight: bold;
}
.error {
background-color: #fff0f0;
color: #c53030;
}
.success {
background-color: #c6f6d5;
color: #2f855a;
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment