Formularios con hojas de datos invertidas


Gracias ante todo por hacer clic en el titular de este artículo a pesar de lo mal que resume el contenido de la entrada. Hay días que uno está más espeso de lo normal... o quizás sea el cambio de hora.

Hace unas semanas hablábamos en este espacio acerca de cómo aplicar formato de modo uniforme a las nuevas respuestas recibidas en la hoja de datos asociada a un formulario. En esta ocasión vamos a tratar de alterar el modo normal en que se reciben las respuestas en una hoja de datos, que como sabemos se van anexando en la parte inferior de la misma de modo predeterminado.

¿Y es esto un problema? Probablemente no, al menos no uno grave, pero lo cierto es que si las respuestas se recibieran en la parte superior de la hoja podríamos revisar de un vistazo la información más reciente, sin necesidad de desplazarnos a la última fila, que potencialmente puede encontrarse muy alejada del extremo superior de la tabla.

Y de paso, aprenderemos un poquito más sobre los scripts de Google Apps que, si me lo permites, a partir de ahora voy a denominar simplemente guiones por aquello de que tenemos un idioma lo suficientemente rico como para no estar constantemente recurriendo a términos en inglés.

En resumidas cuentas, nuestro objetivo es que cada nueva respuesta recogida por medio de un formulario de Google Drive se muestre siempre en la parte superior de la hoja de datos de su formulario asociado, empujando hacia abajo a las ya registradas previamente, para mejorar su visibilidad.

Existen varias estrategias para resolver este problema. La que yo te presento aquí es esta:
  1. Interceptar el evento de recepción de una nueva respuesta desde el formulario.
  2. Identificar la fila de la hoja de datos donde se ha registrado esta respuesta (¡la última, claro!).
  3. Trasladar esta fila a la número 2, justo debajo de la que contiene los encabezados, haciendo que las respuestas ya recibidas se desplacen una fila hacia abajo.
Si no te interesa cómo funcionan los guiones de Google Apps y prefieres ir al grano, al final de este artículo encontrarás una plantilla con todo lo que necesitas para reproducir el funcionamiento descrito en tus propios formularios.

Si por el contrario tienes cierta curiosidad por ver de qué modo se monta todo esto, sigue leyendo este paso-a-paso.

Como probablemente sepas, Google Apps Script (GAS) es una plataforma que permite enriquecer las funciones ofrecidas por los editores de Drive y demás aplicaciones de Google como Calendar, Gmail, Contactos y alguna que otra adicional mediante programas desarrollados por el usuario en algo muy parecido a JavaScript. Sí, el mismo lenguaje que forma parte del ADN de innúmerables sitios web, con la particularidad de que en este caso se ejecutará directamente en los servidores de Google en lugar de localmente, en nuestro propio navegador. GAS proporciona una jerarquía de clases y objetos (entidades) y métodos (funciones) con los que podemos interactuar para hacer cosas interesantes.

La referencia más completa de GAS no es otra que la propia página de Google Apps Script. El contenido es bastante espeso si no estás familiarizado con la programación, pero hay guías y ejemplos para aburrir. Lo más aconsejable, en mi opinión, es centrarse en estos últimos para entender cómo funcionan y copiar ideas y código para los tuyos propios. Tras el comprensible y probable impacto inicial, comprobarás que tu avance es cada vez más rápido a medida que te familiarizas con esta herramienta.

Paso 1. Ejecutando código al recibir respuestas.

Lo primero que necesitaremos es averiguar cómo hacer que nuestro código se ejecute cada vez que se reciba una respuesta procedente del formulario. Para ello necesitaremos recurrir a los llamados triggers o disparadores.

Existes dos tipos de disparadores: simples e instalables. Entre los primeros, y referentes a las hojas de cálculo, los más utilizados son:
  • onOpen(): Se ejecuta cuando se abre el documento.
  • onEdit(): Se ejecuta cada vez que se edita una celda.
Los disparadores simples conllevan un montón de restricciones por lo que hace a lo que pueden o no pueden hacer, pero a menudo son todo lo que necesitamos para lograr nuestro objetivo. No es el caso, en esta ocasión, puesto que ninguno de ellos es capaz de detectar el evento que nos interesa (envío de formulario).

Pasando a los instalables, y siguiendo en el ámbito de las hojas de cálculo, nos topamos con uno denominado Form submit que, como su nombre sugiere, se desencadena cada vez que la hoja de datos recibe una respuesta procedente de su formulario vinculado. Es este al que recurriremos.

Paso 2. Moviendo celdas.

Resuelta la activación, veamos cómo mover la fila de celdas correspondiente a la respuesta recibida a la primera posición. Investigando en la documentación de referencia, nos encontramos con la clase Sheet y su método moveRows, que parece hacer lo que pretendemos. Fijémonos en el ejemplo suministrado.


Este método recibe como parámetro un objeto de tipo Range (un rango de celdas), construido a su vez con el método getRange de la clase Sheet utilizando la notación habitual, y lo desplaza a la fila indicada (destinationIndex). De propina, las filas situadas en el punto de inserción se desplazan hacia abajo. Perfecto, esto está hecho.

Paso 3. Pongámoslo todo junto.

Partimos de una sencilla hoja de cálculo con formulario asociado (te la facilito más abajo). Nos vamos a Herramientas → Editor de secuencias de comandos para abrir el editor de guiones.



Introduciremos este código:


function onFormResponse() {
  var sheet = SpreadsheetApp.getActiveSheet();
  // Obtener última fila con datos
  var lastRow = sheet.getLastRow();
  // Si hay más de 1 respuesta mover arriba (2ª fila) y empujar hacia abajo el resto
  if (lastRow > 2) {
    // Selección de fila completa mediante expresión de tipo rango, ej A4:4
    var rango = sheet.getRange("A" + lastRow.toString() + ":" + lastRow.toString());
    sheet.moveRows(rango, 2);
  }
}

Nota: En el código mostrado aquí arriba, al método getRange se le facilita una expresión de rango de tipo Ax:x (ej. A4:4) para especificar la fila completa. Esto no es estrictamente necesario puesto que, tal y como se indica en la documentación de referencia, este método selecciona completamente por sí mismo todas las filas comprendidas en el rango especificado, pero personalmente me parece conceptualmente más claro.

Ahora hay que activar el disparador para conectar el evento de envío de un formulario con la función onFormResponse() que hemos preparado:


¡Y ya lo tenemos!


Como cabría esperar, no es necesario tener la hoja de cálculo abierta para que nuestro guión haga su magia. En la animación anterior la he presentado junto al formulario simplemente para que puedas ver lo que ocurre en ella en tiempo real.

Aquí tienes esta hoja de cálculo, utilizada a lo largo del artículo, que ya incluye el guión que hemos preparado. Puedes partir de ella (Archivo → Crear una copiapara construir tu propio formulario.

Hoja de cálculo invertida

Como de costumbre, ¡tus comentarios son bienvenidos!

Comentarios