Refresca Hojas de Cálculo Google con Apps Script

Google Apps Script se presenta como una herramienta poderosa basada en JavaScript, diseñada para automatizar tareas y expandir las funcionalidades de las aplicaciones de Google Workspace, como Hojas de Cálculo. Dentro de Hojas de Cálculo, Apps Script abre un abanico de posibilidades, desde la automatización de la entrada de datos y la creación de funciones personalizadas, hasta la construcción de complementos y la integración con otros servicios de Google. Imagina poder automatizar la obtención de datos externos o configurar el envío de correos electrónicos basados en condiciones específicas, todo esto es posible, optimizando flujos de trabajo y elevando la productividad en tus hojas de cálculo.

¿Qué es la función de 'refresh' en Apps Script?

Es importante aclarar que, técnicamente, no existe una función de "refresh" o "refresco" nativa en Apps Script que fuerce la recalculación inmediata y remota de una Hoja de Cálculo de Google como si presionaras un botón de "actualizar" en tu navegador. Sin embargo, lo que sí podemos lograr con Apps Script es simular un "refresh" efectivo para ciertos propósitos, especialmente cuando se trata de la sincronización de datos con herramientas externas como Databox.

El script que vamos a explorar no "refresca" la hoja de cálculo en el sentido de recalcular fórmulas volátiles remotamente (algunas funciones de hoja de cálculo solo se recalculan cuando el documento está abierto en el navegador). En cambio, este script se enfoca en actualizar la marca de tiempo de la última modificación del documento. Esta marca de tiempo es crucial porque es la que utilizan servicios como Databox para detectar si una hoja de cálculo ha sido actualizada y, por lo tanto, necesita sincronizar nuevos datos.

Cómo simular un 'refresh' en Google Sheets usando Apps Script

El método que vamos a implementar utiliza Apps Script para modificar los metadatos del desarrollador de la Hoja de Cálculo de Google. Al cambiar estos metadatos, Google interpreta esto como una edición del documento, lo que provoca que se actualice la marca de tiempo de "última modificación".

Pasos para configurar el script

1. Accede a Apps Script: Abre tu Hoja de Cálculo de Google. En la barra de menú, ve a "Extensiones" > "Apps Script". Esto abrirá el editor de Apps Script en una nueva pestaña.
2. Copia el código: En el editor de scripts, reemplaza el contenido del archivo "Code.gs" con el siguiente código:

function refresh() { // Obtiene la fecha y hora actual. timezone = "GMT+" + new Date().getTimezoneOffset()/60 var now = Utilities.formatDate(new Date(), timezone, "yyyy-MM-dd'T'HH:mm:ss'Z'"); // Abre el archivo de hoja de cálculo activo. const ss = SpreadsheetApp.getActiveSpreadsheet(); // Obtiene el objeto de metadatos del desarrollador. const developerMetaData = ss.getDeveloperMetadata(); // Verifica si nuestra clave de metadatos ya ha sido creada. firstRun = true modifiedTime = false for (var i = 0; i < developerMetaData.length; i++) { if (developerMetaData[i].getKey() == 'modifiedTime') { firstRun = false modifiedTime = developerMetaData[i] } } if (firstRun) { // Agrega la clave 'modifiedTime' y establece el valor a la fecha y hora actual en los metadatos del desarrollador para la hoja de cálculo. ss.addDeveloperMetadata('modifiedTime', now); // console.log(`Key: modifiedTime, Value: ${now}`); } else { // Actualiza el valor de la clave de metadatos. modifiedTime.setValue(now); // console.log(`Key: ${modifiedTime.getKey()}, Value: ${modifiedTime.getValue()}`); } } 

3. Guarda el script: Haz clic en el icono de disquete para guardar el script. Puedes nombrar tu proyecto también.
4. Crea un trigger (activador) por tiempo: En el editor de scripts, haz clic en el icono del reloj ("Activadores") en la barra de herramientas.
5. Añade un nuevo trigger: En el panel de triggers, haz clic en "+ Añadir trigger".
6. Configura el trigger:
   • "Función a ejecutar": Selecciona `refresh`.
   • "Elige qué evento debe activar el trigger": Selecciona "Temporizador".
   • "Tipo de trigger basado en tiempo": Selecciona "Minutos".
   • "Selecciona intervalo en minutos": Elige "Cada 15 minutos" (o el intervalo que prefieras).
7. Guarda el trigger: Haz clic en "Guardar". Es posible que se te solicite autorización para que el script se ejecute en tu cuenta de Google. Sigue los pasos para otorgar los permisos necesarios.

¡Listo! El script `refresh()` se ejecutará automáticamente cada 15 minutos, actualizando la marca de tiempo de tu Hoja de Cálculo de Google.

¿Cómo funciona el script?

El script funciona manipulando los metadatos del desarrollador de la Hoja de Cálculo. Los metadatos del desarrollador son información adicional que los scripts pueden asociar a diferentes partes de una hoja de cálculo (celdas, hojas, etc.). En este caso, estamos utilizando metadatos para almacenar y actualizar una clave llamada `modifiedTime` con la fecha y hora actual cada vez que se ejecuta el script.

Google interpreta cualquier cambio en los metadatos como una modificación del documento, lo que provoca la actualización de la marca de tiempo de "última modificación". Es importante tener en cuenta que la frecuencia con la que Google actualiza esta marca de tiempo no es instantánea, generalmente se actualiza en intervalos de aproximadamente cada tres minutos.

¿Por qué es importante 'refrescar' la marca de tiempo?

Como se mencionó anteriormente, herramientas como Databox utilizan la marca de tiempo de "última modificación" para detectar cambios en tus Hojas de Cálculo de Google. Si actualizas tus datos en Hojas de Cálculo utilizando métodos como formularios de Google, funciones, scripts o integraciones de terceros, es posible que estos cambios no se reflejen inmediatamente en Databox. Esto se debe a que la marca de tiempo no siempre se actualiza de forma fiable con estos métodos.

Al implementar este script, te aseguras de que la marca de tiempo de tu documento se actualice regularmente, lo que permite que Databox (y otras herramientas similares) detecten las actualizaciones y sincronicen los nuevos datos de manera oportuna.

Activadores (Triggers) en Apps Script

En el contexto de Apps Script, los activadores, también conocidos como triggers, son mecanismos que permiten que tus funciones de Apps Script se ejecuten automáticamente en respuesta a ciertos eventos. Estos eventos pueden ser variados, desde la apertura de un documento o la edición de una celda, hasta eventos basados en tiempo, como los que estamos utilizando en este artículo.

Existen dos tipos principales de activadores en Apps Script:

• Activadores Simples: Son un conjunto de funciones reservadas con nombres específicos (como `onOpen(e)`, `onEdit(e)`, etc.) que se ejecutan automáticamente cuando ocurre un evento determinado. Tienen algunas limitaciones en cuanto a los permisos y acciones que pueden realizar.
• Activadores Instalables: Ofrecen mayor flexibilidad y funcionalidad que los activadores simples. Se crean y gestionan a través del panel de activadores en el editor de Apps Script (como hemos hecho en este tutorial). Permiten una mayor variedad de eventos y opciones de configuración.

En nuestro caso, hemos utilizado un activador instalable basado en tiempo para ejecutar la función `refresh()` cada 15 minutos. Esto asegura que la marca de tiempo de la Hoja de Cálculo se actualice periódicamente sin necesidad de intervención manual.

Macros en Hojas de Cálculo Google: Otra forma de automatización

Además de Apps Script y los activadores, Hojas de Cálculo de Google también ofrece macros como una forma sencilla de automatizar tareas repetitivas. Las macros te permiten grabar una secuencia de acciones dentro de la hoja de cálculo (como formatear celdas, insertar fórmulas, etc.) y luego reproducirlas con un atajo de teclado o un elemento de menú.

Aunque las macros son más limitadas en comparación con la flexibilidad de Apps Script, pueden ser útiles para automatizar tareas sencillas sin necesidad de escribir código. Al grabar una macro, Hojas de Cálculo de Google genera automáticamente código Apps Script en segundo plano.

Crear una macro

1. Abre tu Hoja de Cálculo de Google.
2. Ve a "Extensiones" > "Macros" > "Grabar macro".
3. Elige si quieres usar referencias absolutas o relativas para tu macro.
4. Realiza las acciones que deseas grabar en la macro.
5. Haz clic en "Guardar".
6. Nombra la macro y asigna un atajo de teclado (opcional).
7. Haz clic en "Guardar".

Para ejecutar una macro, ve a "Extensiones" > "Macros" y selecciona la macro que deseas ejecutar.

Limitaciones y Consideraciones Importantes

• Recálculo de funciones volátiles: Como se mencionó anteriormente, este script no fuerza el recálculo remoto de funciones volátiles (como `NOW()`, `RAND()`, `TODAY()`, etc.) que solo se recalculan cuando la hoja de cálculo está abierta en el navegador. Para estas funciones, la única forma de forzar la actualización es abrir la hoja de cálculo en un navegador.
• Frecuencia de actualización de la marca de tiempo: La marca de tiempo de "última modificación" no se actualiza instantáneamente. Google suele actualizarla en intervalos de aproximadamente 3 minutos. Por lo tanto, incluso con este script, puede haber un pequeño retraso antes de que la marca de tiempo refleje la última ejecución del script.
• Permisos: El trigger necesita autorización para ejecutarse en tu cuenta de Google. Asegúrate de otorgar los permisos necesarios cuando se te soliciten.

Preguntas Frecuentes (FAQs)

¿Qué es Google Apps Script?

Google Apps Script es un lenguaje de scripting basado en JavaScript para automatizar tareas y extender las funcionalidades de las aplicaciones de Google Workspace.

¿Qué son los metadatos del desarrollador en Hojas de Cálculo de Google?

Son datos adicionales que los scripts pueden asociar a partes de una hoja de cálculo para almacenar información contextual o configuraciones.

¿Qué son los activadores (triggers) en Apps Script?

Son mecanismos que permiten ejecutar funciones de Apps Script automáticamente en respuesta a eventos específicos, como eventos basados en tiempo o acciones del usuario.

¿Puedo forzar un recálculo completo de todas las fórmulas de forma remota con este script?

No directamente. Este script actualiza la marca de tiempo, lo cual es útil para sincronización de datos. Para funciones volátiles que no se recalculan remotamente, generalmente se requiere abrir la hoja de cálculo en un navegador.

¿Con qué frecuencia se actualiza la marca de tiempo de "última modificación" en Hojas de Cálculo de Google?

Aproximadamente cada 3 minutos.

Conclusión

Aunque no existe una función de "refresh" directa para forzar el recálculo remoto en Hojas de Cálculo de Google mediante Apps Script, la técnica de actualizar los metadatos del desarrollador y la marca de tiempo resulta ser una solución efectiva para asegurar la sincronización de datos con herramientas externas como Databox. Al combinar este script con activadores basados en tiempo, puedes automatizar este proceso y mantener tus datos actualizados de manera consistente, optimizando tus flujos de trabajo y garantizando la precisión de tus informes.