Tutorial: crear funciones personalizadas en Excel

  • Artículo

Las funciones personalizadas le permiten añadir a Excel funciones nuevas que se definen en JavaScript como parte de un complemento. Los usuarios de Excel pueden acceder a las funciones personalizadas como lo harían con las funciones nativas de Excel, como SUM(). Puede crear funciones personalizadas que realicen tareas simples como cálculos o tareas más complejas como transmitir datos en tiempo real, desde la web a la hoja de cálculo.

En este tutorial, aprenderá a:

  • Crear un complemento de función personalizada con el Generador de Yeoman para complementos de Office.
  • Usar una función personalizada predefinida para realizar un cálculo sencillo.
  • Crear una función personalizada que solicita los datos desde la web.
  • Crear una función personalizada que transmite datos en tiempo real desde la web.

Requisitos previos

  • Node.js (la última versión de LTS). Visite el sitio deNode.js para descargar e instalar la versión correcta para el sistema operativo.

  • La versión más reciente de Yeoman y Generador de Yeoman para complementos de Office. Para instalar estas herramientas globalmente, ejecute el siguiente comando desde el símbolo del sistema.

    npm install -g yo generator-office

    Nota:

    Incluso si ya ha instalado el generador Yeoman, recomendamos que actualice el paquete de la versión más reciente desde npm.

  • Office está conectado a una suscripción Microsoft 365 (incluido Office en la Web).

    Nota:

    Si aún no tiene Office, puede calificar para una suscripción de desarrollador Microsoft 365 E5 a través del Programa para desarrolladores de Microsoft 365; para obtener más información, consulte las preguntas más frecuentes. Como alternativa, puede registrarse para obtener una evaluación gratuita de 1 mes o comprar un plan de Microsoft 365.

Crear un proyecto de funciones personalizadas

Para empezar, debe crear el proyecto de código para crear el complemento de función personalizada. El generador de Yeoman para complementos de Office configurará el proyecto con algunas funciones personalizadas precompiladas que puede probar. Si ya ha ejecutado el inicio rápido de las funciones personalizadas y ha generado un proyecto, siga usando ese proyecto y avance hasta aquí.

Nota:

Si vuelve a crear el proyecto Yo Office, puede recibir un error porque la memoria caché de Office ya tiene una instancia de una función con el mismo nombre. Para evitarlo, borre la memoria caché de Office antes de ejecutar npm run start.

  1. Ejecute el siguiente comando para crear un proyecto de complemento con el generador Yeoman. Se agregará una carpeta que contiene el proyecto al directorio actual.

    yo office

    Nota:

    Cuando ejecute el comando yo office, es posible que reciba mensajes sobre las directivas de recopilación de datos de Yeoman y las herramientas de la CLI de complementos de Office. Use la información adecuada que se proporciona para responder a los mensajes.

    Cuando se le pida, proporcione la siguiente información para crear el proyecto de complemento.

    • Elija un tipo de proyecto:Excel Custom Functions using a Shared Runtime
    • Elija un tipo de script:JavaScript
    • ¿Cómo desea asignarle el nombre al complemento?My custom functions add-in

    La interfaz de la línea de comandos del generador de complementos de Office de Yeoman solicita proyectos de funciones personalizadas.

    El generador Yeoman creará los archivos de proyecto e instalará componentes auxiliares de Node.

    Nota:

    Si usa Node.js versión 20.0.0 o posterior, es posible que vea una advertencia cuando el generador ejecute la instalación de que tiene un motor no admitido. Estamos trabajando en una solución para esto. Mientras tanto, la advertencia no afecta al generador ni al proyecto que se genera, por lo que se puede omitir.

    Sugerencia

    Se pueden pasar por alto las instrucciones de los pasos siguientes que el generador de Yeoman ofrece después de que se haya creado el proyecto de complemento. Las instrucciones paso a paso de este artículo le dan toda la información que necesitará para completar este tutorial.

  2. Navegue hasta la carpeta principal del proyecto.

    cd "My custom functions add-in"

  3. Cree el proyecto.

    npm run build

    Nota:

    Los complementos de Office deben usar HTTPS y no HTTP, incluso cuando está desarrollando. Si le pedirá que instale un certificado después de ejecutar npm run build, acepte la solicitud para instalar el certificado que proporciona el generador Yeoman.

  4. Inicie el servidor web local, que se ejecuta en Node.js. Puede probar el complemento de la función personalizada en Excel.

