Comment analyser des données Google Sheets avec Python : guide pas à pas

3. Configurer une connexion

Dans un workbook, cliquez sur Affichage > Bases de données puis sur l’icône +. Une vue d’ensemble s’affiche avec toutes les technologies de base de données auxquelles DataLab permet de se connecter :

Sélectionnez Google Sheets. On vous demande ensuite d’accorder à DataCamp l’accès à vos fichiers Google Drive.

Cliquez sur « Accorder l’autorisation à Google Drive ». Un sélecteur de fichiers s’affiche alors :

Sélectionnez la feuille de calcul dont vous souhaitez lire les données. Il peut s’agir de la copie de la feuille d’exemple fournie ou de votre propre feuille Google Sheets.

4. Interroger le fichier Google Sheets

Vous pouvez maintenant interroger le fichier Google Sheets que vous venez de connecter ! Pour cela, utilisez une cellule SQL, un bloc de base dans DataLab pour interroger des bases avec SQL. Si vous avez créé un workbook à partir du modèle fourni, cette cellule SQL est déjà prête dans votre notebook. Si vous êtes parti de zéro, ajoutez‑la via le lanceur en cliquant sur « SQL » :

Dans la cellule SQL, cliquez sur le menu déroulant Source en haut à gauche.

Sélectionnez la feuille que vous souhaitez analyser (dans l’exemple, « Unicorn Companies »). Ensuite, saisissez la requête SQL suivante :

SELECT * FROM unicorn_companies

En cliquant sur Run, cette requête récupère toutes les lignes de l’onglet unicorn_companies dans la feuille Google Sheets « Unicorn Companies », puis stocke le résultat dans un nouveau DataFrame pandas df :

Un DataFrame pandas est une structure tabulaire que vous pouvez utiliser en Python pour stocker et manipuler des données. Ajoutons une cellule Python et exécutons le code suivant, qui calcule la médiane des financements des entreprises « licornes » par pays dans le jeu de données :

median_funding_df = df.groupby("country")["funding"].median().reset_index()
median_funding_df.columns = ['country', 'median_funding']
median_funding_df

Dans la cellule SQL ci‑dessus, nous avons utilisé une requête simple pour récupérer toutes les données, mais vous pouvez tout aussi bien employer une instruction SQL plus élaborée pour, par exemple, extraire le top 10 des licornes les plus valorisées aux États‑Unis :

SELECT *
FROM unicorn_companies
WHERE country = 'United States'
ORDER BY valuation DESC
LIMIT 10

Et voilà ! Pour plus d’astuces sur l’affinage des requêtes SQL, par exemple pour cibler une plage précise d’une feuille, consultez l’article de documentation DataLab correspondant.

Comme mentionné plus haut, cette connexion fluide avec Google Sheets ne sert qu’à lire des données. Si vous souhaitez à la fois lire et réécrire des résultats dans Google Sheets, passez à la section suivante qui explique comment utiliser l’API Google Sheets pour une configuration ultra flexible.

Utiliser l’API Google Sheets

Choisissez cette option si vous voulez à la fois lire des données depuis une feuille Google Sheets et écrire les résultats de vos calculs en Python dans la même feuille ou une autre.

Comme indiqué en introduction, cette flexibilité accrue nécessite une configuration plus poussée. Plus précisément, nous allons :

• Configurer un compte de service Google
• Créer une feuille Google avec des données
• Créer un nouveau workbook
• Stocker les identifiants du compte de service dans DataLab
• Lire les données de Google Sheets
• Analyser les données en Python
• Écrire les résultats dans Google Sheets

Voyons chacune de ces étapes.

1. Configurer un compte de service Google

Pour accéder de manière programmatique aux données de Google Sheets, vous devez créer un compte de service Google, un type de compte spécial utilisé par des programmes pour accéder à des ressources Google, comme une feuille de calcul. Vous utiliserez ensuite ce compte de service pour connecter DataLab à Google Sheets.

Vous ne devez configurer ce compte de service Google qu’une seule fois par compte Google dans lequel vous souhaitez accéder aux feuilles ; vous pourrez ignorer cette étape la prochaine fois que vous travaillerez sur des feuilles dans le même compte.

Suivez les étapes ci‑dessous pour créer le compte de service et générer les identifiants nécessaires.

• Assurez‑vous d’être connecté à votre compte Google.
• Accédez à la bibliothèque d’API Google
• Créez un nouveau projet en cliquant sur le menu déroulant dans la barre de navigation.
• Recherchez « Google Sheets API » et activez‑la. Cela peut prendre jusqu’à 10 secondes.
• Dans la barre latérale « APIs and services », ouvrez l’onglet « Credentials »
• Cliquez sur « + CREATE CREDENTIALS » et sélectionnez « Service Account »
• À l’étape 1 (détails du compte de service), indiquez un nom, par ex. « gsheet-operator », puis cliquez sur « Create and continue »
• À l’étape 2, sélectionnez le rôle « Owner » et cliquez sur « Continue »
• À l’étape 3, ne changez rien et cliquez sur « Done »
• De retour sur la page Credentials, cliquez sur le compte de service que vous venez de créer.
• Allez dans l’onglet Keys, puis « Add Key > Create new key »
• Choisissez « JSON », puis cliquez sur « Create ». Le fichier JSON contenant les identifiants du compte de service est automatiquement téléchargé sur votre ordinateur.

