Funciones personalizadas en Hojas de cálculo de Google

Hojas de cálculo de Google ofrece cientos de funciones integradas, como AVERAGE, SUM y VLOOKUP. Si estas no son suficientes para tus necesidades, puedes usar Apps Script para escribir funciones personalizadas y, luego, usarlas en Hojas de cálculo como si fueran funciones integradas.

Para ver ejemplos de funciones personalizadas, consulta los siguientes instructivos:

• Calcula el precio de venta de artículos con descuento (guía de inicio rápido)
• Calcula un descuento de precios escalonados
• Calcula la distancia en automóvil y convierte metros a millas
• Resume datos de varias hojas
• Verifica la veracidad de las afirmaciones con un agente de IA del ADK y un modelo de Gemini

Cómo comenzar

Las funciones personalizadas se crean con JavaScript estándar. Si es la primera vez que usas JavaScript, Codecademy ofrece un curso para principiantes. Este curso no fue desarrollado por Google ni está asociado con la empresa.

Esta es una función personalizada, llamada DOUBLE, que multiplica un valor de entrada por 2:

/**
 * Multiplies an input value by 2.
 * @param {number} input The number to double.
 * @return The input multiplied by 2.
 * @customfunction
*/
function DOUBLE(input) {
  return input * 2;
}

Si no sabes cómo escribir JavaScript y no tienes tiempo para aprender, consulta la tienda de complementos de Google Workspace para ver si alguien más ya creó la función personalizada que necesitas.

Crea una función personalizada

Para escribir una función personalizada, haz lo siguiente:

1. Crea o abre una hoja de cálculo en Hojas de cálculo.
2. Selecciona el elemento de menú Extensiones > Apps Script.
3. Borra cualquier código que aparezca en el editor de secuencias de comandos. Para la función DOUBLE que se mostró anteriormente, copia y pega el código en el editor de secuencias de comandos.
4. En la parte superior, haz clic en Guardar save.

Ahora puedes usar la función personalizada.

Obtén una función personalizada de Google Workspace Marketplace

Google Workspace Marketplace ofrece varias funciones personalizadas como complementos de Google Workspace para Hojas de cálculo. Para usar o explorar estos complementos, haz lo siguiente:

1. Crea o abre una hoja de cálculo en Hojas de cálculo.
2. En la parte superior, haz clic en Complementos > Obtener complementos.
3. Una vez que se abra Google Workspace Marketplace, haz clic en el cuadro de búsqueda en la esquina superior derecha.
4. Escribe "función personalizada" y presiona Intro.
5. Si encuentras un complemento de función personalizada que te interese, haz clic en Instalar para instalarlo.
6. Es posible que un diálogo te indique que el complemento requiere autorización. Si es así, lee atentamente el aviso y, luego, haz clic en Permitir.
7. El complemento estará disponible en la hoja de cálculo. Para usar el complemento en otra hoja de cálculo, abre la otra hoja y, en la parte superior, haz clic en Complementos > Administrar complementos. Busca el complemento que quieres usar y haz clic en Opciones more_vert > Usar en este documento.

Usa una función personalizada

Una vez que escribas una función personalizada o instales una desde Google Workspace Marketplace, se usará como una función integrada:

1. Haz clic en la celda en la que quieres usar la función.
2. Escribe un signo igual (=) seguido del nombre de la función y cualquier valor de entrada — por ejemplo, =DOUBLE(A1) — y presiona Intro.
3. La celda muestra Loading... por un momento y, luego, muestra el resultado.

Lineamientos para funciones personalizadas

Antes de escribir tu propia función personalizada, debes conocer algunos lineamientos.

Nombres de funciones

Además de las convenciones estándar para nombrar funciones de JavaScript, ten en cuenta lo siguiente:

• El nombre de una función personalizada debe ser distinto de los nombres de las funciones integradas como SUM().
• El nombre de una función personalizada no puede terminar con un guion bajo (_), que denota una función privada en Apps Script.
• El nombre de una función personalizada debe declararse con la sintaxis function myFunction(), no var myFunction = new Function().
• El uso de mayúsculas y minúsculas no importa, aunque los nombres de las funciones de hojas de cálculo suelen estar en mayúsculas.

Argumentos

Al igual que una función integrada, una función personalizada puede tomar argumentos como valores de entrada:

• Si llamas a tu función con una referencia a una sola celda como argumento (como =DOUBLE(A1)), el argumento es el valor de la celda.

