Google Sheets propose des centaines de fonctions intégrées telles que AVERAGE
, SUM
et VLOOKUP
. Lorsque ces ressources ne répondent pas à vos besoins, vous pouvez utiliser Google Apps Script pour écrire des fonctions personnalisées (par exemple, pour convertir des compteurs 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 de 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 Google et n'est pas associé à Google.)
Voici une fonction personnalisée simple, 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 écrire du code JavaScript et que vous n'avez pas le temps d'apprendre à coder, consultez la plate-forme de téléchargement de modules complémentaires 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:
- Créez ou ouvrez une feuille de calcul dans Google Sheets.
- Sélectionnez l'élément de menu Extensions > Apps Script.
- Supprimez le code dans l'éditeur de scripts. Pour la fonction
DOUBLE
ci-dessus, copiez et collez simplement le code dans l'éditeur de scripts. - En haut, 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 découvrir ces modules complémentaires:
- Créez ou ouvrez une feuille de calcul dans Google Sheets.
- En haut de la page, cliquez sur Modules complémentaires > Télécharger des modules complémentaires.
- Une fois l'Google Workspace Marketplace ouverte, cliquez sur le champ de recherche en haut à droite.
- Saisissez "fonction personnalisée", puis appuyez sur Entrée.
- Si vous trouvez un module complémentaire de fonction personnalisée qui vous intéresse, cliquez sur Installer pour l'installer.
- Une boîte de dialogue peut vous indiquer que le module complémentaire nécessite une autorisation. Dans ce cas, lisez attentivement la notification, puis cliquez sur Autoriser.
- Le module complémentaire est alors disponible dans la feuille de calcul. Pour utiliser le module complémentaire dans une autre feuille de calcul, ouvrez cette dernière 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 installé une fonction à partir deGoogle Workspace Marketplace, elle est aussi facile à utiliser qu'une fonction intégrée:
- Cliquez sur la cellule dans laquelle vous souhaitez utiliser la fonction.
- Saisissez un signe égal (
=
) suivi du nom de la fonction et de toute valeur d'entrée (par exemple,=DOUBLE(A1)
), puis appuyez sur Entrée. - 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 connaissance de certaines consignes.
Dénomination
En plus des conventions standards pour l'attribution de noms aux fonctions JavaScript, tenez compte des points suivants:
- 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 (
_
), qui indique qu'il s'agit d'une fonction privée dans Apps Script. - Le nom d'une fonction personnalisée doit être déclaré avec la syntaxe
function myFunction()
, et nonvar myFunction = new Function()
. - La casse n'a pas d'importance, bien que les noms des fonctions de la feuille de calcul soient traditionnellement en majuscules.
Arguments
Comme les fonctions intégrées, une fonction personnalisée peut utiliser des arguments en tant que 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 en utilisant une référence à une plage de cellules en tant qu'argument (comme
=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 commedouble([[1,3],[2,4]])
. Notez que l'exemple de code pourDOUBLE
ci-dessus doit être modifié pour accepter un tableau en entrée.Les arguments de fonction personnalisée doivent être déterministes. Autrement dit, les fonctions intégrées de feuille de calcul qui renvoient un résultat différent à chaque calcul (comme
NOW()
ouRAND()
) 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 afficheLoading...
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 à deux dimensions, les valeurs débordent dans les cellules adjacentes tant que ces cellules sont vides. Si cela entraîne l'écrasement du contenu des cellules existantes, la fonction personnalisée renverra une erreur. Pour obtenir un exemple, consultez la section sur 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 les cellules arbitraires, uniquement les cellules à partir desquelles elle est appelée et leurs cellules adjacentes. Pour modifier les cellules arbitraires, utilisez plutôt un menu personnalisé afin d'exécuter une fonction.
- Un appel de fonction personnalisé doit être renvoyé dans un délai de 30 secondes. Si ce n'est pas le cas, la cellule affiche une erreur:
Internal error executing the custom function.
Types de données
Google 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é dans JavaScript. Voici les sources de confusion les plus courantes:
- 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 (ce problème est rare), la fonction personnalisée doit effectuer une compensation.
- Les valeurs de durée dans Sheets deviennent également des objets
Date
, mais leur utilisation peut être compliquée. - Les valeurs de pourcentage dans Sheets deviennent des nombres décimaux dans Apps Script. Par exemple, une cellule dont la valeur est
10%
devient0.1
dans Apps Script.
Saisie semi-automatique
Google Sheets prend en charge la saisie semi-automatique pour les fonctions personnalisées, comme pour les fonctions intégrées. À mesure que 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 apparaîtront dans cette liste si leur script comprend une balise 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 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 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 des services qui n'ont pas accès aux données à caractère personnel, en particulier les suivants:
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 | Peut calculer des itinéraires, mais pas afficher de cartes |
Propriétés | getUserProperties() ne récupère que les propriétés du propriétaire de la feuille de calcul. Ils 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() ). |
Récupération d'URL | |
Entreprises de services publics | |
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é 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 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 é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 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 le sont également. Toute personne ayant accès à la feuille de calcul peut copier le 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. Cependant, lorsqu'ils en font une copie, ils en deviennent le propriétaire et peuvent voir le script.)
- Publiez le script en tant que module complémentaire d'éditeur pour Google Sheets.
Optimisation
Chaque fois qu'une fonction personnalisée est utilisée dans une feuille de calcul, Google Sheets effectue un appel distinct au serveur Apps Script. Si votre feuille de calcul contient des dizaines (voire des centaines, voire des milliers) d'appels de fonctions personnalisées, ce processus peut être assez lent.
Par conséquent, si vous prévoyez d'utiliser une fonction personnalisée plusieurs fois sur une vaste plage de données, envisagez de la modifier afin qu'elle accepte une plage en tant qu'entrée sous la forme d'un tableau bidimensionnel, puis renvoie un tableau bidimensionnel qui peut déborder dans les cellules appropriées.
Par exemple, la fonction DOUBLE()
présentée ci-dessus peut être réécrite pour accepter une seule cellule 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 la méthode map de l'objet Array
de JavaScript pour appeler DOUBLE
de manière récursive sur chaque valeur du tableau de cellules à deux dimensions. Elle renvoie un tableau bidimensionnel contenant les résultats. Vous pouvez ainsi appeler DOUBLE
une seule fois, mais le calculer pour un grand nombre de cellules à la fois, comme illustré dans la capture d'écran ci-dessous. (Vous pouvez obtenir la même chose avec des instructions if
imbriquées au lieu de l'appel map
.)
De même, la fonction personnalisée ci-dessous extrait efficacement le 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 devra 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 façon répétée dans une feuille de calcul, bien que les détails de l'implémentation varient en fonction du comportement de la fonction.