Google Sheets + Python: créer et partager automatiquement des rapports de données

Finis les longs process manuels ! Automatise la création, l'édition et le partage de vos fichiers Google Sheets avec Python (ou R)

Google Sheets est un tableur en ligne simple, et gratuitement accessible via un compte Gmail. Il offre l’essentiel des fonctionnalités de Microsoft Excel, sans installation préalable ni achat de licence d’usage, ce qui en fait un excellent outil de travail collaboratif pour de nombreuses équipes.

Mais dès qu’il s’agit d’importer des données depuis une source externe sans connecteur natif (comme une base de données produit), la tâche manuelle peut vite devenir répétitive et chronophage. Dans cette note, je montre comment automatiser, avec Python, la création, le remplissage, le formatage, le partage, et l’archivage d’un fichier Google Sheets. Une solution gratuite, efficace, et facile à mettre en place, pour se concentrer sur ce qui compte vraiment.

Mise en situation

Répondre à un besoin métier… sans coder une usine à gaz

Tu accompagnes une PME qui vient de lancer une campagne ponctuelle d’acquisition et d’activation de clients via des agents commerciaux sur le terrain.

Principes d’enregistrement des données opérationnelles:

• Chaque nouveau compte créé chez un agent commercial est accompagné de l’identifiant unique de l’agent.

• Tout premier dépôt d’argent sur le compte client effectué chez un agent commercial est accompagné de l’identifiant unique de l’agent.

La PME veut rémunérer les agents selon leurs performances. Le problème ? Aucun outil interne ne permet pour le moment de calculer et payer tout ça convenablement, et les développeurs sont occupés sur une autre mission.

Tu es chargé de mettre en place un système simple: récupérer les données d’activité de la base de données MongoDB, croiser avec les seuils de configuration, calculer les rémunérations, et générer deux rapports; un pour la compta (salaire par agent), un pour le service support (récapitulatif de performance par agent). Le tout doit être fourni au quotidien, et envoyé aux bonnes équipes.

Si tu dois t’en occuper tous les jours, c’est que le problème se pose toujours.

Règles de rémunération:

• Tout nouveau compte créé donne droit à 2.99 ECO

• Toute nouvelle recharge de compte donne droit à 1.99 ECO

• Un nouveau compte suivi d’une première recharge dans la même journée donne droit à u bonus de 0.99 ECO

Approche de solution

L’idée est simple: automatiser chaque étape du reporting, de l’extraction des données jusqu’à l’envoi des rapports aux équipes.

• Extraire les données de la période cible directement depuis la base.

• Nettoyer, filtrer et structurer les données sous forme de tableaux prêts à l’emploi.

• Créer automatiquement les fichiers Google Sheets dans les bons dossiers via l’API Google Drive.

• Remplir et formater les fichiers avec les bonnes données via l’API Google Sheets.

• Notifier les équipes concernées par email, une fois les rapports prêts.

• Automatiser l’ensemble dans un seul programme Python, exécuté de manière planifiée (quotidienne, etc.).

Implémentation de la solution

Quelques bonnes pratiques…

1. Se mettre d’accord avec les équipes sur:

   1. Quand livrer les rapports (par exemple, chaque jour à midi)

   2. Quels éléments doivent y figurer (colonnes, kpis, formats, filtres…)

2. Stocker chaque type de rapport (paiement, performance) dans un dossier Google Drive dédié, servant d’espace de partage, pour simplifier le contrôle des accès.

3. Adopter une convention de naming claire pour facilitera l’exploitation. (ex: {NomDeCampagne}-{TypeDeRapport}-{DateDesDonnées}). Cela suffirait à éviter toute confusion… mais pour renforcer la fiabilité, on pourra par exemple déplacer automatiquement l’ancien rapport de paiement vers un dossier d’archives, afin d’éviter les traitements double, tout en gardant un historique.

4. Créer une base des règles de rémunération afin de faciliter leurs mises à jour indépendamment du code (optionnel).

5. Créer une base de sauvegarde des métadonnées des rapports générées. (optionnel).

6. Faire valider manuellement le bon fonctionnement du programme (chiffres, formats, fichiers), idéalement avec un second regard, avant l’automatisation.

Une erreur manuelle, c’est embêtant. Une erreur automatisée, c’est une catastrophe dans la chaîne de valeur.

Étape 1 : Préparation de l’environnement et configuration des APIs Google

Pour automatiser des actions sur Google Drive et Google Sheets, votre programme doit utiliser un service account de google auquel vous attribuerez les permissions nécessaires. Si vous ne savez pas comment procéder, ce tutoriel vous guidera étape par étape.

import gspread
from google.oauth2 import service_account
from googleapiclient.discovery import build

scopes = [
    'https://www.googleapis.com/auth/spreadsheets',
    'https://www.googleapis.com/auth/drive'
]

