Google Sheets Automation Utilisation de Python | Analytique vidhya

Google Sheets est l'une des alternatives les plus populaires et les plus utilisées pour exceller, offrant un environnement collaboratif avec des fonctionnalités telles que l'édition en temps réel, le contrôle de version et l'intégration transparente avec Google Suite, permettant aux utilisateurs d'appeler Google Sheets dans Google Docs et de tirer parti du meilleur de l'espace de travail Google. Bien que vous puissiez facilement charger et travailler avec des fichiers Excel par programmation à l'aide de PANDAS, une bibliothèque de science des données principale, une configuration similaire peut être reproduite avec Google Sheets. En tant qu'offre SaaS, l'accès aux données Google Sheets nécessite l'utilisation de son API, qui prend en charge divers langages de programmation, notamment Java, JavaScript, Node.js, PHP, Ruby, Python et le propre appscript de Google. Cet article se concentre sur l'utilisation de Python pour charger efficacement les données Google Sheets dans les trames de données Pandas , permettant des transformations et des analyses rapides de données. Une fois les modifications terminées, la bibliothèque GSPread Python peut être utilisée pour repousser les données mises à jour sur Google Sheets, fournissant une interface pratique pour interagir avec l'API Google Sheets.

Cet article a été publié dans le cadre du Blogathon de la science des données.

Table des matières

• Configuration de votre projet Google Cloud
• Comprendre Gspread
• Établir la connexion et l'ouverture de feuille de calcul
• Créer et gérer Google Sheets
• Gestion des feuilles de travail
• Propriétés cellulaires
• Ajout de nouvelles lignes et colonnes
• Rechercher les valeurs des cellules et des gammes
• Mise à jour des cellules et des gammes
• Suppression de lignes et de colonnes
• Cellules de recherche
• Formatage des cellules
• Gamme claire de cellules et de feuilles de calcul
• Limites de l'API Google
• Questions fréquemment posées

Configuration de votre projet Google Cloud

Comme mentionné précédemment, Google Sheets est une offre SaaS, vous devez donc préparer des étapes supplémentaires pour l'automatisation. Google Cloud Platform (GCP), une plate-forme populaire de cloud computing, propose une variété de services qui aident à interagir avec les produits Google ainsi que le déploiement de vos projets personnalisés.

D'une manière générale, nous devons suivre ces 3 étapes pour commencer avec Google Sheets Automation.

Création et configuration d'un projet Google Cloud

Rendez-vous sur https://console.cloud.google.com/ et inscrivez-vous pour un compte gratuit. Suivant en haut à gauche, cliquez sur le menu de sélection du projet et sélectionnez un nouveau projet. Fournissez un nom de projet, laissant l'organisation comme «pas d'organisation», appuyez sur Créer et votre projet GCP est maintenant défini.

Génération et sécurisation des références API

Nous devons activer l'API Google Sheets et Google Drive et créer un compte de service. Ce type de compte spécial nous permet d'accéder et de gérer Google Cloud Resources sans nécessiter une interaction humaine. Pour activer l'API Google Sheets, recherchez des feuilles dans la barre de recherche supérieure et sélectionnez "Google Sheets API". Cliquez sur Autoriser et il nous redirigera vers la page API Détails. Cliquez ici sur «Créer des informations d'identification» et cela ouvrira le formulaire de création d'identification.

Sélectionnez «Données d'application» dans la zone de sélection et cliquez sur Suivant. Sur l'écran suivant, fournissez un nom significatif pour le compte de service, comme vous l'utiliserez dans les étapes ultérieures. Ensuite, sélectionnez le rôle «éditeur». Enfin, cliquez sur le bas.

De même, l'API Google Drive peut être activé. Veuillez noter que nous n'avons pas besoin de créer un autre compte de service pour cette API. Le compte de service existant pourra accéder aux deux API. Maintenant, nous devons télécharger les informations d'identification JSON, que notre script Python consommera pour accéder à Google Sheets. Cliquez sur l'e-mail du compte de service nouvellement généré, passez à l'onglet Keys, cliquez sur le bouton Ajouter la touche pour sélectionner l'option Créer une nouvelle clé, sélectionner JSON, puis créer.

Accordant l'accès à Google Sheets

Notre script Python utilisera les informations d'identification générées pour accéder aux feuilles Google. Cependant, nous devons accorder manuellement l'accès aux fichiers que notre script utilisera. Pour ce faire, copiez l'e-mail généré pour le compte de service (trouvé dans l'onglet Détails du compte Service) et ajoutez cet e-mail en tant qu'éditeur aux fichiers souhaités.

Comprendre Gspread

GSPread est un emballage API Python pour Google Sheets. Il résume de nombreuses fonctionnalités offertes par Google SheetsAPI dans des classes distinctes et des méthodes d'accès. Cela rend l'interaction avec Sheetsapi facile à naviguer et on peut rapidement le récupérer.

