Do not speak Spanish? Read this post in English.

Jueves, 07 d febrero d 2013.

Sincronizar datos entre archivos en Google Spreadsheets de Google Drive

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<importRanges.length;i++) {
    importExternalData(importRanges[i].destinySheetName,importRanges[i].fromFileKey,importRanges[i].fromSheet,importRanges[i].fromRange);
  }
  
}


function importExternalData(destinySheetName,fromFileKey,fromSheet,fromRange)
{
  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(destinySheetName);
  if (sheet == null )
  {
    ss.insertSheet(destinySheetName);
    sheet = ss.getSheetByName(destinySheetName);
  }
  
  var data = SpreadsheetApp.openById(fromFileKey).getSheetByName(fromSheet).getRange(fromRange).getValues();
  sheet.clearContents();
  sheet.getRange("A1").setValue("Imported Data");
  sheet.getRange("A2").setValue("File:");
  sheet.getRange("B2").setValue(fromFileKey);
  sheet.getRange("A3").setValue("Sheet:");
  sheet.getRange("B3").setValue(fromSheet);
  sheet.getRange("A4").setValue("Range:");
  sheet.getRange("B4").setValue(fromRange);
  sheet.getRange("A6").setValue("-----");
  if ( data[0] ) {
    sheet.getRange(8, 1, data.length, data[0].length).setValues( data );
  }
}

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í.

Temas Relacionados: analitica web tutoriales

Autor:

14 Comentarios para “Sincronizar datos entre archivos en Google Spreadsheets de Google Drive”

  1. manuel dice:

    Hola Iñaki.

    Genial post. Muy útil. Lo estoy implementando ahora mismo pero tengo un problema.

    El error es este

    TypeError: No se puede llamar al método "getRange" de null. (línea XX, archivo "ImportData")

    El método getRange del que habla es el incluido en la "function importExternalData".

    ¿Se te ocurre por qué puede ser?

  2. manuel dice:

    Ya lo he solucionado.

    Quitando los corchetes de "[TuKey]", y "[Nombre Hoja Exacto]" ha realizado el proceso perfectamente.

    Voy a seguir tocando cosillas para dejarlo a mi gusto. Gracias!

  3. llops53 dice:

    Yo tengo que poner en una celda de una hoja de cálculo (1) que ya tengo creada el valor de una celdra de otra hoja de cálculo (2) que también tengo creada y que se actualice al abrir la hoja de cálculo (1).

    No se mucho java pero me veo capaz de aplicar estas funciones pero no de crear la que tiene que sustituir (deduzco) la "destinySheetName" por la celda adecuada.

    ¿Podrías ayudarme?

  4. llops53 dice:

    Aprovechando tus conocimientos:

    ¿Se puede hacer que cuando haya un cambio en una celda de una hoja de cálculo se ponga automáticamente la fecha y hora del día en otra celda de la misma hoja de cálculo?

    Gracias

  5. Xanti dice:

    Muchas gracias Iñaki,

    NO sabes la alegría que me ha dado encontrarme con esta posible solución a mi problema. Todo funcionaba correctamente hasta ayer por la mañana, tengo como 3 importranges en unas 70 páginas que accede diferente gente a ellas y hoy... de pronto no cargan... Llevaban tres meses funcionando perfectamente. Pensaba que sería algún error temporal, pero parece que no. Ya mañana pruebo la posible solución que comentas y te cuento cómo ha ido.

    Muchas gracias.

  6. Xabi dice:

    Hola Iñaki. Como dice Xanti, a mi también se me abrió un mundo de posibilidades nuevo con esta secuencia de comandos. Porque, efectivamente, la funcion importrange falla mucho. Así que, lo primero, muchiiiiiisimas gracias.

    La duda que tengo es al importar dos rangos. Tú explicas cómo hacerlo para importar los dos rangos a la vez, pero yo quería preguntarte si es posible tener dos veces esta secuencia para importar por separado los rangos. Es decir, cuando me interese importo uno y no el otro.

    Lo he probado y creo que no me funciona.

    Un saludo y gracias por el invento.

    P.D. ¿Has escrito algún post en el que expliques para qué sirven las otras dos funciones de la secuencia de comandos (launchImportsByArray y importExternalData)?

  7. JOSE dice:

    Gracias por este post tan útil, Iñaki!!!
    Oye, y si quiero que importe una columna A y la C (pero no la B)...¿cómo habría que hacer?

  8. JOHAN dice:

    hola buen dia, quisiera saber la manera de exportar datos de un spreadsheet a otra spreadsheet que va directamente enlazada a un formulario para imprimirla en un document que luego sera enviado por gmail el proceso como tal esta casi terminado solo me falta exportar de mi base spreadsheet al spreadsheet del google form

  9. Axtapekoob dice:

    Hola Iñaki:

    Este post vale oro. Muchas gracias por compartirlo. Me había estado pegando con mil soluciones distintas para sortear el mal funcionamiento de la función importrange y esto ha sido la solución.

    Te debo una caña o un café

    Un saludo y muchas gracias otra vez

  10. Octavio dice:

    Hola Iñaki!! me da mucho gusto ver tanto soporte desarrollado para Spreadsheets de Google, estoy comenzando a usarlo y utilizo demasiadoa el importrange, sabes si ya se ha solucionado o hay que seguir corriendo lo que memncionas en este post? y otra cosa, como preguntan antes, sabrás como hacerle en caso que quiera importar la columna a y la C pero no la b. Gracias!!!

  11. Filip dice:

    Hola,me gustaría saber si es posible importar tambien el diseño de la hoja ,ya que por el momento no sé si lo estoy haciendo mal,pero me va bastante mal cuando importo la visualización

  12. luis rios dice:

    Hola me sirvió muchísimo el código, ya que estoy empezando a programar scripts para google drive.
    Con este código me funcionó perfecto, el único problema es que tengo que poner la información de distintos archivos uno abajo del otro en la misma hoja y al poner la misma hoja en vez de ponerme la información abajo me la sobre escribe.
    Estuve modificando el codigo pero no me está saliendo.
    Alguna ayuda/idea que puedas brindarme?
    Gracias.
    Saludos,
    Luis.

    • inaki dice:

      Siento no poder ayudarte mucho. Yo te recomendaria que usases la misma función pero aplicandola varias veces con ids y celdas distintas. Seria lo más facil.

Anímate y deja tu comentario