Vous avez maintenant un compte de service et un fichier d’identifiants JSON ! Rendez‑vous dans votre dossier Téléchargements (ou l’emplacement choisi), ouvrez le fichier et jetez‑y un œil. Il devrait ressembler à ceci :

{
  "type": "service_account",
  "project_id": "<your-project-name>",
  "private_key_id": "<something-private>",
  "private_key": "-----BEGIN PRIVATE KEY-----\nM<some-very-private-stuff\n",
  "client_email": "gsheets-operator@steam-verve-386214.iam.gserviceaccount.com",
  "client_id": "123456789012345678901",
  "auth_uri": "https://accounts.google.com/o/oauth2/auth",
  "token_uri": "https://oauth2.googleapis.com/token",
  "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
  "client_x509_cert_url": "https://www.googleapis.com/robot/v1/metadata/x509/gsheets-operator%40<project-name>.iam.gserviceaccount.com"
}

Vous y trouverez un champ « client_email » : gsheets-operator@<google-project-name>.iam.gserviceaccount.com. Copiez cette adresse e‑mail : vous en aurez besoin à l’étape suivante.

2. Créer une feuille Google avec des données

Avant d’analyser des données dans un tableur, assurez‑vous de disposer d’une feuille contenant des données. Si vous n’avez pas de jeu de données sous la main, vous pouvez partir de la feuille Google Sheets préparée pour ce tutoriel : ouvrez la feuille d’exemple puis, dans Google Sheets, cliquez sur « Fichier > Créer une copie », indiquez un nom et validez avec « Créer une copie ». Si vous avez déjà une feuille avec les données à analyser, ouvrez‑la simplement.

Que vous travailliez sur un duplicata de la feuille d’exemple ou sur votre propre feuille, vous devez accorder au compte de service Google créé à l’étape 1 l’accès à la feuille :

• Cliquez sur « Partager »
• Ajoutez l’e‑mail du compte de service copié précédemment en tant qu’éditeur de la feuille (par ex. gsheets-operator@<google-project-name>.iam.gserviceaccount.com)
• Cliquez sur « Envoyer »

Parfait : compte de service, c’est fait. Feuille Google avec les bons droits, c’est fait. Il est temps d’écrire un peu de Python !

3. Créer un nouveau workbook

Créez ensuite un nouveau projet de données dans DataLab, également appelé workbook. Vous pouvez soit créer un workbook vide si vous souhaitez écrire tout le code vous‑même, soit créer un workbook contenant déjà tout le code.

4. Stocker les identifiants du compte de service dans DataLab

Pour utiliser le JSON d’identifiants du compte de service dans votre nouveau workbook, vous devez le stocker dans DataLab.

Vous pourriez copier‑coller le contenu du JSON dans une cellule de code de votre workbook :

Ce n’est pas sécurisé et nous ne le recommandons pas. Si vous partagez votre notebook, d’autres verront ces identifiants en clair et pourraient se faire passer pour vous pour accéder aux ressources associées au compte de service.

De plus, cette approche vous obligerait à répéter ce code dans chaque workbook accédant aux données d’une feuille. Si vous devez faire tourner les identifiants, il faudrait modifier chaque workbook.

Une approche plus sûre et plus scalable consiste à stocker le JSON dans une variable d’environnement. Une fois connectée, cette variable sera disponible dans votre session Python. Dans votre workbook :

• Cliquez sur l’onglet « Environment » à gauche
• Cliquez sur l’icône plus à côté de « Environment Variables »
• Dans la fenêtre « Add Environment Variables » :
  • Renseignez « Name » avec GOOGLE_JSON
  • Renseignez « Value » avec l’intégralité du contenu du fichier JSON du compte de service téléchargé. Pour cela, ouvrez le fichier, sélectionnez tout, copiez‑le puis collez‑le dans le champ Value.
  • Définissez « Environment Variables Set Name » sur « Google Service Account » (le nom est libre)

Après avoir rempli tous les champs, cliquez sur « Create », « Next », puis « Connect ». Votre session DataLab redémarre et GOOGLE_JSON est désormais disponible comme variable d’environnement dans votre workbook. Vous pouvez le vérifier en créant une cellule Python avec le code suivant et en l’exécutant :

import os
google_json = os.environ["GOOGLE_JSON"]
print(google_json)

Si vous souhaitez réutiliser ces mêmes identifiants dans un autre workbook, vous n’avez pas besoin de recréer la variable : vous pouvez la réutiliser telle quelle.

5. Lire les données Google Sheets

Passons à la partie la plus intéressante : écrire du code Python pour se connecter aux données Google Sheets.

Installer et importer les bons packages

