Funciones personalizadas en Hojas de cálculo de Google

Google Sheets ofrece cientos de funciones integradas, como AVERAGE: SUM y VLOOKUP. Cuando no son para tus necesidades, puedes usar Google Apps Script para escribir funciones personalizadas — por ejemplo, para convertir metros en millas o recuperar contenido en vivo de Internet, y luego úsalos en Hojas de cálculo de Google como una función integrada.

Cómo comenzar

Las funciones personalizadas se crean con JavaScript estándar. Si eres nuevo en JavaScript, Codecademy ofrece una excelente curso para principiantes. (Nota: Google no desarrolló este curso ni está asociado con él).

Esta es una función personalizada simple, llamada DOUBLE, que multiplica un valor de valor de entrada de 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 ver si alguien más ya compiló la función personalizada que necesitas.

Crea una función personalizada

Sigue estos pasos para escribir una función personalizada:

  1. Crear o abre una hoja de cálculo en Hojas de cálculo de Google.
  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 anterior, simplemente copia y pega el código en el editor de secuencias de comandos.
  4. En la parte superior, haz clic en Guardar .

Ahora puedes usar la función personalizada.

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

La Google Workspace Marketplace ofrece varias opciones funciona como complementos de Hojas de cálculo de Google. Para usar o explorar estos complementos, sigue estos pasos:

  1. Crear o abre una hoja de cálculo en Hojas de cálculo de Google.
  2. En la parte superior, haz clic en Complementos > Obtener complementos.
  3. Una vez que el elemento Google Workspace Marketplace haz clic en el cuadro de búsqueda de la esquina superior derecha.
  4. Escribe "función personalizada" y presiona Enter.
  5. Si te interesa un complemento de función personalizada, haz clic en Instalar. para instalarlo.
  6. Es posible que aparezca un cuadro de diálogo en el que se te indique que el complemento requiere autorización. Si es así, lee la notificación detenidamente y, luego, haz clic en Permitir.
  7. El complemento estará disponible en la hoja de cálculo. Para usar el complemento en una hoja de cálculo diferente, abre la otra hoja de cálculo y, en la parte superior, haz clic en complementos > Administra complementos. Busca el complemento que quieres usar y haz clic en Opciones > Usar en esta .

Usa una función personalizada

Una vez que hayas escrito una función personalizada o instalado una desde la Google Workspace Marketplace, es tan fácil de usar como 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 mostrará temporalmente Loading... y, luego, mostrará el resultado.

Lineamientos para las funciones personalizadas

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

Nombre

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

  • El nombre de una función personalizada debe ser distinto de los nombres de 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 se debe declarar con la sintaxis function myFunction(), no var myFunction = new Function().
  • El uso de mayúsculas no importa, aunque los nombres de las funciones de la hoja de cálculo tradicionalmente están 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 será el valor de la celda.

  • Si llamas a tu función con una referencia a un rango de celdas como una (como =DOUBLE(A1:B10)), será un argumento bidimensional la matriz de las celdas de salida. Por ejemplo, en la siguiente captura de pantalla, Apps Script interpreta los argumentos de =DOUBLE(A1:B2) como double([[1,3],[2,4]]) Ten en cuenta que el código de muestra para DOUBLE de la parte superior modificado para aceptar un array como entrada.


  • Los argumentos de las funciones personalizadas deterministas. Que funciones integradas de hoja de cálculo que devuelven un resultado diferente cada vez que estos calculan, por ejemplo, NOW() o RAND(), no se permiten como argumentos a una función personalizada. Si una función personalizada intenta mostrar un valor basado en una de estas funciones volátiles integradas, mostrará Loading... indefinidamente.

Valores que se devuelven

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

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

Tipos de datos

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

  • Los horarios y las fechas de Hojas de cálculo pasan a ser Date en Apps Script. Si la hoja de cálculo y los usa diferentes zonas horarias (un problema poco frecuente), la función personalizada que debas compensar.
  • Los valores de duración en Hojas de cálculo también se convierten en objetos Date, pero trabajar con ellas puede ser complicado.
  • Los valores porcentuales en Hojas de cálculo se convierten en números decimales en Apps Script. Para Por ejemplo, una celda con un valor de 10% se convierte en 0.1 en Apps Script.

