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

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


17 respuestas a “Sincronizar datos entre archivos en Google Spreadsheets de Google Drive”

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

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

      • Hola, es excelente esta solución, me ahorró una inmensidad de trabajo. Tengo el mismo problema que comentaba Luis ¿encontraste alguna respuesta para ello? Necesito tomar datos de 135 libros distintos, que tienen la información dispuesta en tablas idénticas entre si, estos datos deberían quedar conformando una única tabla en el libro de destino.

        Mil gracias!

  13. Encontré una manera de avanzar en el problema que menciona Luis. Al parecer, la clave está en la fórmula final:

    en:

    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 );
    }
    }

    borré:

    «sheet.clearContents();»

    para que no borre los datos de la hoja al ejecutar el código.

    Después, borré todo lo que sigue, ya que no me interesa tener estos valores en la hoja:

    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(«—–«);

    Luego reemplacé el «8» en

    sheet.getRange(8, 1, data.length, data[0].length).setValues( data );

    por el número de fila en el cual empieza a ingresarse la información del próximo archivo.

    Hasta ahora me viene funcionando, pero hay un problema que no pude solucionar: debo utilizar un código nuevo para cada archivo del cual voy a tomar información, ya que si pusiera varias veces la fórmula importRanges, los datos importados irían siempre al lugar indicado en sheet.getRange.

    En mi caso tengo 135 archivos, lo cual hace de esto algo bastante engorroso. Pero al menos ya tenemos algo del camino salvado.

Responder a Axtapekoob Cancelar la respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *