API Google Sheets : Guide étape par étape pour les débutants

Google Sheets est souvent l'outil utilisé par les équipes pour stocker et partager des données. Cependant, lorsque vous mettez à jour la même feuille à plusieurs reprises, le copier-coller manuel peut s'avérer chronophage.

L'API Google Sheets vous permet de mettre à jour et de gérer automatiquement des feuilles de calcul à partir de vos propres applications et scripts. Vous pouvez importer des données, publier des mises à jour et maintenir les rapports à jour sans intervention manuelle.

Dans ce guide, nous vous expliquerons étape par étape comment configurer, lire, écrire, formater et automatiser les tâches courantes dans un tableur.

Premiers pas avec l'API Google Sheets

Pour commencer à utiliser l'API Google Sheets, vous devez disposer d'un projet Google Cloud et d'un moyen d'authentifier votre application. Veuillez considérer comment configurer cela à un niveau élevé. 

Création d'un projet Google Cloud

Toutes les API Google s'exécutent dans le cadre d'un projet Google Cloud. Ce projet contient vos informations d'identification, vos paramètres API et vos limites d'utilisation.

Voici comment créer votre premier projet : 

• Veuillez vous rendre sur la console Google Cloud.
• Veuillez cliquer Sélectionnez un projet en haut de la page.
• Une nouvelle fenêtre contextuelle intitulée Sélectionner un projet s'affiche. À partir de là, veuillez cliquer sur « New Project » (Nouveau projet).
• Dans la fenêtre suivante, veuillez saisir un nom de projet.
• Veuillez cliquer Créer.

Veuillez créer un nouveau projet dans Google Console. Image fournie par l'auteur.

Une fois le projet prêt, veuillez activer les API requises : 

• Veuillez ouvrir votre projet.
• Veuillez cliquer sur les trois lignes dans le coin supérieur gauche. 
• Veuillez vous rendre sur API et services > APIs et services activés.
• Cliquez sur + Activer les API et les services.

Veuillez vous rendre dans Activer les API et les services pour activer l'API. Image fournie par l'auteur.

• La fenêtreBibliothèque API s'affiche alors . Dans la barre de recherche, veuillez rechercher «API Google Sheets » ( ) et cliquer dessus.
• Cliquez sur Activer.

Veuillez activer l'API Google Sheets. Image fournie par l'auteur.

Si votre application doit créer, déplacer ou gérer des fichiers tableurs, veuillez également activer l'API Google Drive : 

• Rechercher API Google Drive.
• Cliquez sur Activer.

Authentification et autorisation

L'API Google Sheets prend en charge trois méthodes d'authentification principales. Le choix dépend du fonctionnement de votre application.

OAuth 2.0

Veuillez utiliser cette option lorsque de véritables utilisateurs se connectent et travaillent avec leurs propres feuilles de calcul. Cela permet aux utilisateurs d'autoriser l'accès, et votre application ne peut effectuer que les actions qu'ils autorisent.

Supposons que vous développiez une application web permettant aux utilisateurs de connecter leurs comptes Google afin que votre application puisse consulter ou mettre à jour leurs feuilles de calcul personnelles. Dans ce cas, veuillez opter pour OAuth 2.0. 

Voici comment procéder :

• Dans la console Google, veuillez vous rendre dans Menu de navigation (les trois lignes dans le coin supérieur gauche) > API et services > Écran de consentement OAuth

Veuillez vous rendre sur l'écran de consentement OAuth. Image fournie par l'auteur.

• Veuillez maintenant remplir les informations suivantes : 
• Veuillez saisir le nom de l'application qui nécessite un consentement dans le champ Nom de l'application.
• Veuillez saisir l'adresse e-mail du service d'assistance afin que les utilisateurs puissent y poser leurs questions, puis cliquez sur Suivant
• Veuillez maintenant sélectionner l'option type d'audience externe et cliquez sur Suivant
• Veuillez ensuite saisir vos coordonnées (adresse e-mail) afin que Google puisse vous informer des modifications apportées à votre projet. Ensuite, veuillez cliquer sur «Suivant » dans la fenêtre « ».
• Veuillez accepter leur politique en cochant la case et cliquez sur Continuer.

• Veuillez cliquer sur Enregistrer. Un message indiquant « Configuration OAuth créée » apparaîtra.

À présent, pour authentifier les utilisateurs finaux et accéder à leurs données Google Sheets, veuillez créer des informations d'identification client OAuth 2.0. Un identifiant client identifie de manière unique votre application auprès des serveurs OAuth de Google.

Voici comment procéder : 

• Veuillez cliquer sur le menu de navigation > API et services > Identifiants.
• Dans les fenêtre Informations d'identification , veuillez cliquer sur + Créer des informations d'identification et sélectionnez ID client OAuth.

Veuillez vous rendre à l'identifiant client OAuth. Image par l'auteur.

