[PYTHON] Exemple de résumé de code lorsque vous travaillez avec des feuilles de calcul Google de Google Colab

Récemment, je touche souvent aux feuilles de calcul Google de Google Colab.

(J'ai aussi une vidéo comme celle-ci, alors jetez un œil si vous le souhaitez! → [[Apprendre Python avec Safa's Colab -9] Comment écrire des valeurs de Google Colab vers Google Spreadsheet (YouTube)](https :: //www.youtube.com/watch?v=4RCWYQk9bfM))

Quand je le touche, je tape souvent le code habituel, alors j'ai décidé de rassembler les descriptions fréquemment utilisées dans Qiita comme exemple de code.

Document référencé

En gros, j'écrirai en me référant au document officiel de gspread. gspread(Docs)

Installer gspread

La bibliothèque gspread, qui est essentielle pour travailler avec les feuilles de calcul Google. Sur Google Colab, lorsque vous appuyez sur une commande, ajoutez ! Au début.

!pip install gspread

Comme je l'ai écrit jusqu'à présent, j'ai remarqué que Google Colab semblait avoir installé gspread depuis le début. À propos, la version installée est «3.0.1».

import gspread
print(gspread.__version__)
# => 3.0.1

Une chose à garder à l'esprit ici est que ce 3.0.1, qui est installé depuis le début, n'est pas la dernière version, donc certaines fonctionnalités ne peuvent pas être utilisées. burnash/gspread(Releases)

Par conséquent, si vous souhaitez utiliser la dernière fonction gspread, vous devez installer la dernière gspread en appuyant sur la commande suivante.

!pip install --upgrade gspread

import gspread
print(gspread.__version__)
# => 3.6.0

** Cependant, cette fois, j'ai pensé à écrire avec Google Colab en tête pour gérer immédiatement les feuilles de calcul Google, donc j'écrirai en utilisant 3.0.1 qui est installé par défaut. .. ** ** (Étant donné que les fonctions ajoutées dans la dernière version sont également attrayantes, je voudrais écrire cela dans un article séparé.)

Processus d'authentification requis pour gérer les feuilles de calcul Google à partir du code

Il s'agit d'un processus essentiel pour gérer les feuilles de calcul Google de Google Colab. L'exécution de ce code vous donnera l'authentification requise pour travailler avec des feuilles de calcul Google à partir de Google Colab. (Presque comme «magique», il est utilisé lors de l'écriture de code qui gère les feuilles de calcul Google. Si vous avez des difficultés à écrire, vous pouvez coller cette description sur Colab avec une copie et l'exécuter.)

from google.colab import auth
from oauth2client.client import GoogleCredentials
import gspread

auth.authenticate_user()
gc = gspread.authorize(GoogleCredentials.get_application_default())

Je ne sais pas si cette authentification est un produit Google, Google Colab et Google Spreadsheet, mais c'est vraiment intelligent, donc c'est toujours génial sans aucun problème.

Plus précisément, les étapes sont les suivantes.

Lorsque vous exécutez le code ci-dessus, l'URL pour l'authentification s'affiche, alors cliquez dessus

Vous serez redirigé vers l'écran d'authentification du côté Google, alors authentifiez-vous. Lorsque l'authentification est terminée, le «code de vérification» s'affiche, alors copiez-le.

Si vous collez le code que vous avez copié précédemment du côté Colab, l'authentification est terminée.

C'est facile et merveilleux.

Comment obtenir une feuille de calcul Google

En ce qui concerne la façon de faire fonctionner la feuille de calcul Google, gspread fournit plusieurs méthodes, je vais donc vous présenter chacune d'elles. À partir de là, j'écrirai le code en supposant que l'authentification susmentionnée a été effectuée. (Si vous l'exécutez sans être authentifié, vous obtiendrez une erreur d'authentification.)

Obtenir par nom de fichier

C'est une méthode pour ouvrir directement par nom de fichier.

ss_name = "Nom du fichier de feuille de calcul"
workbook = gc.open(ss_name)

Obtenir par identifiant de feuille de calcul

Comment ouvrir avec l'ID inclus dans l'URL de la feuille de calcul. (En passant, dans la mesure où j'ai recherché les informations en japonais, j'ai eu l'impression que la façon la plus courante de l'ouvrir était de cette façon.)

# https://docs.google.com/spreadsheets/d/{Ceci est l'ID de la feuille de calcul}/edit#gid=0
ss_id = "ID de la feuille de calcul"
workbook = gc.open_by_key(ss_id)

Obtenir par URL

C'est une méthode pour ouvrir en entrant l'URL de la feuille de calcul telle quelle

ss_url = "URL de la feuille de calcul"
workbook = gc.open_by_url(ss_url)

Obtenez toutes les feuilles de calcul

Il y avait également un moyen d'obtenir toutes les feuilles de calcul dans Google Drive. https://gspread.readthedocs.io/en/latest/api.html#gspread.Client.openall

Lorsqu'il est exécuté, il renvoie une liste de feuilles de calcul.

workbook_list = gc.openall()
for workbook in workbook_list:
  print(workbook.title) #Afficher le titre du fichier récupéré

Comment obtenir une feuille

Vous pouvez obtenir une feuille spécifique pour la feuille de calcul que vous avez obtenue ci-dessus par la méthode suivante.

Comment obtenir en passant le nom de la feuille à acquérir dans la feuille de calcul ()

ss_name = "Nom du fichier de feuille de calcul"
workbook = gc.open(ss_name)
worksheet = workbook.worksheet("Feuille 1")

Comment obtenir en passant l'index de la feuille à acquérir à get_worksheet ()

ss_name = "Nom du fichier de feuille de calcul"
workbook = gc.open(ss_name)
worksheet = workbook.get_worksheet(0)

Comment obtenir la valeur dans la feuille

Comment obtenir en spécifiant une cellule (acell)

ss_name = "Nom du fichier de feuille de calcul"
workbook = gc.open(ss_name)
worksheet = workbook.worksheet("Feuille 1")

print(worksheet.acell("A1").value)
# =>Valeur de la cellule A1

print(worksheet.acell("B2").value)
# =>Valeur de la cellule B2

print(worksheet.cell(3,2).value)
# =>Valeur de la cellule B3

Comment obtenir en spécifiant une cellule (plage)

ss_name = "Nom du fichier de feuille de calcul"
workbook = gc.open(ss_name)
worksheet = workbook.worksheet("Feuille 1")
print(worksheet.range(3,2)[0].value)
# =>Pour la plage, renvoie un tableau de cellules
# =>Dans le cas de cette spécification, une seule information de cellule de B3 est incluse dans le tableau, donc comme ceci[0]La valeur est obtenue en spécifiant avec

#Obtenez des informations sur la cellule dans la plage que vous souhaitez spécifier. Puisqu'il change avec un tableau, toutes les valeurs sont sorties dans une boucle
cell_list = worksheet.range("A1:B3")
for cell in cell_list:
  print(cell.value)

A propos de l'objet de cellule

À propos, la cellule acquise ici présente les trois propriétés suivantes.

• valeur (valeur de la cellule)
• row
• col

Par exemple, voici un exemple de code pour obtenir et afficher la valeur d'une cellule dans la plage spécifiée (" A1: B3 ").

ss_name = "Nom du fichier de feuille de calcul"
workbook = gc.open(ss_name)
worksheet = workbook.worksheet("Feuille 1")

#Obtenez des informations sur la cellule dans la plage que vous souhaitez spécifier. Puisqu'il change avec un tableau, toutes les valeurs sont sorties dans une boucle
cell_list = worksheet.range("A1:B3")
for cell in cell_list:
  print('%Lignes%La valeur de la colonne s est%s' % (cell.row, cell.col, cell.value))

Comment obtenir les valeurs dans la feuille (tout obtenir)

Il existe les méthodes suivantes pour obtenir la valeur dans la feuille spécifiée.

get_all_records renvoie une liste de dictés

Si vous utilisez get_all_records, la valeur de la feuille cible sera renvoyée sous la forme d'une liste de dict (dictionnaire).

ss_name = "Nom du fichier de feuille de calcul"
workbook = gc.open(ss_name)
worksheet = workbook.worksheet("Nom de la feuille que vous souhaitez obtenir")

print(worksheet.get_all_records())
# => [{'item1': 2, 'item2': 4, 'item3': 31}, {'item1': 3, 'item2': 6, 'item3': 32}, ...

Si vous l'appelez sans spécifier d'options, la première ligne sera traitée comme un en-tête. Veuillez vous reporter à ici pour savoir comment spécifier les options.

Par exemple, si vous souhaitez convertir la valeur de la feuille en json, le code sera le suivant.

#Lors de la conversion vers la valeur de feuille spécifiée json
import json
ss_name = "Nom du fichier de feuille de calcul"
workbook = gc.open(ss_name)
worksheet = workbook.worksheet("Nom de la feuille cible")

dict_list = worksheet.get_all_records()
print(json.dumps(dict_list))

get_all_values renvoie les valeurs de la feuille sous forme de tableau

Une autre option est de simplement l'obtenir sous forme de tableau de valeurs en utilisant get_all_values.

ss_name = "Nom du fichier de feuille de calcul"
workbook = gc.open(ss_name)
worksheet = workbook.worksheet("Nom de la feuille que vous souhaitez obtenir")

print(worksheet.get_all_values())
# => [['item1', 'item2', 'item3'], ['2', '4', '31'], ['3', '6', '32'], ...

Comment ajouter une valeur à une feuille

Utilisez ʻappend_row` pour ajouter des valeurs à la feuille avec des lignes. L'exemple ci-dessous est un exemple de code qui ajoute 100 lignes avec la valeur à la feuille spécifiée. (Par exemple, s'il y a déjà une description dans la feuille, une nouvelle ligne sera ajoutée à partir de là)

import random

ss_name = "Nom du fichier de feuille de calcul"
workbook = gc.open(ss_name)
worksheet = workbook.worksheet("Feuille 2")

for i in range(100):
  worksheet.append_row([i, i * 2, random.randint(1, 100)])

Vous trouverez ci-dessous une capture de la feuille de calcul réellement saisie.

Si vous souhaitez supprimer tout le contenu de la feuille (peu pratique)

Si vous souhaitez supprimer tout le contenu de la feuille spécifiée, il semble que vous deviez exécuter comme suit.

Cependant, comme il s'agit d'un code très délicat (le nombre de lignes existantes est supprimé ligne par ligne), il est préférable de tout supprimer d'un coup en supprimant la feuille elle-même écrite ci-dessous. Cela semble bon. </ font>

#Si vous souhaitez supprimer toutes les valeurs de la feuille spécifiée
ss_name = "Nom du fichier de feuille de calcul"
workbook = gc.open(ss_name)
worksheet = workbook.worksheet("Nom de la feuille")

for i in range(1, len(worksheet.get_all_values())):
  worksheet.delete_row(1)

ss_name = "Nom du fichier de feuille de calcul"
workbook = gc.open(ss_name)
worksheet = workbook.worksheet("Nom de la feuille que vous souhaitez supprimer")

workbook.del_worksheet(worksheet)