Comment lire et écrire dans Google Sheets avec Python

26 mai 2021 Hibou Gourou

La syntaxe de Python peut sembler étrange et inhabituelle au premier abord. Mais c'est facile à apprendre et à utiliser une fois que vous avez compris. Python alimente des jeux de codage comme Minecraft Pi Edition, de nombreux algorithmes d'apprentissage automatique et une multitude de sites Web.

Mais dans cet article, vous apprendrez à lire et à écrire dans Google Sheets à l'aide de Python.

Configuration de Google

Avant de vous lancer dans le code, il y a une configuration initiale pour ne pas vous gêner sur Google Sheets.

Commencez par créer vous-même une nouvelle feuille. Vous pouvez ignorer cette étape si vous en avez déjà configuré une. Nous utilisons une liste de voitures de rallye pour cet exemple. Mais vous pouvez suivre ce tutoriel avec vos propres données:

Vous devez maintenant configurer vos options de partage. Mais d'abord, vous devez générer des identifiants signés à partir de Google Developers Console. C'est facile; accédez à la Google Developers Console et procédez comme suit:

Cliquez sur CRÉER UN PROJET pour créer un nouveau projet (ou utiliser un projet existant):
Donnez à votre projet un nom approprié, puis cliquez sur CRÉER :
Dans la notification qui apparaît, cliquez sur SELECT PROJECT sous le projet que vous venez de créer pour y accéder.
Faites glisser le menu latéral et placez votre curseur sur les API et les services , puis sélectionnez Tableau de bord .
Cliquez sur ACTIVER LES APIS ET LES SERVICES en haut de la page. Dans les options, sélectionnez l' API Google Sheets (utilisez la barre de recherche si vous ne la trouvez pas):
Choisissez ACTIVER :
Cliquez sur Créer et sélectionnez ACCRÉDITATION pouvoirs dans le menu à gauche:
Cliquez sur le bouton CRÉER DES CRÉDENTIELS en haut de la page:
Ensuite, sélectionnez Compte de service :
Remplissez le champ du nom du compte de service et cliquez sur CRÉER , suivi de TERMINÉ :
Vous verrez le compte de service maintenant répertorié dans le tableau Comptes de service au bas de la page suivante. Cliquez sur l'icône d'édition à côté:
Choisissez KEYS . Cliquez ensuite sur le bouton AJOUTER UNE CLÉ et sélectionnez Créer une nouvelle clé :
Choisissez JSON comme format:
Cliquez sur CRÉER et un fichier JSON devrait être téléchargé sur votre PC. Déplacez-le dans le répertoire de votre projet et donnez-lui votre nom préféré avec un format de fichier .json ajouté.
Enfin, ouvrez le fichier et recherchez client_email . Cela devrait être quelque chose comme: id.gserviceaccount.com . Copiez cette adresse.
Ouvrez Google Sheets et partagez-le avec cette adresse e-mail (en haut à droite > Partager > Entrer un e-mail ). Cliquez sur l'adresse e-mail une fois qu'elle apparaît, puis appuyez sur Envoyer pour accorder l'accès.

C'est tout pour le côté Google Sheets.

Configuration de Python

Si vous utilisez le système d'exploitation Windows, vous devrez peut-être télécharger et installer Python . Vous n'avez pas besoin de vous en soucier si vous êtes sur macOS car il est livré avec Python déjà installé.

En relation: Les meilleurs sites Web pour apprendre Python

Tout d'abord, ouvrez un nouveau terminal et créez un environnement virtuel Python .

Vous devrez installer un cadre d'autorisation Web appelé oauth2client . Il est facile à installer à l'aide de pip :

 pip install oauth2client

Vous devrez peut-être également installer PyOpenSSL, en fonction de votre configuration:

 pip install PyOpenSSL

Vous devez maintenant également installer un package de communication Google Sheets appelé gspread . Encore une fois, c'est facile à installer à l'aide de pip :

 pip install gspread

Ouvrez maintenant votre éditeur de texte préféré . Créez ensuite un nouveau fichier Python avec une extension de fichier .py ajoutée et enregistrez-le dans le répertoire de votre projet.

De retour dans votre terminal, cd dans le répertoire de votre projet. Vous pouvez utiliser dir pour lister les fichiers de ce répertoire. Vous pouvez également afficher votre répertoire de travail si vous le souhaitez.

Une fois dans le répertoire de votre projet, vous pouvez toujours exécuter votre script Python en l'appelant via la ligne de commande comme ceci:

 python [file_name].py

La sortie de votre code apparaît alors dans votre ligne de commande.

En relation: Comment exécuter un script Python

Maintenant que Python fonctionne, allons-y et configurons les bibliothèques. Ouvrez le fichier Python que vous avez créé précédemment et importez les bibliothèques suivantes:

 import gspread
 from oauth2client.service_account import ServiceAccountCredentials
 import json

Ensuite, exécutez le code.

Si les choses fonctionnent correctement, rien ne se passera. Si vous obtenez une erreur, peut-être en disant aucun module nommé X où X est le nom de l'un des modules importés, assurez-vous d'abord que vous avez activé votre environnement virtuel.