• Si llamas a tu función con una referencia a un rango de celdas como argumento (como =DOUBLE(A1:B10)), el argumento es un array bidimensional de los valores de las celdas. Por ejemplo, en la siguiente captura de pantalla, Apps Script interpreta los argumentos en =DOUBLE(A1:B2) como double([[1,3],[2,4]]). Ten en cuenta que el código de muestra para DOUBLE que se describió anteriormente debería modificarse para aceptar un array como entrada.

  
  

• Los argumentos de las funciones personalizadas deben ser deterministas. Es decir, las funciones integradas de hojas de cálculo que muestran un resultado diferente cada vez que se calculan, como NOW() o RAND(), no se permiten como argumentos para una función personalizada. Si una función personalizada intenta mostrar un valor basado en una de estas funciones integradas volátiles, muestra Loading... de forma indefinida.

• Para activar el recálculo, debes pasar una celda o un rango de celdas al que se hace referencia directamente como argumento a la función personalizada. De lo contrario, la función personalizada no se vuelve a calcular hasta que editas la función o cambias el valor de una celda a la que se hace referencia. Si usas el método getValue en funciones personalizadas, ten en cuenta que el rango al que se hace referencia no se pasa directamente como argumento a la función personalizada.

Valores que se muestran

Cada función personalizada debe mostrar un valor para mostrar, de modo que:

• Si una función personalizada muestra un valor, este se muestra en la celda desde la que se llamó a la función.
• Si una función personalizada muestra un array bidimensional de valores, los valores se desbordan en las celdas adyacentes, siempre que estén vacías. Si esto hace que el array reemplace el contenido de la celda existente, la función personalizada arroja un error. Para ver un ejemplo, consulta la sección sobre cómo optimizar funciones personalizadas.
• Una función personalizada no puede afectar a celdas que no sean aquellas a las que muestra un valor. En otras palabras, una función personalizada no puede editar celdas arbitrarias, solo las celdas desde las que se llama y sus celdas adyacentes. Para editar celdas arbitrarias, usa un menú personalizado para ejecutar una función en su lugar.
• Una llamada a función personalizada debe mostrarse en un plazo de 30 segundos. Si no lo hace, la celda muestra #ERROR! y la nota de la celda es Exceeded maximum execution time (line 0).

Tipos de datos

Hojas de cálculo almacena datos en diferentes formatos según la naturaleza de los datos. Cuando estos valores se usan en funciones personalizadas, Apps Script los trata como el tipo de datos adecuado en JavaScript. Estas son las áreas de confusión más comunes:

• Las horas y las fechas en Hojas de cálculo se convierten en objetos Date en Apps Script. Si la hoja de cálculo y la secuencia de comandos usan zonas horarias diferentes (un problema poco común), la función personalizada debe compensarlo.
• Los valores de duración en Hojas de cálculo también se convierten en Date objetos, pero trabajar con ellos puede ser complicado.
• Los valores porcentuales en Hojas de cálculo se convierten en números decimales en Apps Script. Por ejemplo, una celda con un valor de 10% se convierte en 0.1 en Apps Script.

Autocompletar

Hojas de cálculo admite la función de autocompletar para funciones personalizadas de forma similar a las funciones integradas. A medida que escribes un nombre de función en una celda, ves una lista de funciones integradas y personalizadas que coinciden con lo que ingresas.

Las funciones personalizadas aparecen en esta lista si su secuencia de comandos incluye una JSDoc @customfunction, como en el DOUBLE() ejemplo.

/**
 * Multiplies the input value by 2.
 *
 * @param {number} input The value to multiply.
 * @return {number} The input multiplied by 2.
 * @customfunction
 */
function DOUBLE(input) {
  return input * 2;
}

Avanzado

En esta sección, se tratan temas avanzados de funciones personalizadas.

Usa servicios de Google Apps Script

Las funciones personalizadas pueden llamar a ciertos servicios de Apps Script para realizar tareas más complejas. Por ejemplo, una función personalizada puede llamar al servicio Language para traducir una frase en inglés al español.

A diferencia de la mayoría de los otros tipos de Apps Script, las funciones personalizadas nunca les piden a los usuarios que autoricen el acceso a datos personales. Por lo tanto, solo pueden llamar a servicios que no tienen acceso a datos personales, específicamente los siguientes:

Si tu función personalizada arroja el mensaje de error You do not have permission to call X service., el servicio requiere autorización del usuario y, por lo tanto, no se puede usar en una función personalizada.

Para usar un servicio que no esté en la lista anterior, crea un menú personalizado que ejecute una función de Apps Script en lugar de escribir una función personalizada. Una función que se activa desde un menú le solicita autorización al usuario si es necesario y, por lo tanto, puede usar todos los servicios de Apps Script.