Pour configurer la bibliothèque dans l'environnement local, on peut utiliser une commande PIP simple, comme pour tout autre package Python. Installez la bibliothèque dans un environnement séparé comme meilleure pratique pour éviter tout conflit de dépendance.

 pip install gspread

Une note rapide sur les références de cellules

Les adresses cellulaires dans Google Sheets peuvent être référencées en utilisant deux notations populaires:

• NOTATION A1: Cette référence de cellule se compose du nom de la feuille, du numéro de ligne et de la lettre de colonne. Cette référence fonctionne sans mentionner le nom de la feuille et vous permet de vous référer à une seule cellule, à une gamme de cellules ou à une colonne entière.
• Plage nommée: Il s'agit d'une gamme définie de cellules ayant un nom personnalisé pour une identification facile et une référence simplifiée sur la feuille Google.

Établir la connexion et l'ouverture de feuille de calcul

Maintenant que nous avons configuré l'accès et les bibliothèques nécessaires, testons notre code. Dans la feuille de calcul contenant l'e-mail du compte de service, entrez un texte aléatoire dans la première cellule. Nous tenterons de récupérer cette valeur en utilisant notre script Python.

Nous utiliserons le module JSON pour charger nos informations d'identification et la transmettre à la fonction «Service_Account_From_Dict ()» du GSPread. Cela renverra un objet client Google Sheets et cet objet peut être utilisé pour ouvrir n'importe quelle feuille Google à l'aide de la fonction "Open ()". Voir le code ci-dessous.

 importer gspread
Importer JSON

avec open ('créns.json') comme f:
    Création de commande = JSON.Load (F)

