BAS#003 | Importando archivos CSV de Google Drive a una hoja de cálculo con Apps Script

Por Pablo Felip

Cuenta la leyenda que para montar automatizaciones con Google Apps Script que realmente resuelvan problemas reales y resulten de cierta utilidad hay que saber un montón.

Pero eso es totalmente falso 👎.

Precisamente para tratar de desmentir esa preconcepción notablemente errónea, el año pasado me pareció una buena idea comenzar a publicar una serie de "píldoras" 💊 de iniciación a GAS.

Mi intención era desmenuzar, pasito a pasito, algunas automatizaciones Apps Script sencillas, inspiradas siempre que fuera posible en las consultas que suelo recibir con bastante frecuencia, y así como quien no quiere la cosa ir introduciendo de paso conceptos BÁSicos 😆 de Apps Script , tocando sus diferentes palos (servicios).

Y no se me ocurrió otra cosa que agrupar esos artículos futuribles bajo el paraguas de algo llamado Básicos Apps Script {BAS}, así entre corchetes, que parecía que quedaba como más molón.

El banner de {BAS} en el repositorio GitHub que acoge lo poco aprovechable que hay del proyecto.

Las dos primeras píldoras BAS se publicaron, bastante juntitas ellas, en la sección Área Developers de la Comunidad de GEG Spain, y allí puedes dirigirte ahora mismo si te apetece pegarles una leída:

  • BAS#001. Consolidar un intervalo de datos de todas las hojas de cálculo contenidas en una carpeta de Drive.
  • BAS#002. Exportar diapositivas de una presentación como PNG.

Pero como muchas de mis iniciativas, esto se quedó en agua de borrajas. Y aunque en mi Notion hay restos de tres o cuatro cadáve... digo borradores de píldoras BAS, solo he sido capaz de publicar dos en ¡dieciséis meses! ¡Olé yo 👻!

¡Pero hasta aquí hemos llegado! Hoy retomo el proyecto {BAS} y de paso me lo traigo a este espacio, donde inauguro el hashtag #BAS.

No, no es que mis compis de GEG Spain me hayan echado de casa (aún, creo), sino que honestamente pienso que probablemente no era el mejor lugar para {BAS}, así que voy a dejar de darles la lata 

Vamos por tanto con el BAS#003 (no sé si reírme o llorar mientras me empecino en mantener dignamente esos dos ceros delante del tres).

Hoy aprenderás a importar archivos en formato CSV almacenados en Google Drive a tus hojas de cálculo gracias a Google Apps Script. Además, diseñaremos juntos un pequeño panel de control para configurar fácilmente la importación usando exclusivamente las herramientas del editor de hojas de cálculo de Google.

TABLA DE CONTENIDOS

Descripción del problema

Disponemos de una carpeta de Google Drive en la que tenemos una hoja de cálculo de Google, en dos de cuyas hojas deseamos importar dos archivos de tipo CSV, también localizados dentro de la misma carpeta. Los datos importados se analizarán, usando fórmulas mondas y lirondas, en una tercera hoja de la hoja de cálculo.

Los archivos CSV proceden de otro sistema de información, cuál no nos importa demasiado. Podrían ser obtenidos periódicamente gracias a otro script GAS o ser el resultado de una exportación realizada de manera manual por un sufrido usuario anónimo. Pero el caso es que necesitamos que se incorporen datos frescos regularmente a la hoja de cálculo para mantener actualizado el análisis que se hace de ellos.

Importando archivos CSV desde Google Drive a una hoja de cálculo.

La idea es que mediante un pequeño panel de ajustes, que puedes ver en la parte superior derecha del diagrama anterior, nuestro muy estimado y nunca bien ponderado usuario pueda configurar el proceso escogiendo:

  • Los nombres de los archivos CSV a importar (recuerda, ubicados en la misma carpeta que la hoja de cálculo de análisis / control).
  • Las hojas de datos en las que se realizará la importación.
  • Si se sobreescribirán los datos importados previamente o por el contrario se deben anexar a ellos los resultantes de la importación más reciente. Esta elección dependerá, lógicamente, de las características de los archivos CSV obtenidos de ese hipotético servicio de información, cuya naturaleza no necesitamos sin embargo concretar más en tutorial .

Pero, vamos a ver... ¿De verdad hay que complicarse tanto la vida? Después de todo, ya disponemos de una estupenda función de importación en el comando de menú Archivo → Importar del editor de hojas de cálculo, ¿no es así?

