Consulta de Notas de Alumnos para Escuela Secundaria con Google Apps Script

Este ejemplo me lo pidió el profe Augusto, y consiste en un script que permite a los padres de sus alumnos realizar una consulta online de las notas y promedio obtenido por sus hijos.

Momentos el video:

0:06 En que consiste.

0:35 Cómo acceder a google drive.

1:09 Creación de base de datos alumnos.

1:51 Creación de Planilla Consultas y formulario.

5:45 Editor de Secuencias de Comandos (Programación).

7:29 Obtener el id de la base de datos.

9:06 Programar la ejecución del Script. (Reloj Activador).

11:05 Enviar y probar formulario.

12:57 El Correo llegó a Spam.

Esta es la secuencia de funcionamiento a grandes rasgos:
1) Los padres completan un formulario con su email y el ID del alumno, dicho formulario puede estar en un blog, página de la institución educativa o como opciones alternativa se lo puede enviar por email, whatsapp, facebook o cualquier medio a través su enlace.
2) Los datos son recibidos en una planilla la cual contiene un script, entonces al recibir los datos se ejecuta el mismo.
3) El script se empezó a ejecutar, toma los datos que necesita de "4)" que es la planilla de consultas, accede a la base de datos de alumnos y comienza la búsqueda según el id ingresado.
Cuando encuentra el id correcto, extrae los datos y arma un email para enviar la información.
En el caso de no encontrar los datos, se enviará un email informando al respecto.

Para realizarlo la secuencia es un poco distinta:
Lo primero será abrir google drive y crear un lugar para almacenar las consultas, los pasos son los siguientes.
a) Crear una hoja de cálculo de google vacía.
b) Acceder a la misma y darle un nombre ejemplo: "Consultas"
c) Pulsar menú Herramientas, Crear Formulario.

d) Darle un título y una descripción al formulario.

e) Darle un color, pulsar el ícono de la paleta de colores y luego elegir el color, finalmente la X para cerrar.

f) Cambiar el tipo de preguntas por una de respuesta corta, seguido escribir id alumno, y activar obligatorio

g) Ahora para solicitar el email:

i. Agregar pregunta, pulsar en +.

ii. Escribir "Ingrese email"

iii. Menú, Validación de respuesta.

iv. Tipo Texto y elegir Dirección de correo electrónico.

v. Activar Obligatorio.

h) El formulario ya está listo, y se puede cerrar desde la x.

i) En la hoja de cálculo aparecen ahora una hoja del formulario.

j) La hoja 1 se puede eliminar, hacer clic derecho y pulsar en eliminar.

k) La planilla queda de la siguiente manera, aparece Marca temporal, Id alumno e Ingrese email.

Pasemos a crear el script, pero antes debemos tener la base de datos de alumnos.

Hicimos esta planilla de ejemplo con los siguientes datos:

El script no lo voy a realizar en esta planilla, sino en la de consultas (la que tiene el formulario), así que regresemos a dicha planilla.

1. Ir a Herramientas y pulsar en Editor de secuencia de comandos.

2. De la hoja de cálculo activa necesitamos tomar los datos del Id para realizar la búsqueda, y del email para poder enviar la información.

Y algo importante, es que sean de la última consulta.

i. Obtener el número de la última fila con datos.

ii. Tomar los valores. Id Alumno y el email.

Aclaración sobre el código:

Las palabras en azul como por ejemplo ultimafila, idAlumno, email son variables que almacenan información, y que luego cuando se requiera se las puede llamar y tomar esa información.

function myFunction() {

var hoja1 = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0]; // Abrimos la hoja1

var ultimafila = hoja1.getLastRow(); // Obtenemos la última fila con datos

var idAlumno = hoja1.getRange(ultimafila,2).getValue(); // Obtenemos el Id del Alumno

var email = hoja1.getRange(ultimafila,3).getValue(); // Obtenemos el email

}

3. Ahora debemos empezar a recorrer la base de datos de los alumnos, buscando el id solicitado.

i. Crear una variable para guardar los datos del identificado (el id de la base de datos de los alumnos), al misma se encuentra en la barra de dirección luego de d/ y antes de /edit.

ii. Se abre la hoja 1 de la base de datos de los alumnos para posteriormente extraer los datos.

iii. Se identifica la última fila, que es la última consulta realizada.

iv. Se asigna a la variable u la última fila para luego ser usada en el mientras (while).

v. Se crea una bandera con valor 1, es simplemente un indicador booleano, es decir, toma valor 1 o 0 según se encuentre el resultado buscado. También se lo va a utilizar en el while.

vi. Se crea el while para recorrer las celdas, y dentro de este un if para ir comprobando si se encontró el valor.

var identificador = "pegar el identificado del archivo aquí para que funcione"; // Identificador de hoja de cálculo base de datos alumnos.

