Erste Schritte mit Google Sheets in Python. Von der Registrierung bis zum Lesen von Daten

Ich habe es eine ganze Weile geschafft, Daten nach Excel hochzuladen, aber der Mod ändert sich, Benutzer wollen ihn in den Clouds.

Nachdem ich angefangen hatte, eine Reihe von Projekten in Python zu übersetzen, entschied ich, dass es Zeit war, Excel durch etwas Moderneres zu ersetzen (oder zu ergänzen).

Als ich zum ersten Mal auf die Notwendigkeit stieß, mit Google-Arbeitsblättern von Python aus zu arbeiten, hatte ich die Illusion, dass dies alles mit ein paar Klicks erledigt werden kann. Die Realität stellte sich als weniger rosig heraus, aber wir haben keinen anderen Globus.

Die Artikel haben mir sehr geholfen:

• Wir generieren eine schöne Google-Tabelle aus unserem Programm (unter Verwendung der Google Sheets API v4).
• Erste Schritte mit der Google Sheets API v4 /
• Arbeiten mit der Google Drive-API mit Python

Wie üblich - wenn Sie sich zum ersten Mal mit etwas beschäftigen, werden Sie mit einer Vielzahl von Fragen konfrontiert, die später nur Verwirrung stiften -, wie könnte man überhaupt darüber nachdenken? Grundlegend!

Vielleicht bin ich nur einen weiten Weg gegangen - ich freue mich, wenn Sie mich korrigieren.

Alle Aktionen wurden auf einem Computer mit Windows + Python 3.6.6 ausgeführt, und es wurde auch ein Jupyter-Notizbuch verwendet.

Die Hauptschwierigkeiten hatte ich bei den Voreinstellungen. Einen brauchbaren Code zu finden ist nicht schwierig.

Der im Artikel verwendete Code ist im Repository verfügbar .

Melden Sie sich für Google-Dienste an und installieren Sie Bibliotheken

Um mit Tabellen arbeiten zu können, müssen Sie sich bei Google registrieren, das Projekt einrichten und die erforderlichen Bibliotheken installieren.

Die offizielle Dokumentation in englischer Sprache finden Sie hier .

Zuerst müssen Sie sich auf gmail.com registrieren (Sie können es selbst tun). Dann müssen Sie ein Projekt erstellen (da Google Zugriff auf seine Dienste bietet).

Dies ist ein langer und langwieriger Prozess, mit dem Sie nachvollziehen können, warum die Oberflächen von Google nicht als die bequemsten und intuitivsten bezeichnet werden (einige glauben, dass das soziale Netzwerk Google+ aus diesem Grund nicht gestartet ist).

Rufen Sie dazu console.developers.google.com/cloud-resource-manager auf und klicken Sie auf "Projekt erstellen".



Geben Sie den Namen des Projekts ein und klicken Sie auf "Erstellen"



In der aktualisierten Liste der Projekte gehen Sie zum Menü "Berechtigungen"


Klicken Sie im folgenden Fenster auf "Hinzufügen", geben Sie Ihre E-Mail-Adresse aus der Domain "gmail.com" ein und wählen Sie die Gruppe "Projekt" - "Eigentümer" aus



Speichern Sie die Änderungen.

Es mag seltsam erscheinen, dass Sie ein Projekt erstellt haben, aber gezwungen sind, sich selbst Rechte zu erteilen. Und das ist eigentlich seltsam, aber es war ein solcher Weg, der zum Zeitpunkt des Schreibens dieses Kurses eingeschlagen werden musste, damit alles so funktioniert, wie es sollte.

Rufen Sie console.developers.google.com/cloud-resource-manager erneut auf

Wählen Sie in Ihrem Projekt das Menü Einstellungen



Wählen Sie im folgenden Fenster "Dienstkonten" und dann "Dienstkonto erstellen".



Geben Sie den Namen des Kontos ein und klicken Sie auf "Erstellen".



Wählen Sie die Eigentümerrolle aus und klicken Sie auf Weiter.



Klicken Sie im angezeigten Fenster auf Schlüssel erstellen



Wählen Sie den json-Schlüsseltyp und klicken Sie auf Erstellen



Eine Datei mit Schlüsseln wird erstellt und sofort heruntergeladen. Speichern Sie es, damit wir auf Google-Dienste zugreifen können.

Klicken Sie auf die Schaltfläche mit drei horizontalen Strichen links neben der Aufschrift „Google APIs“, wählen Sie „API and Services“ und dort den Unterpunkt „Control Panel“.



Klicken Sie im folgenden Fenster auf "API und Dienste aktivieren".



Geben Sie "google drive" in die Suchleiste ein und klicken Sie auf den Dienst "Google Drive API"



Klicken Sie auf Aktivieren



Die Site benachrichtigt Sie, dass die API aktiviert ist, und warnt Sie, dass Anmeldeinformationen erstellt werden müssen. Ignorieren Sie diese Warnung (wir haben bereits ein Dienstkonto erstellt).