sa_credentials = service_account.Credentials.from_service_account_file(chemin/vers/ta_cle.json')

creds = sa_credentials.with_scopes(scopes)
gw_client = gspread.authorize(credentials = creds)

Étape 2 : Création des fonctions personnalisées

Fonction: Générer automatiquement un google sheets

def create_sheet_get_id(sheet_title, folder_id, service_account_creds):
    '''
    This function will create a new google sheet file within a specific doogle drive folder.
    Args:
        sheet_title (str): The title of the new Google Sheet.( E.g. Tuto - Python x Google Sheets)
        folder_id (str): The ID of the Google Drive folder where the sheet will be created. If the link of the drive folder is https://drive.google.com/drive/folders/1a2b3c4d5e6f7g8h9i0j, then the folder_id is 1a2b3c4d5e6f7g8h9i0j.
        service_account_creds (service_account.Credentials): The service account credentials to authenticate with Google Drive API.
    '''
    drive_api = build('drive', 'v3', credentials=service_account_creds)

    # Create the new Google Sheet
    file_metadata = {'name': sheet_title, 'parents': [folder_id], 'mimeType': 'application/vnd.google-apps.spreadsheet',}
    newsheet = drive_api.files().create(body=file_metadata).execute()
    sheet_id = newsheet['id']
    
    return sheet_id

Fonction: Exporter des DataFrames vers google sheets

def fill_sheet_with_pandas_dataframes(sheet_id, data_frames_dict, service_account_creds):
    '''
    This function will fill a Google Sheet with data.
    Args:
        sheet_id (str): The ID of the Google Sheet to fill with data.
        data_frames_dict (dict): The data to fill the Google Sheet with. It should be a dictionary where keys are sheet names and values are pandas DataFrames.
        service_account_creds (service_account.Credentials): The service account credentials to authenticate with Google Sheets API.
    '''
    # Authorize the Google Sheets API
    gw_client = gspread.authorize(credentials=service_account_creds)
    workbook = gw_client.open_by_key(sheet_id)
    for sheet_name, df in data_frames_dict.items():
        if not isinstance(df, pd.DataFrame):
            raise TypeError(f"Data for sheet '{sheet_name}' must be a pandas DataFrame.")
        
        # Clear the existing content of the sheet
        try:
            worksheet = workbook.worksheet(sheet_name)
            worksheet.clear()
        except gspread.WorksheetNotFound:
            worksheet = workbook.add_worksheet(title=sheet_name, rows="100", cols="20")
        
        # Set the DataFrame to the Google Sheet
        set_with_dataframe(worksheet, df, include_index=False, include_column_header=True)
        print(f"Data for sheet '{sheet_name}' has been written to the Google Sheet.")
    
    sheet_to_delete = workbook.worksheet("Sheet1") # Delete the default sheet created by Google Sheets
    workbook.del_worksheet(sheet_to_delete)  

Fonction: Partager un google sheets avec un message

def share_google_sheet_with_message(sheet_id, email_list, acess, service_account_creds, message=""):
    # Authenticate with service account
    drive_service = build('drive', 'v3', credentials=service_account_creds)

    for email_to_share in email_list:
        # Create permission
        permission = {
            'type': 'user',
            'role': acess, #'reader',
            'emailAddress': email_to_share
        }

        # Share the file
        drive_service.permissions().create(
            fileId=sheet_id,
            body=permission,
            sendNotificationEmail=True,
            emailMessage=message
        ).execute()

    print(f"Google Sheet shared with {email_to_share} as {acess}.")

Étape 3 : Initialisation des variables globales

Dans cette étape, on définit des variables clés qu’on réutilisera tout au long du script. Cela permet d’éviter les répétitions et de rendre le code plus clair et plus facile à maintenir. Dans notre cas, on définit simplement les bornes de l’intervalle de la période à traiter. Ces dates serviront aussi à nommer automatiquement les fichiers générés plus tard.

Etape 4: Extraction et traitement des données

Ici, tu récupères les données issues de la base d’activité, puis tu les analyses pour calculer les indicateurs à fournir à chaque équipe. Dans notre application, nous exploitons les données d’une base-test mongodb:

• la table des agents commerciaux

• la table des comptes clients

• la table des transactions

Étape 5 : Export des rapports d’analyse et notification des équipes

Une fois les traitements de données terminés, tu utilises les fonctions définies plus haut pour les exporter vers les dossiers drive des équipes concernées. La notification, bien qu’optionnelle, permet d’envoyer automatiquement un email à l’adresse de ton choix pour l’informer que les rapports sont disponibles.

Deux options sont envisageables :

• Partager le fichier avec un message personnalisé (via Drive API)

• Envoyer un véritable email contenant les infos utiles (via Gmail API)

Dans cet exemple, j’ai opté pour la première solution, simple et rapide à mettre en place.

NB: Le notebook final de ce tutoriel est accessible dans ce dépôt GitHub. Pour automatiser l’exécution de votre programme, convertissez votre notebook en script .py et planifier son execution sur un serveur via cron ou un orchestrateur comme Airflow.

⚠️ DISCLAIMER ⚠️

• Some parts of this article have been reformulated with ChatGPT.

• The header image has been generated with ChatGPT.