Fonctions personnalisées dans Google Sheets

Google Sheets propose des centaines de fonctions intégrées, telles que AVERAGE, SUM et VLOOKUP. Si elles ne répondent pas à vos besoins, vous pouvez utiliser Apps Script pour écrire des fonctions personnalisées, puis les utiliser dans Sheets comme des fonctions intégrées.

Pour obtenir des exemples de fonctions personnalisées, consultez les tutoriels suivants :

• Calculer le prix de vente des articles soldés (Guide de démarrage rapide)
• Calculer une remise progressive
• Calculer la distance en voiture et convertir les mètres en miles
• Synthétiser les données de plusieurs feuilles
• Vérifier des affirmations avec un agent IA ADK et un modèle Gemini

Premiers pas

Les fonctions personnalisées sont créées à l'aide de JavaScript standard. Si vous ne connaissez pas JavaScript, Codecademy propose un cours pour les débutants. Ce cours n'a pas été développé par Google et n'y est pas associé.

Voici une fonction personnalisée, nommée DOUBLE, qui multiplie une valeur d'entrée par 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 vous ne savez pas comment écrire en JavaScript et que vous n'avez pas le temps d'apprendre, consultez le Google Workspace Marketplace pour voir si quelqu'un d'autre a déjà créé la fonction personnalisée dont vous avez besoin.

Créer une fonction personnalisée

Pour écrire une fonction personnalisée :

1. Créez ou ouvrez une feuille de calcul dans Sheets.
2. Sélectionnez l'élément de menu Extensions > Apps Script.
3. Supprimez le code dans l'éditeur de scripts. Pour la fonction DOUBLE présentée précédemment, copiez et collez le code dans l'éditeur de scripts.
4. En haut, cliquez sur Enregistrer save.

Vous pouvez maintenant utiliser la fonction personnalisée.

Obtenir une fonction personnalisée depuis Google Workspace Marketplace

Google Workspace Marketplace propose plusieurs fonctions personnalisées sous forme de modules complémentaires Google Workspace pour Sheets. Pour utiliser ou explorer ces modules complémentaires :

1. Créez ou ouvrez une feuille de calcul dans Sheets.
2. En haut, cliquez sur Modules complémentaires > Télécharger des modules complémentaires.
3. Une fois Google Workspace Marketplace ouvert, cliquez sur le champ de recherche en haut à droite.
4. Saisissez "fonction personnalisée", puis appuyez sur Entrée.
5. Si vous trouvez un module complémentaire de fonction personnalisée qui vous intéresse, cliquez sur Installer pour l'installer.
6. Une boîte de dialogue peut vous indiquer que le module complémentaire nécessite une autorisation. Si c'est le cas, lisez attentivement la notification, puis cliquez sur Autoriser.
7. Le module complémentaire devient disponible dans la feuille de calcul. Pour l'utiliser dans une autre feuille de calcul, ouvrez-la et, en haut, cliquez sur Modules complémentaires > Gérer les modules complémentaires. Recherchez le module complémentaire que vous souhaitez utiliser, puis cliquez sur Options more_vert > Utiliser dans ce document.

Utiliser une fonction personnalisée

Une fois que vous avez écrit une fonction personnalisée ou que vous en avez installé une depuis Google Workspace Marketplace, elle s'utilise comme une fonction intégrée :

1. Cliquez sur la cellule dans laquelle vous souhaitez utiliser la fonction.
2. Saisissez un signe égal (=), suivi du nom de la fonction et de toute valeur d'entrée — par exemple, =DOUBLE(A1) — et appuyez sur Entrée.
3. La cellule affiche brièvement Loading..., puis renvoie le résultat.

Consignes concernant les fonctions personnalisées

Avant d'écrire votre propre fonction personnalisée, vous devez connaître quelques consignes.

Nommage des fonctions

Outre les conventions standards de nommage des fonctions JavaScript, tenez compte des points suivants :

• Le nom d'une fonction personnalisée doit être différent de celui des fonctions intégrées, comme SUM().
• Le nom d'une fonction personnalisée ne peut pas se terminer par un trait de soulignement (_), qui désigne une fonction privée dans Apps Script.
• Le nom d'une fonction personnalisée doit être déclaré avec la syntaxe function myFunction(), et non var myFunction = new Function().
• La mise en majuscules n'a pas d'importance, bien que les noms des fonctions de feuille de calcul soient traditionnellement en majuscules.

Arguments

Comme une fonction intégrée, une fonction personnalisée peut accepter des arguments en tant que valeurs d'entrée :