Gehen Sie zurück zur Systemsteuerung



Klicken Sie im folgenden Fenster auf "API und Dienste aktivieren".



Geben Sie "sheet" in die Suchleiste ein und klicken Sie auf den Dienst "Google Sheets API"



Stellen Sie sicher, dass diese API verbunden ist. Es sollte sich automatisch einschalten, wenn Sie die Google Drive-API verbinden. Wenn eine Verbindung besteht, wird die Schaltfläche "API verwalten" angezeigt, andernfalls die Schaltfläche "Aktivieren". Schalten Sie es bei Bedarf ein.

Besuchen Sie zum letzten Mal console.developers.google.com/cloud-resource-manager

Wählen Sie in Ihrem Projekt das Menü Einstellungen


Wählen Sie im folgenden Fenster "Dienstkonten" aus und kopieren und speichern Sie die E-Mail-Adresse des Dienstkontos. Es ist praktisch, wenn Sie Zugriff auf Tabellen gewähren.



Nun fahren wir mit der Installation der Bibliotheken fort. Führen Sie den Befehl in der Konsole aus

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

Und danach

 pip3 install oauth2client 

Wenn Sie den zweiten Befehl ausführen, wird möglicherweise eine Meldung angezeigt, dass die oauth2client-Bibliothek bereits installiert ist.

Gehen Sie zu raw.githubusercontent.com/gsuitedevs/python-samples/master/sheets/quickstart/quickstart.py

Drücken Sie die rechte Maustaste und wählen Sie "Speichern unter"



Speichern Sie die Datei als quickstart.py

und führen Sie es mit dem Befehl

 python quickstart.py 

Im Browser wird eine neue Seite geöffnet (möglicherweise wird darauf hingewiesen, dass die Seite unsicher ist, Sie müssen jedoch die Bedingungen akzeptieren).

Damit ist unser Weg abgeschlossen.

Eine Tabelle füllen und formatieren

Erstellen Sie die erste Tabelle

 #   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) 

Wenn alles ohne Fehler gelaufen ist, wird ein Link zur Tabelle angezeigt.

Die Dateikennung wird in diesem Link verwendet, wir speichern sie in der variablen Tabellenkalkulations-ID und werden sie in Zukunft verwenden.

Folge ihm. Google teilt Ihnen mit, dass Sie keinen Zugriff haben



Bitte nicht um Erlaubnis! Sie erhalten eine Benachrichtigung, dass es nicht möglich ist, ein Anforderungsschreiben an die Adresse zu senden, die Google selbst dem Systemkonto zugewiesen hat. Sie können diese Adresse jedoch nicht ändern. Vielleicht funktioniert das nicht nur im freien Modus.

Wir können uns jedoch über Google Drive Zugang verschaffen. Sie müssen my_test_address@gmail.com durch Ihre eigene ersetzen.

 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() 

Jetzt haben Sie Zugriff, schließen Sie die Tabelle nicht, wir werden sie verwalten und die Änderungen sofort überprüfen.

Jedes Dokument hat seinen eigenen Code - spreadsheetId - genau das wird in der Adressleiste angezeigt, wenn wir die Tabelle im Browser öffnen (in der URL der Seite mit der geöffneten Tabelle befindet sie sich zwischen "https://docs.google.com/spreadsheets/d/" und "/ edit # gid = 0").

Wir haben es in der variablen Tabellenkalkulations-ID gespeichert und werden weiterhin damit arbeiten.

Zunächst eine kleine Theorie.

In jeder Datei (Kalkulationstabelle) gibt es Blatt-Tabulatoren (Tabelle).

Jedes Blatt hat einen eigenen numerischen Code (sheetId). Das erste im Dokument erstellte Blatt hat eine ID von 0. Die übrigen Blätter haben eine sehr unterschiedliche ID ungleich Null (dh sie werden nicht fortlaufend nummeriert).

Stellen Sie dies sicher

 #   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) 

So etwas wird auf dem Bildschirm angezeigt:

0 Blatt Nummer eins
415832263 Ein weiteres Blatt
Wir werden ein Blatt mit Id = 0 verwenden

Tatsächlich hat das erste Blatt eine ID von Null und das zweite Blatt ist anders nummeriert.

Noch eine Frage: Wie werden Zellbereiche angegeben? Anscheinend wurden Google-Tabellen von verschiedenen Teams unter Anleitung verschiedener Manager und mit Hilfe verschiedener Architekten entwickelt. Weil die Koordinaten der Zellen auf zwei verschiedene Arten festgelegt werden.

Option 1: im Textformat "Blatt Nummer eins! B2: D5", d.h. der Name des Blattes, gefolgt von einem Ausrufezeichen, nach - der oberen linken Zelle im Format "Buchstabe (Spalte) + Zahl (Zeichenfolge)" + der unteren rechten Zelle im gleichen Format.

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

Option 2: im JSON-Format, mit Angabe der Blatt-ID und der Koordinaten der oberen linken und unteren rechten Zelle in numerischer Form (Zeilennummer und Spaltennummer)

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