Importando datos en una hoja de cálculo de Google de manera manual.

Cierto, pero es que hay hacer muchos clics para traernos un CSV. Y además dos veces, una para cada archivo que necesitamos importar.

Cierto, para el usuario experimentado el proceso anterior es coser y cantar. Pero aún así, cuando pones el piloto automático no es infrecuente meter la pata, seleccionar la opción errónea en el selector de ubicación de importación y tener que recular. Seguro que te ha pasado. Y es molesto.

Y para aquellos no tan experimentados, o simplemente más ocupados, que posiblemente solo quieran abrir la hoja de cálculo y revisar los numeritos que hay en su interior, resulta un galimatías innecesario, un pequeño suplicio que estamos en disposición de evitarles con un puñado de líneas de código.

Y si las razones anteriores no te han convencido aún, bueno, pues digamos que automatizaremos el proceso... simplemente porque podemos hacerlo.

Y es que Apps Script viene para estas cosas como anillo al dedo. Te garantizo que el script de 1º de GAS que usaremos apenas tiene 30 líneas, una vez descontadas las de comentarios y las que se han dejado en blanco, se monta en dos minutos y puede ahorrarte considerable tiempo y dolores de cabeza a lo largo de su vida útil .

Que no, que en este caso no va a ser así. De verdad.

Y además, a pesar de su sencillez, o precisamente por ella, el script resultante nos va a dar mucho juego para aprender áun más GAS... ¡Atento/a al al apartado final de este artículo!

Algunos preparativos (sin GAS)

Usaremos como pista de entrenamiento para ejercitar nuestros GASmúsculos la hoja de cálculo, acompañada de dos archivos CSV de prueba, que encontrarás dentro de esta carpeta:

📂 BAS#003 Importar archivos CSV desde Drive 📂

No te distraigas mucho con ella aún, ya tendrás tiempo de destriparla cuando hayas acabado de leer todo lo que tengo que contarte. Dentro de la hoja de cálculo encontrarás también (Extensiones → Apps Script) el script GAS de importación.

Esta hoja de cálculo contiene tres pestañas distintas:

1️⃣ Importación

Se trata de un sencillo panel de control que usaremos para configurar la automatización. 

Hoja "Importación".

Soy muy fan de utilizar las características de formato condicional y validación de datos de las hojas de cálculo de Google para mostrar pistas visuales que faciliten su uso e impedir la introducción de valores no permitidos en ciertas celdas. De este modo se reduce la probabilidad de que se produzcan errores o resultados inesperados, que siempre va bien.

En este caso, encontrarás que se han aplicado sendas fórmulas de formato condicional sobre las celdas B3, E3, B6 y E6 para modificar su color de fondo dependiendo de si están o no, señalizando de este modo que deben ser rellenadas.

Dos reglas de formato condicional facilitas.

Adicionalmente, he utilizado la validación de datos sobre las celdas B6 y E6, en las que el usuario tiene que introducir los nombres de las hojas destino de la importación. Así nos aseguramos de que realmente existan, evitando posibles errores (o comprobaciones) posteriores en el código Apps Script. Nadie dijo que todo el peso de una automatización tuviera que estar en el script, si otra estrategia es más rápida, ¡úsala!

Para ello recurro a una fórmula de validación que trata de acceder a la pestaña cuyo nombre se ha introducido usando la función INDIRECTO. Envolveremos esta función con una estructura condicional SI / ESERROR, de manera que si la hoja no existe...¡zasca, mensaje de advertencia al canto y error evitado!

La validación de datos impide introducir el nombre de una hoja que no existe.
 =NO(ESERROR(INDIRECTO(E6 & "!A1")))
¡Viva la validación de datos!

En este 🐦 tuit hablaba de ello hace nada, en él encontrarás una microanimación en la que se demuestra el uso de este patrón de diseño tanto en la validación de datos como en el formato condicional.

Sí, ya sé que esto tiene poco que ver con Apps Script, pero por favor, no descuidemos ciertas cosas aparentemente insignificantes que sin embargo contribuyen a mejorar la experiencia de uso de nuestros tinglados hojacalcúlicos. Atención al detalle, por favor.

Debajo encontrarás un botón, generado con Insertar → Dibujo, al que se le ha asignado la función GAS que montaremos en un momento, y que será la encargada de realizar la importación de los archivos CSV. Personalmente, prefiero botones a menús personalizados, siempre y cuando no necesitemos inundar la hoja de cálculo con ellos, claro está.

