Recevoir et gérer des données à l’aide de fonctions personnalisées

L’une des façons dont les fonctions personnalisées améliorent la puissance d’Excel consiste à recevoir des données à partir d’emplacements autres que le classeur, tels que le web ou un serveur (par le biais de WebSockets). Vous pouvez demander des données externes via une API comme Fetch ou en utilisant XmlHttpRequest(XHR), une API web standard qui émet des requêtes HTTP pour interagir avec les serveurs.

Importante

Notez que les fonctions personnalisées Excel sont disponibles sur les plateformes suivantes.

• Office sur le web
• Office pour Windows
  • Abonnement Microsoft 365
  • retail perpetual Office 2016 et versions ultérieures
  • avec licence en volume avec licence perpétuelle Office 2021 et versions ultérieures
• Office sur Mac

Les fonctions personnalisées Excel ne sont actuellement pas prises en charge dans les éléments suivants :

• Office sur iPad
• versions perpétuelles avec licence en volume d’Office 2019 ou version antérieure sur Windows

Fonctions qui retournent des données provenant de sources externes

Si une fonction personnalisée récupère des données d’une source externe comme le web, elle doit :

1. Renvoyer un code JavaScript Promise à Excel.
2. Résolvez le Promise avec la valeur finale à l’aide de la fonction de rappel.

Exemple de récupération

Dans l’exemple de code suivant, la webRequest fonction accède à une API externe hypothétique qui effectue le suivi du nombre de personnes actuellement sur la Station spatiale internationale. La fonction retourne un code JavaScript Promise et utilise fetch pour demander des informations à l’API hypothétique. Les données obtenues sont transformées en JSON et la names propriété est convertie en chaîne, qui est utilisée pour résoudre la promesse.

Lorsque vous développez vos propres fonctions, vous souhaitez peut-être effectuer une action si la requête Web ne se termine pas en temps voulu ou envisager de regrouper plusieurs demandes API.

/**
 * Requests the names of the people currently on the International Space Station.
 * Note: This function requests data from a hypothetical URL. In practice, replace the URL with a data source for your scenario.
 * @customfunction
 */
function webRequest() {
  let url = "https://www.contoso.com/NumberOfPeopleInSpace"; // This is a hypothetical URL.
  return new Promise(function (resolve, reject) {
    fetch(url)
      .then(function (response){
        return response.json();
        }
      )
      .then(function (json) {
        resolve(JSON.stringify(json.names));
      })
  })
}

Notes

L’utilisation de fetch permet d’éviter les rappels imbriqués et peut être préférable à XHR dans certains cas.

Exemple avec XHR

Dans l’exemple de code suivant, la getStarCount fonction appelle l’API Github pour découvrir la quantité d’étoiles attribuées au dépôt d’un utilisateur particulier. Il s’agit d’une fonction asynchrone qui retourne un Code JavaScript Promise. Lorsque des données sont obtenues à partir de l’appel web, la promesse est résolue, ce qui retourne les données à la cellule.

/**
 * Gets the star count for a given Github organization or user and repository.
 * @customfunction
 * @param userName string name of organization or user.
 * @param repoName string name of the repository.
 * @return number of stars.
 */
async function getStarCount(userName: string, repoName: string) {

  const url = "https://api.github.com/repos/" + userName + "/" + repoName;

  let xhttp = new XMLHttpRequest();

  return new Promise(function(resolve, reject) {
    xhttp.onreadystatechange = function() {
      if (xhttp.readyState !== 4) return;

      if (xhttp.status == 200) {
        resolve(JSON.parse(xhttp.responseText).watchers_count);
      } else {
        reject({
          status: xhttp.status,

          statusText: xhttp.statusText
        });
      }
    };

    xhttp.open("GET", url, true);

    xhttp.send();
  });
}

Créer une fonction de diffusion en continu

Les fonctions personnalisées de diffusion vous aident à copier des données vers des cellules à plusieurs reprises, sans exiger qu’un utilisateur actualise explicitement quoi que ce soit. Cela peut s’avérer utile pour vérifier les données actives d’un service en ligne, comme la fonction dans le didacticiel sur les fonctions personnalisées.

Pour déclarer une fonction de diffusion en continu, vous pouvez utiliser l’une des deux options suivantes.

• Balise @streaming JSDoc.
• Paramètre CustomFunctions.StreamingInvocation d’appel.

L’exemple de code suivant est une fonction personnalisée qui ajoute un nombre au résultat chaque seconde. Notez ce qui suit à propos de ce code.

