Hoy vengo con una pequeña solución que he tenido que desarrollar para crear un dashboard que entre otras fuentes recogía datos de distintos archivos de Google Spreadsheets con distintas procedencias.

Normalmente cuando trabajamos con estos ficheros (los mal llamados «Excel de Google»), solemos tener el control sobre todo lo que hacemos en ellos, con lo que las fuentes las colocamos donde deseamos y la introducción de datos o la hacemos nosotros o la realizamos mediante formularios asociados a hojas concretas.

Pero, cuando trabajas con varias campañas a veces te encuentras con que tienes que ir importando datos de unas hojas a otras. Los motivos pueden ser varios:

• Distintas personas con acceso a distintos datos (y por tanto necesidad de compartir unas hojas con unos y otras con otros).
• Comodidad de visualización, sobretodo cuando las fuentes de datos son realmente muchas
• Disponer de acceso solo de visualización a algunas hojas. Muy típico cuando el reporting de los proveedores llega mediante Google Docs compartidos

En estos casos te enfrentas a la necesidad de crear una hoja resumen que te permita unificar todos los datos antes de extrerlos para tus propios propósitos o crear el dashboard en el propigo Google Drive. Ahi te encuentras con que las funciones de Google, no son todo lo buenas que parecen…

Los problemas con la función importrange()

Google Spreadsheets incluye una fantastica función llamada ImportRange que promete realizar importaciones de datos entre las hojas a las que tienes acceso. Esta función se usa de la siguiente forma:

=importrange( KeyDeTuArchivo ; RangoDeDatos )

Donde la Key es la variable «key» que podemos ver en nuestra url al abrir los archivos.

Por ejemplo, si yo abro un documento de Google SpreadSheets me encontraré con algo parecido a esto:

https://docs.google.com/spreadsheet/ccc?key=[Key-del-archivo]#gid=0

El rago es el formato normal de rango en Google Spreadsheets, muy parecido al de Excel. Aquí la única diferencia que existe es que se debe especificar como variable y no directamente. Por ejemplo si quiero acceder a las celdas A1, A2, B1 y B2 indicaré «A1:B2» (entre comillas) y no direactamente A1:B2 como suele hacerse.

Bien, probamos a realizar las importaciones de esta forma y vemos que para importaciones sencillas nos funcionarán pero pronto empezaremos a encontrar 2 tipos de bugs muy frecuentes:

• 1. La función falla aleatoriamente sin estar mal definida. Esto además se vuelve más probable cuantas más veces la usemos. Cuando esto sucede todos nuestros datos empiezan a fallar y no tenemos forma de arreglarlo, pues no hemos hecho nada mal.
• 2. Si accedemos a los datos remotamente (a través de API) estos no se actualizan y podemos llegar a cargar los datos vacíos si pasa mucho tiempo desde la última vez que abrimos el archivo.

En definitiva, con estos dos fallos, esta claro que estas importaciones solo nos sirven para cargar datos puntualmente… algo totalmente estúpido, pues si solo los necesito en un momento dado, tardo menos en hacer un corta y pega que una importación.

Podemos ver como estos fallos se han reportado a Google en varias ocasiones, pero también como no están contemplados en la lista de tareas y bugs, por lo que dudosamente se resolverán pronto.

La solución: Usar Secuencias de comandos

Google Spreadshetts (y todo google Docs) dispone de secuencias de comandos. Un sistema basado en javascript, al que se le han añadido varios objetos para poder interactuar con nuestras hojas de datos. Este sistema tiene acceso a todas las funciones internas de Google Spreadsheets y además nos permite controlar perfectamente las acciones que realizamos.

Lo que he hecho ha sido por lo tanto, crear las importaciones mediante Secuencias de comandos en lugar de con funciones directas. Estas además nos van a permitir actualizar los datos automáticamente cada cierto tiempo gracias a los «Activadores».

Empecemos…

1. Crea tu propia hoja de datos

Entramos en drive.google.com y creamos un archivo del tipo hoja de cálculo.

2. Entra en La consola de Secuencias de comandos

La encontraréis en «Herramientas» >> «Editor de Secuencia de Comandos».

Ahi encontraremos un editor de funciones javascript con una función de prueba ya creado.

3. Copia las funciones para importar datos

Borra la función que aparece y copia las siguientes funciones:

function launchPredefinedImports()
{
  var importRanges = [
    { 
      destinySheetName : "Importacion_1", 
      fromFileKey : "[TuKey]",
      fromSheet : "[Nombre Hoja Exacto]",
      fromRange : "A:E"
    },
  ];
    
  launchImportsByArray( importRanges );
}