Para probar el complemento en Excel en Windows o Mac, ejecute el siguiente comando. Cuando ejecute este comando, el servidor web local se iniciará y Excel se abrirá con el complemento cargado.

npm run start:desktop

Nota:

Los complementos de Office deben usar HTTPS, no HTTP, aunque esté desarrollando. Si se le pide que instale un certificado después de ejecutar uno de los siguientes comandos, acepte el mensaje para instalar el certificado que proporciona el generador de Yeoman. Es posible que también deba ejecutar el símbolo del sistema o el terminal como administrador para que se realicen los cambios.

Probar una función personalizada creada previamente

El proyecto de funciones personalizadas que ha creado contiene algunas funciones personalizadas creadas previamente, definidas en el archivo src/functions/functions.js. El archivo manifest.xml especifica que todas las funciones personalizadas pertenecen al espacio de nombres CONTOSO. Deberá usar el espacio de nombres CONTOSO para acceder a las funciones personalizadas en Excel.

A continuación, pruebe la función personalizada ADD completando los pasos siguientes.

  1. En Excel, vaya a cualquier celda y escriba =CONTOSO. Observe que el menú Autocompletar muestra la lista de todas las funciones en el espacio de nombres CONTOSO.

  2. Ejecute la función CONTOSO.ADD con los números 10 y 200 como parámetros de entrada, especificando el valor =CONTOSO.ADD(10,200) en la celda y presionando la tecla Entrar:

La función personalizadaADD calcula la suma de los dos números que ha especificado y devuelve el resultado 210.

Si el espacio de nombres CONTOSO no está disponible en el menú autocompletar, siga estos pasos para registrar el complemento en Excel.

  1. En Excel, seleccioneComplementos de inicio> y, a continuación, seleccione Más complementos.

  2. En la pestaña MIS COMPLEMENTOS , seleccione Administrar mis complementos y elija Cargar mi complemento.

  3. Elija Examinar... y navegue hasta el directorio raíz del proyecto que creó el generador Yeoman.

  4. Seleccione el archivo manifest.xml y elija Abrir, después, seleccione Subir.

  5. Pruebe la nueva función. En la celda B1 escriba el texto =CONTOSO.GETSTARCOUNT("OfficeDev", "Excel-Custom-Functions") y presione Entrar. Debe ver que el resultado en la celda B1 es el número de estrellas actual proporcionado al repositorio de Github de funciones personalizadas de Excel.

Nota:

Consulte la sección Solución de problemas de este artículo si se producen errores al transferir localmente el complemento.

Crear una función personalizada que solicita los datos desde la web