Comparte funciones personalizadas

Las funciones personalizadas comienzan vinculadas a la hoja de cálculo en la que se crearon. Esto significa que una función personalizada escrita en una hoja de cálculo no se puede usar en otras hojas de cálculo, a menos que uses uno de los siguientes métodos:

• Haz clic en Extensiones > Apps Script para abrir el editor de secuencias de comandos. Luego, copia el texto de la secuencia de comandos de la hoja de cálculo original y pégalo en el editor de secuencias de comandos de otra hoja de cálculo.
• Para crear una copia de la hoja de cálculo que contiene la función personalizada, haz clic en Archivo > Crear una copia. Cuando se copia una hoja de cálculo, también se copian las secuencias de comandos adjuntas. Cualquier persona que tenga acceso a la hoja de cálculo puede copiar la secuencia de comandos. (Los colaboradores que solo tienen acceso de lectura no pueden abrir el editor de secuencias de comandos en la hoja de cálculo original. Sin embargo, cuando crean una copia, se convierten en propietarios de la copia y pueden ver la secuencia de comandos).
• Publica la secuencia de comandos como un complemento del editor de Hojas de cálculo Editor add-on.

Todas las secuencias de comandos vinculadas a contenedores comparten las mismas listas de acceso que sus contenedores. Esto significa que cualquier persona con permiso para editar la hoja de cálculo también puede editar cualquier código de Apps Script adjunto a ella. Para obtener más información, consulta el acceso a secuencias de comandos vinculadas.

Optimización

Cada vez que se usa una función personalizada en una hoja de cálculo, Hojas de cálculo realiza una llamada independiente al servidor de Apps Script. Si tu hoja de cálculo contiene docenas (o cientos, o miles) de llamadas a funciones personalizadas, este proceso puede ser lento. Es posible que algunos proyectos con muchas funciones personalizadas o funciones personalizadas complejas experimenten una demora temporal en las ejecuciones.

Por lo tanto, si planeas usar una función personalizada varias veces en un rango grande de datos, considera modificar la función para que acepte un rango como entrada en forma de un array bidimensional y, luego, muestre un array bidimensional que pueda desbordarse en las celdas adecuadas.

Por ejemplo, la función DOUBLE() que se mostró anteriormente se puede volver a escribir para aceptar una sola celda o un rango de celdas de la siguiente manera:

/**
 * Multiplies the input value by 2.
 *
 * @param {number|Array<Array<number>>} input The value or range of cells
 *     to multiply.
 * @return The input multiplied by 2.
 * @customfunction
 */
function DOUBLE(input) {
  return Array.isArray(input) ?
      input.map(row => row.map(cell => cell * 2)) :
      input * 2;
}

Este enfoque usa el map método del objeto Array de JavaScript en el array bidimensional de celdas para obtener cada fila y, luego, para cada fila, vuelve a usar map para mostrar el doble del valor de cada celda. Muestra un array bidimensional que contiene los resultados. De esta manera, puedes llamar a DOUBLE solo una vez, pero hacer que se calcule para una gran cantidad de celdas a la vez, como se muestra en la siguiente captura de pantalla. Podrías lograr lo mismo con instrucciones if anidadas en lugar de la llamada map.

Del mismo modo, la siguiente función personalizada recupera de manera eficiente contenido en vivo de Internet y usa un array bidimensional para mostrar dos columnas de resultados con una sola llamada a función. Si cada celda requiriera su propia llamada a función, la operación tardaría mucho más tiempo, ya que el servidor de Apps Script tendría que descargar y analizar el feed XML cada vez.

/**
 * Show the title and date for the first page of posts on the
 * Developer blog.
 *
 * @return Two columns of data representing posts on the
 *     Developer blog.
 * @customfunction
 */
function getBlogPosts() {
  var array = [];
  var url = 'https://gsuite-developers.googleblog.com/atom.xml';
  var xml = UrlFetchApp.fetch(url).getContentText();
  var document = XmlService.parse(xml);
  var root = document.getRootElement();
  var atom = XmlService.getNamespace('http://www.w3.org/2005/Atom');
  var entries = document.getRootElement().getChildren('entry', atom);
  for (var i = 0; i < entries.length; i++) {
    var title = entries[i].getChild('title', atom).getText();
    var date = entries[i].getChild('published', atom).getValue();
    array.push([title, date]);
  }
  return array;
}

Estas técnicas se pueden aplicar a casi cualquier función personalizada que se use de forma repetida en una hoja de cálculo, aunque los detalles de implementación varían según el comportamiento de la función.