Fonctions personnalisées dans Google Sheets

Google Sheets propose des centaines de fonctions intégrées telles que AVERAGE, SUM et VLOOKUP. Lorsque ces fonctionnalités ne répondent pas à vos besoins, vous pouvez utiliser Google Apps Script pour écrire des fonctions personnalisées (par exemple pour convertir des mètres en miles ou extraire du contenu en direct sur Internet), puis les utiliser dans Google Sheets, comme une fonction intégrée.

Premiers pas

Les fonctions personnalisées sont créées à l'aide du code JavaScript standard. Si vous débutez avec JavaScript, Codecademy propose un cours idéal pour les débutants. Remarque: Ce cours n'a pas été développé par et n'est pas associé à Google.

Voici une fonction personnalisée simple nommée DOUBLE, qui multiplie une valeur d'entrée par 2:

function DOUBLE(input) {
  return input * 2;
}

Si vous ne savez pas écrire du code JavaScript et si vous n'avez pas le temps de vous former, consultez le module complémentaire pour savoir 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 Google 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 ci-dessus, il vous suffit de copier le code et de le coller dans l'éditeur de scripts.
  4. En haut de la page, cliquez sur Enregistrer .

Vous pouvez maintenant utiliser la fonction personnalisée.

Obtenir une fonction personnalisée à partir de Google Workspace Marketplace

Google Workspace Marketplace propose plusieurs fonctions personnalisées en tant que modules complémentaires pour Google Sheets. Pour utiliser ou explorer ces modules complémentaires:

  1. Créez ou ouvrez une feuille de calcul dans Google Sheets.
  2. En haut, cliquez sur Modules complémentaires > Télécharger des modules complémentaires.
  3. Une fois le 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é qui vous intéresse, cliquez sur 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 utiliser le module complémentaire dans une autre feuille de calcul, ouvrez l'autre feuille de calcul, puis cliquez sur Modules complémentaires > Gérer les modules complémentaires en haut de la page. Recherchez le module complémentaire que vous souhaitez utiliser, puis cliquez sur Options > Utiliser dans ce document.

Utiliser une fonction personnalisée

Une fois que vous avez écrit ou installé une fonction personnalisée à partir deGoogle Workspace Marketplace, vous pouvez l'utiliser facilement en tant que fonction intégrée:

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

Consignes pour les fonctions personnalisées

Avant d'écrire votre propre fonction personnalisée, vous devez prendre en compte quelques consignes.

Dénomination

Outre les conventions standards pour l'attribution des noms aux fonctions JavaScript, tenez compte des points suivants:

  • Le nom d'une fonction personnalisée doit être différent du nom des fonctions intégrées telles que SUM().
  • Le nom d'une fonction personnalisée ne peut pas se terminer par un trait de soulignement (_), qui représente 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().
  • L'utilisation de minuscules/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 comme valeurs d'entrée:

  • Si vous appelez votre fonction avec une référence à une seule cellule en tant qu'argument (comme =DOUBLE(A1)), l'argument sera la valeur de la cellule.
  • Si vous appelez votre fonction avec une référence à une plage de cellules en tant qu'argument (par exemple, =DOUBLE(A1:B10)), l'argument sera un tableau bidimensionnel des valeurs des cellules. Par exemple, dans la capture d'écran ci-dessous, 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 ci-dessus devra être modifié pour accepter un tableau en entrée.


  • Les arguments de fonction personnalisés doivent être déterministes. Autrement dit, les fonctions de feuille de calcul intégrées qui renvoient un résultat différent à chaque calcul, 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 volatiles intégrées, elle affiche Loading... indéfiniment.

Valeurs renvoyées

Chaque fonction personnalisée doit renvoyer une valeur à afficher, par exemple:

  • Si une fonction personnalisée renvoie une valeur, celle-ci s'affiche dans la cellule à partir de laquelle elle a été appelée.
  • Si une fonction personnalisée renvoie un tableau de valeurs bidimensionnel, les valeurs débordent dans les cellules adjacentes tant que ces cellules sont vides. Si le tableau écrase alors le contenu des cellules, la fonction personnalisée générera une erreur. Pour obtenir un exemple, consultez la section sur l'optimisation des fonctions personnalisées.
  • Une fonction personnalisée ne peut affecter que les cellules auxquelles elle renvoie une valeur. En d'autres termes, une fonction personnalisée ne peut pas modifier les cellules arbitraires, mais uniquement les cellules dont elle est appelée et les cellules adjacentes. Pour modifier des cellules arbitraires, utilisez plutôt un menu personnalisé.
  • Un appel de fonction personnalisé doit être renvoyé dans les 30 secondes. Si ce n'est pas le cas, la cellule affichera une erreur: Internal error executing the custom function.