gc = gspread.service_account_from_dict (informations d'identification)

sh = gc.open ("articlesmo")

Il existe deux autres façons d'ouvrir une feuille Google au lieu d'un nom de titre. Ces moyens alternatifs éliminent la dépendance du nom de titre comme dans Google Workspace, plusieurs fichiers peuvent avoir le même titre. Dans le cas des feuilles de calcul, s'il y a deux fichiers avec le même titre, le dernier fichier sera accessible par l'API. Nous pouvons accéder aux feuilles de calcul à l'aide de l'URL de fichier ou de l'ID unique de la feuille de calcul qui procède le lien suivant: «https://docs.google.com/spreadsheets/d//edit». Vous trouverez ci-dessous le code pour accéder à la feuille de calcul via URL ou ID unique.

 ## Accès via une pièce d'identité unique
sh = gc.open_by_key ("1r97twcm0fffnssrh_0fjddg-hcqf5plhbhrxu9ptv_q")


## Accès via URL
sh = gc.open_by_url ("https://docs.google.com/spreadsheets/d/1r97twcm0fffnssrh_0fjddg-hcqf5plhbhrxu9ptv_q/edit?gid=0#gid=0")

Le code suivant lira la valeur entrée plus tôt dans la feuille. Le code fonctionnel sera expliqué dans les sections ultérieures de l'article.

 print (sh.sheet1.acell ('a1'). valeur)

Cela renverra la valeur présente dans la cellule A1 de la feuille, ce qui dans notre cas est «Gsheet: c'est la première cellule». Maintenant, nous sommes prêts à plonger en profondeur dans la bibliothèque GSPread et à explorer toutes les options disponibles.

Remarque: le shvariable détient l'objet de feuille de calcul et il sera mentionné tout au long du guide

Créer et gérer Google Sheets

Il pourrait y avoir de nombreux cas d'utilisation où une feuille de calcul est créée par programme. On pourrait constituer une solution pour publier des données et des informations pour leurs utilisateurs. Parallèlement à cela, ils pourraient vouloir partager cette feuille directement avec l'utilisateur.

• Pour créer une nouvelle feuille de calcul, utilisez la fonction create () du client GSPread. Passez le titre de la nouvelle feuille de calcul en tant que paramètre, et si vous souhaitez spécifier l'emplacement, utilisez le paramètre Folder_ID.
• La nouvelle feuille de calcul créée n'est accessible que par l'utilisateur du compte de service. Cela signifie que la feuille de calcul ne sera pas visible même par l'utilisateur qui a créé le compte de service. À cette fin, nous pouvons utiliser la fonction «Share ()» de l'objet de feuille de calcul. Cette fonction nécessite 3 paramètres obligatoires: «Email_Address» (adresse e-mail), «perm_type» (type d'autorisation) et «rôle». Le type d'autorisation peut prendre les valeurs suivantes: utilisateur, groupe, domaine ou n'importe qui. Pour la plupart des cas d'utilisation, la valeur de l'utilisateur fonctionnera. Le perm_type a également un nombre fixe de valeurs acceptables: «lecteur», «commentateur», «écrivain», «file organisateur», «organisateur» et «propriétaire». Il existe également des paramètres facultatifs supplémentaires, offrant un niveau d'informations granulaires.
  • «Notifier»: valeur booléenne à contrôler si l'utilisateur doit obtenir une notification du fichier partagé.
  • «Email_Message»: valeur de la chaîne pour que le message soit envoyé avec l'e-mail de notification.

 sh = gc.create ('omniprémotest') ## Création d'une nouvelle feuille de calcul
sh.share (email_address = '[e-mail protégé]', perm_type = 'utilisateur', role = 'écrivain', notify = true, email_message = "Ceci est un fichier de test")

Gestion des feuilles de travail

Chaque feuille de calcul est une collection de feuilles de calcul. Une simple analogie avec cela est la façon dont un livre a plusieurs pages. À l'aide de GSPread, les utilisateurs peuvent accéder, modifier, supprimer ou créer de nouvelles feuilles de calcul. Jetons un coup d'œil à chacune de ces fonctions.

Sélection de feuille de travail

La (s) feuille de calcul d'un objet de feuille de calcul est accessible en utilisant les méthodes suivantes de l'objet de feuille de travail:

• Accès par index: La fonction «get_worksheet ()» prend l'index de la feuille de calcul qui doit être accessible.
• Accès par titre: La fonction «Worksheet ()» reprend le titre de la feuille de calcul. Notez que les titres de feuille de travail sont uniques par nature et par conséquent, il n'y a pas deux feuilles de calcul ne peuvent avoir le même titre.
• Accès par raccourci de notation de points: Le raccourci de notation de points permet d'accéder à la première feuille de calcul de la feuille de calcul sans distribuer un titre, un index ou une ID.
• ACCÈS ALL: La fonction «Feuilles de travail ()» renvoie toutes les feuilles de calcul de la feuille de calcul. Il les renvoie sous forme d'objets de feuille de travail GSPREAD. Le «titre» et «ID» sont quelques propriétés importantes de cette classe qui aident à accéder aux feuilles de calcul souhaitées en vrac.
• Accès par ID: En développant des scripts d'automatisation, il peut y avoir des cas d'utilisation où nous avons affaire à ID de la feuille de travail au lieu des titres. Dans de tels scénarios, la fonction «get_worksheet_by_id ()» peut être utilisée.

Voici l'exemple de code pour toutes les méthodes énumérées.

 print (sh.get_worksheet (0))
print (sh.worksheet ("Articleworksheet1"))
imprimer (sh.sheet1)
print (sh.get_worksheet_by_id (0))

imprimer ("maintenant aller chercher toutes les feuilles ...")

## Retour toutes les feuilles de travail
pour ws dans sh.worksheets ():
    Imprimer (WS)

Toutes ces instructions d'impression renvoient l'objet de la feuille de travail

Création d'une nouvelle feuille de travail

Outre les feuilles de travail existantes dans la feuille de calcul, nous pouvons créer par programme de nouvelles feuilles de calcul dans la même feuille de calcul. Cette approche peut être utile lors du traitement des données à partir d'une feuille de travail existante et de la publication des résultats dans une feuille de calcul distincte.

Pour créer une nouvelle feuille de calcul, nous devons utiliser la fonction «add_worksheet ()» de l'objet de feuille de travail. Il prend les paramètres suivants.

• Titre: Le titre de la feuille de travail
• lignes, colonnes, index (facultatif): nous devons définir le nombre de lignes et de colonnes pour la feuille de calcul nouvellement créée. Le paramètre «Index» est facultatif et il contrôle la commande de la feuille de calcul.

Le code ci-dessous créera une feuille de calcul avec 100 lignes et 20 colonnes et placera la feuille de calcul en deuxième position.

 sh.add_worksheet ('Articleworksheet1.5', lignes = 100, cols = 20, index = 1)

Et il l'a placé en deuxième position (index 1)

Renommer une feuille de travail

Vous pouvez renommer les titres de feuille de calcul à l'aide de la fonction update_title () de l'objet de feuille de calcul, qui accepte le nouveau titre en tant que paramètre.

 print (sh.worksheet ("Articleworksheet3"). Update_title ("Articleworksheet2.5"))

Suppression d'une feuille de travail

Une feuille de calcul peut être supprimée d'une feuille de calcul en utilisant la fonction suivante de l'objet de feuille de calcul:

• Supprimer une feuille de calcul à l'aide de l'objet de la feuille de travail: la fonction «del_worksheet ()» prend l'objet de feuille de travail en tant que paramètre et supprime la feuille de calcul de la feuille de calcul.
• Supprimez une feuille de calcul à l'aide de l'ID de la feuille de calcul: la fonction «del_worksheet_by_id ()» prend l'ID de feuille de calcul en entrée pour supprimer la feuille de calcul.

La sélection de la fonction à utiliser dépend du cas d'utilisation que le script est effectué. Vous trouverez ci-dessous l'échantillon de code démontrant l'utilisation des deux fonctions.

 sh.del_worksheet (sh.worksheet ("Articleworksheet2.5"))
sh.del_worksheet_by_id ('602396579')

Propriétés cellulaires

Nous nous rétrécissons lentement de haut en bas et atteignons la plus petite unité (et la plus importante) de notre feuille de travail, une cellule. Une cellule est une intersection d'une ligne et d'une colonne. Pour la bibliothèque GSPread, il détient les propriétés suivantes:

• Row: Numéro de ligne pour la cellule
• Col: numéro de colonne pour la cellule
• Valeur: la valeur de la cellule
• Adresse: l'adresse de la cellule dans la notation A1

L'exemple de code ci-dessous accède à toutes les propriétés d'une cellule. La cellule particulière pour l'inspection est renvoyée en utilisant la fonction cellulaire de la feuille de calcul.

 samplecell = sh.worksheet ("Articleworksheet1"). Cell (row = 1, col = 1)
print ('row: {} \ nColumn: {} \ nValue: {} \ naddress: {}'. Format (samplecell.row, samplecell.col, samplecell.value, samplecell.address))

Tous ces accessoires entreront en jeu une fois que nous aurons affaire à des fonctions d'ordre supérieur de la bibliothèque.

Ajout de nouvelles lignes et colonnes

Commençons par ajouter de nouvelles lignes et colonnes à notre échantillon de feuille de calcul existante pour avoir des données sur lesquelles travailler pour les sections ultérieures de ce guide. L'insertion en tant qu'opération est prise en charge de deux manières par la bibliothèque GSPread.

Insertion à une position particulière

Vous pouvez insérer une ligne ou une colonne à une position spécifique à l'aide des fonctions INSERT_ROW (), insert_rows () et insert_cols () de l'objet de feuille de travail. Ces fonctions nous permettent d'ajouter les lignes ou les colonnes à un emplacement particulier dans une feuille de calcul. Les spécificités de la fonction sont comme ci-dessous:

• INSERT_ROW: La fonction nécessite le paramètre «valeurs» comme une liste de valeurs à insérer. L'ordre des valeurs dans la liste détermine l'ordre des lignes insérées. Le paramètre «Index», qui par défaut 1, spécifie la position de l'insertion de ligne. Des paramètres facultatifs comme "Value_Input_Option" et "HEHERIT_FROM_BEFore" contrôlent comment la fonction interprète les données et les lignes d'entrée, et si elle doit pousser les données directement ou l'analyser comme si l'utilisateur tapait l'interface utilisateur.
• INSERT_ROWS: il prend la liste des listes dans le paramètre «Valeurs» pour insérer plusieurs lignes. Chaque liste agit comme une seule ligne. En interne, il s'agit de l'implémentation réelle de la façon dont les lignes sont insérées dans la feuille de calcul via GSPread. La fonction "la fonction insert_row ()" appelle la fonction "insert_rows ()" et, par conséquent, tous les paramètres décrits pour la fonction "insert_row ()" sont vraies pour "insert_rows ()" sauf un paramètre. Dans la fonction insert_row (), vous déterminez le décalage à l'aide du paramètre d'index, tandis que dans la fonction insert_rows (), vous le spécifiez avec le paramètre de ligne.
• insert_cols: cette fonction est une réplique de la fonction "insert_rows ()" avec un nom de paramètre modifié pour le décalage de "Row" à "Col". Le reste de la fonction de paramètre facultatif reste le même.

Insertion après une plage de table

Cette insertion ne s'applique qu'aux lignes. Il nous permet d'insérer des lignes après une plage de table particulière, où la position est inconnue. Encore une fois, l'insertion peut être effectuée de manière unique ou multi-rangs.

• APPEND_ROW: il prend les valeurs de ligne en tant que liste via le paramètre «Valeurs». Le paramètre «Table_Range» aide à définir la plage de table, après quoi l'insertion de ligne devrait se produire. La plage est donnée en notation A1.
• APPEND_ROWS: De même, le "INSERT_ROWS ()", le "APPEND_ROWS ()" est l'implémentation réelle de l'insertion des lignes après une plage de table. Tous les paramètres pour les deux fonctions restent les mêmes avec la différence que «APPEND_ROWS ()» prend une liste de listes dans le paramètre «Valeurs».

Voici l'exemple de code qui:

• Ajoute une ligne pour les colonnes: A, B, C et D
• Ajoute 4 lignes sous ces colonnes

 sampleworksheet.insert_row (
    ['A', 'B', 'C', 'D']
)
sampleworksheet.insert_rows (
    [
        ['Kg', 54, 23, 12],
        ['OG', 34, 12, 34],
        [«Moi», 23, 45, 90],
        ['Ye', 65, 12, 54]
    ], ligne = 2
)

Maintenant, faisons les étapes suivantes en plus de ceci:

• Ajoutez 2 rangées pour continuer dans cette gamme de table
• Ajouter une autre colonne E

 sampleworksheet.append_rows (
    [
        ['Sn', 67, 87, 45],
        ['AR', 56, 23, 65]
    ],
    table_range = "a1: d5"
)
sampleworksheet.insert_cols (
    [
        ['E', 56, 34, 65, 34, 76, 45]
    ],
    col = 5
)

Remarque: La variable d'échantillon de feuille de travail contient l'objet de feuille de travail et il sera mentionné tout au long du guide.

Rechercher les valeurs des cellules et des gammes

Dans les dernières sections, nous avons préparé nos données par programme en utilisant diverses opérations d'insertion. Maintenant, nous pouvons récupérer les données insérées à l'aide de diverses fonctions de lecture. Nous verrons comment récupérer les cellules, puis nous déplacer pour récupérer les valeurs à partir d'une gamme de cellules et de l'ensemble de la feuille de travail.

Récupérer une seule cellule

L'opération de lecture la plus élémentaire sur une feuille de calcul consiste à obtenir la valeur ou toute autre propriété cellulaire comme décrit dans les sections précédentes. Pour récupérer une seule cellule, il y a deux fonctions:

• ACELL: Cela prend l'adresse cellulaire dans la notation A1 et renvoie un objet cellulaire.
• Cellule: Cela prend les coordonnées cellulaires dans l'ordre de (ligne, colonne).

Ces deux fonctions renvoient un objet cellulaire et nous avons déjà vu comment obtenir la valeur de ces objets. La fonction ACELL a été utilisée dans la section où nous avons établi une connexion avec l'API Google Sheets.

 Imprimer (Sampleworksheet.acell ('A1'). Row)
Imprimer (échantillon de travail

Récupérer toutes les cellules de la feuille de travail ou de la plage

• Nous pouvons obtenir toutes les cellules de la feuille de calcul dans une liste d'objets cellulaires à l'aide de la fonction «get_all_cells ()» de l'objet de feuille de calcul. Il n'y a pas de paramètre pour cette fonction et il peut être directement appelé sur un objet de feuille de calcul.
• Pour récupérer des objets cellulaires pour une plage spécifique, utilisez la fonction Range () de l'objet de feuille de calcul. Cette fonction accepte diverses formes d'entrée, telles que la notation A1, les limites numériques ou les plages nommées. S'il est utilisé sans entrée, il renvoie toutes les cellules dans la feuille de calcul dans un seul appel API. Pour les cas d'utilisation impliquant des propriétés cellulaires, cette fonction aide à filtrer les cellules souhaitées et à effectuer d'autres actions.

 imprimer (sampleworksheet.get_all_cells ())
Imprimer (exemples de travaux de travail.Range ('B4: E5'))

Remplir les valeurs de la gamme de cellules

Les utilisateurs créent généralement plusieurs tables miniatures dans la même feuille de calcul pour une meilleure accessibilité. Dans de tels cas, nous devons affiner notre plage de récupération aux adresses exactes de ces gammes de table. Pour récupérer ces plages de table, nous pouvons utiliser les deux fonctions suivantes de l'objet de feuille de calcul:

• GET: La fonction «get ()» prend la plage de table dans la notation A1 ou une plage nommée et renvoie la liste des listes de valeurs.
• Batch_get: la fonction "get ()" ne peut prendre qu'une plage, mais si nous avons affaire à plusieurs gammes, nous pouvons utiliser Batch_get. Cette fonction fait un appel API, ce qui économise le coût.

 print ('get gamme: {}'.
print ('batch get range: {}'.
    "A1: D4",
    "B4: E3"
]))

Récupérer toutes les valeurs à partir d'une ligne ou d'une colonne

Nous pouvons récupérer toutes les valeurs d'une ligne ou d'une colonne à l'aide des fonctions «Row_values ​​()» et «col_values ​​()» de l'objet de feuille de travail. Les deux fonctions prennent la position (numérotation à partir de 1) d'une ligne ou d'une colonne et renvoie les valeurs dans une liste.

 Imprimer (exemples de travaux de travail.row_values ​​(1))
Imprimer (Sampleworksheet.col_values ​​(4))

Remplir des valeurs de feuille de calcul entières

L'un des meilleurs cas d'utilisation de récupération d'une feuille de travail entière serait de charger ces données directement dans un cadre de données Pandas, puis de faire le post-traitement ou l'analyse selon l'exigence. Les données entières peuvent être renvoyées à l'aide des fonctions suivantes de l'objet de la feuille de travail:

• Liste des listes: la fonction «get_all_values ​​()» renvoie chaque ligne comme une liste, puis toutes les listes de lignes dans une liste. Cette fonction est un alias à la fonction «get_values ​​()» mais intéressant, la fonction «get_values ​​()» est implémentée à l'aide de la fonction «get ()». La fonction «get ()» sans entrées renvoie la liste des listes. Par conséquent, les 3 fonctions sont les mêmes.
• Liste des dictionnaires: la fonction «get_all_records ()» renvoie une liste de dictionnaires. Chaque dictionnaire est un mappage de valeurs de clé où les clés sont les valeurs de première rangée et les valeurs comme valeurs de ligne suivante. Chaque ligne obtient son dictionnaire. Par défaut, il suppose que la première ligne est la clé, mais nous pouvons le faire prendre conscience d'une ligne différente comme clé en utilisant le paramètre «En-tête». Il existe également des paramètres supplémentaires qui peuvent aider à gérer les cellules vides et les en-têtes attendus.

Vous pouvez transmettre directement les sorties des deux fonctions à la fonction Pandas DataFrame pour obtenir la table de feuille de travail en tant que Pandas DataFrame.

 Importer des pandas en tant que PD
print (pd.dataframe (samptansworksheet.get_all_records ()))
print (pd.dataframe (samptansworksheet.get_all_values ​​()))

Mise à jour des cellules et des gammes

La mise à jour des données existantes de la feuille de calcul est la fonction la plus cruciale qui peut être effectuée facilement à l'aide de fonctions de bibliothèque gspread. Il existe plusieurs façons de mettre à jour les cellules d'une feuille de calcul, une mise à jour unique de cellules à plusieurs cellules d'une plage, puis à plusieurs gammes avec un seul appel API.

Mise à jour d'une seule cellule

Une seule cellule d'une feuille de calcul peut être mise à jour en utilisant les fonctions suivantes de l'objet de feuille de calcul.

• Update_acell: cette fonction prend deux paramètres, l'adresse cellulaire dans la notation A1 et la valeur à mettre à jour
• Update_cell: cette fonction prend les coordonnées de la cellule dans l'ordre des colonnes de ligne et la valeur de mise à jour
• Mise à jour: Bien que cette fonction ait une plus grande portée pour mettre à jour plusieurs cellules, elle peut également être utilisée pour mettre à jour une seule cellule. L'ordre des paramètres d'entrée est différent des deux fonctions ci-dessus. La fonction «Update ()» prend une liste de listes comme première valeur puis l'adresse cellulaire.

 Imprimer (Exemple de travail
imprimer (sampleworksheet.update_acell ('a3', 'oggy')))
print (sampleworksheet.update ([['bonjour']], 'a4'))

Mettre à jour une gamme de cellules

Vous pouvez mettre à jour une gamme de cellules dans une feuille de calcul en utilisant les deux fonctions suivantes de l'objet de la feuille de calcul.

• Update_cells: cette fonction fonctionne mieux en combinaison avec la fonction «range ()». La fonction «Update_cells ()» prend la saisie comme liste des cellules. Cette liste de cellules peut faire modifier leurs valeurs par la boucle sur les objets cellulaires renvoyés de la fonction de plage et accéder à leur propriété de valeur.
• MISE À JOUR: Comme indiqué précédemment dans les mises à jour unicellulaires, vous pouvez utiliser cette fonction pour mettre à jour une plage nommée ou une plage notée A1.

 rangeofcells = samponworksheet. range ('b2: ​​b7')
pour la cellule dans RangeofCells:
    newValue = int (Cell.Value) 10
    Cell.Value = NewValue
Imprimer (Sampleworksheet.update_cells (rangeofcells)))

Le code ci-dessus récupère une gamme de cellules, ajoute 10 à leur valeur et les met à jour en un seul appel API.

Mise à jour de plusieurs gammes de cellules

Dans la section ci-dessus, nous avons pu mettre à jour plusieurs cellules dans une plage avec un seul appel API. Ce comportement peut également être étendu à plusieurs gammes. Cela signifie que nous pouvons mettre à jour plusieurs groupes de cellules avec un seul appel. La fonction «Batch_update ()» prend une liste de dictionnaires avec des clés comme plage et valeurs. La valeur de clé de plage doit être la plage de notation A1 ou une plage nommée et la valeur de clé de valeurs comme liste de la liste des valeurs.

 plage1 = 'C2: C7'
plage2 = 'e2: e7'
BothrangeValues ​​= Sampleworksheet.batch_get ([
    gamme 1,
    plage2
])
plage1Values, gall2Values ​​= BothRangeValues
plage1updatedValues ​​= [[int (x [0]) 10] pour x dans range1Values]
range2updatedValues ​​= [[int (x [0]) 20] pour x dans range2Values]
print (sampleworksheet.batch_update ([
    {
        'plage': plage1,
        «valeurs»: plage1 updatedValues
    },

    {
        'plage': range2,
        «valeurs»: plage2updatedValues
    }
]))

Le code ci-dessus récupère deux plages à l'aide de la fonction "Batch_get ()", puis met à jour leurs valeurs localement, puis utilise la fonction "batch_update ()" pour repousser les valeurs mises à jour sur les feuilles Google. La sortie de cette mise à jour ressemble à ceci:

Suppression de lignes et de colonnes

Jusqu'à ce point, nous avons inséré, lu et mis à jour les données de la feuille de calcul. Nous pouvons effectuer des opérations de suppression pour supprimer les données redondantes ou inutiles de la feuille de calcul. La fonction "Delete_Rows ()" et "Delete_Colums ()" prend le "start_index" à supprimer. Si "end_index" est spécifié, il supprime toutes les colonnes dans la plage d'index du début et de la fin.

 Imprimer (Sampleworksheet.Delete_Columns (4))
Imprimer (Sampleworksheet.Delete_Rows (6))

Cellules de recherche

L'API Google Sheets vous permet de rechercher des cellules en faisant correspondre une chaîne ou une expression régulière. Vous pouvez effectuer des recherches sensibles à la casse ou insensibles à des cas et affiner la recherche à des lignes ou des colonnes spécifiques si vous le souhaitez. Utilisez ces deux fonctions de feuille de calcul pour trouver des cellules correspondantes:

• Find: La fonction «Find ()» renvoie la première occurrence du match. Cela prend la chaîne de recherche ou un regex, des paramètres «in_row» ou «in_column» pour affiner la recherche et l'indicateur «Case_Sentitive» pour contrôler le type de recherche. Les paramètres «in» prennent les positions de ligne ou de colonne (index 1)
• Findall: la "findall ()" est l'étape avancée de la fonction "Find ()" où il renvoie toutes les correspondances de la recherche.

 Importer RE
Imprimer (sampleworksheet.find ('64 ', in_column = 2))
searchre = re.Compile (r '(a | a)')
Imprimer (Sampleworksheet.Findall (Searchre))

Formatage des cellules

Dans Excel, vous pouvez formater les feuilles de calcul de diverses manières, y compris les moments forts de texte, la mise en forme, les frontières, l'alignement et de nombreuses fonctions. Les feuilles Google propose également une variété d'options de formatage pour les cellules. Toute la liste des champs est disponible dans la documentation des cellules Google Sheets .

Vous pouvez utiliser la fonction format () de l'objet de feuille de calcul GSPread pour spécifier la cellule ou la plage où vous souhaitez appliquer le formatage. Fournissez le format en tant que dictionnaire JSON, qui comprend tous les champs clés de formatage et leurs valeurs.

Le code ci-dessous appliquera les frontières à toutes les cellules de la table.

 borderFormatting = {
    "style": "solide",
    "Colorstyle": {"rgbcolor": {"rouge": 0, "vert": 0, "bleu": 0, "alpha": 1}},
}

imprimer(
    Sampleworksheet.format (
        "A1: D6",
        format = {
            "Borders": {
                "Top": BorderFormatting,
                "en bas": borderformatting,
                "à gauche": borderformatting,
                "à droite": borderformatting,
            },
        },
    )
)

Nous pouvons également appliquer le formatage par lots pour formater plusieurs plages en même temps. Cela fait gagner beaucoup de temps à écrire différents appels de format pour chaque nouveau changement. La fonction «batch_format ()» prend la liste des dictionnaires contenant deux clés importantes. La première clé est la clé de plage qui définit la portée de la cellule et la clé de format qui contient le dictionnaire de formatage.

Faisons ce qui suit sur notre tableau d'échantillonnage à l'aide de la fonction «Batch_Format ()»:

• Appliquer des frontières sur toutes les cellules de la table.
• Audace du texte de la première ligne, indiquant que ce sont les colonnes de notre table.
• Alignez toutes les données du centre.
• Ajoutez une couleur bleu clair pour les colonnes (la première ligne).

 borderFormatting = {
    "style": "solide",
    "Colorstyle": {"rgbcolor": {"rouge": 0, "vert": 0, "bleu": 0, "alpha": 1}},
}
formats = [
    {
        "plage": "a1: d6",
        "format": {
            "Borders": {
                "Top": BorderFormatting,
                "en bas": borderformatting,
                "à gauche": borderformatting,
                "à droite": borderformatting,
            },
            "Horizontalalignment": "Centre",
        },
    },
    {
        "plage": "A1: D1",
        "format": {
            "TextFormat": {
                "Bold": vrai,
            },
            "backgroundColorstyle": {
                "rgbcolor": {"rouge": 0,8, "vert": 0,8, "bleu": 1, "alpha": 0,8}
            },
        },
    },
]]