Vous pouvez également afficher la liste de tous les modules que vous avez installés dans cet environnement en exécutant pip freeze via la ligne de commande. Si le module manquant n'est pas là, exécutez à nouveau pip install [module] . Assurez-vous d'éviter les fautes de frappe.

Voici le code pour vous aider à lire et à écrire vos feuilles de calcul Google:

 from oauth2client.service_account import ServiceAccountCredentials
 import gspread
 import json
 scopes = [
 'https://www.googleapis.com/auth/spreadsheets',
 'https://www.googleapis.com/auth/drive'
 ]
 credentials = ServiceAccountCredentials.from_json_keyfile_name("[JSON_key_name].json", scopes) #access the json key you downloaded earlier
 file = gspread.authorize(credentials) # authenticate the JSON key with gspread
 sheet = file.open("Python_MUO_Google_Sheet") #open sheet
 sheet = sheet.sheet_name #replace sheet_name with the name that corresponds to yours, eg, it can be sheet1

Ce bloc de code récupère vos détails à partir du fichier .json contenant votre clé d'authentification. Ensuite, il l'utilise pour s'authentifier auprès de Google à l'aide du module gspread . Il ouvre ensuite une feuille appelée Python_MUO_Google_Sheet . Vous devrez peut-être le remplacer par le nom de votre feuille (à condition que vous l'ayez partagé correctement). Python est sensible à la casse, alors assurez-vous de saisir ce code correctement.

Lire vos feuilles de calcul Google avec Python

Maintenant que tout est configuré, c'est un jeu d'enfant de lire ou d'écrire des données dans Google Sheets avec Python. Voici comment sélectionner une plage de cellules (dans ce cas, toutes les cellules de voiture):

 all_cells = sheet.range('A1:C6')
 print(all_cells)

Voici à quoi cela ressemble:

La sortie ci-dessus n'a pas l'air sympa car Python a vidé le contenu sans tenir compte du formatage.

Voici donc comment imprimer toutes les valeurs de cellule dans un format plus agréable à l'aide de la boucle for de Python et de la fonction de valeur intégrée:

 for cell in all_cells:
 print(cell.value)

Et cela ressemble à ceci:

Il est possible d'accéder aux cellules individuellement:

 A1 = sheet.acell('A2').value
 print(A1)
 Output: Ford

Ou vous pouvez utiliser les coordonnées de la cellule. Cela se produit de manière ligne par colonne. Par exemple, le code ci-dessous obtient les données sur la cinquième ligne et la troisième colonne:

 coord = sheet.cell(5, 3).value

Il est également facile d'obtenir toutes les valeurs d'une ligne:

 row = sheet.row_values(1) #first row
 print(row)

Ou vous pouvez obtenir une colonne entière. L'exemple ci-dessous obtient la deuxième colonne:

 col = sheet.col_values(2)
 print(col)

Écrire dans vos feuilles de calcul Google

Il est tout aussi facile de réécrire dans la feuille et vous pouvez utiliser des noms de cellules ou des coordonnées comme lors de la lecture:

 sheet.update_acell('C2', 'Blue')
 sheet.update_cell(2, 3, 'Blue') #updates row 2 on column 3

La mise à jour d'une plage de cellules est également simple:

 sheet.update('A2:B3', [["Not Ford", "Not Lancia"], ["Nothing", "Not"]])

Remarque: vous pouvez également ajouter votre feuille en mettant à jour les cellules vides auxquelles vous souhaitez ajouter des données à l'aide de la méthode update () .

Mettez en forme les en-têtes de vos feuilles de calcul Google en texte gras si vous le souhaitez:

 sheet.format('A1:C1', {'textFormat': {'bold': True}})

Vous pouvez également utiliser gspread avec pandas et numpy. Jetez un œil à la documentation de gspread si vous souhaitez en savoir plus sur les divers rebondissements et modifications à ce sujet.

Si vous écrivez sur une feuille importante que vous gérez avec d'autres personnes, vous voudrez peut-être envisager une cellule de sécurité . Stockez une valeur dans une certaine cellule, puis lisez d'abord cette cellule. Si le contenu a changé, d'autres ont changé ou ajouté des colonnes à la feuille, vous ne pouvez donc pas poursuivre l'écriture. Voici comment vous pouvez y parvenir:

 if sheet.acell('B3') != 'SAFETY':
 # something has changed in the sheet, DO NOT PROCEED
 print("Sheet already updated.")
 else:
 # continue with your writing
 sheet.update_acell('C2','Blue')

C'est une bonne pratique. Cela garantit que votre script ne peut pas écrire accidentellement dans une colonne déjà mise à jour. Cependant, ce n'est pas un substitut à des sauvegardes appropriées.

Automatisez vos tâches de feuille de calcul avec Python

Maintenant que vous connaissez les bases, allez-y et faites quelque chose de cool! Au lieu de réinventer la roue, vous pouvez automatiser les tâches en créant des fonctions dédiées et appelables qui lisent et écrivent dans vos feuilles de calcul Google.

De plus, si vous utilisez également Microsoft Excel, vous pouvez importer des données Excel dans des scripts Python et manipuler votre feuille de calcul Excel à votre guise.