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. Cuando estos no sean suficientes para tus necesidades, puedes usar Google Apps Script a fin de escribir funciones personalizadas, por ejemplo, para convertir medidores a millas o recuperar contenido en vivo de Internet, y usarlos en Hojas de cálculo de Google como una función integrada.

Primeros pasos

Las funciones personalizadas se crean con JavaScript estándar. Si eres nuevo en JavaScript, Codecademy ofrece un excelente curso para principiantes. Nota: Este curso no fue desarrollado por Google y no está asociado con Google.

Esta es una función personalizada simple, 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 y descubre si otra persona ya compiló la función personalizada que necesitas.

Crea una función personalizada

Para escribir una función personalizada, sigue estos pasos:

1. Crea 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, solo debes copiar y pegar 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

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

1. Crea o abre una hoja de cálculo en Hojas de cálculo de Google.
2. En la parte superior, haga clic en Complementos > Obtener complementos.
3. Una vez que se abra Google Workspace Marketplace, haz clic en el cuadro de búsqueda de la esquina superior derecha.
4. Escribe “función personalizada” y presiona Intro.
5. Si encuentras un complemento de función personalizada que te interesa, haz clic en Instalar para instalarlo.
6. Un cuadro de diálogo puede indicar que el complemento requiere autorización. Si es así, lee el aviso con atención 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 > Administrar complementos. Busca el complemento que quieres usar y haz clic en Opciones > Usar en este documento.

Usa una función personalizada

Una vez que hayas escrito una función personalizada o instalado una desde elGoogle Workspace Marketplace, es tan fácil de usar como una función integrada:

1. Haz clic en la celda en la que deseas 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. En un momento, la celda mostrará Loading... y, luego, mostrará el resultado.

Lineamientos para 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, ten en cuenta lo siguiente:

• El nombre de una función personalizada debe ser diferente 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 con var myFunction = new Function().
• El uso de mayúsculas no tiene importancia, aunque los nombres de las funciones de las hojas de cálculo suelen ser 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 un argumento (como =DOUBLE(A1:B10)), el argumento será un arreglo bidimensional de los valores de las celdas. 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 arriba deberá modificarse a fin de aceptar un arreglo como entrada.

  
  

• Los argumentos de las funciones personalizadas deben ser deterministas. Es decir, las funciones integradas de la hoja de cálculo que muestran un resultado diferente cada vez que 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 volátiles integradas, mostrará Loading... de manera indefinida.

Valores de retorno

Cada función personalizada debe mostrar un valor para mostrar, como el siguiente:

• Si una función personalizada muestra un valor, el valor se muestra en la celda desde la que se llamó a la función.
• Si una función personalizada muestra un arreglo de valores bidimensional, los valores se desbordan en celdas adyacentes, siempre que esas celdas estén vacías. Si esto hace que el arreglo reemplace el contenido existente de las celdas, la función personalizada arrojará un error. Para ver un ejemplo, consulta la sección sobre cómo optimizar funciones personalizadas.
• Una función personalizada no puede afectar las celdas excepto 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 las celdas adyacentes. Si quieres editar celdas arbitrarias, usa un menú personalizado para ejecutar una función.
• Las llamadas a funciones personalizadas deben mostrarse dentro de los 30 segundos. De lo contrario, la celda mostrará un error: Internal error executing the custom function.

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, Apps Script los trata como el tipo de datos apropiado en JavaScript. Estas son las áreas de confusión más comunes:

• Los horarios y las fechas en Hojas de cálculo se convierten en objetos de Fecha en Apps Script. Si la hoja de cálculo y la secuencia de comandos usan diferentes zonas horarias (un problema poco frecuente), la función personalizada deberá compensar.
• Los valores de duración en Hojas de cálculo también se convierten en objetos Date, pero trabajar con ellos puede ser complicado.
• Los valores porcentuales de 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 de Google admite el autocompletado para funciones personalizadas de la misma manera que para las funciones integradas. Cuando escribas el nombre de una función en una celda, verás una lista de las funciones integradas y personalizadas que coinciden con lo que ingreses.

Las funciones personalizadas aparecerán en esta lista si la secuencia de comandos incluye una etiqueta JsDoc @customfunction, como en el ejemplo de DOUBLE() que se muestra 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

Usar los servicios de Google Apps Script

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

A diferencia de la mayoría de los otros tipos de Apps Script, las funciones personalizadas nunca solicitan a los usuarios que autoricen el acceso a los datos personales. En consecuencia, solo pueden llamar a los servicios que no tienen acceso a datos personales, específicamente lo siguiente:

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

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

Para usar un servicio distinto de los mencionados anteriormente, 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ú solicitará al usuario una autorización si es necesario y, en consecuencia, puede usar todos los servicios de Apps Script.

Uso compartido

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 original y pégalo en el editor de otra hoja de cálculo.
• Haz una copia de la hoja de cálculo que contiene la función personalizada. Para ello, haz clic en Archivo > Crear una copia. Cuando se copia una hoja de cálculo, también se copia cualquier secuencia de comandos adjunta a ella. 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 realizan una copia, se convierten en propietarios de la copia y pueden ver la secuencia de comandos).
• Publique la secuencia de comandos como un complemento de Editor de Hojas de cálculo de Google.

Todas las secuencias de comandos vinculadas a contenedores tienen las mismas listas de acceso que sus contenedores. Esto significa que cualquier persona que tenga permiso para editar la hoja de cálculo puede editar cualquier código de Apps Script adjunto.

Optimización

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

En consecuencia, si planeas usar una función personalizada varias veces en un gran rango de datos, considera modificar la función para que acepte un rango como entrada en el formato de un arreglo bidimensional y, luego, mostrar un arreglo bidimensional que pueda desbordarse en las celdas adecuadas.

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

El enfoque anterior usa el método map del objeto Array de JavaScript para llamar a DOUBLE de forma recursiva en cada valor del arreglo de celdas de dos dimensiones. Muestra un arreglo bidimensional que contiene los resultados. De esta manera, puedes llamar a DOUBLE una sola vez, pero hacer que se calcule para una gran cantidad de celdas a la vez, como se muestra en la siguiente captura de pantalla. (Puedes lograr lo mismo con las declaraciones if anidadas en lugar de la llamada map).

De manera similar, la siguiente función personalizada recupera de manera eficiente el contenido en vivo desde Internet y usa un arreglo bidimensional para mostrar dos columnas de resultados con solo una llamada a una función. Si cada celda requiriera su propia llamada a función, la operación demorarí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 manera repetida en una hoja de cálculo, aunque los detalles de implementación variarán según el comportamiento de la función.