Imprimer (sampleworksheet.batch_format (formats))

Et c'est l'état final de notre table.

Gamme claire de cellules et de feuilles de calcul

Il est possible que nous souhaitions effacer la plage avant d'effectuer les opérations. Pour nettoyer les plages de cellules, la fonction «Batch_Clear ()» de l'objet de calcul de l'objet de travail doit être utilisée. Cela prend la liste des gammes qui doivent être effacées. Effacement de la colonne C de notre tableau d'échantillons.

 Imprimer (sampleworksheet.batch_clear (["C1: C6"]))

Remarque: la fonction effacer uniquement les valeurs et non le formatage appliqué.

La feuille de calcul entière peut être effacée à l'aide de la fonction «Clear ()» de l'objet de feuille de travail.

 imprimer (sampleworksheet.clear ())

Découvrez cet article sur la façon d'utiliser Python pour automatiser les feuilles Google

Limites de l'API Google

Nous avons fait beaucoup d'opérations sur Google Sheets en utilisant la bibliothèque GSPread. Cette bibliothèque est juste un wrapper qui prépare les données adoptées par l'utilisateur dans le format acceptable et rend les appels d'API aux projets Google associés aux feuilles. Le développeur interagit uniquement avec les fonctions abstraites.

• Comprendre la consommation de quotas API : les développeurs doivent comprendre comment les appels API consomment le quota, en particulier pour la production et les tâches critiques.
• Utilisation gratuite mais restreinte : l'API Google Sheets est gratuite mais a des restrictions sur le nombre d'appels API qui peuvent être effectués.
• Erreur commune - 429 («Trop de demandes») : de nombreux utilisateurs rencontrent l'erreur 429 en raison de la dépassement des limites d'appel de l'API.
• Exemple de limite de quota : le quota actuel permet 300 demandes par minute par projet.
• Excédant quota : Si un script envoie plus de 300 demandes, les demandes supplémentaires ne sont pas traitées.
• Solution de backoff exponentielle : un mécanisme de réessayer comme le backoff exponentiel, qui utilise des temps d'attente aléatoires, peut aider à gérer ces limites.

