Fonctions personnalisées dans Google Sheets

Google Sheets propose des centaines fonctions intégrées telles que AVERAGE SUM et VLOOKUP Dans le cas contraire 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, tout comme une fonction intégrée.

Premiers pas

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

Voici une fonction personnalisée simple, nommée DOUBLE, qui multiplie une 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 la boutique de modules complémentaires 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éer ou ouvrir 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 suffit de copiez et collez le code dans l'éditeur de scripts.
  4. En haut, cliquez sur (Enregistrer).

Vous pouvez maintenant utiliser la fonction personnalisée.

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

Le Google Workspace Marketplace propose plusieurs en tant que modules complémentaires pour Google Sheets. Pour utiliser ou découvrir ces modules complémentaires:

  1. Créer ou ouvrir une feuille de calcul dans Google Sheets.
  2. En haut de la page, cliquez sur Modules complémentaires > Télécharger des modules complémentaires
  3. Une fois que Google Workspace Marketplace s'ouvre, cliquez sur le champ de recherche dans l'angle supérieur droit.
  4. Saisissez "fonction personnalisée" et appuyez sur Entrée.
  5. Si un module complémentaire de fonction personnalisée 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 oui, Lisez attentivement la notification, puis cliquez sur Allow (Autoriser).
  7. Le module complémentaire devient disponible dans la feuille de calcul. Pour utiliser le module complémentaire feuille de calcul différente, ouvrez l'autre feuille de calcul 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 > 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 à partir du Google Workspace Marketplace, il est aussi facile à utiliser qu'un 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 Loading... dans un instant, puis renvoie le résultat.

Consignes concernant les fonctions personnalisées

Voici quelques consignes à connaître avant de créer votre propre fonction personnalisée.

Dénomination

En plus des conventions standard d'attribution de noms aux fonctions JavaScript, tenir compte de ce qui suit:

  • Le nom d'une fonction personnalisée doit être distinct des noms 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 (_), indique 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 casse n'a pas d'importance, même si les noms des fonctions de la feuille de calcul sont généralement en majuscules.

Arguments

Comme les fonctions intégrées, les fonctions personnalisées peuvent accepter des arguments en tant que valeurs d'entrée:

  • Si vous appelez votre fonction avec une référence à une cellule unique comme 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 que (comme =DOUBLE(A1:B10)), il s'agit d'un argument bidimensionnel le tableau des cellules valeurs. Par exemple, dans la capture d'écran ci-dessous, arguments dans =DOUBLE(A1:B2) sont interprétés par Apps Script comme double([[1,3],[2,4]]) Notez que l'exemple de code pour DOUBLE de ci-dessus devrait être modifié pour accepter un tableau en entrée.


  • Les arguments de fonction personnalisée doivent être déterministe. Cela des fonctions de feuille de calcul intégrées qui renvoient un résultat différent à chaque fois qu'ils calculent, comme NOW() ou RAND(), ne sont pas autorisés en tant qu'arguments à 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, il affichera Loading... indéfiniment.

Valeurs renvoyées

Chaque fonction personnalisée doit renvoyer une valeur à afficher, telle 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 de valeurs bidimensionnel, les valeurs débordent dans les cellules adjacentes tant que ces cellules sont vides. Si c'était le cas le tableau remplace le contenu des cellules existantes, la fonction personnalisée génère une erreur. Pour voir un exemple, consultez la section l'optimisation des fonctions personnalisées.
  • Une fonction personnalisée ne peut pas affecter les cellules autres que celles auxquelles elle renvoie une valeur. En d'autres termes, une fonction personnalisée ne peut pas modifier des cellules arbitraires, seule la cellules à partir desquelles elle est appelée et ses 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 renvoyer un résultat dans un délai de 30 secondes. Si ce n'est pas le cas, la cellule affiche #ERROR! et la note est Exceeded maximum execution time (line 0).

Types de données

Google Sheets stocke les données différents formats en fonction la nature des données. Lorsque ces valeurs sont utilisées dans des fonctions personnalisées, Le script les traite comme le type de données approprié dans JavaScript. Voici les sujets 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 utilisent des fuseaux horaires différents (problème rare), la fonction personnalisée avoir besoin de compenser.
  • Les valeurs de durée dans Sheets deviennent également des objets Date, mais travailler avec elles peut être compliqué.
  • Les valeurs de pourcentage dans Sheets deviennent des nombres décimaux dans Apps Script. Pour Par exemple, une cellule dont la valeur est 10% devient 0.1 dans Apps Script.