• Si vous appelez votre fonction avec une référence à une seule cellule comme argument (par exemple, =DOUBLE(A1)), l'argument est la valeur de la cellule.

• Si vous appelez votre fonction avec une référence à une plage de cellules comme argument (par exemple, =DOUBLE(A1:B10)), l'argument est un tableau bidimensionnel des valeurs des cellules. Par exemple, dans la capture d'écran suivante, les arguments de =DOUBLE(A1:B2) sont interprétés par Apps Script comme double([[1,3],[2,4]]). Notez que l'exemple de code pour DOUBLE décrit précédemment devrait être modifié pour accepter un tableau en entrée.

  
  

• Les arguments des fonctions personnalisées doivent être déterministes. Autrement dit, les fonctions de feuille de calcul intégrées qui renvoient un résultat différent chaque fois qu'elles sont calculées, telles que NOW() ou RAND(), ne sont pas autorisées en tant qu'arguments d'une fonction personnalisée. Si une fonction personnalisée tente de renvoyer une valeur basée sur l'une de ces fonctions intégrées volatiles, elle affiche Loading... indéfiniment.

• Pour déclencher un nouveau calcul, vous devez transmettre directement une cellule ou une plage de cellules référencée en tant qu'argument à la fonction personnalisée. Sinon, la fonction personnalisée n'est pas recalculée tant que vous ne la modifiez pas ou que vous ne changez pas la valeur d'une cellule référencée. Si vous utilisez la méthode getValue dans des fonctions personnalisées, sachez que la plage référencée n'est pas directement transmise en tant qu'argument à la fonction personnalisée.

Valeurs renvoyées

Chaque fonction personnalisée doit renvoyer une valeur à afficher, de sorte que :

• Si une fonction personnalisée renvoie une valeur, celle-ci s'affiche dans la cellule à partir de laquelle la fonction a été appelée.
• Si une fonction personnalisée renvoie un tableau bidimensionnel de valeurs, celles-ci sont déversées dans les cellules adjacentes tant qu'elles sont vides. Si cela entraîne l'écrasement du contenu de cellules existantes, la fonction personnalisée génère une erreur. Pour obtenir un exemple, consultez la section sur l'optimisation des fonctions personnalisées.
• Une fonction personnalisée ne peut pas affecter d'autres cellules que celles auxquelles elle renvoie une valeur. En d'autres termes, une fonction personnalisée ne peut pas modifier des cellules arbitraires, mais uniquement celles à partir desquelles elle est appelée et leurs cellules adjacentes. Pour modifier des cellules arbitraires, utilisez plutôt un menu personnalisé pour exécuter une fonction.
• Un appel de fonction personnalisée doit être renvoyé dans un délai de 30 secondes. Si ce n'est pas le cas, la cellule affiche #ERROR! et la note de la cellule est Exceeded maximum execution time (line 0).

Types de données

Sheets stocke les données dans différents formats en fonction de leur nature. Lorsque ces valeurs sont utilisées dans des fonctions personnalisées, Apps Script les traite comme le type de données approprié en JavaScript. Voici les sources de confusion les plus courantes :

• Les heures et les dates dans Sheets deviennent Date objets dans Apps Script. Si la feuille de calcul et le script utilisent des fuseaux horaires différents (un problème rare), la fonction personnalisée doit compenser.
• Les valeurs de durée dans Sheets deviennent également des objets Date, mais il peut être compliqué de les utiliser.
• Les valeurs de pourcentage dans Sheets deviennent des nombres décimaux dans Apps Script. Par exemple, une cellule dont la valeur est 10% devient 0.1 dans Apps Script.

Saisie semi-automatique

Sheets est compatible avec la saisie semi-automatique pour les fonctions personnalisées, comme pour les fonctions intégrées. Lorsque vous saisissez un nom de fonction dans une cellule, une liste de fonctions intégrées et personnalisées correspondant à ce que vous saisissez s'affiche.

Les fonctions personnalisées apparaissent dans cette liste si leur script inclut un JSDoc @customfunction tag, comme dans l'exemple DOUBLE().