Unterschiedliche Funktionen verwenden unterschiedliche Formate.

Jetzt wissen wir genug, um die Zellen mit Daten zu füllen, einen Rahmen zu zeichnen und die Überschriften hervorzuheben.

 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() 

Füllen Sie mehrere Zellen mit Daten. Weil Wenn der Parameter USER_ENTERED angegeben ist, nimmt die Tabelle diese Daten als Benutzereingaben wahr - konvertiert numerische Werte in Zahlen und Werte, die mit dem Gleichheitszeichen beginnen, in Formeln.

Schauen Sie sich Ihren Tisch an, er ist mit Daten gefüllt



Stellen Sie die Spaltenbreite ein. Die batchUpdate-Funktion kann mehrere Befehle gleichzeitig akzeptieren. Daher wird die Breite von drei Spaltengruppen mit einer Anforderung festgelegt. Es gibt eine Spalte in der ersten und dritten Gruppe und zwei in der zweiten.

 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() 

Schauen Sie sich die Tabelle an, die Spaltenbreiten haben sich geändert.



Zeichnen Sie einen Rahmen um den Tisch

 #   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() 

Kombinieren Sie die Zellen über der Tabelle und geben Sie den Titel ein.

 #   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() 

Legen Sie das Format für Tabellenkopfzellen fest

 #    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() 

Auf einfache Weise können Sie herausfinden, welche Breite oder Farbe Sie für eine Zelle festlegen müssen. Dazu reicht es aus, eine der Zellen manuell zu formatieren und ihre Eigenschaften zu lesen.

 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']) 

Wir bekommen eine Antwort

Stammdaten
{'title': 'Erstes Testdokument', 'locale': 'ru_RU', 'autoRecalc': 'ON_CHANGE', 'timeZone': 'Etc / GMT', 'defaultFormat': {'backgroundColor': {'red' : 1, 'grün': ​​1, 'blau': 1}, 'Polsterung': {'oben': 2, 'rechts': 3, 'unten': 2, 'links': 3}, 'vertikale Ausrichtung': 'BOTTOM', 'wrapStrategy': 'OVERFLOW_CELL', 'textFormat': {'foregroundColor': {}, 'fontFamily': 'arial, sans, sans-serif', 'fontSize': 10, 'bold': False, 'kursiv': Falsch, 'durchgestrichen': Falsch, 'unterstrichen': Falsch}}}

Werte und Farbgebung
[{'values': [{'userEnteredValue': {'stringValue': 'Cell C2'}, 'effectiveValue': {'stringValue': 'Cell C2'}, 'formatedValue': 'Cell C2', 'userEnteredFormat' : {'backgroundColor': {'red': 1, 'green': 0.6}, 'horizontalAlignment': 'CENTER', 'textFormat': {'fontSize': 14, 'fett': True, 'kursiv': 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, 'fett': True, 'kursiv': True, 'durchgestrichen': False, 'unterstrichen': False}, 'hyperlinkDisplayType': 'PLAIN_TEXT'}}}]]

Zellenhöhe
[{'pixelSize': 21}]

Zellenbreite
[{'pixelSize': 150}]

Dieser Code zeigt die Eigenschaften der Zelle C2 an. Sie können die Schriftart und die Füllfarbe manuell auswählen (in der Tabelle) und dann sehen, wie sie in json widergespiegelt werden.

Daten aus einer Tabelle lesen

Um die Merkmale des Datenlesens vollständig aufzuzeigen, habe ich die Zellen B4, C7 und D5 wie in der Abbildung gezeigt manuell ausgefüllt.



Code zum Lesen von Daten

 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) 

Ergebnis

[['', 'Zelle B2', 'Zelle C2', 'Zelle D2'], ['', '25', '36', '0,9999996829']]

Einige Parameter der Funktion: valueRenderOption - Format zum Lesen numerischer Daten.

• FORMATTED_VALUE - Lesen basierend auf dem Anzeigeformat. Das heißt Was in der Tabelle sichtbar war, wird gelesen. In Zelle D3 lautet die Zahl beispielsweise 0,9999999, das Format ist jedoch "zwei Dezimalstellen", sodass "1,00" angezeigt wird und in diesem Format gelesen wird.
• UNFORMATTED_VALUE - der Inhalt der Zelle wird gelesen, die Formatierung wird nicht berücksichtigt (d. H. 0,9999999 würde gelesen)
• FORMEL - Die Formel wird angezeigt (in diesem Fall "= sin (3,14 / 2)". Wenn eine Zahl in die Zelle eingegeben wird, wird sie in diesem Modus gelesen.

Dieser Code liest die Daten und zeigt sie zeilenweise an. Lesebereich A2: F8.
Wie auf dem Bildschirm zu sehen:

• Wenn keine Zelle in der zu lesenden Zeile gefüllt ist, werden die Daten in der Zeile nicht angezeigt.
• Daten nach der letzten gefüllten Zelle werden nicht angezeigt.