¡Ojo, el nombre de la función no debe escribirse aquí con paréntesis finales ()!

2️⃣ Anotaciones

Aquí se escribirán los datos importados del primer archivo  CSV.

Anotaciones registradas por los docentes en diferentes sesiones de clase.

3️⃣ Asistencia

Y en esta otra hoja los procedentes del segundo.

Registros de asistencia del alumnado. Ya sé que faltan datos, pero no tenía ganas de inventarme más cosas.

4️⃣ Resumen

Y aquí es donde finalmente, tirando de fórmulas, realizaremos algún tipo de cálculo de resumen a partir de los datos contenidos en las dos hojas anteriores.

Un par de fórmulas en las columnas B y C y ya tenemos información del proceso.

Con el problema que deseamos resolver debidamente caracterizado (nadie dijo que fuera para tirar cohetes), ahora sí, vamos a por la...

Solución GAS

Como de costumbre, aquí te dejo el script completo. Y ahora vayamos por partes.

Primeramente definimos una serie de constantes para interactuar con las celdas de la hoja que usamos como panel de ajustes.

/**
 * Este script importa el contenido de los dos archivos
 * csv indicados en la hoja "Importación", sustituyendo
 * el contenido de las hojas destino o anexando datos.
 * 
 * Se trata de una simple demostración de lo sencillo
 * que resulta acceder al contenido de archivos csv
 * almacenados en Google Drive usando Apps Script.
 * 
 * Demo: https://drive.google.com/drive/folders/1QnLKXh5KWSUzzg92hpBYjIviee9N-W3l?usp=sharing
 * 
 * BAS#003 Copyright (C) 2022 Pablo Felip (@pfelipm)
 * 
 * Se distribuye bajo licencia MIT.
 */