• Veuillez maintenant sélectionner l' Application Web dans le Type d'application .
• Veuillez saisir le nom du client OAuth 2.0 pour identification dans la console.
• Sous « URI de redirection autorisés », veuillez cliquer sur Ajouter une URI
• Veuillez saisir l'URL de redirection à laquelle Google enverra la réponse d'autorisation. Cela doit correspondre exactement à l'URI de redirection configuré dans votre application (par exemple, votre point de terminaison de rappel OAuth).
• Veuillez cliquer Créer
• Veuillez copier et enregistrer l' identifiant client et le clé secrète client. 

Clés API

Les clés API identifient votre projet, et non un utilisateur. Ils ne travaillent qu'avec des feuilles de calcul publiques et ne peuvent pas modifier les fichiers privés.

C'est pourquoi il est recommandé de n'utiliser cette fonctionnalité que lorsque vous souhaitez lire des données provenant d'une feuille Google Sheet publique afin de les afficher sur un site web ou de tester un script simple.

Voici comment générer votre clé API : 

• Veuillez vous rendre dans le Menu de navigation > API et services > Identifiants.

Veuillez vous rendre dans la section Clés API. Image fournie par l'auteur.

• Veuillez cliquer + Créer des informations d'identification, puis sélectionnez Clé API.
• Veuillez faire défiler vers le bas, sélectionner la clé boîte Restreindre la clé , puis sélectionnez dans le menu déroulant les API pour lesquelles cette clé API peut être utilisée. 
• Veuillez cliquer Créer.
• Veuillez copier et enregistrer la clé générée.

Veuillez enregistrer la clé API générée. Image fournie par l'auteur.

Comptes de service

Les comptes de service permettent à votre application de fonctionner de manière autonome, sans que l'utilisateur ait besoin de se connecter. Vous partagez des feuilles de calcul spécifiques avec l'adresse e-mail du compte de service et contrôlez ses actions.

Si vous souhaitez exécuter un script planifié qui met à jour un rapport chaque nuit ou synchronise les données d'une base de données dans une feuille Google Sheet partagée, les comptes de service constituent la meilleure option. 

Veuillez noter comment les créer et les configurer : 

Création et configuration de comptes de service

Voici comment procéder : 

• Veuillez cliquer sur l'API Google Sheets. API Google Sheets que nous avons activée précédemment. 
• En haut à gauche, veuillez cliquer sur le Créer des informations d'identification .

Veuillez créer des informations d'identification dans l'API Google Sheets. Image fournie par l'auteur.

• Dans la fenêtre Créer des informations d'identification, veuillez vous assurer que l'API Google Sheets est sélectionnée dans le champ Sélectionner une API. Veuillez maintenant sélectionner le type de données auquel vous souhaitez accéder. Étant donné que vous utilisez un compte de service, veuillez sélectionner «Données d'application » ( ) et cliquer sur «Suivant » (Next).

Veuillez saisir vos identifiants. Image fournie par l'auteur.

• Une nouvelle fenêtre intitulée Créer un compte de service s'affiche :
• Veuillez saisir le nom de votre compte de service, l'identifiant de votre compte de service et la description de votre compte de service. 
• Cliquez sur Créer et continuer.

Une fenêtre contextuelle s'affichera pour indiquer que le compte de service a été créé.

Veuillez créer un compte de service dans l'API Google Sheets. Image fournie par l'auteur.

Un compte de service n'utilise pas d'écran de connexion ou de consentement. Au lieu de cela, il procède à l'authentification à l'aide d'une clé privée stockée dans un fichier de clés JSON. Ce fichier clé permet au serveur d'applications de prouver son identité à Google et de demander automatiquement des jetons d'accès.

Maintenant, pour générer un fichier clé : 

• Veuillez ouvrir le compte de service. (Vous pouvez également le trouver sous IAM & Admin > Compte de service) 
• Veuillez vous rendre sur le site onglet .
• Veuillez cliquer Ajouter une clé > Créer une nouvelle clé.
• Veuillez sélectionner JSON et cliquez sur Créer.

Le téléchargement du fichier va maintenant commencer. 

Veuillez générer un fichier de clé JSON pour l'API Google Sheets. Image fournie par l'auteur.

Partager des feuilles de calcul Google avec des comptes de service

Les comptes de service n'ont pas automatiquement accès à vos feuilles de calcul. Il est nécessaire de partager chaque fichier avec l'adresse e-mail du compte de service.

Pour ce faire : 

• Veuillez ouvrir le fichier que nous avons téléchargé et rechercher l'client_email. Ou cliquez sur le compte de service que nous avons créé dans le menu Compte de service, puis recherchez l'adresse e-mail. Il s'agit des mêmes courriels. 
• Veuillez maintenant copier cet e-mail. 

Veuillez copier l'adresse e-mail du client à partir du fichier clé JSON. Image fournie par l'auteur.