Commencez par installer et importer tous les packages nécessaires pour établir une connexion à votre compte Google API. Nous utiliserons google-auth pour l’authentification auprès de l’API Google, gspread pour interagir facilement avec les feuilles Google, et pandas pour manipuler des données tabulaires en Python.

%%capture
!pip install gspread
from google.oauth2 import service_account
import pandas as pd
import gspread
import json
import os

Configurer un client avec des identifiants « scopés »

Chargez la variable d’environnement GOOGLE_JSON et stockez‑la dans une variable :

import os
google_json = os.environ["GOOGLE_JSON"]

Créez un objet d’identifiants à partir du JSON du compte de service :

service_account_info = json.loads(google_json)
credentials = service_account.Credentials.from_service_account_info(service_account_info)

Assignez le bon « scope » d’API aux identifiants afin d’obtenir les permissions nécessaires pour interagir avec la feuille Google Sheets :

scope = ['https://spreadsheets.google.com/feeds','https://www.googleapis.com/auth/drive']
creds_with_scope = credentials.with_scopes(scope)

Créez un nouveau client avec ces identifiants « scopés » :

client = gspread.authorize(creds_with_scope)

Charger les données de la feuille dans un DataFrame

Le client étant prêt, nous pouvons lire les données de la feuille créée précédemment.

Commencez par créer une instance de feuille à partir de l’URL du tableur :

spreadsheet = client.open_by_url('https://docs.google.com/spreadsheets/d/spreadsheet_id/edit#gid=0')

Ensuite, ciblez la feuille de travail (l’onglet dans le tableur Google) dont vous voulez extraire les données. Récupérons la première feuille via l’index 0 :

worksheet = spreadsheet.get_worksheet(0)

Commencez par récupérer tous les enregistrements au format JSON :

records_data = worksheet.get_all_records()

Convertissez ensuite ces enregistrements JSON en DataFrame pandas :

records_df = pd.DataFrame.from_dict(records_data)
records_df

Succès !

6. Analyser les données de la feuille en Python

Vous pouvez désormais utiliser Python pour analyser les données lues depuis le tableur Google. Le code minimal ci‑dessous suppose que vous travaillez avec la feuille d’exemple. Si vous utilisez un autre jeu de données, ces étapes ne fonctionneront pas telles quelles. Pour aller plus loin, consultez le cours Data Manipulation with pandas de DataCamp !

Calculons la médiane des financements par pays :

median_funding_df = records_df.groupby("country")["funding"].median().reset_index()
median_funding_df.columns = ['country', 'median_funding']
median_funding_df

7. Écrire les résultats dans Google Sheets

Si vous souhaitez que les utilisateurs du tableur avec qui vous collaborez visualisent le fruit de votre travail directement dans une feuille, vous pouvez réécrire les résultats de votre analyse Python dans un nouvel onglet du tableur d’origine.

Dans cet exemple, nous créons un onglet median_country s’il n’existe pas encore, puis nous y insérons le contenu du DataFrame median_funding_df, en incluant l’en‑tête avec les noms de colonnes. Ce code a été généré avec l’aide de l’AI Assistant !

# Convert the median_funding_df DataFrame to a list of lists
medians_list = median_funding_df.values.tolist()

# Add the header row to the list
medians_list.insert(0, median_funding_df.columns.tolist())

# Check if the 'median_by_country' sheet already exists
sheet_titles = [sheet.title for sheet in spreadsheet.worksheets()]
sheet_name = "median_by_country"
if sheet_name not in sheet_titles:
    # Create a new worksheet named 'median_by_country' in the Google Sheet
    medians_sheet = spreadsheet.add_worksheet(title=sheet_name, rows=len(medians_list), cols=len(medians_list[0]))
else:
    medians_sheet = spreadsheet.worksheet(sheet_name)

# Write the medians_list to the new worksheet
medians_sheet.clear()
medians_sheet.insert_rows(medians_list, row=1)

En retournant sur votre tableur, vous verrez un nouvel onglet avec les données issues du DataFrame ! Cette « réécriture » des résultats n’est possible que si vous utilisez la méthode basée sur l’API Google Sheets, et non le connecteur Google Sheets intégré de DataCamp présenté plus haut.

Conclusion

Selon vos besoins, analyser des données Google Sheets avec Python peut être d’une simplicité déconcertante ou nécessiter une phase initiale de configuration. Si vous souhaitez uniquement lire des données depuis une feuille, optez pour le connecteur intégré et vous serez opérationnel en moins de cinq clics. Si vous voulez à la fois lire et écrire dans Google Sheets, activer l’API Google Sheets et configurer un compte de service sera payant.

Dans les deux cas, une fois la configuration en place, accéder aux données de Google Sheets est presque aussi simple que d’interroger un fichier CSV. L’avantage majeur, c’est que vous rejoignez vos parties prenantes là où elles se trouvent : il y a fort à parier que certains de vos collègues ne jurent que par les tableurs.

Fini les exports CSV laborieux ; place à des workflows puissants et automatisés pour travailler les données Google Sheets sans jamais quitter DataLab.