/**
 * 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;
}

Avancé

Cette section traite des sujets avancés concernant les fonctions personnalisées.

Utiliser les services Google Apps Script

Les fonctions personnalisées peuvent appeler certains services Apps Script pour effectuer des tâches plus complexes. Par exemple, une fonction personnalisée peut appeler le service Language pour traduire une expression anglaise en espagnol.

Contrairement à la plupart des autres types d'Apps Scripts, les fonctions personnalisées ne demandent jamais aux utilisateurs d'autoriser l'accès à des données personnelles. Par conséquent, elles ne peuvent appeler que des services qui n'ont pas accès à des données personnelles, en particulier les suivants :

Si votre fonction personnalisée génère le message d'erreur You do not have permission to call X service., cela signifie que le service nécessite une autorisation utilisateur et ne peut donc pas être utilisé dans une fonction personnalisée.

Pour utiliser un service autre que ceux de la liste précédente, créez un menu personnalisé qui exécute une fonction Apps Script au lieu d'écrire une fonction personnalisée. Une fonction déclenchée à partir d'un menu demande l'autorisation à l'utilisateur si nécessaire et peut donc utiliser tous les services Apps Script.

Partager des fonctions personnalisées

Les fonctions personnalisées sont initialement liées à la feuille de calcul dans laquelle elles ont été créées. Cela signifie qu'une fonction personnalisée écrite dans une feuille de calcul ne peut pas être utilisée dans d'autres feuilles de calcul, sauf si vous utilisez l'une des méthodes suivantes :

• Cliquez sur Extensions > Apps Script pour ouvrir l'éditeur de scripts, puis copiez le texte du script à partir de la feuille de calcul d'origine et collez-le dans l'éditeur de scripts d'une autre feuille de calcul.
• Créez une copie de la feuille de calcul contenant la fonction personnalisée en cliquant sur Fichier > Créer une copie. Lorsqu'une feuille de calcul est copiée, tous les scripts qui y sont associés sont également copiés. Toute personne ayant accès à la feuille de calcul peut copier le script. (Les collaborateurs qui n'ont qu'un accès en lecture seule ne peuvent pas ouvrir l'éditeur de scripts dans la feuille de calcul d'origine. Toutefois, lorsqu'ils créent une copie, ils en deviennent propriétaires et peuvent voir le script.)
• Publiez le script en tant que module complémentaire de l'éditeur Sheets Editor add-on.

Tous les scripts liés à un conteneur partagent les mêmes listes d'accès que leurs conteneurs. Cela signifie que toute personne autorisée à modifier la feuille de calcul peut également modifier n'importe quel code Apps Script qui y est associé. Pour en savoir plus, consultez la section Accès aux scripts liés.

Optimisation

Chaque fois qu'une fonction personnalisée est utilisée dans une feuille de calcul, Sheets effectue un appel distinct au serveur Apps Script. Si votre feuille de calcul contient des dizaines (voire des centaines ou des milliers) d'appels de fonctions personnalisées, ce processus peut être lent. Certains projets comportant de nombreuses fonctions personnalisées ou des fonctions personnalisées complexes peuvent subir un délai temporaire lors de l'exécution.

Par conséquent, si vous prévoyez d'utiliser une fonction personnalisée plusieurs fois sur une grande plage de données, envisagez de la modifier afin qu'elle accepte une plage en entrée sous la forme d'un tableau bidimensionnel, puis renvoie un tableau bidimensionnel qui peut être déversé dans les cellules appropriées.

Par exemple, la fonction DOUBLE() présentée précédemment peut être réécrite pour accepter une seule cellule ou une plage de cellules comme suit :

/**
 * 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;
}

Cette approche utilise la méthode map de l'objet Array de JavaScript sur le tableau bidimensionnel de cellules pour obtenir chaque ligne, puis, pour chaque ligne, elle utilise map à nouveau pour renvoyer le double de la valeur de chaque cellule. Elle renvoie un tableau bidimensionnel contenant les résultats. Ainsi, vous pouvez appeler DOUBLE une seule fois, mais le faire calculer pour un grand nombre de cellules à la fois, comme illustré dans la capture d'écran suivante. Vous pouvez obtenir le même résultat avec des instructions if imbriquées au lieu de l'appel map.

De même, la fonction personnalisée suivante récupère efficacement du contenu en direct sur Internet et utilise un tableau bidimensionnel pour afficher deux colonnes de résultats avec un seul appel de fonction. Si chaque cellule nécessitait son propre appel de fonction, l'opération prendrait beaucoup plus de temps, car le serveur Apps Script devrait télécharger et analyser le flux XML à chaque fois.

/**
 * 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;
}

Ces techniques peuvent être appliquées à presque toutes les fonctions personnalisées utilisées de manière répétée dans une feuille de calcul, bien que les détails d'implémentation varient en fonction du comportement de la fonction.