Types de données

Google Sheets stocke des données dans différents formats en fonction de la nature des données. Lorsque ces valeurs sont utilisées dans des fonctions personnalisées, Apps Script les traite comme des types de données appropriés dans JavaScript. Voici les points de confusion les plus courants:

  • Les heures et les dates dans Sheets deviennent des objets Date dans Apps Script. Si la feuille de calcul et le script utilisent des fuseaux horaires différents (problème rare), la fonction personnalisée devra compenser.
  • Dans Sheets, les valeurs de durée deviennent également des objets Date, mais l'utilisation de ces valeurs peut être complexe.
  • Les valeurs en pourcentage dans Sheets deviennent des nombres décimaux dans Apps Script. Par exemple, une cellule avec la valeur 10% devient 0.1 dans Apps Script.

Saisie semi-automatique

Google Sheets permet la saisie semi-automatique pour les fonctions personnalisées, de la même manière que pour les fonctions intégrées. Lorsque vous saisissez le nom d'une fonction dans une cellule, une liste de fonctions intégrées et personnalisées s'affiche.

Les fonctions personnalisées apparaissent dans cette liste si leur script inclut une balise @customfunction JsDoc, comme dans l'exemple DOUBLE() ci-dessous.

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

Analyses

Utiliser les services 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 de langue pour traduire une expression anglaise en espagnol.

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

Services compatibles Remarques
Cache Fonctionne, mais n'est pas particulièrement utile pour les fonctions personnalisées
HTML Peut générer du code HTML, mais ne peut pas l'afficher (rarement utile)
JDBC
Language
Bloquer Fonctionne, mais n'est pas particulièrement utile pour les fonctions personnalisées
Maps Peut calculer un itinéraire, mais pas afficher les cartes
Propriétés getUserProperties() n'obtient que les propriétés du propriétaire de la feuille de calcul. Les éditeurs de feuilles de calcul ne peuvent pas définir les propriétés utilisateur dans une fonction personnalisée.
Spreadsheet Lecture seule (peut utiliser la plupart des méthodes get*(), mais pas set*()).
Impossible d'ouvrir d'autres feuilles de calcul (SpreadsheetApp.openById() ou SpreadsheetApp.openByUrl()).
URL Fetch
Utilitaires
XML

Si votre fonction personnalisée génère le message d'erreur You do not have permission to call X service., 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 répertoriés ci-dessus, 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.

Partage

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 employez l'une des méthodes suivantes:

  • Cliquez sur Extensions > Apps Script pour ouvrir l'éditeur de scripts, puis copiez le texte du script 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 lui sont associés sont également copiés. Toute personne ayant accès à la feuille de calcul peut copier le script. (Les collaborateurs disposant d'un accès en lecture seule ne peuvent pas ouvrir l'éditeur de scripts dans la feuille de calcul d'origine. Toutefois, lorsqu'il en crée une copie, il en devient propriétaire et peut voir le script.)
  • Publiez le script en tant que module complémentaire Google Sheets.

Optimisation

Chaque fois qu'une fonction personnalisée est utilisée dans une feuille de calcul, Google Sheets appelle le serveur Apps Script séparément. Si votre feuille de calcul contient des dizaines, voire des centaines, voire des milliers ! d'appels de fonctions personnalisés, ce processus peut être assez lent.

Par conséquent, si vous prévoyez d'utiliser une fonction personnalisée plusieurs fois sur une large plage de données, envisagez de modifier la fonction de sorte qu'elle accepte une plage sous forme de tableau bidimensionnel, puis renvoie un tableau bidimensionnel pouvant déborder dans les cellules appropriées.

Par exemple, la fonction DOUBLE() ci-dessus 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;
}

L'approche ci-dessus utilise la méthode map de l'objet JavaScript de Array pour appeler DOUBLE de manière récursive sur chaque valeur du tableau de cellules en deux dimensions. Il renvoie un tableau bidimensionnel contenant les résultats. Vous pouvez ainsi appeler DOUBLE une seule fois, mais faire en sorte qu'elle soit calculée pour un grand nombre de cellules à la fois, comme illustré dans la capture d'écran ci-dessous. (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 ci-dessous récupère efficacement le contenu en direct d'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 à plusieurs reprises dans une feuille de calcul, bien que les détails de la mise en œuvre varient en fonction du comportement de la fonction.