Geekly articles weekly

Premiers pas avec Google Sheets en Python. De l'enregistrement à la lecture des données

Pendant un certain temps, j'ai réussi à télécharger des données vers Excel, mais le mod change, les utilisateurs le veulent dans les nuages.

Ayant commencé à traduire un certain nombre de projets en Python, j'ai décidé qu'il était temps de changer (ou d'ajouter) à Excel avec quelque chose de plus moderne.

Lorsque j'ai rencontré pour la première fois le besoin de travailler avec des feuilles de calcul Google à partir de Python, j'avais l'illusion que tout cela peut être fait en quelques clics. La réalité s'est avérée moins rose, mais nous n'avons pas d'autre globe.

Les articles m'ont beaucoup aidé:


Comme d'habitude - lorsque vous abordez quelque chose pour la première fois, vous êtes confronté à une masse de questions qui ne causent plus que de la confusion - comment quelqu'un pourrait-il même y penser. Élémentaire!

Peut-ĂŞtre que je viens de marcher un long chemin - je serai heureux si vous me corrigez.

Toutes les actions ont été effectuées sur un ordinateur exécutant Windows + Python 3.6.6, et un bloc-notes Jupyter a également été utilisé.

Les principales difficultés que j'ai eues au stade des réglages préliminaires. Trouver un code exploitable n'est pas difficile.

Le code utilisé dans l'article est disponible dans le référentiel.

Inscrivez-vous aux services Google et installez des bibliothèques


Pour travailler avec des tableaux, vous devez vous inscrire auprès de Google, configurer le projet et installer les bibliothèques nécessaires.

La documentation officielle en anglais est ici .

Vous devez d'abord vous inscrire sur gmail.com (vous pouvez le faire vous-même). Ensuite, vous devez créer un projet (car Google donne accès à ses services).

Il s'agit d'un processus long et fastidieux qui vous permet de comprendre pourquoi les interfaces de Google ne sont pas considérées comme les plus pratiques et intuitives (certaines personnes pensent que le réseau social Google+ n'a pas décollé pour cette raison).

Pour ce faire, accédez à console.developers.google.com/cloud-resource-manager et cliquez sur "Créer un projet"



Entrez le nom du projet et cliquez sur "Créer"



Dans la liste mise Ă  jour des projets, allez dans le menu "Autorisations"


Dans la fenêtre qui s'ouvre, cliquez sur "Ajouter", entrez votre adresse e-mail depuis le domaine gmail.com et sélectionnez le groupe "Projet" - "Propriétaire"



Enregistrez les modifications.

Il peut sembler étrange que vous ayez créé un projet mais que vous soyez obligé de vous accorder des droits. Et c'est en fait étrange, mais c'était une telle voie qui devait être prise au moment d'écrire ce cours, pour que tout commence à fonctionner comme il se doit.

Accédez à nouveau à console.developers.google.com/cloud-resource-manager

Sélectionnez le menu Paramètres de votre projet



Dans la fenêtre qui s'ouvre, sélectionnez «Comptes de service» puis «Créer un compte de service»



Saisissez le nom du compte et cliquez sur "Créer"



Sélectionnez le rôle Propriétaire et cliquez sur Continuer.



Dans la fenêtre qui apparaît, cliquez sur Créer une clé



Sélectionnez le type de clé json et cliquez sur Créer



Un fichier avec des clés sera créé et téléchargé immédiatement. Enregistrez-le, grâce à lui, nous pourrons accéder aux services Google.

Cliquez sur le bouton à trois traits horizontaux, à gauche de l'inscription "API Google", sélectionnez "API et services", et en son sein le sous-élément "Panneau de configuration".



Dans la fenĂŞtre qui s'ouvre, cliquez sur "Activer l'API et les services"



Tapez "google drive" dans la barre de recherche et cliquez sur le service "Google Drive API"



Cliquez sur Activer



Le site vous informera que l'API est activée et vous avertira que les informations d'identification doivent être créées. Ignorez cet avertissement (nous avons déjà créé un compte de service).



Revenez au panneau de contrĂ´le



Dans la fenĂŞtre qui s'ouvre, cliquez sur "Activer l'API et les services"



Saisissez «feuille» dans la barre de recherche et cliquez sur le service «API Google Sheets»



Assurez-vous que cette API est connectée. Il devrait s'activer automatiquement lorsque vous connectez l'API Google Drive. S'il est connecté, vous verrez le bouton "Gérer l'API", sinon, le bouton "Activer". Allumez-le si nécessaire.

La dernière fois, visitez console.developers.google.com/cloud-resource-manager

Sélectionnez le menu Paramètres de votre projet


Dans la fenêtre qui s'ouvre, sélectionnez "Comptes de service", puis copiez et enregistrez l'adresse e-mail du compte de service. Il vous sera utile de donner accès aux tables.



Nous passons maintenant à l'installation des bibliothèques. Exécutez la commande dans la console

pip3 install --upgrade google-api-python-client

et puis

 pip3 install oauth2client

Il est possible que lorsque vous exécutez la deuxième commande, vous recevrez un message indiquant que la bibliothèque oauth2client est déjà installée.

Accédez à raw.githubusercontent.com/gsuitedevs/python-samples/master/sheets/quickstart/quickstart.py

Appuyez sur le bouton droit de la souris et sélectionnez «Enregistrer sous»



Enregistrez le fichier sous quickstart.py

et l'exécuter avec la commande

 python quickstart.py

Une nouvelle page s'ouvrira dans le navigateur (peut-être que cela dira que la page n'est pas sûre, mais allez-y hardiment) et vous devrez accepter les conditions.

Sur ce chemin, notre chemin est achevé.

Remplir et mettre en forme un tableau


Créer la première table

 #   import httplib2 import apiclient.discovery from oauth2client.service_account import ServiceAccountCredentials CREDENTIALS_FILE = 'seraphic-effect-248407-7ac2c44ec709.json' #     ,     #     credentials = ServiceAccountCredentials.from_json_keyfile_name(CREDENTIALS_FILE, ['https://www.googleapis.com/auth/spreadsheets', 'https://www.googleapis.com/auth/drive']) httpAuth = credentials.authorize(httplib2.Http()) #    service = apiclient.discovery.build('sheets', 'v4', http = httpAuth) #      4  API spreadsheet = service.spreadsheets().create(body = { 'properties': {'title': '  ', 'locale': 'ru_RU'}, 'sheets': [{'properties': {'sheetType': 'GRID', 'sheetId': 0, 'title': '  ', 'gridProperties': {'rowCount': 100, 'columnCount': 15}}}] }).execute() spreadsheetId = spreadsheet['spreadsheetId'] #    print('https://docs.google.com/spreadsheets/d/' + spreadsheetId)

Si tout s'est déroulé sans erreur, un lien vers le tableau sera affiché.

L'identifiant du fichier est utilisé dans ce lien, nous l'enregistrons dans la variable spreadsheetId et nous l'utiliserons à l'avenir.

Suivez-le. Google vous informera que vous n'avez pas accès



Ne demandez pas la permission! Vous recevrez une notification indiquant qu'il n'est pas possible d'envoyer une lettre de demande à l'adresse que Google a elle-même attribuée au compte système. Mais vous ne pouvez pas changer cette adresse. Peut-être que cela ne fonctionne pas uniquement en mode gratuit.

Mais nous pouvons nous donner accès via Google Drive. Vous devez remplacer my_test_address@gmail.com par le vôtre.

 driveService = apiclient.discovery.build('drive', 'v3', http = httpAuth) #    Google Drive  3  API access = driveService.permissions().create( fileId = spreadsheetId, body = {'type': 'user', 'role': 'writer', 'emailAddress': 'my_test_address@gmail.com'}, #     fields = 'id' ).execute()

Maintenant que vous avez accès, ne fermez pas la table, nous allons la gérer et regarder immédiatement les changements.

Chaque document a son propre code - spreadsheetId - c'est exactement ce qui est affiché dans la barre d'adresse lorsque nous ouvrons la table dans le navigateur (dans l'URL de la page avec la table ouverte, c'est entre "https://docs.google.com/spreadsheets/d/" et "/ edit # gid = 0").

Nous l'avons enregistré dans le tableur variable et continuerons de travailler avec.

Tout d'abord, un peu de théorie.

Dans chaque fichier (feuille de calcul), il y a des onglets (feuille).

Chaque feuille a son propre code numérique (sheetId). La première feuille créée dans le document a cet ID égal à 0. Les feuilles restantes ont un ID différent de zéro très différent (c'est-à-dire qu'elles ne sont pas numérotées consécutivement).

Assurez-vous de cela

 #   results = service.spreadsheets().batchUpdate( spreadsheetId = spreadsheetId, body = { "requests": [ { "addSheet": { "properties": { "title": "  ", "gridProperties": { "rowCount": 20, "columnCount": 12 } } } } ] }).execute() #   ,  Id   spreadsheet = service.spreadsheets().get(spreadsheetId = spreadsheetId).execute() sheetList = spreadsheet.get('sheets') for sheet in sheetList: print(sheet['properties']['sheetId'], sheet['properties']['title']) sheetId = sheetList[0]['properties']['sheetId'] print('     Id = ', sheetId)

Quelque chose comme ça apparaîtra à l'écran:

0 Feuille numéro un
415832263 Une autre feuille
Nous utiliserons une feuille avec Id = 0


En fait, la première feuille a un Id de zéro et la seconde est numérotée différemment.

Une autre question: comment spécifier des plages de cellules. Apparemment, les tables Google ont été développées par différentes équipes, sous la direction de différents gestionnaires et avec l'aide de différents architectes. Parce que les coordonnées des cellules sont définies de deux manières différentes.

Option 1: au format texte «Feuille numéro un! B2: D5», c'est-à-dire le nom de la feuille, suivi d'un point d'exclamation, après - la cellule supérieure gauche au format "lettre (colonne) + nombre (chaîne)" + la cellule inférieure droite au même format.

 {"range": "  !B2:D5"}

Option 2: au format json, indiquant l'ID de la feuille et les coordonnées des cellules supérieure gauche et inférieure droite sous forme numérique (numéro de ligne et numéro de colonne)

 {"range": { "sheetId": sheetId, # ID  "startRowIndex": 1, #    startRowIndex "endRowIndex": 5,#  endRowIndex - 1 (endRowIndex  !) "startColumnIndex": 0, #    startColumnIndex "endColumnIndex": 1 #  endColumnIndex - 1 }}

Différentes fonctions utilisent différents formats.

Nous en savons maintenant assez pour remplir les cellules de données, dessiner un cadre et mettre en évidence les en-têtes.

 results = service.spreadsheets().values().batchUpdate(spreadsheetId = spreadsheetId, body = { "valueInputOption": "USER_ENTERED", #  ,    (  ) "data": [ {"range": "  !B2:D5", "majorDimension": "ROWS", #   ,   "values": [ [" B2", " C2", " D2"], #    ['25', "=6*6", "=sin(3,14/2)"] #    ]} ] }).execute()

Remplissez plusieurs cellules avec des données. Parce que le paramètre USER_ENTERED est spécifié, le tableau perçoit ces données comme il percevrait les entrées utilisateur - convertit les valeurs numériques en nombres et les valeurs commençant par le signe égal en formules.

Regardez votre table, elle est remplie de données



Définissez la largeur de colonne. La fonction batchUpdate peut accepter plusieurs commandes à la fois, nous allons donc définir la largeur de trois groupes de colonnes avec une seule demande. Il y a une colonne dans les premier et troisième groupes et deux dans le second.

 results = service.spreadsheets().batchUpdate(spreadsheetId = spreadsheetId, body = { "requests": [ #    A: 20  { "updateDimensionProperties": { "range": { "sheetId": sheetId, "dimension": "COLUMNS", #    "startIndex": 0, #     "endIndex": 1 #    startIndex  endIndex - 1 (endIndex  !) }, "properties": { "pixelSize": 20 #    }, "fields": "pixelSize" # ,     pixelSize } }, #    B  C: 150  { "updateDimensionProperties": { "range": { "sheetId": sheetId, "dimension": "COLUMNS", "startIndex": 1, "endIndex": 3 }, "properties": { "pixelSize": 150 }, "fields": "pixelSize" } }, #    D: 200  { "updateDimensionProperties": { "range": { "sheetId": sheetId, "dimension": "COLUMNS", "startIndex": 3, "endIndex": 4 }, "properties": { "pixelSize": 200 }, "fields": "pixelSize" } } ] }).execute()

Regardez le tableau, les largeurs des colonnes ont changé.



Dessinez un cadre autour de la table

 #   results = service.spreadsheets().batchUpdate( spreadsheetId = spreadsheetId, body = { "requests": [ {'updateBorders': {'range': {'sheetId': sheetId, 'startRowIndex': 1, 'endRowIndex': 3, 'startColumnIndex': 1, 'endColumnIndex': 4}, 'bottom': { #      'style': 'SOLID', #   'width': 1, #  1  'color': {'red': 0, 'green': 0, 'blue': 0, 'alpha': 1}}, #   'top': { #      'style': 'SOLID', 'width': 1, 'color': {'red': 0, 'green': 0, 'blue': 0, 'alpha': 1}}, 'left': { #      'style': 'SOLID', 'width': 1, 'color': {'red': 0, 'green': 0, 'blue': 0, 'alpha': 1}}, 'right': { #      'style': 'SOLID', 'width': 1, 'color': {'red': 0, 'green': 0, 'blue': 0, 'alpha': 1}}, 'innerHorizontal': { #       'style': 'SOLID', 'width': 1, 'color': {'red': 0, 'green': 0, 'blue': 0, 'alpha': 1}}, 'innerVertical': { #       'style': 'SOLID', 'width': 1, 'color': {'red': 0, 'green': 0, 'blue': 0, 'alpha': 1}} }} ] }).execute()



Combinez les cellules au-dessus du tableau et entrez-y le titre.

 #   A2:D1 results = service.spreadsheets().batchUpdate( spreadsheetId = spreadsheetId, body = { "requests": [ {'mergeCells': {'range': {'sheetId': sheetId, 'startRowIndex': 0, 'endRowIndex': 1, 'startColumnIndex': 1, 'endColumnIndex': 4}, 'mergeType': 'MERGE_ALL'}} ] }).execute() #    results = service.spreadsheets().values().batchUpdate(spreadsheetId = spreadsheetId, body = { "valueInputOption": "USER_ENTERED", #  ,    (  ) "data": [ {"range": "  !B1", "majorDimension": "ROWS", #   ,   "values": [[" " ] ]} ] }).execute()



DĂ©finir le format des cellules d'en-tĂŞte de tableau

 #    results = service.spreadsheets().batchUpdate( spreadsheetId = spreadsheetId, body = { "requests": [ { "repeatCell": { "cell": { "userEnteredFormat": { "horizontalAlignment": 'CENTER', "backgroundColor": { "red": 0.8, "green": 0.8, "blue": 0.8, "alpha": 1 }, "textFormat": { "bold": True, "fontSize": 14 } } }, "range": { "sheetId": sheetId, "startRowIndex": 1, "endRowIndex": 2, "startColumnIndex": 1, "endColumnIndex": 4 }, "fields": "userEnteredFormat" } } ] }).execute()



Il existe un moyen simple de déterminer la largeur ou la couleur à définir pour une cellule. Pour ce faire, il suffit de formater manuellement l'une des cellules et de lire ses propriétés.

 ranges = ["  !C2:C2"] # results = service.spreadsheets().get(spreadsheetId = spreadsheetId, ranges = ranges, includeGridData = True).execute() print(' ') print(results['properties']) print('\n  ') print(results['sheets'][0]['data'][0]['rowData'] ) print('\n ') print(results['sheets'][0]['data'][0]['rowMetadata']) print('\n ') print(results['sheets'][0]['data'][0]['columnMetadata'])

Nous obtenons en réponse
Données de base
{'title': 'First test document', 'locale': 'ru_RU', 'autoRecalc': 'ON_CHANGE', 'timeZone': 'Etc / GMT', 'defaultFormat': {'backgroundColor': {'red' : 1, 'vert': 1, 'bleu': 1}, 'rembourrage': {'haut': 2, 'droit': 3, 'bas': 2, 'gauche': 3}, 'verticalAlignment': 'BOTTOM', 'wrapStrategy': 'OVERFLOW_CELL', 'textFormat': {'foregroundColor': {}, 'fontFamily': 'arial, sans, sans-serif', 'fontSize': 10, 'bold': False, 'italique': Faux, 'barré': Faux, 'souligné': Faux}}}

Valeurs et coloration
[{'values': [{'userEnteredValue': {'stringValue': 'Cell C2'}, 'effectiveValue': {'stringValue': 'Cell C2'}, 'formatedValue': 'Cell C2', 'userEnteredFormat' : {'backgroundColor': {'red': 1, 'green': 0.6}, 'horizontalAlignment': 'CENTER', 'textFormat': {'fontSize': 14, 'bold': True, 'italic': True }}, 'effectiveFormat': {'backgroundColor': {'red': 1, 'green': 0.6}, 'padding': {'top': 2, 'right': 3, 'bottom': 2, ' left ': 3},' horizontalAlignment ':' CENTER ',' verticalAlignment ':' BOTTOM ',' wrapStrategy ':' OVERFLOW_CELL ',' textFormat ': {' foregroundColor ': {},' fontFamily ':' Arial ', 'fontSize': 14, 'bold': True, 'italic': True, 'strikethrough': False, 'underline': False}, 'hyperlinkDisplayType': 'PLAIN_TEXT'}}]}}]]

Hauteur de cellule
[{'pixelSize': 21}]

Largeur de cellule
[{'pixelSize': 150}]
Ce code affichera les propriétés de la cellule C2. Vous pouvez sélectionner la police et la couleur de remplissage manuellement (dans le tableau), puis voir comment elles se reflètent dans json.

Lecture des données d'une table


Afin de révéler pleinement les caractéristiques de la lecture des données, j'ai rempli manuellement les cellules B4, C7 et D5 comme indiqué sur la figure.



Code pour lire les données

 ranges = ["  !A2:F8"] # results = service.spreadsheets().values().batchGet(spreadsheetId = spreadsheetId, ranges = ranges, valueRenderOption = 'FORMATTED_VALUE', dateTimeRenderOption = 'FORMATTED_STRING').execute() sheet_values = results['valueRanges'][0]['values'] print(sheet_values)

RĂ©sultat
[['', 'Cell B2', 'Cell C2', 'Cell D2'], ['', '25', '36', '0,9999996829']]

Certains paramètres de la fonction: valueRenderOption - format de lecture des données numériques.

  • FORMATTED_VALUE - lecture basĂ©e sur le format d'affichage. C'est-Ă -dire ce qui Ă©tait visible dans le tableau sera lu. Par exemple, dans la cellule D3, le nombre est 0,9999999, mais le format est "deux dĂ©cimales", donc "1,00" est affichĂ© et il sera lu dans ce format.
  • UNFORMATTED_VALUE - le contenu de la cellule est lu, le formatage n'est pas pris en compte (c'est-Ă -dire que 0,9999999 serait lu)
  • FORMULE - la formule est affichĂ©e (dans ce cas «= sin (3,14 / 2)». Si un nombre est entrĂ© dans la cellule, alors dans ce mode il sera lu.

Ce code lit les données et les affiche ligne par ligne. Plage lisible A2: F8.
Comme vu Ă  l'Ă©cran:

  • Si aucune cellule de la ligne en cours de lecture n'est remplie, les donnĂ©es de la ligne ne s'affichent pas.
  • Les donnĂ©es après la dernière cellule remplie ne sont pas affichĂ©es.

Source: https://habr.com/ru/post/fr483302/

More articles:


All Articles