Utilisez Google Apps Script pour étendre Sheets. Ajoutez des menus personnalisés, des boîtes de dialogue et des barres latérales à Sheets. Écrivez des fonctions personnalisées pour Sheets et intégrez-les à d'autres services Google comme Google Agenda, Google Drive et Gmail.
La plupart des scripts conçus pour Sheets manipulent des tableaux pour interagir avec les cellules, les lignes et les colonnes d'une feuille de calcul. Si vous ne connaissez pas les tableaux en JavaScript, Codecademy propose un excellent module de formation sur les tableaux. Ce cours n'a pas été développé par Google et n'y est pas associé.
Pour une introduction rapide à l'utilisation d'Apps Script avec Sheets, consultez le guide de démarrage rapide de cinq minutes sur les macros, les menus et les fonctions personnalisées.
Premiers pas
Apps Script inclut des API spéciales permettant de créer, lire et modifier des feuilles de calcul de manière programmatique. Apps Script interagit avec Sheets de deux manières : tout script peut créer ou modifier une feuille de calcul si l'utilisateur du script dispose des autorisations appropriées, et un script peut également être lié à une feuille de calcul. Les scripts liés ont des capacités spéciales pour modifier l'interface utilisateur ou répondre lorsque la feuille de calcul est ouverte. Pour créer un script lié, sélectionnez Extensions > Apps Script dans Sheets.
Le service de feuille de calcul traite les feuilles de calcul comme une grille et fonctionne avec des tableaux bidimensionnels. Pour récupérer les données de la feuille de calcul, accédez à la feuille de calcul où les données sont stockées, obtenez la plage contenant les données, puis obtenez les valeurs des cellules. Apps Script facilite l'accès aux données en lisant les données structurées de la feuille de calcul et en créant des objets JavaScript pour celles-ci.
Lire des données
Supposons que vous disposiez d'une liste de noms et de numéros de produits que vous stockez dans une feuille de calcul, comme illustré dans l'image suivante.
L'exemple suivant montre comment récupérer et consigner les noms et numéros de produits.
function logProductInfo() {
const sheet = SpreadsheetApp.getActiveSheet();
const data = sheet.getDataRange().getValues();
for (let i = 0; i < data.length; i++) {
Logger.log('Product name: ' + data[i][0]);
Logger.log('Product number: ' + data[i][1]);
}
}
Afficher les journaux
Pour afficher les données enregistrées, cliquez sur Journal d'exécution en haut de l'éditeur de script.
Écrire des données
Pour stocker des données, telles que le nom et le numéro d'un nouveau produit, dans la feuille de calcul, ajoutez le code suivant à la fin du script.
function addProduct() {
const sheet = SpreadsheetApp.getActiveSheet();
sheet.appendRow(['Cotton Sweatshirt XL', 'css004']);
}
Le code précédent ajoute une ligne en bas de la feuille de calcul, avec les valeurs spécifiées. Si vous exécutez cette fonction, une nouvelle ligne est ajoutée à la feuille de calcul.
Menus et interfaces utilisateur personnalisés
Personnalisez Sheets en ajoutant des menus, des boîtes de dialogue et des barres latérales personnalisés. Pour apprendre les bases de la création de menus, consultez le guide sur les menus. Pour savoir comment personnaliser le contenu d'une boîte de dialogue, consultez le guide du service HTML.
Associez une fonction de script à une image ou à un dessin dans une feuille de calcul. La fonction s'exécute lorsqu'un utilisateur clique sur l'image ou le dessin. Pour en savoir plus, consultez Images et dessins dans Sheets.
Si vous prévoyez de publier votre interface personnalisée dans un module complémentaire, suivez le guide de style pour assurer la cohérence avec le style et la mise en page de l'éditeur Sheets.
Associer votre source de données à Google Forms
Associez Google Forms à Sheets via les services Forms et Spreadsheet. Cette fonctionnalité crée automatiquement un formulaire Google Forms à partir des données d'une feuille de calcul.
Apps Script vous permet également d'utiliser des déclencheurs, tels que onFormSubmit, pour effectuer une action spécifique après qu'un utilisateur a répondu au formulaire. Pour en savoir plus sur l'association de Sheets à Forms, essayez le démarrage rapide de cinq minutes Gérer les réponses pour Forms.
Mettre les données en forme
La classe Range comporte des méthodes telles que setBackground pour accéder au format d'une cellule ou d'une plage de cellules et le modifier. L'exemple suivant définit le style de police d'une plage :
function formatMySpreadsheet() {
// Set the font style of the cells in the range of B2:C2 to be italic.
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheets()[0];
const cell = sheet.getRange('B2:C2');
cell.setFontStyle('italic');
}
Validation des données
Accédez aux règles de validation des données existantes dans Sheets ou créez-en. Par exemple, l'exemple suivant montre comment définir une règle de validation des données qui n'autorise que les nombres compris entre 1 et 100 dans une cellule.
function validateMySpreadsheet() {
// Set a rule for the cell B4 to be a number between 1 and 100.
const cell = SpreadsheetApp.getActive().getRange('B4');
const rule = SpreadsheetApp.newDataValidation()
.requireNumberBetween(1, 100)
.setAllowInvalid(false)
.setHelpText('Number must be between 1 and 100.')
.build();
cell.setDataValidation(rule);
}
Pour en savoir plus sur l'utilisation des règles de validation des données, consultez SpreadsheetApp.newDataValidation, DataValidationBuilder et Range.setDataValidation.
Graphiques
Intégrez des graphiques dans une feuille de calcul pour représenter les données d'une plage spécifique. L'exemple suivant génère un graphique à barres intégré, en supposant que vous disposez de données graphiques dans les cellules A1:B15 :
function newChart() {
// Generate a chart representing the data in the range of A1:B15.
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheets()[0];
const chart = sheet.newChart()
.setChartType(Charts.ChartType.BAR)
.addRange(sheet.getRange('A1:B15'))
.setPosition(5, 5, 0, 0)
.build();
sheet.insertChart(chart);
}
Pour en savoir plus sur l'intégration d'un graphique dans votre feuille de calcul, consultez EmbeddedChart et les outils de création de graphiques spécifiques, comme EmbeddedPieChartBuilder.
Fonctions personnalisées dans Google Sheets
Une fonction personnalisée est semblable à une fonction de feuille de calcul intégrée telle que =SUM(A1:A5), sauf que vous définissez le comportement de la fonction avec Apps Script. Par exemple, vous pouvez créer une fonction personnalisée, in2mm(), qui convertit une valeur de pouces en millimètres, puis utiliser la formule dans votre feuille de calcul en saisissant =in2mm(A1) ou =in2mm(10) dans une cellule.
Pour en savoir plus sur les fonctions personnalisées, essayez le démarrage rapide de cinq minutes Menus et fonctions personnalisées ou consultez le guide plus détaillé sur les fonctions personnalisées.
Macros
Les macros sont un autre moyen d'exécuter du code Apps Script à partir de l'interface utilisateur de Sheets. Contrairement aux fonctions personnalisées, vous les activez à l'aide d'un raccourci clavier ou du menu Sheets. Pour en savoir plus, consultez Macros dans Sheets.
Modules complémentaires pour Google Sheets
Les modules complémentaires sont des projets Apps Script spécialement empaquetés qui s'exécutent dans Sheets et peuvent être installés à partir de la plate-forme de téléchargement de modules complémentaires Sheets. Si vous avez développé un script pour Sheets et que vous souhaitez le partager, Apps Script vous permet de le publier en tant que module complémentaire afin que d'autres utilisateurs puissent l'installer.
Performances et scaling
À mesure que vos ensembles de données augmentent, vous pouvez rencontrer des problèmes de performances. Pour optimiser votre feuille de calcul et vos scripts :
- Suivez les bonnes pratiques : consultez le guide des bonnes pratiques pour obtenir des conseils sur la réduction des appels de service et l'utilisation des opérations par lot.
- Optimiser les formules : si votre feuille de calcul est lente en raison de formules complexes (comme
VLOOKUP,ARRAYFORMULAouIMPORTRANGE), envisagez d'utiliser Apps Script pour effectuer ces calculs en mémoire et réécrire les résultats par lots. - Envisagez d'autres bases de données : pour les ensembles de données très volumineux (près de 10 millions de cellules) ou la saisie de données à haute fréquence (par exemple, de nombreux formulaires connectés), envisagez d'utiliser Google Cloud SQL avec JDBC ou BigQuery.
Déclencheurs
Les scripts associés à un fichier Sheets peuvent utiliser des déclencheurs simples, comme les fonctions onOpen() et onEdit(), pour répondre automatiquement lorsqu'un utilisateur disposant d'un accès en modification à la feuille de calcul l'ouvre ou la modifie.
Comme les déclencheurs simples, les déclencheurs installables permettent à Sheets d'exécuter automatiquement une fonction lorsqu'un événement spécifique se produit.
Toutefois, les déclencheurs installables offrent plus de flexibilité que les déclencheurs simples et sont compatibles avec les événements suivants : ouverture, modification, changement, envoi de formulaire et déclencheur temporel (horloge).