• Veuillez ouvrir la feuille Google Sheet.
• Veuillez vous rendre sur le option Partager en haut à droite. 
• Veuillez coller l'adresse e-mail que nous avons copiée précédemment dans le champ de partage.
• Veuillez sélectionner Viewer (permet la lecture et l'écriture des données) ou Éditeur (permet uniquement la lecture) . 
• Veuillez cliquer Envoyer pour partager l'accès. 

Veuillez partager l'accès au compte de service via Google Sheets. Image fournie par l'auteur.

Installation des bibliothèques clientes

Google fournit des bibliothèques clientes officielles qui simplifient l'utilisation de l'API Sheets. Ils gèrent l'authentification, le formatage des requêtes et les nouvelles tentatives pour vous. En réalité, cette méthode est plus simple et plus sécurisée que l'envoi de requêtes HTTP brutes.

Voici quelques exemples d'installationcourants pour les langues les plus utilisées:

Python

pip install google-api-python-client google-auth google-auth-oauthlib

Node.js

npm install googleapis

Java (veuillez ajouter la bibliothèque cliente à l'aide de Maven)

<dependency>
  <groupId>com.google.apis</groupId>
  <artifactId>google-api-services-sheets</artifactId>
</dependency>

Remarque: Les bibliothèques clientes permettent de gagner du temps lors de la configuration et de réduire les erreurs à mesure que votre projet évolue.

Opérations de données de base

L'API Google Sheets est principalement utilisée pour lire, écrire, mettre à jour et formater des données de feuilles de calcul. Comprenons ces éléments : 

Lecture de données à partir de feuilles de calcul

Pour lire les données d'une feuille Google Sheet, vous avez besoin de deux éléments : l'identifiant de la feuille de calcul et la plage de cellules à laquelle vous souhaitez accéder.

L'identifiant de la feuille de calcul est dérivé de l'URL Google Sheets. Il s'agit de la longue chaîne entre /d/ et /edit.. Elle indique à l'API quelle feuille de calcul lire.

Par exemple, si l'URL de ma feuille de calcul est : https://docs.google.com/spreadsheets/d/1s4-xSl1ztvdXmCUISe14XZWduQoJ4Ignz_HrgqsExPI/edit?gid=0#gid=0  

Dans ce cas, l'identifiant de la feuille de calcul est : 1s4-xSl1ztvdXmCUISe14XZWduQoJ4Ignz_HrgqsExPI

Identifiant de la feuille de calcul. Image fournie par l'auteur.

La plage est écrite en utilisant la notation A1, qui décrit les cellules à l'aide de lettres de colonnes et de numéros de lignes. Par exemple :

• A1 lit une seule cellule

• A1:C10 lit un bloc de cellules

• Sheet1!A:A lit une colonne entière

• Sheet1!1:1 lit une ligne entière

Pour récupérer des valeurs, l'API propose deux méthodes principales :

• spreadsheets.values.get lit les données d'une seule plage. Veuillez l'utiliser lorsque vous connaissez précisément le tableau ou le bloc dont vous avez besoin. 

• spreadsheets.values.batchGet lit les données de plusieurs plages en une seule requête. Veuillez l'utiliser lorsque vous souhaitez obtenir des données provenant de plusieurs endroits à la fois, par exemple les totaux d'une feuille et les détails d'une autre.

La lecture des données n'apporte aucune modification à la feuille de calcul. Il renvoie simplement des valeurs que votre application peut utiliser ailleurs. Vous pouvez donc l'utiliser pour extraire des données dans des tableaux de bord, exporter des données à des fins d'analyse et générer des rapports automatisés.

Écriture de données dans des feuilles de calcul

L'écriture des données fonctionne de manière similaire à la lecture des données. Vous avez toujours besoin d'un identifiant de feuille de calcul et d'une plage, mais cette fois-ci, vous devez également envoyer les valeurs que vous souhaitez écrire.

L'API Google Sheets propose deux méthodes principales pour cela :

• spreadsheets.values.update: Veuillez utiliser cette option lorsque vous souhaitez enregistrer des données dans une plage spécifique et remplacer celles qui s'y trouvent déjà. Par exemple, si vous souhaitez remplacer la ligne d'en-tête d'un tableau par de nouveaux noms de colonnes, la fonction ` update ` est la solution appropriée. Les valeurs que vous envoyez remplaceront les cellules existantes dans cette plage.

• spreadsheets.values.append: Veuillez utiliser cette option lorsque vous souhaitez ajouter de nouvelles lignes sans affecter les données existantes. Cela est particulièrement utile lorsque vos données continuent d'augmenter, par exemple lors de l'enregistrement de mesures quotidiennes, de l'ajout de nouveaux enregistrements ou du stockage de réponses à des formulaires. L'API insère automatiquement les nouvelles valeurs après la dernière ligne non vide.

Options de saisie de valeur

Lorsque vous écrivez des données, vous devez également choisir la manière dont Google Sheets doit traiter les valeurs que vous envoyez. Ceci est contrôlé par ValueInputOption.

• RAW stocke la valeur exactement telle que vous l'envoyez

• USER_ENTERED traite la valeur comme si elle avait été saisie dans la feuille

Par exemple, si vous envoyez =SUM(1,2):

• Avec RAW, il est stocké sous forme de texte brut.

• Avec USER_ENTERED, cela s'évalue comme une formule.

Remplacer ou ajouter des données

Le choix entre update et append dépend de la structure de votre feuille.

Veuillez utiliser la mise à jour lorsque :

• L'emplacement des données est fixe.
• Vous remplacez des en-têtes ou des cellules identifiées.

Veuillez utiliser append lorsque :

• Les données augmentent au fil du temps
• Vous souhaitez éviter de remplacer les lignes existantes.

Opérations par lots

Les opérations par lots vous permettent de regrouper plusieurs modifications dans une seule requête API. Cela réduit le nombre d'appels effectués par votre application et vous aide à respecter les limites d'utilisation.

Il existe deux modèles de traitement par lots courants dans l'API Google Sheets.

Pour travailler avec des valeurs, vous pouvez utiliser :

• spreadsheets.values.batchGet pour lire plusieurs plages simultanément

• spreadsheets.values.batchUpdate Écrire dans plusieurs plages en une seule requête

Pour les modifications structurelles et de formatage, veuillez utiliser spreadsheets.batchUpdate.. Il s'agit de la méthode de traitement par lots la plus efficace, qui prend en charge de nombreuses actions en une seule requête.

Avec spreadsheets.batchUpdate, vous pouvez combiner des modifications telles que :

• Création ou suppression de feuilles
• Renommer une feuille de calcul ou un onglet
• Geler les lignes d'en-tête
• Modification de la largeur des colonnes
• Application de la mise en forme
• Mise à jour simultanée de plusieurs plages

Par exemple, au lieu d'envoyer des requêtes distinctes pour renommer une feuille de calcul et créer un nouvel onglet, vous pouvez effectuer ces deux opérations en un seul appel batchUpdate. Cela est plus rapide et plus facile à gérer à mesure que vos flux de travail se développent.

Vous pouvez utiliser les opérations par lots pour configurer des rapports, appliquer une mise en forme cohérente ou effectuer des mises à jour importantes dans une feuille de calcul.

Mise à jour et suppression des données

La mise à jour des données consiste à remplacer les valeurs dans des cellules ou des plages spécifiques. Cette opération est généralement effectuée lorsque vous souhaitez actualiser des chiffres, mettre à jour des indicateurs clés de performance ou remplacer une zone de sortie fixe dans une feuille.

Pour mettre à jour les valeurs existantes, veuillez utiliser spreadsheets.values.update.. Ici, vous devez spécifier la plage cible et envoyer les nouvelles valeurs, et les données existantes dans cette plage seront remplacées.

Effacement ou suppression des données

Effacer des données et supprimer des données ne sont pas des actions identiques :

L'effacement des données supprime les valeurs contenues dans les cellules, mais conserve la feuille, la mise en page et le formatage. Veuillez utiliser la fonction « spreadsheets.values.clear » lorsque vous souhaitez vider une plage sans affecter la structure.

Exemple : Effacer une ancienne section du rapport avant d'inscrire de nouvelles données dans les mêmes cellules.

La suppression de données entraîne la suppression de l'onglet de la feuille entière. Cela s'effectue à l'aide de spreadsheets.batchUpdate avec une requête deleteSheet. La suppression d'une feuille entraîne la suppression définitive de ses données et de sa structure.

Étant donné que la suppression d'une feuille est irréversible, il est recommandé de l'utiliser avec parcimonie et uniquement lorsque vous êtes certain que les données ne sont plus nécessaires.

Fonctionnalités et fonctionnalités avancées

En plus de la lecture et de l'écriture de données, l'API Google Sheets vous permet de contrôler l'apparence et le comportement des feuilles de calcul. Cela permet d'automatiser le formatage et les règles visuelles à grande échelle.

Mise en forme et mise en forme conditionnelle

L'API Google Sheets vous permet d'appliquer un formatage de cellule par programmation plutôt que via l'interface utilisateur de Sheets. Cela inclut les styles de texte, les couleurs, les formats numériques et les modifications de mise en page.

Toutes les modifications de formatage sont envoyées sous forme de requêtes JSON à l'adresse spreadsheets.batchUpdate.. Chaque requête décrit les modifications à apporter et leur emplacement d'application.

Les tâches courantes de mise en forme comprennent :

• Mettre les lignes d'en-tête en gras
• Application de formats numériques pour les devises ou les pourcentages
• Modification de la largeur des colonnes
• Définition des couleurs d'arrière-plan pour des plages spécifiques

Par exemple, une demande de mise en forme peut cibler la première ligne d'une feuille et la mettre en gras avec une couleur de fond claire. La requête utilise des index à base zéro, où l' 0 e de ligne représente la première ligne.

Mise en forme conditionnelle

Le formatage conditionnel vous permet de styliser automatiquement les cellules en fonction de leurs valeurs. Si vous souhaitez que Sheets mette en évidence les données importantes sans révision manuelle, le formatage conditionnel peut vous aider. 

À l'aide de l'API, les règles de mise en forme conditionnelle sont définies dans JSON et appliquées via spreadsheets.batchUpdate.

Vous pouvez utiliser ceci pour :

• Mise en évidence des valeurs négatives en rouge
• Signaler les valeurs supérieures ou inférieures à un seuil
• Appliquer automatiquement un style aux lignes qui répondent à certaines conditions

Par exemple, vous pouvez définir une règle qui met en évidence toute valeur inférieure à 0 dans une colonne spécifique. Une fois appliqué, le formatage se met à jour automatiquement lorsque les données changent.

Tableaux croisés dynamiques et agrégation de données

L'API Google Sheets permet de créer et de mettre à jour des tableaux croisés dynamiques à l'aide de code. Les tableaux croisés dynamiques permettent de résumer de grands ensembles de données sans modifier les données d'origine, ce qui facilite la création de rapports et l'analyse.

Il vous suffit de définir des tableaux croisés dynamiques à l'aide de requêtes JSON et de les appliquer à l'aide d'spreadsheets.batchUpdate.. Ces requêtes décrivent la manière dont les données doivent être regroupées, résumées et affichées.

Grâce à l'API, vous pouvez :

• Veuillez sélectionner les colonnes qui deviendront les étiquettes de ligne.
• Veuillez sélectionner des méthodes d'agrégation telles que la somme, le nombre ou la moyenne.
• Contrôler l'emplacement d'affichage du tableau croisé dynamique
• Actualisez ou modifiez les tableaux croisés dynamiques à mesure que de nouvelles données sont ajoutées.

Par exemple, vous pouvez regrouper les lignes par mot-clé et calculer automatiquement le nombre total de clics et d'impressions. Lorsque les données sous-jacentes changent, le tableau croisé dynamique se met automatiquement à jour.

Intégration de Connected Sheets et BigQuery

Les feuilles connectées permettent à Google Sheets de fonctionner directement avec BigQuery. BigQuery traite les ensembles de données volumineux, tandis que Sheets est utilisé pour l'analyse et la création de rapports. 

Au lieu de copier des millions de lignes dans une feuille, Connected Sheets exécute des requêtes sur BigQuery et affiche les résultats. Cela permet aux feuilles de calcul de rester rapides et réactives, même avec des ensembles de données très volumineux.

La connexion est établie depuis Google Sheets. L'API Google Sheets n'est pas utilisée pour configurer la connexion elle-même.

Voici comment cela fonctionne :

• Les données sont stockées dans une table BigQuery.
• Veuillez connecter une feuille Google Sheet à ce tableau.
• Sheets exécute des requêtes et affiche les résultats.
• Les données peuvent être actualisées sans exporter de fichiers.

Une fois connecté, vous pouvez créer des tableaux croisés dynamiques, appliquer des formules et générer des rapports directement dans Sheets.

Authentification et autorisations

L'accès à Connected Sheets est contrôlé par Google Cloud Identity and Access Management.

Le compte reliant Sheets à BigQuery doit disposer des éléments suivants :

• Autorisation pour BigQuery Data Viewer
• Autorisation utilisateur pour les tâches BigQuery
• Accès à la feuille Google Sheet

L'authentification est gérée automatiquement via l'interface Google Sheets.

Cependant, il est important de prendre en compte certaines restrictions. Bien que l'API Sheets puisse lire les données de Connected Sheets et appliquer des mises en forme ou créer des tableaux croisés dynamiques, elle ne peut pas modifier la requête BigQuery sous-jacente ni réécrire des données dans les plages prises en charge par BigQuery.

C'est pourquoi les feuilles connectées sont particulièrement adaptées à l'analyse et à la création de rapports, et non à l'insertion ou à la modification de données brutes.

Utilisation d'outils d'intégration sans code

Tous les flux de travail ne nécessitent pas de code personnalisé. Plusieurs outils sans code et à faible code se connectent directement à l'API Google Sheets et gèrent les détails techniques pour vous. Les trois options les plus populaires sont les suivantes :

• Apipheny
• Supermetrics
• Entonnoir

Ces outils offrent des interfaces visuelles permettant de sélectionner des sources de données, de définir des calendriers et d'envoyer les résultats vers Google Sheets. Ils gèrent l'authentification, les requêtes API et le traitement des erreurs en arrière-plan.

Cela rend les outils sans code particulièrement adaptés aux spécialistes du marketing et aux analystes qui ont besoin de pipelines de données fiables sans avoir à écrire de code. Ils réduisent également le temps de configuration pour les cas d'utilisation courants des rapports et des tableaux de bord.

Tarifs et limites de l'API

Pour la plupart des projets, vous pouvez utiliser l'API Google Sheets gratuitement. Il est essentiel de comprendre le fonctionnement des limites d'utilisation.

Aperçu des tarifs de l'API

Google ne vous facture pas par requête ou par action sur une feuille de calcul. Au lieu de cela, l'utilisation est contrôlée par des quotas. 

Tant que votre projet respecte ces limites, vous pouvez utiliser l'API sans frais. Si vous dépassez ces limites, les requêtes seront temporairement bloquées jusqu'à ce que l'utilisation diminue ou que les limites soient augmentées.

Limites d'utilisation et quotas

L'API Google Sheets impose des limites afin de maintenir la stabilité des performances et d'empêcher les abus. Ces limites s'appliquent généralement par projet et, dans certains cas, par utilisateur.

La plupart des projets rencontrent deux types de contraintes principales :

1. Demandes par minute : Cette limite contrôle le nombre d'appels API que votre projet peut envoyer dans un court laps de temps. Un nombre excessif de petites requêtes envoyées simultanément peut entraîner des erreurs temporaires.

Par défaut, l'API Google Sheets autorise :

Les limites s'appliquent principalement au nombre de requêtes envoyées par minute, et non au nombre total de requêtes envoyées par jour.

Pour respecter les limites et éviter tout problème :

• Regroupez plusieurs mises à jour en une seule requête.
• Évitez les lectures répétées pour les modifications mineures.
• Mettre les données en cache au lieu de les récupérer à nouveau
• Surveillez l'utilisation dans Google Cloud Console et procédez à des ajustements en temps opportun.

Optimisation des performances et gestion des quotas

Lorsque vous automatisez Google Sheets, les problèmes de performances proviennent généralement de l'envoi d'un nombre trop important de requêtes trop rapidement. Il est donc essentiel de comprendre le fonctionnement des quotas. 

Comprendre les quotas et les limites de débit des API

Google applique des quotas afin de limiter le nombre de requêtes que votre projet peut envoyer au cours d'une période donnée. Certaines limites s'appliquent par projet, tandis que d'autres s'appliquent par utilisateur.

Ces limites ont une incidence directe sur les flux de travail automatisés. Si votre script envoie un nombre excessif de requêtes trop rapidement, l'API commence à renvoyer des erreurs et le flux de travail s'interrompt. Cela se produit fréquemment lorsque les scripts :

• Mettre à jour les lignes une par une
• Lire à plusieurs reprises de grandes plages
• Exécuter trop fréquemment sans traitement par lots

La surveillance de l'utilisation vous permet de détecter les problèmes à un stade précoce. Dans la console Google Cloud, vous pouvez évaluer dans quelle mesure votre projet approche de ses limites et détecter les pics d'activité soudains. Voici comment procéder :

• Veuillez ouvrir votre projet.
• Veuillez vous rendre dans API et services > Services API activés. 
• Veuillez cliquer sur l'API Google Sheets.
• Quotas ouverts et limites du système.

Si vous constatez des pics soudains, cela indique généralement des modèles de requêtes inefficaces.

Quotas et limites du système. Image fournie par l'auteur.

Mise en œuvre du backoff exponentiel

Même les scripts bien rédigés peuvent rencontrer des erreurs API temporaires. Les plus courants sont faciles à repérer :

• Les erreurs 429 indiquent qu'un nombre excessif de requêtes a été envoyé en peu de temps.
• Les erreurs 5xx indiquent que l'API a rencontré un problème temporaire au niveau du serveur.

Ces erreurs se résolvent souvent d'elles-mêmes. Réessayer immédiatement la demande pourrait aggraver le problème.

Le backoff exponentiel est la méthode standard pour réessayer des requêtes en toute sécurité. Au lieu de réessayer immédiatement, votre script attend avant de réessayer. Le temps d'attente augmente après chaque échec.

Un modèle de backoff simple se présente comme suit :

• Veuillez envoyer une demande
• Si vous rencontrez une erreur 429 ou 5xx, veuillez patienter une seconde.
• Veuillez réessayer
• Si cela échoue à nouveau, veuillez patienter deux secondes, puis quatre, puis huit.
• Arrêter après un délai d'attente maximal ou un nombre maximal de tentatives

Cette approche réduit la pression sur l'API et augmente les chances de réussite d'une nouvelle tentative.

Le backoff exponentiel en pratique

Voici un exemple Python simple qui réessaie une requête en utilisant le backoff exponentiel :

import time
from googleapiclient.errors import HttpError

def run_with_backoff(callable_request, max_retries=5):
    delay = 1

    for attempt in range(max_retries):
        try:
            return callable_request()
        except HttpError as e:
            status = getattr(e.resp, "status", None)

            if status in [429, 500, 502, 503, 504]:
                time.sleep(delay)
                delay = min(delay * 2, 32)
                continue

            raise

    raise RuntimeError("Request failed after retries.")

Vous pouvez ensuite encapsuler n'importe quel appel API avec cette aide :

result = run_with_backoff(
    lambda: service.spreadsheets().values().get(
        spreadsheetId=spreadsheet_id,
        range="Report!A1:E100"
    ).execute()
)

Cela empêche votre script de se bloquer immédiatement lorsque le problème est temporaire, ce qui contribue à améliorer la fiabilité de vos flux de travail.

Stratégies d'optimisation

Les performances s'améliorent lorsque vous envoyez moins de requêtes et que vous réduisez la taille des charges utiles. La plupart des ralentissements surviennent lorsque les scripts effectuent trop de tâches en un seul appel.

Voici les techniques les plus efficaces pour éviter les ralentissements :

Demandes groupées

Les méthodes batch telles que spreadsheets.values.batchGet, spreadsheets.values.batchUpdate et spreadsheets.batchreadsheets.batchUpdate vous permettent de regrouper plusieurs actions en une seule requête. Cela permet d'obtenir le gain de performance le plus important.

Mise en cache

La lecture répétée des mêmes données entraîne une perte de temps et de quota. Récupérez les métadonnées de la feuille de calcul ou les identifiants des feuilles une seule fois, puis réutilisez-les pendant l'exécution du script.

Pagination

Certaines réponses API renvoient des données sous forme de pages. Veuillez traiter une page à la fois au lieu de charger tout le document en une seule fois. Cela permet d'éviter les délais d'attente et les problèmes de mémoire lors du traitement de grands ensembles de données.

Compression Gzip

La plupart des bibliothèques clientes Google et des clients HTTP prennent automatiquement en charge la compression gzip. La compression réduit la taille lorsque les réponses sont volumineuses, par exemple lors de la lecture de plages étendues ou de la récupération de métadonnées de feuilles de calcul.

Réponses partielles

Lorsque cela est possible, veuillez demander uniquement les champs dont vous avez besoin au lieu de la ressource complète. Les réponses plus courtes sont plus rapides et utilisent moins de bande passante.

Réduire au minimum la taille de la charge utile

Veuillez éviter de remplir les cellules une par une. Veuillez plutôt indiquer les plages complètes. Veuillez ignorer les mises à jour de formatage si aucun changement n'a été effectué et ne renvoyez pas les données inutilement.

Une règle simple s'applique ici : si un script parcourt les lignes en boucle et effectue un appel API à l'intérieur de cette boucle, il ne sera pas évolutif. Regroupez les tâches, réduisez le nombre de requêtes et veillez à ce que les charges utiles restent modestes.

Gestion des erreurs et dépannage

Les erreurs sont inévitables lorsque l'on utilise une API. Examinons donc comment résoudre rapidement les problèmes et garantir la fiabilité de l'automatisation. 

Problèmes courants liés à l'authentification

Les problèmes d'authentification constituent la cause la plus fréquente d'erreurs lors de la prise en main de l'API Google Sheets. La plupart d'entre eux sont dus à des problèmes de configuration plutôt qu'à des erreurs dans votre code.

Les problèmes courants incluent :

• Erreurs d'autorisation refusée: La feuille de calcul n'a pas été partagée avec l'adresse e-mail du compte de service, ou l'utilisateur n'a pas accordé les autorisations OAuth requises.
• Erreurs liées à des informations d'identification non valides ou à des fichiers introuvables: Le chemin d'accès au fichier clé JSON est incorrect ou le nom du fichier ne correspond pas.
• Erreurs liées à l'API non activée: L'API Google Sheets n'est pas activée dans le même projet Google Cloud que les informations d'identification.

Les problèmes liés à OAuth surviennent souvent lorsque les champs d'application ne correspondent pas à l'action que vous essayez d'effectuer. Par exemple, les champs d'application en lecture seule ne permettent pas l'écriture.

Les problèmes liés aux comptes de service sont généralement dus à l'absence de partage de fichiers. Un compte de service ne peut pas consulter de feuille de calcul à moins qu'elle ne soit explicitement partagée avec son adresse e-mail.

Erreurs de formatage des données et de requête

Les erreurs de requête surviennent généralement lorsque les données envoyées ne correspondent pas à ce que l'API attend. Les messages d'erreur sont souvent descriptifs, mais ils peuvent néanmoins prêter à confusion au premier abord.

Les causes courantes comprennent :

• Notation A1 non valide: Les noms des feuilles sont mal orthographiés ou les plages n'existent pas.

• Structure de données incorrecte: Les valeurs doivent être envoyées sous forme de tableaux bidimensionnels, même pour une seule ligne.

• Incompatibilités de types: Les nombres, les dates et les formules se comportent différemment selon valueInputOption

Lors du dépannage, veuillez toujours lire l'intégralité du message d'erreur, car les API Google indiquent généralement le champ exact qui n'a pas passé la validation.

Disponibilité des services et défis en matière de performance

Comme tout service cloud, l'API Google Sheets peut parfois renvoyer des erreurs serveur telles que 503 Service indisponible lors de pics de charge ou de pannes temporaires.

Ces erreurs sont généralement temporaires. Cependant, il est déconseillé de réessayer immédiatement, car cela pourrait aggraver la situation.

Pour gérer plus efficacement les problèmes de fiabilité :

• Veuillez réessayer uniquement en cas d'erreurs temporaires telles que les réponses 429 et 5xx.
• Utiliser un délai exponentiel entre les tentatives
• Regroupez les requêtes afin de réduire le trafic.
• Surveillez l'utilisation des quotas afin d'éviter d'atteindre les limites.

Une intégration résiliente suppose que certaines requêtes échoueront. Des tentatives de reprise, un traitement par lots et une surveillance appropriés permettent à vos scripts de se rétablir automatiquement et de continuer à s'exécuter sans intervention manuelle.

Meilleures pratiques et sécurité

L'API Google Sheets est facile à utiliser, mais sa fiabilité à long terme dépend de la manière dont nous concevons et sécurisons notre configuration.

Outils et ressources de développement

Google fournit des outils et des ressources officiels pour rendre le développement plus sûr et plus facile :

• L'explorateur d'API Google Workspace vous permet de tester les requêtes API Sheets directement dans le navigateur. Vous pouvez tester les points de terminaison, afficher les paramètres requis et examiner les réponses sans avoir à écrire de code. 
• Les guides de démarrage rapide de l'API Sheets présentent les configurations de base pour différents langages. Ils présentent l'authentification, un appel simple de lecture ou d'écriture, ainsi que les étapes de configuration courantes. 
• La documentation officielle reflète le comportement actuel et les limites connues.
• Une feuille de calcul test vous permet d'expérimenter sans compromettreles données de production ni écraser les rapports actifs.

Utilisation des bibliothèques clientes et du SDK

Google gère des bibliothèques clientes officielles pour l'API Sheets. Ces bibliothèques gèrent l'authentification, les nouvelles tentatives et le formatage des requêtes. Nous pouvons réduire les erreurs et gagner du temps grâce aux bibliothèques prises en charge. Ils s'adaptent également mieux aux mises à jour de l'API qu'un code HTTP personnalisé.

Les langages pris en charge sont Python, JavaScript avec Node.js, Java et Go.

Chaque bibliothèque suit la même structure API de base, mais certaines proposent des aides spécifiques à chaque langage. Python et Node.js sont souvent privilégiés pour l'automatisation en raison de leur configuration plus simple et du soutien important de leur communauté.

Les bibliothèques clientes s'intègrent également de manière transparente à d'autres API Google, ce qui est utile lorsque le flux de travail implique Drive, BigQuery ou Gmail.

Gestion des identifiants et sécurité

Les identifiants contrôlent l'accès à vos données, veuillez donc les gérer correctement en suivant ces bonnes pratiques :

• Veuillez ne jamais soumettre les fichiers clés au contrôle de version.

• Veuillez stocker les clés en dehors des référentiels publics.

• Veuillez utiliser des variables d'environnement ou des gestionnaires de secrets en production.

• N'accordez que les autorisations nécessaires.

• Préférez les rôles spécialisés aux rôles généraux.

• Limiter l'accès au compte de service à des tâches spécifiques

• Soyez attentif aux pics inhabituels ou aux modèles d'accès inconnus.

• Veuillez changer régulièrement les clés et révoquer et remplacer immédiatement les clés compromises.

Conclusions finales 

La meilleure étape suivante consiste à automatiser une petite tâche que vous effectuez déjà manuellement. Veuillez sélectionner une feuille de calcul dont vous avez le contrôle, lire une plage, puis y inscrire ou y ajouter des données. Une fois que cela fonctionne, établissez un calendrier et laissez le processus se dérouler de manière autonome.

C'est là que l'API Google Sheets commence à démontrer son utilité. 

Il n'est pas nécessaire de disposer immédiatement d'un système complexe. Un flux de travail fiable suffit pour gagner du temps et facilite considérablement la création du suivant. Si vous souhaitez perfectionner ces compétences parallèlement à l'automatisation, veuillez consulter notre cursus sur les principes fondamentaux de Google Sheets ou nos cours d'introduction à Google Sheets et de niveau intermédiaire sur Google Sheetsafin de tirer le meilleur parti de chaque flux de travail que vous automatisez.