Licence CC BY

Parcourir des sheet

Publié :
Auteur :
Catégories :
Temps de lecture estimé : 1 minute

Aujourd’hui un petit trucs et astuces dans le domaine de l’extraction de données depuis Google Spreadsheet. J’utiliserai python 3.8 pour extraire des données d’une fiche d’un drive privé.

Problématique et solution

Cas d’étude

Dans mon boulot on essaie d’étudier un peu les stats que fournissent nos différents outils ainsi que les documents qu’on produit via nos process pour ensuite faire des croisements. Cela est dû à la culture BI qu’on a puisqu’on est un fournisseur d’outils d’administrations, de PRA et de tests de régression pour les outils d’informatiques décisionnelle tels que Business Objects (de SAP) ou Tableau (de Salesforce).

Une partie de nos documents sont stockés sur drive et le cas du jour consiste à trouver des données stockées dans une spreadsheet google.

Comme on veut faire de l’extraction de données, ici je n’ai pas choisi d’utiliser les macro google spreadsheet mais l’API v2 décrite par google.

Code de base

De manière périodique, nous créons une feuille qui est toujours basée sur un tableau de 6 colonnes mais un nombre non défini de lignes.

Comme ce n’est qu’un billet, je passe tout de suite au code commenté

# service est obtenu par le code pour se connecter à spreadsheet avec les bons credentials
# puis j'ai sélectionné la spreadsheet dont je connais l'id, stocké dans la variable wanted_sheet_id
sheet_service = service.spreadsheets()
result = sheet_service.get(spreadsheetId=wanted_sheet_id).execute()["sheets"]
data = {}
for candidate_sheet in result:
    sheet_name = candidate_sheet['properties']['title']
    headers = sheet_service.values().get(spreadsheetId=wanted_sheet_id,
                                         range=f"{sheet_name}!A1:G1").execute().get(
        "values", [[]])
    data[candidate_sheet["properties"]["title"]] = {
       "values": []
    }
    values = sheet_service.values().get(spreadsheetId=wanted_sheet_id,
                                        range=f"{candidate_sheet['properties']['title']}!A2:G")
                                   .execute().get("values", [[]])
    for value_row in enumerate(values):
        data[candidate_sheet["properties"]["title"]]["values"].append({})
        for i, header in enumerate(headers[0]):
            # in google value_row is a tuple (index, values)
            data[candidate_sheet["properties"]["title"]]["values"][-1][header] = value_row[1][i]

Et voilà, on a un dictionnaire avec toutes les valeurs de toutes les sheets.

Seul problème : l’API google possède une limite de requête par heure et là on fait beaucoup de requêtes par feuille.

Améliorations pour éviter le query rate

Un bon moyen est d’utiliser la méthode batchGet sur le endpoint values. Cela implique de builder les range avant puis on va pouvoir tout traiter d’un coup :

ranges = []
# on va retenir les nom des sheet dans un tableau à côté
sheets = []
for candidate_sheet in result:
    sheet_name = candidate_sheet['properties']['title']
    ranges.append(f'{sheet_name}!A1:G1') # get headers
    ranges.append(f'{sheet_name}!A2:G') # values
    sheets.append(sheet_name)
values = sheet_service.values().batch_get(spreadsheetId=wanted_sheet_id,
                                        ranges=ranges)
# values est désormais un tableau contenant aux indices pairs les headers des feuilles
# et aux indices impairs les valeurs à extraire

Et voilà le travail.

2 commentaires

Malheureusement… je ne sais pas quoi dire de plus.

J’ai remarqué que sur stackoverflow ils n’avaient pas la réponse, j’ai donc dû trouver la manière de faire ça par essai/échec et une fois que j’ai réussi je me suis dit "bon bah voilà je vais le dire sur zds comme ça d’autres pourront s’en servir".

Si tu as des questions, je peux néanmoins tenter d’y répondre ;)

+0 -0
Connectez-vous pour pouvoir poster un message.
Connexion

Pas encore membre ?

Créez un compte en une minute pour profiter pleinement de toutes les fonctionnalités de Zeste de Savoir. Ici, tout est gratuit et sans publicité.
Créer un compte