function launchImportsByArray( importRanges )
{
  
  for (var i=0;i
Esto generará tres funciones. Todas estan destinadas al mismo proceso, pero están divididas en partes por si alguien quiere basarse en ellas para hacer importaciones un poco más avanzadas.
La función que no sinteresa es "launchPredefinedImports" donde vemos que simplemente se define un objeto de importación y luego se lanza la función para importarlo.
Una vez hayamos creado estas funciones (y le hayamos dado a guardar) ya estamos preparados para realizar nuestras importaciones (y que no fallen).
4- Editamos nuestros objetos a importar
Podemos editar el objeto para indicar los datos de la importación a realizar.
function launchPredefinedImports()
{
  var importRanges = [
    { 
      destinySheetName : "Importacion_1", 
      fromFileKey : "[TuKey]",
      fromSheet : "[Nombre Hoja Exacto]",
      fromRange : "A:E"
    },
  ];
    
  launchImportsByArray( importRanges );
}


destinySheetName: es el nombre de la hoja que se creará automáticamente con la importación que realicemos
fromFileKey: Es la misma key que usabamos para importRange. Tal cual debemos copiarla.
fromSheet: Es la hoja dentro del archivo remoto a la que queremos acceder
fromRange: Es el rango de celdas en formato "A1:B2" que queremos importar. Recordemos que podemos importar columnas enteras con el formato "A:C"

¿Sencillo verdad? Probémoslo. Indica los datos de tu importación, y guarda el resultado. Ahora busca el selector de funciones en la barra de herramientas. Lo encontraréis al final de la segunda fila, junto al icono del "bichito". Ahi debemos seleccionar "launchPredefinedImports" para que se ejecute la función que hemos creado.
5- Realizar la importación manualmente
Por ultimo solo nos faltará darle al icono de Play y la importación se realizará. Podemos volver a nuestra hoja y veremos como se ha creado la nueva hoja con los datos. Si esto no sucediese (a veces pasa que no se actualizan los datos), deberemos recargar la página con F5. 
6- Programando más de una importación de golpe
Si queremos realizar más de una importación (de más de una hoja o varios datos de la misma hoja) solo deberemos añadir nuevos objetos de importación a nuestra función.
Volvemos al editor y copiamos y pegamos nuestro objeto uno detrás de otro...
function launchPredefinedImports()
{
  var importRanges = [
    { 
      destinySheetName : "Importacion_1", 
      fromFileKey : "[TuKey]",
      fromSheet : "[Nombre Hoja Exacto]",
      fromRange : "A:E"
    },
    { 
      destinySheetName : "Importacion_2", 
      fromFileKey : "[TuKey]",
      fromSheet : "[Nombre Hoja Exacto]",
      fromRange : "A:E"
    },
  ];
    
  launchImportsByArray( importRanges );
}

Ahora al darle a play se nos creará una segunda hoja nueva. Podemos realizar este proceso todas las veces que lo necesitemos.
7- Automatizando el proceso
Como decíamos, no solo queremos realizar la importación, sino que esta se actualice sola cada cierto tiempo. Para ello disponemos de los "activadores"
En la página de edición de secuencias de comandos (donde habéis copiado el código) encontraremos los activadores en el menú "Recursos" >> "Activadores del proyecto activo...". También podemos encontrarlo como un icono con forma de reloj en la barra de herramientas de iconos.
Aquí básicamente podemos definir las reglas por las que deben lanzarse algunas funciones. Crearemos un nuevo activador y seleccionaremos nuestra función "launchPredefinedImports". Ahora tendremos dos opciones, dependiendo del uso que vayamos a darle a nuestra hoja:

"De la hoja de Cálculo" >> "Al Abrir". Para que se recarguen los datos siempre que alguien entre en esa hoja
"Basado en el tiempo" >> [Lo que necesites]. Si lo que necesitamos es que ellos solos se actualicen cada cierto tiempo

Conclusión
Todo este post, no es más que un parche a una función que debería hacer esto directamente. No es complejo seguir estos pasos pero sin duda, si importRange termina de funcionar bien algún día este tipo de importaciones de datos se volverán mucho más sencillas.
Espero que a alguno le sirva este post. Se que es rebuscado y muchos no os enfrentareis nunca a esto, pero dado que yo he tenido que haceme las funciones y he visto a mucha gente preguntando sobre el tema he pensado que no era mala idea colgarlo aquí.