var datosAlumnos = SpreadsheetApp.openById(identificador).getSheets()[0]; // Abrimos la hoja1 de la base de datos de los alumnos.

var ultimaFilaDAlumnos = datosAlumnos.getLastRow(); // Obtenemos la última fila.

var u = ultimaFilaDAlumnos; // Asignamos a la variable u el número de filas de la base de datos alumnos.

var bandera = 1; // Se utiliza para salir del while cuando encontremos el resultado buscado.

while ((u > 2) && (bandera == 1)) { // Mientras u sea mayor a la fila 2 y además la bandera esté en 1, hacer lo siguiente.

var valorExtraido = datosAlumnos.getRange(u,1).getValue(); // Extrae el valor de la fila u y columna 1.

if (valorExtraido == idAlumno){ // Si se encuentra el id del alumno hacer lo siguiente.

var nombreAlumno = datosAlumnos.getRange(u,2).getValue(); // Tomar el nombre del alumno.

var nota1 = datosAlumnos.getRange(u,3).getValue(); // Tomar nota 1.

var nota2 = datosAlumnos.getRange(u,4).getValue(); // Tomar nota 2.

var nota3 = datosAlumnos.getRange(u,5).getValue(); // Tomar nota 3.

var promedio = datosAlumnos.getRange(u,6).getValue(); // Tomar el promedio.

var bandera = 0; // Esto indica que se ha encontrado el dato, y que se debe salir del while.

}

u-- // resta uno a u

}

Aclaración sobre el código:

== es para comparar el valor de dos elementos, y devuelve verdadero o falso.

= es para guardar un valor en una variable.

&& es el conector lógico "y".

u-- equivale a decir var u = u - 1; y lo que hace es estar 1 a la variable 1 cada vez que se repite el while.

4. Ahora que ya tenemos el buscador y ya extrae los datos deseados, vamos a hacer que los mande por email.

i. Primero comprobar si se ha encontrado el dato, esto se realiza con un "si" (if). Veamos un pequeño ejemplo para comprender el esquema.

a. Hay una variable llamada bandera que toma el valor 0 o 1.

b. El if comprueba si la variable bandera tiene el valor 0.

c. Si ficha variable tiene el valor 0, envía un email con los datos.

d. De lo contrario, si no encuentra un 0 en la variable, envía un email avisando que no se encuentra el id en la base de datos.

ii. La variable bandera ya la definimos anteriormente, por lo tanto, no será necesario volver a escribirla.

iii. Armamos el bloque if, colocamos el código para que envíe el email.

if (bandera == 0){ // Si se ha encontrado el id en la base de datos se enviarán los datos.

var emailBody = "Datos de " + nombreAlumno + ": " + " Nota1: " + nota1 + " Nota2: " +

nota2 + " Nota3: " + nota3 + " Promedio Final: " + promedio + " Atte El Profesor"; // El email por si no se puede visualizar el html.

var htmlBody = // El email en formato html.

"Datos de " + nombreAlumno + "!!!" +

" Nota 1: " + nota1 +

" Nota 2: " + nota2 +

" Nota 3: " + nota3 +

" Promedio final: " + promedio +

" " +

" Saluda Atte." +

" El profesor";

var OpcionesAvanzadas = { name: "Escuela Octaedrus",htmlBody: htmlBody };

MailApp.sendEmail(email, "Respuesta a consulta de notas Alumnos", emailBody, OpcionesAvanzadas);

}else { // de lo contrario, se debe avisar que no se ha encontrado el id del alumno.

var emailBody = "No se ha encontrado información para el id " + idAlumno + " comuníquese con el profesor"; // El email por si no se puede visualizar el html.

var htmlBody = // El email en formato html.

"No se ha encontrado información para el id " + idAlumno + " comuníquese con el profesor";

var OpcionesAvanzadas = { name: "Escuela Octaedrus",htmlBody: htmlBody };

MailApp.sendEmail(email, "Dato no encontrado", emailBody, OpcionesAvanzadas);

}

Aclaración sobre el código:

Al enviar un email el receptor puede o no tener habilitada la opción de visualizar como html, por tal motivo lo programamos de las dos maneras.

El código completo del script de la planilla de consultas es el siguiente:

function myFunction() {

var hoja1 = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0]; // Abrimos la hoja1.

var ultimafila = hoja1.getLastRow(); // Obtenemos la última fila con datos.

var idAlumno = hoja1.getRange(ultimafila,2).getValue(); // Obtenemos el Id del Alumno.

var email = hoja1.getRange(ultimafila,3).getValue(); // Obtenemos el email.

var identificador = "1JfsW07T4RbDIMVlt26VM86hTj60c8nTJYNmSrYsw5JA"; // Identificador de hoja de cálculo base de datos alumnos.