Conclusion

Dans ce guide, nous avons créé un compte Google Service pour effectuer toutes les opérations que l'on effectuerait sur l'interface utilisateur Google Sheets. Nous avons exploré de nombreuses fonctions telles que l'ajout, la mise à jour et la suppression des données. Nous avons également exploré comment formater les feuilles et l'automatisation des feuilles Google à l'aide de Python.

L'API Google Sheets offre beaucoup plus de fonctionnalités telles que la fusion des cellules, la fabrication de gammes protégées, la masquage de cellules, l'ajout de notes, les gammes de copies ou même l'ajout de filtres, toutes les opérations par programme! Bien que la documentation de la bibliothèque GSPread n'ait pas ces explications, on peut aller de l'avant pour explorer la documentation officielle de Google Sheets et également vérifier la section de référence API de la documentation GSPread qui donne des informations de haut niveau sur toutes les fonctions implémentées dans la bibliothèque.

Principaux à retenir

• Google Sheets Automation nécessite de configurer un projet Google Cloud, qui n'en résulte aucun coût. Cependant, si vous n'optimisez pas le code, vous pourriez épuiser le quota, ce qui entraîne des erreurs plus tard dans le code. Visez à utiliser la fonction de lot dans la mesure du possible.
• Il existe de nombreuses possibilités de ce qui peut être réalisé en utilisant l'API Google Sheets et la bibliothèque GSPread n'est qu'un point de départ. Il faut explorer la documentation officielle pour implémenter les fonctionnalités manquantes dans la bibliothèque (et peut-être contribuer)
• Vous pouvez utiliser les configurations d'automatisation Google Sheets pour construire des pipelines d'approvisionnement qui maintiennent un enregistrement principal et le poussent vers une base de données plus avancée, comme une base de données OLAP. Connectez ces bases de données à des logiciels BI, tels que Tableau, pour terminer un projet de bout en bout.
• Google Sheets Automations peut vous aider à éliminer la saisie manuelle des données en configurant des workflows qui mettent automatiquement à jour, importe ou organisent automatiquement des données, améliorant considérablement l'efficacité.
• À l'aide de Google Sheets Automations, vous pouvez planifier des tâches récurrentes telles que l'envoi de rapports, effectuer des calculs ou mettre à jour les cellules, réduisant le besoin d'actions manuelles répétitives.