Autocompletar

Hojas de cálculo de Google admite la función de autocompletar para funciones personalizadas, al igual que para funciones integradas. Como escribe el nombre de una función en una celda, verás una lista de funciones funciones que coincidan con lo que escribas.

Las funciones personalizadas aparecerán en esta lista si su secuencia de comandos incluye un JsDoc @customfunction, como en el ejemplo de DOUBLE() a continuación.

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

Avanzado

Usa los servicios de Google Apps Script

Las funciones personalizadas pueden llamar a determinadas servicios de Google Apps Script para realizar tareas tareas. Por ejemplo, una función personalizada puede llamar al Language para traducir un inglés al español.

A diferencia de la mayoría de los otros tipos de Apps Script, las funciones personalizadas nunca solicitan a los usuarios que autorizar el acceso a los datos personales. En consecuencia, solo pueden llamar a servicios que no tienen acceso a datos personales, en particular los siguientes:

Servicios compatibles Notas
Caché Funciona, pero no es particularmente útil en funciones personalizadas
HTML Puede generar HTML, pero no puede mostrarlo (rara vez útil).
JDBC
Idioma
Bloquear Funciona, pero no es particularmente útil en funciones personalizadas
Maps Puede calcular indicaciones, pero no mostrar mapas.
Propiedades getUserProperties() solo obtiene las propiedades del elemento propietario de la hoja de cálculo. Los editores de hojas de cálculo no pueden establecer propiedades del usuario en un función personalizada.
Spreadsheet Solo lectura (puede usar la mayoría de los métodos get*(), pero no set*()).
No puedes abrir otras hojas de cálculo (SpreadsheetApp.openById() o SpreadsheetApp.openByUrl()).
Recuperación de URL
Utilidades
XML

Si la función personalizada muestra el mensaje de error You do not have permission to call X service., el servicio requerirá la autorización del usuario y, por lo tanto, no se puede usarse en una función personalizada.

Para usar un servicio distinto de los anteriores, crea una menú personalizado que ejecuta una función de Apps Script en lugar de escribir una función personalizada. Función que se activa desde un menú solicitarán autorización al usuario si es necesario y, en consecuencia, pueden usar todas Servicios de Apps Script.

Uso compartido

Las funciones personalizadas comienzan vinculadas a la 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 utilices uno de con los siguientes métodos:

  • Haz clic en Extensiones > Apps Script para abre el editor de secuencias de comandos y, luego, copia 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 > Crea una copia. Cuando se copia una hoja de cálculo, cualquier secuencia de comandos adjunta a también se copia. Cualquier persona que tenga acceso a la hoja de cálculo puede copiar el 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 hacen una copia, se convierten en propietario de la copia y puede ver la secuencia de comandos).
  • Publica la secuencia de comandos como un complemento del editor de Hojas de cálculo de Google.

Optimización

Cada vez que se usa una función personalizada en una hoja de cálculo, Hojas de cálculo de Google crea 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 bastante lento.

Por lo tanto, si planeas usar una función personalizada varias veces en una rango de datos, puedes modificar la función para que acepte un rango de entrada en la forma de un array bidimensional, luego muestra un que pueda desbordarse hacia las celdas correspondientes.

Por ejemplo, la función DOUBLE() que se muestra arriba se puede reescribir para aceptar un 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;
}

El enfoque anterior usa el map del objeto Array de JavaScript para que se realice de manera recursiva. llama a DOUBLE en cada valor del array bidimensional de celdas. Muestra un un array bidimensional que contiene los resultados. De esta manera, puedes llamar a DOUBLE pero haz 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 if anidadas en lugar de la llamada a map).

Del mismo modo, la siguiente función personalizada recupera de forma eficiente contenido en vivo de la Internet y usa un array bidimensional para mostrar dos columnas de resultados con una sola llamada a función. Si cada celda requiere su propia llamada a función, la operación llevaría mucho más tiempo, ya que el servidor de Apps Script debes 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 pueden aplicarse a casi cualquier función personalizada que se use repetidamente a lo largo de una hoja de cálculo, aunque los detalles de implementación varían según el comportamiento de la función.