Saisie semi-automatique

Google Sheets prend en charge la saisie semi-automatique pour les fonctions personnalisées, tout comme pour fonctions intégrées. En saisir le nom d'une fonction dans une cellule, la liste des fonctions intégrées et personnalisées s'affiche correspondant à votre saisie.

Les fonctions personnalisées apparaissent dans cette liste si leur script inclut un JsDoc @customfunction, 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;
}

Avancé

Utilisation des services Google Apps Script

Les fonctions personnalisées peuvent appeler certains services Google Apps Script, pour des opérations plus complexes tâches. Par exemple, une fonction personnalisée peut appeler Service Language permettant de traduire un contenu en anglais en espagnol.

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

Services compatibles Remarques
Cache Fonctionne, mais pas particulièrement utile dans les fonctions personnalisées
HTML Peut générer du code HTML, mais ne peut pas l'afficher (rarement utile)
JDBC
Langue
Verrouiller Fonctionne, mais pas particulièrement utile dans les fonctions personnalisées
Maps Permet de calculer des itinéraires, mais pas d'afficher des cartes
Propriétés getUserProperties() ne récupère que les propriétés de propriétaire de la feuille de calcul. Les éditeurs de feuilles de calcul ne peuvent pas définir les propriétés utilisateur dans un personnalisée.
Feuille de calcul Lecture seule (permet d'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
Fournisseurs
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 l'autorisation de l'utilisateur et ne peut donc pas être utilisées dans une fonction personnalisée.

Pour utiliser un service autre que ceux répertoriés ci-dessus, créez un menu personnalisé exécutant une fonction Apps Script au lieu d'écrire une fonction personnalisée. Fonction déclenchée depuis un menu demande l'autorisation de 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 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 ouvrez l'éditeur de script, puis copiez le texte du script de la feuille de calcul d'origine et le coller dans l'éditeur de script d'une autre feuille de calcul.
  • Faites une copie de la feuille de calcul qui contient la fonction personnalisée en cliquant sur Fichier > Créez une copie. Lorsqu'une feuille de calcul est copiée, tous les scripts associés il est également copié. Tous les utilisateurs ayant accès à la feuille de calcul peuvent copier la script. (Les collaborateurs qui ne disposent que d'un accès en lecture ne peuvent pas ouvrir l'éditeur de script. dans la feuille de calcul d'origine. Toutefois, lorsqu'ils en font une copie, ils deviennent propriétaire de la copie et peut voir le script.)
  • Publiez le script en tant que module complémentaire de l'éditeur Google Sheets.

Optimisation

Chaque fois qu'une fonction personnalisée est utilisée dans une feuille de calcul, Google Sheets crée une au serveur Apps Script. Si votre feuille de calcul contient des dizaines (ou des centaines voire des milliers d'appels de fonctions personnalisées, ce processus peut être lents.

Par conséquent, si vous prévoyez d'utiliser une fonction personnalisée plusieurs fois sur un grand plage de données, pensez à modifier la fonction afin qu'elle accepte une plage sous la forme d'un tableau à deux dimensions, puis renvoie une valeur qui peut déborder dans les cellules appropriées.

Par exemple, la fonction DOUBLE() ci-dessus peut être réécrite pour accepter une cellule unique ou 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 map de l'objet Array JavaScript de manière récursive appeler DOUBLE sur chaque valeur du tableau de cellules à deux dimensions. Elle renvoie un un tableau bidimensionnel contenant les résultats. Vous pourrez ainsi appeler DOUBLE une seule fois, mais de le faire calculer pour un grand nombre de cellules à la fois, comme indiqué dans la capture d'écran ci-dessous. Vous pouvez obtenir le même résultat avec des if imbriqués. au lieu de l'appel map.)

De même, la fonction personnalisée ci-dessous récupère efficacement le contenu en direct à partir de la et utilise un tableau bidimensionnel pour afficher deux colonnes de résultats avec avec un seul appel de fonction. Si chaque cellule nécessitait son propre appel de fonction, le cette opération prendrait considérablement plus de temps, car le serveur Apps Script vous devrez 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, même si les détails de la mise en œuvre varient en fonction du comportement de la fonction.