Objectif

Je souhaite utiliser gspread pour corriger et colorer automatiquement les lignes afin de rendre la feuille de calcul plus facile à lire.

Fiche échantillon

Utilisez cette feuille.

code

import gspread
from gspread_formatting import *
from oauth2client.service_account import ServiceAccountCredentials
from pydrive.auth import GoogleAuth
from pydrive.drive import GoogleDrive
import os

#Trouvez l'alphabet à partir des nombres
def num2alpha(num):
    if num<=26:
        return chr(64+num)
    elif num%26==0:
        return num2alpha(num//26-1)+chr(90)
    else:
        return num2alpha(num//26)+chr(64+num%26)

"""
Formatage dans une feuille de calcul
"""
#Ligne d'en-tête
header_fmt = cellFormat(
    backgroundColor=color(1, 0.7, 0.3),
    textFormat=textFormat(bold=True, foregroundColor=color(0, 0, 0)),
    horizontalAlignment='CENTER'
)
#Ligne de données
data_fmt = cellFormat(
    backgroundColor=color(1, 0.9, 0.7)
)
"""
Modification de la feuille de calcul
"""
#Le chemin où il est stocké
abs_path = f'{os.path.dirname(os.path.abspath(__file__))}\\'
# GoogleAPI
scope = ['https://spreadsheets.google.com/feeds',
         'https://www.googleapis.com/auth/drive']
#Clé d'authentification
json_keyfile_path = f'{abs_path}credentials.json'
#Lire la clé du compte de service
credentials = ServiceAccountCredentials.from_json_keyfile_name(json_keyfile_path, scope)
#Effectuer l'authentification OAuth pour pydrive
gauth = GoogleAuth()
gauth.credentials = credentials
drive = GoogleDrive(gauth)
#Dossier de stockage de la feuille de calcul
folder_id = 'ID de dossier'
#Obtenez une liste de fichiers dans le dossier de stockage de la feuille de calcul
file_list = drive.ListFile({'q': "'%s' in parents and trashed=false" % folder_id}).GetList()
#Extraire uniquement le nom du fichier de la liste des fichiers
title_list = [file['title'] for file in file_list]
#S'authentifier pour gspread
gc = gspread.authorize(credentials)
#ID de la feuille de calcul
sheet_id = [file['id'] for file in file_list if file['title'] == 'test']
sheet_id = sheet_id[0]
#Ouvrir le classeur
workbook = gc.open_by_key(sheet_id)
#Ouvrez la feuille de calcul
worksheet = workbook.worksheet('Feuille 1')
#Obtenez la dernière colonne saisie
last_column_num = len(list(worksheet.row_values(1)))
#Convertir la dernière colonne du nombre en alphabet
last_column_alp = num2alpha(last_column_num)
#Filtre
worksheet.set_basic_filter(name=(f'A:{last_column_alp}'))
#Réparez la première ligne
worksheet.freeze(rows=1)
#Coloration de la ligne d'en-tête + gras + centrage
format_cell_range(worksheet, f'A1:{last_column_alp}1', header_fmt)
#Obtenez la dernière ligne de données d'entrée
last_row_num = len(list(worksheet.col_values(1)))
#Colorez toutes les deux lignes de données
for row in range(2, last_row_num + 1, 2):
    format_cell_range(worksheet, f'A{row}:{last_column_alp}{row}', data_fmt)

Résultat d'exécution

Description du code

Je vais vous expliquer étape par étape.

Mise en page

"""
Formatage dans une feuille de calcul
"""
#Ligne d'en-tête
header_fmt = cellFormat(
    backgroundColor=color(1, 0.7, 0.3),
    textFormat=textFormat(bold=True, foregroundColor=color(0, 0, 0)),
    horizontalAlignment='CENTER'
)
#Ligne de données
data_fmt = cellFormat(
    backgroundColor=color(1, 0.9, 0.7)
)

Partie de formatage.

Définissez la couleur avec backgroundColor. (R, G, B peuvent être définis arbitrairement avec une valeur numérique de 0 à 1) Définissez le texte en gras et la couleur du texte avec textFormat. Définissez la position du caractère avec horizontalAlignment.

Le lien de référence est ci-dessous. Ajouter des commentaires, des couleurs et des bordures aux cellules Google SpreadSheet en Python

Accès à Google Drive-Filter

Veuillez vous référer aux articles précédents publiés par l'auteur. [Python] Filtrer les feuilles de calcul avec gspread

Réparez la première ligne

#Réparez la première ligne
worksheet.freeze(rows=1)

Vous pouvez le réparer où vous le souhaitez en spécifiant n'importe quel nombre de lignes par lignes.

Coloration de la ligne d'en-tête + gras + centrage

#Coloration de la ligne d'en-tête + gras + centrage
format_cell_range(worksheet, f'A1:{last_column_alp}1', header_fmt)

Les arguments sont (feuille de calcul que vous souhaitez modifier, plage de cellules (A1 à C1), format).

Colorez toutes les deux lignes de données

#Obtenez la dernière ligne de données d'entrée
last_row_num = len(list(worksheet.col_values(1)))
#Colorez toutes les deux lignes de données
for row in range(2, last_row_num + 1, 2):
    format_cell_range(worksheet, f'A{row}:{last_column_alp}{row}', data_fmt)

Obtenir la dernière ligne de lignes d'entrée de données ↓ Coloration en boucle de la deuxième ligne à la dernière ligne toutes les deux lignes

Le flux de traitement.

Vous pouvez maintenant colorier toutes les autres lignes.

Tenez compte des limites de demande

Dans l'exemple précédent, le nombre de lignes est petit, donc le code ci-dessus est correct, mais si le nombre de lignes augmente, il sera pris dans la limite de demande de la feuille de calcul et échouera avec une erreur. (Il y a une limite de 100 requêtes par 100 secondes)

Nous allons le corriger pour qu'il ne soit pas pris dans cette limite.

Le code avant réparation est un processus de sélection ligne par ligne → coloration → sélection ligne par ligne.

Après la réparation, le processus passe de la sélection des lots à la coloration. Cela réduira le nombre de demandes.

code

#Stocker la plage de coloration et le format de la partie de données dans la liste sous forme de type taple
ranges = [(f'A{row}:{last_column_alp}{row}', data_fmt) for row in range(2, last_row_num + 1, 2)]
print(ranges)
#Stocker le format de la partie d'en-tête au début de la liste créée
ranges.insert(0, (f'A1:{last_column_alp}1', header_fmt))
print(ranges)
#Coloration en une seule fois
format_cell_ranges(worksheet, ranges)

`Résultat d'exécution`


[('A2:C2', <CellFormat backgroundColor=(red=1;green=0.9;blue=0.7)>), ('A4:C4', <CellFormat backgroundColor=(red=1;green=0.9;blue=0.7)>)]
[('A1:C1', <CellFormat backgroundColor=(red=1;green=0.7;blue=0.3);horizontalAlignment=CENTER;textFormat=(foregroundColor=(red=0;green=0;blue=0);bold=True)>), ('A2:C2', <CellFormat backgroundColor=(red=1;green=0.9;blue=0.7)>), ('A4:C4', <CellFormat backgroundColor=(red=1;green=0.9;blue=0.7)>)]

Stockez toute la gamme que vous souhaitez colorer dans la liste ↓ Coloration en une seule fois

C'est un processus appelé. (Veuillez vous référer au contenu des plages affichées dans la partie résultat de l'exécution)

Contrairement à avant, j'utilise un module appelé format_cell_ranges.

Échantillon officiel

fmt = cellFormat(
    backgroundColor=color(1, 0.9, 0.9),
    textFormat=textFormat(bold=True, foregroundColor=color(1, 0, 1)),
    horizontalAlignment='CENTER'
    )

fmt2 = cellFormat(
    backgroundColor=color(0.9, 0.9, 0.9),
    horizontalAlignment='RIGHT'
    )

format_cell_ranges(worksheet, [('A1:J1', fmt), ('K1:K200', fmt2)])

De cette manière, le traitement par lots peut être effectué en stockant la plage et le format dans un taple dans la liste.

Avec cela, vous pouvez fixer la ligne + la couleur une ligne sur deux sans être pris dans la limite!

Lien officiel

gspread-formatting 0.3.0

[Python] Lignes fixes avec formatage gspread + Coloration toutes les deux lignes

Objectif

Fiche échantillon

code

Résultat d'exécution

Description du code

Mise en page

Accès à Google Drive-Filter

Réparez la première ligne

Coloration de la ligne d'en-tête + gras + centrage

Colorez toutes les deux lignes de données

Tenez compte des limites de demande

code

Résultat d'exécution

Échantillon officiel

Lien officiel

`Résultat d'exécution`