Questions fréquemment posées

Q1. Que peut-on faire pour éviter l'erreur «429: trop de demandes»?

A. Un mécanisme de réessayer doit être mis en œuvre qui essaie de refaire les demandes dans un certain temps. Un tel exemple est l'algorithme de backoff exponentiel

Q2.Peux-tu faire l'automatisation dans Google Sheets?

A. Oui, vous pouvez automatiser les tâches dans Google Sheets à l'aide de fonctionnalités intégrées comme les macros , le script Google Apps et des outils tiers comme Zapier ou Make (anciennement Integromat) . Ceux-ci vous permettent d'automatiser les tâches répétitives telles que la saisie de données, la mise en forme et les calculs.

Q3. Google a-t-il un outil d'automatisation?

A. Oui, Google propose Google Apps Script , une plate-forme JavaScript pour l'automatisation des tâches sur Google Workspace Apps (par exemple, feuilles, documents, Gmail). Additionally, tools like Google Cloud Workflows and Zapier integrations can be used for broader automation across Google services.

Q4. How can I set up Google Sheets automations to save time on repetitive tasks?

A. You can set up Google Sheets automations using built-in tools like Macros and Google Apps Script. Macros allow you to record actions and replay them, while Google Apps Script lets you create custom automations using JavaScript. These automations can streamline repetitive tasks such as data entry, formatting, or running specific functions automatically in Google Sheets.

Q5. What are some popular Google Sheets automations for data analysis?

A. Popular Google Sheets automations for data analysis include automated data import from external sources, scheduled reports using Google Apps Script, and conditional formatting to highlight trends. These automations help optimize the data analysis process, making Google Sheets a powerful tool for managing and interpreting large datasets efficiently.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!