• Excel affiche chaque nouvelle valeur automatiquement à l’aide de la méthode setResult.
• Le deuxième paramètre d’entrée invocation, n’est pas visible aux utilisateurs finaux dans Excel lorsqu’ils sélectionnent la fonction à partir du menu de saisie semi-automatique.
• Le onCanceled rappel définit la fonction qui s’exécute lorsque la fonction est annulée.
• La diffusion en continu n’est pas nécessairement liée à la création d’une requête web. Dans ce cas, la fonction n’effectue pas de requête web, mais obtient toujours des données à intervalles définis. Elle nécessite donc l’utilisation du paramètre de diffusion en continu invocation .

/**
 * Increments a value once a second.
 * @customfunction INC increment
 * @param {number} incrementBy Amount to increment
 * @param {CustomFunctions.StreamingInvocation<number>} invocation
 */
function increment(incrementBy, invocation) {
  let result = 0;
  const timer = setInterval(() => {
    result += incrementBy;
    invocation.setResult(result);
  }, 1000);

  invocation.onCanceled = () => {
    clearInterval(timer);
  };
}

Notes

Pour obtenir un exemple de retour d’un tableau de débordement dynamique à partir d’une fonction de diffusion en continu, consultez Retourner plusieurs résultats à partir de votre fonction personnalisée : Exemples de code.

Annuler une fonction

Excel annule l’exécution d’une fonction dans les situations suivantes.

• L’utilisateur modifie ou supprime une cellule qui fait référence à la fonction.
• Un des arguments (entrées) de la fonction est modifié. Dans ce cas, un appel de nouvelle fonction est déclenché en plus de l’annulation.
• L’utilisateur déclenche manuellement le recalcul. Dans ce cas, un appel de nouvelle fonction est déclenché en plus de l’annulation.

Vous pouvez également définir une valeur de diffusion en continu par défaut pour gérer les cas lorsqu’une demande est effectuée, mais que vous êtes en mode hors connexion.

Notes

Il existe également une catégorie de fonctions appelées fonctions annulables qui utilisent la @cancelable balise JSDoc. Les fonctions annulables permettent de terminer une requête web au milieu de la demande.

Une fonction de diffusion en continu ne peut pas utiliser la @cancelable balise, mais les fonctions de diffusion en continu peuvent inclure une fonction de onCanceled rappel. Seules les fonctions personnalisées asynchrones qui retournent une valeur peuvent utiliser la @cancelable balise JSDoc. Consultez Autogenerate JSON metadata: @cancelable pour en savoir plus sur la @cancelable balise.

Utiliser un paramètre d’appel

Par défaut, le paramètre invocation est le dernier de toute fonction personnalisée. Le invocation paramètre donne un contexte sur la cellule (par exemple, son adresse et son contenu) et vous permet d’utiliser la méthode et onCanceled l’événement setResult pour définir ce que fait une fonction lorsqu’elle diffuse (setResult) ou est annulée (onCanceled).

Le gestionnaire d’appel doit être de type CustomFunctions.StreamingInvocation ou CustomFunctions.CancelableInvocation traiter les demandes web.

Consultez Paramètre d’appel pour en savoir plus sur les autres utilisations potentielles de l’argument invocation et comment il correspond à l’objet Invocation .

Réception de données via WebSockets

Dans une fonction personnalisée, vous pouvez utiliser WebSockets afin d’échanger des données avec un serveur via une connexion permanente. À l’aide de WebSockets, votre fonction personnalisée peut ouvrir une connexion avec un serveur, puis recevoir automatiquement des messages du serveur lorsque certains événements se produisent, sans avoir à interroger explicitement le serveur pour obtenir des données.

Exemple avec WebSockets

L’exemple de code suivant établit une connexion WebSocket, puis consigne chaque message entrant provenant du serveur.

let ws = new WebSocket('wss://bundles.office.com');

ws.onmessage(message) {
    console.log(`Received: ${message}`);
}

ws.onerror(error){
    console.err(`Failed: ${error}`);
}

Étapes suivantes

• En savoir plus sur les différents types de paramètres que vos fonctions peuvent utiliser.
• Découvrez comment traiter par lots plusieurs appels d’API.

Voir aussi

• Valeurs volatiles dans les fonctions
• Créer des métadonnées JSON pour des fonctions personnalisées
• Créer manuellement des métadonnées JSON pour les fonctions personnalisées
• Créer des fonctions personnalisées dans Excel
• Didacticiel de fonctions personnalisées Excel