function importarCsv() {

  // Constantes de parametrización del script
  const AJUSTES = {
    hoja: 'Importación',
    nombre1: 'B3',
    nombre2: 'E3',
    hojaDestino1: 'B6',
    hojaDestino2: 'E6',
    anexar: 'B8',
    resultado: 'B12'
  };

...y señalizamos el inicio del proceso mostrando un mensaje informativo en la celda B12.

Somos seres pesimistas por naturaleza, así que precargaremos en la variable resultado un mensaje de error, que será sustituido por otro más feliz si el proceso de importación se desarrolla del modo esperado.

//  Hoja de cálculo y pestaña de ajustes
  const hdc = SpreadsheetApp.getActive()
  const hoja = hdc.getSheetByName(AJUSTES.hoja);

  // Señalizar inicio del proceso
  hoja.getRange(AJUSTES.resultado).setValue('🟠 Importando archivos csv...');
  let resultado = '🔴 No se ha podido realizar la importación';

A continuación, el script obtiene el ID de la hoja de cálculo sobre la que estamos trabajando y la carpeta dentro de la que se encuentra (línea 39) y localiza dentro de ella los archivos CSV indicados por el usuario en las celdas B3 y E3 (líneas 40 y 41).

  // Trata de abrir los archivos csv indicados por el usuario
  const carpeta = DriveApp.getFileById(hdc.getId()).getParents().next();
  const csv1 = carpeta.getFilesByName(hoja.getRange(AJUSTES.nombre1).getValue() + '.csv');
  const csv2 = carpeta.getFilesByName(hoja.getRange(AJUSTES.nombre2).getValue() + '.csv');

El script presupone que estos archivos tienen siempre la extensión .csv, que es añadida automáticamente a los nombres facilitados.

☝ En las píldoras BAS#001 y BAS#002 ya le dimos una vuelta a cómo usar la clase DriveApp para recorrer los archivos dentro de una carpeta de Google Drive. Allí introdujimos el concepto de iterador de archivos y de carpetas, además de explicar cómo leer y escribir en las celdas de una hoja de cálculo. Todo eso se supone que ya lo tenemos superado, pero si no es así, pégales una leída a ambos artículos, te ayudarán a entender mejor qué estamos haciendo aquí. Venga, te espero.

Como probablemente sepas, una carpeta de Google Drive puede contener varios archivos (o carpetas) con idéntico nombre. Por esa razón, el método getFilesByName() devuelve un iterador de archivos, del que simplemente extraeremos el primer elemento con el método next().

¿Y qué pasa si alguno de los archivos cuyo nombre ha introducido el usuario en la hoja de ajustes no existe? Pues que los iteradores correspondientes en las constantes csv1 y/o  csv2 estarán vacíos. Por esa razón nos aseguraremos de que no se dé esa circunstancia antes de continuar.

  // ¿Existen ambos archivos?
  if (csv1.hasNext() && csv2.hasNext()) {

Solo si ambos archivos existen tiraremos adelante, entrando entonces en la sección del código que realmente realiza la importación de datos.

    // Drive File → Blob → String → String[][] 
    // Espera que el delimitados sea un coma [,], en caso contrario usar
    // parseCsv(csv, delimiter)
    // https://developers.google.com/apps-script/reference/utilities/utilities#parsecsvcsv,-delimiter
    const datos1 = Utilities.parseCsv(csv1.next().getBlob().getDataAsString());
    const datos2 = Utilities.parseCsv(csv2.next().getBlob().getDataAsString());

Y he aquí el quid de la cuestión. Contempla, oh querido iniciado o iniciada en las artes de GAS, con qué facilidad pasmosa puedes leer el contenido de un archivo CSV:

  1. El método getBlob() de la clase File nos permite acceder al contenido del archivo por medio de un objeto de la clase Blob. ¿Y qué demonios es un blob? Pues te tengo que remitir nuevamente (ódiame solo lo justo y necesario 😬) al BAS#002, donde también hablamos, ciertamente de modo tangencial, de estos inefables blobs, objetos que encapsulan los datos utilizados por algunos servicios de Apps Script para facilitar su intercambio.
  2. El método getDataAsString() extrae de las entrañas de nuestros dos blobs su contenido CSV como sendas cadenas de texto.
  3. Finalmente, con el método parseCSV() de la útil (valga la redundancia) clase Utilities, por su parte, transformamos el texto extraído en dos arrays (matrices) bidimensionales, personificados en las constantes datos1 y datos2, que ya podemos manipular sin problemas.

Resumiendo, la cosa va así:

De archivo CSV almacenado en Drive a matriz bidimensional de cadenas de texto.

¿Y tanto artículo para esto? ¡Claro, esto es {BAS} 🤓, amigo o amiga!

Sigamos, que nos queda el repecho final. Ahora tenemos que:

  1. Obtener las hojas en las que deben escribirse los datos procedentes de cada archivo CSV (líneas 53 - 55).
  2. Anexar los datos importados a los ya existentes o sobreescribirlos (líneas 57 - 69).
  3. Mostrar el mensaje de estado que informa al usuario de cómo ha ido la cosa (líneas 71 - 77).
    // Obtener hojas destino
    const hojaDestino1 = hdc.getSheetByName(hoja.getRange(AJUSTES.hojaDestino1).getValue());
    const hojaDestino2 = hdc.getSheetByName(hoja.getRange(AJUSTES.hojaDestino2).getValue());
    
    // Anexamos o sobreescribimos datos según ajuste en hoja "Importación"
    const anexar = hoja.getRange(AJUSTES.anexar).getValue();
    if (anexar) {
      hojaDestino1.getRange(hojaDestino1.getLastRow() + 1, 1, datos1.length - 1, datos1[0].length)
        .setValues(datos1.slice(1));
      hojaDestino2.getRange(hojaDestino2.getLastRow() + 1, 1, datos1.length - 1, datos2[0].length)
        .setValues(datos2.slice(1));
    } else {
      hojaDestino1.clearContents()
        .getRange(1, 1, datos1.length, datos1[0].length).setValues(datos1);
      hojaDestino2.clearContents()
        .getRange(1, 1, datos2.length, datos2[0].length).setValues(datos2);
    }

    // Si llegamos aquí es que todo ha ido aparentemente bien
    resultado = '🟢 Importación de datos finalizada';

  }

  // Señalizar fin/resultado del proceso
  hoja.getRange(AJUSTES.resultado).setValue(resultado);

}

Dos cosillas que no habíamos visto hasta ahora en un {BAS}:

📌 Cuando se anexan datos (líneas 60 - 63):

Usamos el método getLastRow() de la clase Sheet del servicio Apps Script de hojas de cálculo para obtener la última fila con datos de la hoja y escribir los nuevos valores bajo ella. No temas, si son necesarias más filas en la hoja los duendes de Google las añadirán automágicamente, no es necesario que nuestro código se preocupe por ello.

El método de JavaScript Array.prototype.slice() sirve para eliminar la primera fila del conjunto de datos que hemos importado con las etiquetas de cada columna, que no parecemos necesitar dado que se supone que ya se ha realizado una importación previa (si algo te está chirriando ahora mismo no te preocupes, luego repensaremos esto) .