La integración de datos desde la Web es una buena forma de extender Excel mediante funciones personalizadas. A continuación, deberá crear una función personalizada llamada getStarCount que muestre cuántas estrellas posee un repositorio de Github determinado.

  1. En el proyecto Complemento Mis funciones personalizadas, busque el archivo ./src/functions/functions.js y ábralo en Editor de código.

  2. En function.js agregue el código siguiente.

    /**
  * Gets the star count for a given Github repository.
  * @customfunction 
  * @param {string} userName string name of Github user or organization.
  * @param {string} repoName string name of the Github repository.
  * @return {number} number of stars given to a Github repository.
  */
  async function getStarCount(userName, repoName) {
    try {
      //You can change this URL to any web request you want to work with.
      const url = "https://api.github.com/repos/" + userName + "/" + repoName;
      const response = await fetch(url);
      //Expect that status code is in 200-299 range
      if (!response.ok) {
        throw new Error(response.statusText)
      }
        const jsonResponse = await response.json();
        return jsonResponse.watchers_count;
    }
    catch (error) {
      return error;
    }
  }

  3. Ejecute el siguiente comando para volver a compilar el proyecto.

    npm run build

  4. Complete los pasos siguientes (para Excel en la web, Windows o Mac) para volver a registrar el complemento en Excel. Debe completar estos pasos antes de que esté disponible la nueva función.

  1. Cierre Excel y vuelva a abrirlo.

  2. En la cinta de Opciones de Excel, seleccioneComplementos deinicio>.

  3. En la sección Complementos para desarrolladores , seleccione El complemento Mis funciones personalizadas para registrarlo.

    El cuadro de diálogo Mis complementos que muestra los complementos activos, con el botón Mi complemento de función personalizada resaltado.

  4. Pruebe la nueva función. En la celda B1, escriba el texto =CONTOSO. GETSTARCOUNT("OfficeDev", "Office-Add-in-Samples") y presione Entrar. Debería ver que el resultado en la celda B1 es el número actual de estrellas que se proporcionan al repositorio Office-Add-in-Samples.

Nota:

Consulte la sección Solución de problemas de este artículo si se producen errores al transferir localmente el complemento.

Crear una función personalizada asincrónica de transmisión de datos

La función getStarCount devuelve el número de estrellas que un repositorio tiene en un momento específico en el tiempo. Las funciones personalizadas también devuelven datos que cambian continuamente. Estas funciones se denominan funciones de transmisión de datos. Deben incluir un parámetro invocation que haga referencia a la celda que llamó a la función. El parámetro invocation se usa para actualizar el contenido de la celda en cualquier momento.

En el ejemplo de código siguiente, observará que hay dos funciones, currentTime y clock. La función currentTime es una función estática que no usa la transmisión de datos. Devuelve la fecha como una cadena. La función clock usa la función currentTime para ofrecer cada segundo la hora actualizada a una celda en Excel. Usa invocation.setResult para ofrecer la hora a la celda de Excel y invocation.onCanceled para controlar la cancelación de la función.

El proyecto de Complemento Mis funciones personalizadas ya contiene las dos funciones siguientes en el archivo ./src/functions/functions.js.

/**
 * Returns the current time
 * @returns {string} String with the current time formatted for the current locale.
 */
function currentTime() {
  return new Date().toLocaleTimeString();
}
    
/**
 * Displays the current time once a second
 * @customfunction
 * @param {CustomFunctions.StreamingInvocation<string>} invocation Custom function invocation
 */
function clock(invocation) {
  const timer = setInterval(() => {
    const time = currentTime();
    invocation.setResult(time);
  }, 1000);
    
  invocation.onCanceled = () => {
    clearInterval(timer);
  };
}

Para probar las funciones, escriba el texto =CONTOSO.CLOCK() en la celda C1 y presione Entrar. Debe ver la fecha actual, que transmite una actualización cada segundo. Este reloj es solo un temporizador en un bucle, pero puede usar la misma idea para crear un temporizador con funciones más complejas que hagan solicitudes web para datos en tiempo real.

Solución de problemas

Puede encontrar problemas si ejecuta el tutorial varias veces. Si la memoria caché de Office ya tiene una instancia de una función con el mismo nombre, el complemento recibe un error al transferir localmente.

Para evitar este conflicto, borre la memoria caché de Office antes de ejecutar npm run start. Si el proceso de npm ya se está ejecutando, escriba npm stop, borre la caché de Office y reinicie npm.

Un mensaje de error en Excel titulado

Pasos siguientes

¡Enhorabuena! Ha creado un nuevo proyecto de funciones personalizadas, ha probado una función predefinida, ha creado una función personalizada que solicita datos de la web y una función personalizada que transmite esos datos. A continuación, obtenga información sobre cómo compartir datos de funciones personalizadas con el panel de tareas.