Desagrupando y agrupando filas con valores múltiples en una hoja de cálculo con Apps Script 

Que levante la mano el que no haya creado en alguna ocasión un formulario de Google con preguntas de esas que pueden responderse con casillas de verificación ☑️ y se haya topado entonces con el problema que supone su análisis  posterior en una hoja de cálculo o incluso en un estupendo informe creado en nuestro adorado Data Studio.

Resulta que cuando un usuario marca varias casillas de verificación en un formulario, el texto de todas las opciones seleccionadas se almacena en una misma celda de la fila de respuesta utilizando la secuencia ", " (coma - punto) como separador.

Esto vuelve un poco majareta tanto a las ya tradicionales tablas dinámicas como a los algo más flamantes controles de filtro (o vistas / filtros de-toda-la-vida), que identifican como respuestas independientes cada una de las posibles combinaciones registradas en estas columnas, con sus comas y espacios y todo. Un pequeño infierno a la hora de realizar cualquier análisis estadístico sobre ellas, oiga.

No, las hojas de cálculo no se llevan especialmente bien con las celdas que contienen valores múltiples.

A propósito de esta cuestión, me gustaría rescatar un viejo artículo en mi blog sobre G Suite para Educación en el que hablo de esta circunstancia con cierto detenimiento e incluso propongo una alternativa para evitar estas maléficas casillas de verificación en nuestros formularios. Si quieres leer sobre tickets de salida, campos calculados en tablas dinámicas y preguntas de tipo cuadrícula de varias opciones, échale un vistazo.

👉 Contabilizar preguntas de respuesta múltiple en Formularios de Google sin morir en el intento 👈

Pero ya se sabe: a grandes males, grandes remedios.

Para mejorar la situación, he recurrido a Apps Script para crear una función personalizada para las hojas de cálculo de Google que permite desacoplar este tipo de respuestas múltiples y facilitar su análisis posterior: DESACOPLAR().

Y recogiendo la acertada sugerencia que mi compañero de coordinación en GEG Spain, Alberto Gilsanz me hizo en uno de nuestros "tardeos" virtuales de verano 🍸, además he complementado la anterior con una segunda función personalizada, ACOPLAR(), que realiza un proceso aproximadamente simétrico, es decir, reagrupa las filas de un intervalo de datos utilizando una serie de campos definidos como clave. No estoy muy convencido de que esta última función sirva para algo, pero la cosa parecía quedarse a medias sin ella. Muchas gracias, querido Alberto, por hacerme inevitable echarle un rato más al asunto 😁.

Funciones personalizadas DESACOPLAR() y ACOPLAR().

Una demo rápida de DESACOPLAR(), que en mi opinión sí tiene utilidad inmediata:

Desagrupando  columnas con valores múltiples.

Pero para demostrar sus posibilidades de uso, nada mejor que contexto. Fíjate en esta bonita hoja de cálculo, que pretende servir para algo así como gestionar las inscripciones a unas supuestas actividades de formación interna en una organización cualquiera:

Una hoja de cálculo que utiliza la función Apps Script DESACOPLAR() .

Una breve explicación de qué hay en cada pestaña y ya te dejo para que te hagas una copia, si quieres, y la examines tranquilamente:

  • Formulario 📥: Se supone que estas son las inscripciones recibidas desde un hipotético formulario (no suministrado): nombre y apellidos, cursos a los que se realiza la inscripción y turno (horario) para el que se tiene disponibilidad.
  • Formulario + análisis 🔎: Esta hoja está ahí simplemente para demostrar qué pasa cuando tenemos respuestas múltiples en algunas de las preguntas (columnas).  Hemos arrancado el artículo hablado justo de esto , así que no insistiré.
  • Respuestas desacopladas ♻️: Aquí se utiliza la fórmula  =DESACOPLAR('Formulario 📥'!A:D;;;2;3) para generar tantas inscripciones por cada solicitante como combinaciones posibles de curso y turno haya marcado en su solicitud.
  • Análisis 📊: Unos sencillos gráficos basados en la pestaña anterior que demuestran cómo ahora sí podemos obtener estadísticas basadas en el recuento individual de las respuestas facilitadas en las columnas curso y turno.
  • Inscritos  y Disponibilidad 🔍:  Y más de lo mismo, pero esta vez con sendas tablas dinámicas que ofrecen información de resumen del proceso de inscripción.
  • Test ACOPLAR con fx estándar ❓:  Una pequeña demostración de uso de la nueva función ACOPLAR() sobre un conjunto de datos de prueba (celda F6) y la alternativa recurriendo únicamente a las funciones estándar incorporadas en las HdC de Google (F2:I3). Tú eliges 😏.

Aquí la hoja de cálculo:

👉 fx (DES)ACOPLAR # demo 👈

Los detalles técnicos de DESACOPLAR() y ACOPLAR() , así como sus instrucciones de uso y, cómo no, un recorrido por la implementación en Apps Script, que tiene alguna que otra cosilla digna de mención, los encontrarás en este repositorio de GitHub.

👉 https://github.com/pfelipm/desacoplar-acoplar 👈

 

Espero que estos dos artefactos te resulten de utilidad.

Comentarios