📌 Cuando se sobreescriben los datos existentes (líneas 65 - 68):

Se realiza un borrado de todos los valores (pero no del formato) de la hoja de datos con el método clearContents(). La clase Sheet cuenta con algunos métodos adicionales para borrar cosillas, seguro que no te cuesta encontrarlos.

Atención, el método clearContents() devuelve un objeto de tipo Sheet que representa la hoja sobre la que se ha invocado. Por tanto, podemos encadenar limpiamente a continuación el .getRange().setValues() que finalmente escribe los valores en la hoja de cálculo.

Y ya estaría.

¡BAS#003 en acción!

Siguientes pasos

Y como en todas las píldoras {BAS}, te propongo varias mejoras (➕):

Mejora nº 1️⃣:

A diferencia del BAS#002, en esta ocasión sí hemos tomado algunas medidas para evitar errores en tiempo de ejecución (algo muy a tener en cuenta, ¡siempre!). Hemos implementado una comprobación inicial usando las funciones de formato condicional y validación de datos propias de las hojas de cálculo de Google.

Y más tarde, ya en el código del script, nos hemos asegurado también de que los archivos CSV escogidos por el usuario al menos existieran.

Pero pueden pasar muchas otras cosas. Por ejemplo, tal vez alguno de los archivos no contenga datos en formato CSV. O quizás un usuario manazas haya eliminado una de las hojas destino mientras el script se ejecutaba o... ¡quién sabe! (prepárate para lo inesperado).

Como ya te adelantaba en los dos {BAS} anteriores, podemos fallar graciosamente cuando se produzcan errores imprevistos en tiempo de ejecución usando un bloque  try...catch.  Investiga por tu cuenta para saber más... o espera a la próxima píldora {BAS}, donde le hincaremos el diente a esta cuestión tan sugerente.

Mejora nº 2️⃣:

Hay archivos con datos separados por comas (CSV). Pero también por puntos y comas o tabuladores. Y nuestro script solo se lleva bien con los primeros. Por suerte, el método parseCSV() admite un segundo parámetro —opcional— que permite especificar una secuencia delimitadora arbitraria.

¿Te hace modificar este tinglado para que el usuario puede escoger, por ejemplo, entre coma, punto y coma o tabulador? O mejor aún, ¿cómo te las ingeniarías para detectar automáticamente el carácter separador de cada archivo?

Fuente: Documentación oficial de Google Apps Script.

Mejora nº 3️⃣:

Nuestro script importador se ejecuta manualmente. El usuario llega, toca el botoncito y a correr.

Pero a menudo toca subir un puntito en la escala evolutiva de la automatización y hacer que las cosas pasen por sí mismas, de manera periódica. Y para eso están los triggers Apps Script (activadores). Estos activadores se pueden crear y gestionar de manera programática o (más facilito) manualmente, desde el mismísimo editor de Apps Script.

¿Sabrías establecer un activador que ejecutase nuestro script de importación cada lunes (por ejemplo), para que de ese modo solo tuviéramos que preocuparnos de actualizar periódicamente los archivos CSV de la carpeta de Drive?

Venga, una pista...

Mejora nº 4️⃣:

 ¿Y qué tal si tras cada importación nuestro script se ocupase de eliminar todas las filas y columnas sobrantes de las hojas destino? Tener miles de celdas innecesarias no solo es estéticamente deplorable, sino que puede hacer que la hoja de cálculo reaccione de un modo más pesado.

Para lograrlo podrías tomar prestadas las funciones cropToData() y cropToRange() que encontrarás en el archivo code.js de este simple pero utilísimo complemento para hojas de cálculo de Google publicado por el legendario Eric Koleda.

Mejora nº 5️⃣:

Y para terminar, una última propuesta (si quieres más solo tienes que decirlo 😏).

Cuando le pedimos a nuestro script que anexe datos podría darse la situación de que alguna de las hojas destino estuviera vacía. En ese caso, como se descarta la primera fila que contiene las etiquetas de cada columna, nos encontraríamos con una tabla con datos pero sin encabezado. Mal, muy mal. ¿Te animas a meterle mano a las líneas 60 - 63 para solventar esto?

Como ves nos queda mucho de lo que hablar en {BAS}. Pero si quieres ir haciendo camino y te surgen dudas, no tienes más que utilizar la caja de comentarios de aquí abajo. O mejor aún, pasarte por el canal #gas-iniciación de nuestra comunidad Apps Script Ñ, seguro que alguien te echa una mano.