Google Sheets ist eine der beliebtesten und am h?ufigsten verwendeten Alternativen zu Excel und bietet eine kollaborative Umgebung mit Funktionen wie Echtzeitbearbeitung, Versionskontrolle und nahtloser Integration in Google Suite, mit der Benutzer Google Sheets in Google-Dokumenten anrufen und das Beste aus dem Google-Arbeitsbereich nutzen k?nnen. W?hrend Sie programmgesteuert mit Pandas, einer führenden Data Science -Bibliothek, programmatisch mit Excel -Dateien laden und arbeiten k?nnen, kann ein ?hnliches Setup mit Google Sheets repliziert werden. Als SaaS -Angebot erfordert der Zugriff auf Google Sheets -Daten die Verwendung seiner API, die verschiedene Programmiersprachen unterstützt, darunter Java, JavaScript, Node.js, PHP, Ruby, Python und Googles eigenes AppScript. Dieser Artikel konzentriert sich auf die Verwendung von Python, um Google Sheets -Daten effizient in PANDAS -Datenrahmen zu laden und schnelle Datentransformationen und -analysen zu aktivieren. Sobald die ?nderungen abgeschlossen sind, kann die GSPREED Python Library verwendet werden, um die aktualisierten Daten zurück in Google Sheets zu bringen, was eine bequeme Schnittstelle für die Interaktion mit der Google Sheets -API bietet.

Dieser Artikel wurde als Teil des Data Science -Blogathons ver?ffentlicht.

Inhaltsverzeichnis

• Einrichten Ihres Google Cloud-Projekts
• GSPREED VERSTEHEN
• Herstellung von Verbindungen und ?ffnungsfreiheit
• Google Sheets erstellen und verwalten
• Arbeitsbl?tter verwalten
• Zelleigenschaften
• Hinzufügen neuer Zeilen und Spalten
• Zellen und Bereiche abrufen Werte
• Zellen und Bereiche aktualisieren
• Zeilen und Spalten l?schen
• Zellen suchen
• Formatierende Zellen
• Klarer Bereich von Zellen und Arbeitsblatt
• Einschr?nkungen der Google -API
• H?ufig gestellte Fragen

Einrichten Ihres Google Cloud-Projekts

Wie bereits erw?hnt, ist Google Sheets ein SaaS -Angebot, sodass Sie zus?tzliche Schritte für die Automatisierung vorbereiten müssen. Die Google Cloud -Plattform (GCP), eine beliebte Cloud -Computing -Plattform, bietet eine Vielzahl von Diensten, die dazu beitragen, mit Google -Produkten zusammenzuarbeiten, zusammen mit der Bereitstellung Ihrer benutzerdefinierten Projekte.

Im Gro?en und Ganzen müssen wir diese drei Schritte befolgen, um mit der Google Sheets Automation zu beginnen.

Erstellen und Konfigurieren eines Google Cloud -Projekts

Gehen Sie zu https://console.cloud.google.com/ und melden Sie sich für ein kostenloses Konto an. Klicken Sie als n?chstes von oben links auf das Menü Projektauswahl und w?hlen Sie ein neues Projekt aus. Geben Sie einen Projektnamen an und lassen Sie die Organisation als ?keine Organisation“, klicken Sie auf Erstellen und Ihr GCP -Projekt ist jetzt festgelegt.

API -Anmeldeinformationen generieren und sicherstellen

Wir müssen die Google Sheets und die Google Drive -API aktivieren und ein Servicekonto erstellen. Mit diesem speziellen Kontotyp k?nnen wir auf Google Cloud -Ressourcen zugreifen und diese verwalten, ohne die menschliche Interaktion zu erfordern. Suchen Sie in der obersten Suchleiste, um die Google Sheets -API zu aktivieren, und w?hlen Sie "Google Sheets -API" aus. Klicken Sie auf Zulassung und es wird uns auf die API -Detail -Seite weitergeleitet. Hier klicken Sie auf "Anmeldeinformationen erstellen" und es wird das Erstellungsformular für Anmeldeinformationen ge?ffnet.

W?hlen Sie im Feld Auswahl ?Anwendungsdaten“ aus und klicken Sie auf Weiter. Geben Sie auf dem n?chsten Bildschirm einen aussagekr?ftigen Namen für das Servicekonto an, da Sie es in sp?teren Schritten verwenden. W?hlen Sie dann die Rolle "Editor" aus. Klicken Sie schlie?lich unten auf Fertig.

In ?hnlicher Weise kann die Google Drive -API aktiviert werden. Bitte beachten Sie, dass wir kein anderes Servicekonto für diese API erstellen müssen. Das vorhandene Servicekonto kann auf beide APIs zugreifen. Jetzt müssen wir die Anmeldeinformationen JSON herunterladen, die unser Python -Skript konsumiert, um auf Google Sheets zuzugreifen. Klicken Sie auf die neu generierte E -Mail -E -Mail, wechseln Sie zur Registerkarte Tasten, klicken Sie auf die Schaltfl?che Taste hinzufügen, um die Option Neue Schlüssel zu erstellen, JSON auszuw?hlen und dann zu erstellen.

Zugriff auf Google Sheets gew?hren

Unser Python -Skript verwendet die generierten Anmeldeinformationen, um auf Google Sheets zuzugreifen. Wir müssen jedoch den Zugriff auf die Dateien, die unser Skript verwendet, manuell erlassen. Kopieren Sie dazu die für das Servicekonto generierte E -Mail (gefunden in der Registerkarte Details des Dienstkontos) und fügen Sie diese E -Mail als Editor zu den gewünschten Dateien hinzu.

GSPREED VERSTEHEN

GSPREAD ist eine Python -API -Wrapper für Google Sheets. Es enth?lt viele Funktionen, die die Google Sheetsapi unter separaten Klassen und Zugriffsmethoden angeboten haben. Es macht die Interaktion mit Bl?ttern leicht zu navigieren und man kann es schnell aufnehmen.

Um die Bibliothek in der lokalen Umgebung einzurichten, kann man wie bei jedem anderen Python -Paket einen einfachen PIP -Befehl verwenden. Installieren Sie die Bibliothek in einer separaten Umgebung als bew?hrte Verfahren, um Abh?ngigkeitskonflikte zu vermeiden.

 PIP Installieren Sie GSPREET

Ein kurzer Hinweis zu Zellreferenzen

Die Zellenadressen in Google -Bl?ttern k?nnen mit zwei beliebten Notationen verwiesen werden:

• A1 -Notation: Diese Zellreferenz besteht aus dem Blattnamen, der Zeilennummer und dem Spaltenbuchstaben. Diese Referenz funktioniert ohne Erw?hnung des Blattnamens und erm?glicht es Ihnen, sich auf eine einzelne Zelle, einen Zellbereich oder eine gesamte Spalte zu beziehen.
• Benannte Bereich: Dies ist ein definierter Zellenbereich mit einem benutzerdefinierten Namen für eine einfache Identifizierung und vereinfachte Referenz über das Google -Blatt.

Herstellung von Verbindungen und ?ffnungsfreiheit

Nachdem wir die erforderlichen Zugriff und Bibliotheken eingerichtet haben, testen wir unseren Code. Geben Sie in der Tabelle mit der E -Mail von Service -Konto einen zuf?lligen Text in die erste Zelle ein. Wir werden versuchen, diesen Wert mit unserem Python -Skript abzurufen.

Wir werden das JSON -Modul verwenden, um unsere Anmeldeinformationen zu laden und es an die Funktion "service_account_from_dict ()" des GSPREAD zu übergeben. Dadurch wird ein Google Sheets -Kundenobjekt zurückgegeben, und dieses Objekt kann verwendet werden, um jedes Google -Blatt mit der Funktion ?Open ()“ zu ?ffnen. Siehe den Code unten.

 GSPREET importieren
JSON importieren

mit Open ('Creds.json') als F:
    Anmeldeinformationen = json.load (f)

gc = gspede.service_account_from_dict (Anmeldeinformationen)

sh = gc.open ("Articledemo")

Es gibt zwei alternative M?glichkeiten, ein Google -Blatt anstelle eines Titelnamens zu ?ffnen. Diese alternativen M?glichkeiten beseitigen die Abh?ngigkeit von dem Titelnamen Wie in Google Workspace k?nnen mehrere Dateien den gleichen Titel haben. Bei Tabellenkalkulationen wird bei zwei Dateien mit dem gleichen Titel auf die neueste Datei von der API zugegriffen. Wir k?nnen mit der URL der Datei oder der eindeutigen ID des Tabellenkalkul?rs auf die Tabellenkalkulationen zugreifen, die den folgenden Link fortsetzt: "https://docs.google.com/spreadsheets/d//edit". Im Folgenden finden Sie den Code, der über URL oder eindeutige ID auf die Tabelle zugreifen kann.

 ## Zugriff über eindeutige ID
sh = gc.open_by_key ("1r97twcm0fffnssrh_0fjddg-hcqf5plHbhrxu9ptv_q")


## Zugriff über URL
sh = gc.open_by_url ("https://docs.google.com/spreadsheets/d/1r97twcm0fffnssrh_0fjddg-hcqf5plhbhrxu9ptv_q/edit?gid=0#gid=0")))

Der folgende Code liest den zuvor im Blatt eingegebenen Wert. Der Code, der Arbeiten in den sp?teren Abschnitten des Artikels erl?utert.

 print (sh.sheet1.acell ('a1'). Wert)

Dadurch wird der in der A1 -Zelle im Blatt vorhandene Wert zurückgegeben, in dem in unserem Fall "GSheet: Dies ist die erste Zelle". Jetzt sind wir alle auf die GSPREAD -Bibliothek eingestellt und werden alle verfügbaren Optionen erkunden.

HINWEIS: Die Shvariable enth?lt das Tabellenkalkulationsobjekt und wird im gesamten Leitfaden bezeichnet

Google Sheets erstellen und verwalten

Es k?nnte viele Anwendungsf?lle geben, in denen programmatisch eine Tabelle erstellt wird. Man k?nnte eine L?sung erstellen, um Daten und Erkenntnisse für ihre Benutzer zu ver?ffentlichen. Dabei m?chten sie dieses Blatt m?glicherweise direkt mit dem Benutzer weitergeben.

• Um eine neue Tabelle zu erstellen, verwenden Sie die Funktion create () des GSPREAD -Clients. Geben Sie den Titel der neuen Tabelle als Parameter übergeben. Wenn Sie den Speicherort angeben m?chten, verwenden Sie den Parameter order_id.
• Die neue Tabelle erstellt ist nur vom Benutzer des Dienstkontos zug?nglich. Dies bedeutet, dass die Tabelle nicht einmal für den Benutzer, der das Servicekonto erstellt hat, sichtbar ist. Zu diesem Zweck k?nnen wir die Funktion ?Share ()“ des Tabellenkalkulationsobjekts verwenden. Diese Funktion erfordert 3 obligatorische Parameter: ?E -Mail_address“ (E -Mail -Adresse), ?Perm_type“ (Berechtigungsart) und ?Rolle“. Der Berechtigungsart kann die folgenden Werte annehmen: Benutzer, Gruppen, Dom?ne oder irgendjemanden. Für die meisten Anwendungsf?lle funktioniert der Benutzerwert. Der Perm_Type hat auch eine feste Anzahl akzeptabler Werte: "Leser", "Kommentator", "Schriftsteller", "Fileorganizer", "Organizer" und "Eigentümer". Es gibt auch einige zus?tzliche optionale Parameter, die ein detailliertes Informationen bieten.
  • "Benachrichtigung": Booleaner Wert, um zu steuern, ob der Benutzer eine Benachrichtigung über die gemeinsame Datei erhalten sollte.
  • "E -Mail_Message": String -Wert, damit die Nachricht zusammen mit der Benachrichtigungs -E -Mail gesendet wird.

 sh = gc.create ('articledemotest') ## Erstellen einer neuen Tabelle erstellen
sh.share (E -Mail_Address = '[E -Mail geschützt]', perm_type = 'user', rollen = 'writer', notify = true, E -Mail_Message = "Dies ist eine Testdatei")

Arbeitsbl?tter verwalten

Jede Tabelle ist eine Sammlung von Arbeitsbl?ttern. Eine einfache Analogie dazu ist, wie ein Buch mehrere Seiten hat. Mit GSPREAD k?nnen Benutzer zugreifen, zugreifen, l?schen oder neue Arbeitsbl?tter erstellen. Schauen wir uns jede dieser Funktionen an.

Arbeitsblattauswahl

Auf das Arbeitsblatt eines Tabellenkalkulationsobjekts kann mit den folgenden Methoden des Arbeitsblattobjekts zugegriffen werden:

• Zugriff nach Index: Die Funktion ?get_worksheet ()“ erfolgt im Index des Arbeitsblatts, auf das zugegriffen werden muss.
• Zugriff nach Titel: Die Funktion ?Worksheet ()“ nimmt den Titel des Arbeitsblatts auf. Beachten Sie, dass die Arbeitsblatt -Titel von Natur aus einzigartig sind und daher keine zwei Arbeitsbl?tter denselben Titel haben k?nnen.
• Zugriff nach DOT -Notationsverknüpfung: Die DOT -Notationsverknüpfung erm?glicht den Zugriff auf das erste Arbeitsblatt der Tabelle, ohne einen Titel, einen Index oder eine ID herauszugeben.
• Greifen Sie auf alle zu: Die Funktion ?Arbeitsbl?tter ()“ gibt alle Arbeitsbl?tter der Tabelle zurück. Es gibt sie als GSPREAD -Arbeitsblattobjekte zurück. Der ?Titel“ und ?ID“ sind einige wichtige Eigenschaften dieser Klasse, die beim Zugriff auf die gewünschten Arbeitsbl?tter auf massenhafte Weise helfen.
• Zugriff nach ID: W?hrend der Entwicklung von Automatisierungsskripten gibt es m?glicherweise einige Anwendungsf?lle, in denen wir mit Arbeitsblatt -ID anstelle von Titeln zu tun haben. In solchen Szenarien kann die Funktion ?get_worksheet_by_id ()“ verwendet werden.

Hier ist der Beispielcode für alle aufgelisteten Methoden.

 print (sh.get_worksheet (0))
print (sh.worksheet ("articleworksheet1"))
print (sh.sheet1)
print (sh.get_worksheet_by_id (0))

print ("Jetzt alle Bl?tter holen ...")

## Alle Arbeitsbl?tter zurückgeben
für WS in sh.worksheets ():
    Druck (WS)

Alle diese Druckanweisungen geben das Arbeitsblattobjekt zurück

Erstellen eines neuen Arbeitsblatts

Abgesehen von vorhandenen Arbeitsbl?ttern in der Tabelle k?nnen wir programmgesteuert neue Arbeitsbl?tter in derselben Tabelle erstellen. Dieser Ansatz kann nützlich sein, wenn Daten aus einem vorhandenen Arbeitsblatt verarbeitet und die Ergebnisse in einem separaten Arbeitsblatt ver?ffentlicht werden.

Um ein neues Arbeitsblatt zu erstellen, müssen wir die Funktion ?add_worksheet ()“ des Arbeitsblattobjekts verwenden. Es dauert die folgenden Parameter.

• Titel: Der Titel des Arbeitsblatts
• Zeilen, Spalten, Index (optional): Wir müssen die Anzahl der Zeilen und Spalten für das neu erstellte Arbeitsblatt definieren. Der Parameter ?Index“ ist optional und steuert die Bestellung des Arbeitsblatts.

Der folgende Code erstellt ein Arbeitsblatt mit 100 Zeilen und 20 Spalten und platziert das Arbeitsblatt in der zweiten Position.

 sh.add_worksheet ('articleworksheet1.5', rows = 100, cols = 20, index = 1)

Und es hat es in die zweite Position gebracht (Index 1)

Ein Arbeitsblatt umbenennen

Sie k?nnen Arbeitsblatt -Titel mithilfe der Funktion update_title () des Arbeitsblattobjekts umbenennen, das den neuen Titel als Parameter akzeptiert.

 print (sh.worksheet ("articleworksheet3"). update_title ("articleworksheet2.5"))

L?schen eines Arbeitsblatts

Ein Arbeitsblatt kann mit der folgenden Funktion des Arbeitsblattobjekts aus einer Tabelle gel?scht werden:

• L?schen Sie ein Arbeitsblatt mit dem Arbeitsblattobjekt: "Del_worksheet ()" -Funktion nimmt das Arbeitsblattobjekt als Parameter und l?scht das Arbeitsblatt aus der Tabelle.
• L?schen Sie ein Arbeitsblatt mit der Arbeitsblatt -ID: "Del_Worksheet_By_ID ()" "Die Funktion" Arbeitsblatt -ID "zum L?schen des Arbeitsblatts.

Die Auswahl der zu verwendenden Funktion h?ngt vom Anwendungsfall ab, das das Skript erstellt wird. Im Folgenden finden Sie die Code -Probe, die die Verwendung beider Funktionen zeigt.

 sh.del_worksheet (sh.worksheet ("articleworksheet2.5"))
sh.del_worksheet_by_id ('602396579')

Zelleigenschaften

Wir verengen uns langsam von oben nach unten und erreichen die kleinste (und wichtigste) Einheit unseres Arbeitsblatts, eine Zelle. Eine Zelle ist ein Schnittpunkt einer Reihe und einer Spalte. Für die GSPREAD -Bibliothek enth?lt sie die folgenden Eigenschaften:

• Zeile: Zeilennummer für die Zelle
• Col: Spaltennummer für die Zelle
• Wert: Der Wert der Zelle
• Adresse: Die Adresse der Zelle in der A1 -Notation

Der folgende Beispielcode greift auf alle Eigenschaften einer Zelle zu. Die bestimmte Zelle zur Inspektion wird mit der Zellfunktion des Arbeitsblatts zurückgegeben.

 SampleCell = sh.worksheet ("artikelworksheet1"). Cell (row = 1, col = 1)
print ('row: {} \ ncolumn: {} \ nValue: {} \ naddress: {}'. format (samplecell.row, samplecell.col, samplecell.value, samplecell.address)))

Alle diese Zubeh?r werden ins Spiel kommen, sobald wir mit Funktionen h?herer Ordnung der Bibliothek zu tun haben.

Hinzufügen neuer Zeilen und Spalten

Fügen wir unserem vorhandenen Beispiel -Arbeitsblatt neue Zeilen und Spalten hinzu, um einige Daten für die sp?teren Abschnitte dieses Handbuchs zu bearbeiten. Die Einführung als Operation wird von der GSPREAD -Bibliothek auf zwei Arten unterstützt.

Einfügung an einer bestimmten Position

Sie k?nnen eine Zeile oder Spalte an einer bestimmten Position unter Verwendung der Funktionen des Arbeitsblatt -Objekts in Einsertrag (), Insert_rows () und Insert_cols () einfügen. Mit diesen Funktionen k?nnen wir die Zeilen oder Spalten an einem bestimmten Ort in einem Arbeitsblatt hinzufügen. Die Funktionspezifikationen finden Sie unten:

• Insert_row: Die Funktion ben?tigt den Parameter ?Werte“ als eine Liste von Werten zum Einfügen. Die Reihenfolge der Werte in der Liste bestimmt die Reihenfolge der eingefügten Zeilen. Der Parameter ?Index“, der standardm??ig 1 ist, gibt die Position für die Zeileninsertion an. Optionale Parameter wie "value_input_option" und "inherit_from_before" steuern, wie die Funktion die Eingabedaten und Zeilen interpretiert und ob sie die Daten direkt drücken oder analysieren sollten, als würde der Benutzer die Benutzeroberfl?che eingeben.
• Insert_rows: Die Liste der Listen im Parameter ?Werte“ zum Einfügen mehrerer Zeilen. Jede Liste fungiert als einzelne Zeile. Intern ist dies die tats?chliche Implementierung, wie die Zeilen über GSPread in das Arbeitsblatt eingefügt werden. Die Funktion "Insert_row ()" Auf die Funktion "Insert_rows ()" wird alle für die Funktion "Insert_row ()" beschriebene Parameter für "Insert_rows ()" mit Ausnahme eines Parameters richtig gehalten. In der Funktion Insert_row () bestimmen Sie den Offset mit dem Indexparameter, w?hrend Sie in der Funktion Insert_rows () ihn mit dem Zeilenparameter angeben.
• Insert_cols: Diese Funktion ist eine Replik der Funktion "Insert_rows ()" mit einem ge?nderten Parameternamen für den Offset von "Zeile" zu "Col". Der Rest der optionalen Parameterfunktion bleibt gleich.

Einfügung nach einem Tischbereich

Diese Einfügung gilt nur für Zeilen. Es erm?glicht es uns, Zeilen nach einem bestimmten Tabellenbereich einzufügen, in dem die Position unbekannt ist. Auch hier kann die Einfügung auf einzelne oder mehrstufige Weise erfolgen.

• append_row: Es wird die Zeilenwerte als Liste über den Parameter ?Werte“ angelegt. Der Parameter ?table_range“ hilft dabei, den Tabellenbereich zu definieren, nach dem die Zeileninsertion auftreten sollte. Der Bereich ist in der A1 -Notation angegeben.
• append_rows: Ebenso ist die "Insert_rows ()", die "append_rows ()" die tats?chliche Implementierung der Zeileneinfügungen nach einem Tabellenbereich. Alle Parameter für beide Funktionen bleiben gleich mit dem Unterschied, dass ?append_rows ()“ eine Liste von Listen im Parameter ?Werte“ einnimmt.

Hier ist der Beispielcode, der:

• Fügt eine Zeile für Spalten hinzu: a, b, c und d
• Fügt 4 Zeilen unter diesen Spalten hinzu

 Beispielworksheet.insert_row (
    ['A', 'B', 'C', 'D']
)
Beispielworksheet.insert_rows (
    [
        ['Kg', 54, 23, 12],
        ['Og', 34, 12, 34],
        ['Me', 23, 45, 90],
        ['Ye', 65, 12, 54]
    ], row = 2
)

Lassen Sie uns nun die folgenden Schritte darüber hinaus ausführen:

• Gehen Sie 2 Zeilen an, um in diesem Tabellenbereich fortzufahren
• Fügen Sie eine andere Spalte E hinzu e

 Beispielworksheet.Append_rows (
    [
        ['Sn', 67, 87, 45],
        ['Ar', 56, 23, 65]
    ],
    table_range = "a1: d5"
)
Beispielworksheet.insert_cols (
    [
        ['E', 56, 34, 65, 34, 76, 45]
    ],
    col = 5
)

HINWEIS: Die Variable mit Beispielworksheet enth?lt das Arbeitsblattobjekt und wird w?hrend des gesamten Handbuchs bezeichnet.

Zellen und Bereiche abrufen Werte

In den letzten Abschnitten haben wir unsere Daten programmgesteuert anhand verschiedener Einfügungsvorg?nge vorbereitet. Jetzt k?nnen wir die Daten mit verschiedenen Lesefunktionen abrufen. Wir werden sehen, wie man Zellen abholt und dann Werte aus einer Reihe von Zellen und dem gesamten Arbeitsblatt abholt.

Einzelzelle holen

Der grundlegendste Lesevorgang in einem Arbeitsblatt besteht darin, den Wert oder eine andere Zelleigenschaft wie in den vorherigen Abschnitten beschrieben zu erhalten. Um eine einzelne Zelle zu holen, gibt es zwei Funktionen:

• ACELL: Dies nimmt die Zelladresse in der A1 -Notation an und gibt ein Zellobjekt zurück.
• Zelle: Dies nimmt die Zellkoordinaten in der Reihenfolge von (Zeile, Spalte) auf.

Beide Funktionen geben ein Zellobjekt zurück und wir haben bereits gesehen, wie wir den Wert von diesen Objekten erhalten. Die Acell -Funktion wurde in dem Abschnitt verwendet, in dem wir eine Verbindung mit der Google Sheets -API hergestellt haben.

 print (Beispielworksheet.acell ('A1'). Zeile)
print (Beispielworksheet.Cell (1, 1) .Value)

Abrufen aller Zellen des Arbeitsblatts oder des Bereichs

• Wir k?nnen alle Zellen des Arbeitsblatts in einer Liste von Zellobjekten unter Verwendung der Funktion ?get_all_cells ()“ des Arbeitsblattobjekts erhalten. Für diese Funktion gibt es keinen Parameter und kann direkt auf ein Arbeitsblattobjekt aufgerufen werden.
• Verwenden Sie die Funktion "Range () des Arbeitsblattobjekts, um Zellobjekte für einen bestimmten Bereich abzurufen. Diese Funktion akzeptiert verschiedene Eingabeforms wie A1 -Notation, numerische Grenzen oder benannte Bereiche. Wenn es ohne Eingabe verwendet wird, gibt es alle Zellen im Arbeitsblatt in einem einzelnen API -Aufruf zurück. Für Anwendungsf?lle mit Zelleigenschaften hilft diese Funktion, die gewünschten Zellen herauszufiltern und weitere Aktionen auszuführen.

 print (Beispielworksheet.get_all_cells ())
print (Beispielworksheet.Range ('b4: e5'))

Abrufwerte des Zellbereichs abrufen

Benutzer erstellen normalerweise mehrere Miniaturtabellen im selben Arbeitsblatt, um eine bessere Zug?nglichkeit zu erhalten. In solchen F?llen müssen wir unseren Abrufbereich mit den genauen Adressen dieser Tabellenbereiche verfeinern. Um solche Tabellenbereiche abzurufen, k?nnen wir die folgenden zwei Funktionen des Arbeitsblattobjekts verwenden:

• GET: Die Funktion ?Get ()“ übernimmt den Tabellenbereich in A1 -Notation oder einen benannten Bereich und gibt die Liste der Wertelisten zurück.
• batch_get: Die Funktion ?get ()“ kann nur einen Bereich erfolgen, aber wenn wir uns mit mehreren Bereichen befassen, k?nnen wir batch_get verwenden. Diese Funktion macht einen API -Anruf, der die Kosten spart.

 print ('Range: {}'. Format (Beispielworksheet.get ("A1: D4")))
print ('batch rave range: {}'. Format (Beispielworksheet.batch_get ([[
    "A1: D4",
    "B4: E3"
])))

Alle Werte aus einer Zeile oder Spalte abrufen

Wir k?nnen alle Werte einer Zeile oder Spalte mit den Funktionen "row_values ??()" und "col_values ??()" des Arbeitsblattobjekts abrufen. Beide Funktionen nehmen die Position (Nummerierung von 1) einer Zeile oder Spalte ein und geben die Werte in einer Liste zurück.

 print (Beispielworksheet.ROW_VALUES (1))
print (Beispielworksheet.col_values ??(4))

Gesamte Arbeitsblattwerte abrufen

Einer der besten Anwendungsf?lle beim Abrufen eines gesamten Arbeitsblatts w?re, diese Daten direkt in einen Pandas-Datenrahmen zu laden und dann die Nachbearbeitung oder Analyse gem?? den Anforderungen durchzuführen. Die gesamten Daten k?nnen mit den folgenden Funktionen des Arbeitsblattobjekts zurückgegeben werden:

• Liste der Listen: Die Funktion ?get_all_values ??()“ gibt jede Zeile als Liste zurück und dann alle Zeilenlisten in einer Liste. Diese Funktion ist ein Alias ??der Funktion "get_values ??()", aber interessanterweise wird die Funktion "get_values ??()" mit der Funktion "get ()" implementiert. Die Funktion ?get ()“ ohne Eingaben gibt die Liste der Listen zurück. Daher sind alle 3 Funktionen gleich.
• Liste der W?rterbücher: Die Funktion ?get_all_records ()“ gibt eine Liste von W?rterbüchern zurück. Jedes W?rterbuch ist eine Schlüsselwert-Zuordnung, bei der die Tasten die Werte der ersten Reihe und die Werte als Next-Row-Werte sind. Jede Reihe bekommt ihr W?rterbuch. Standardm??ig wird davon ausgegangen, dass die erste Zeile der Schlüssel ist, aber wir k?nnen sie auf eine andere Zeile als Schlüssel mit dem Parameter ?Header“ aufmerksam machen. Es gibt auch einige zus?tzliche Parameter, die beim Umgang mit leeren Zellen und den erwarteten Headern helfen k?nnen.

Sie k?nnen die Ausg?nge beider Funktionen direkt an die PANDAS -Datenfunktion übergeben, um die Arbeitsblatttabelle als Pandas -Datenframe zu erhalten.

 Pandas als PD importieren
print (pd.dataframe (Beispielworksheet.get_all_records ())))
print (pd.dataframe (Beispielworksheet.get_all_values ??())))

Zellen und Bereiche aktualisieren

Die Aktualisierung der vorhandenen Daten der Tabelle ist die wichtigste Funktion, die mithilfe von GSPREAD -Bibliotheksfunktionen problemlos ausgeführt werden kann. Es gibt mehrere M?glichkeiten, die Zellen einer Tabelle zu aktualisieren, einzelne Zellen -Aktualisierungen auf mehrere Zellen eines Bereichs und dann auf mehrere Bereiche mit einem einzelnen API -Aufruf.

Aktualisieren einer einzigen Zelle

Eine einzelne Zelle eines Arbeitsblatts kann mit den folgenden Funktionen des Arbeitsblattobjekts aktualisiert werden.

• update_acell: Diese Funktion nimmt zwei Parameter vor, die Zelladresse in der A1 -Notation und der zu aktualisierte Wert
• UPDATE_CELL: Diese Funktion nimmt die Koordinaten der Zelle in der Reihens?ule-Reihenfolge und den Wert für die Aktualisierung ein
• UPDATE: Obwohl diese Funktion einen gr??eren Umfang für die Aktualisierung mehrerer Zellen hat, kann sie auch zur Aktualisierung einer einzelnen Zelle verwendet werden. Die Reihenfolge der Eingabeparameter unterscheidet sich von den oben genannten zwei Funktionen. Die Funktion ?update ()“ enth?lt eine Liste von Listen als erster Wert und dann die Zelladresse.

 print (Beispielworksheet.Update_acell ('A2', 'Kaustubh'))
print (Beispielworksheet.Update_acell ('A3', 'Oggy'))
print (Beispielworksheet.Update ([['Hallo'], 'A4'))

Aktualisieren einer Reihe von Zellen

Sie k?nnen eine Reihe von Zellen in einem Arbeitsblatt mit den folgenden zwei Funktionen aus dem Arbeitsblattobjekt aktualisieren.

• UPDATE_CELLS: Diese Funktion funktioniert am besten in Kombination mit der Funktion ?range ()“. Die Funktion ?update_cells ()“ nimmt Eingabe als Zellliste an. Diese Liste von Zellen kann ihre Werte ver?ndern lassen, indem sie über die Zellobjekte aus der Bereichsfunktion zurückgegeben und auf ihre Werteigenschaft zugreifen.
• Update: Wie bereits in Einzelzell-Updates angezeigt, k?nnen Sie diese Funktion verwenden, um einen benannten Bereich oder einen A1-Notated-Bereich zu aktualisieren.

 Rangeofcells = Sampleworksheet.Range ('B2: B7')
Für Zellen in Rangeofcells:
    newValue = int (cell.Value) 10
    cell.Value = newValue
print (Beispielworksheet.Update_cells (rangeofcells)))

Der obige Code holt einen Bereich von Zellen ab, fügt 10 zu ihrem Wert hinzu und aktualisiert sie in einem einzelnen API -Aufruf.

Aktualisieren mehrerer Zellenbereiche

Im obigen Abschnitt konnten wir mehrere Zellen in einem Bereich mit einem einzelnen API -Aufruf aktualisieren. Dieses Verhalten kann auch auf mehrere Bereiche ausgedehnt werden. Dies bedeutet, dass wir mehrere Zellengruppen mit einem Anruf aktualisieren k?nnen. Die Funktion ?batch_update ()“ enth?lt eine Liste von W?rterbüchern mit Schlüssel als Bereich und Werten. Der Bereichsschlüsselwert sollte der A1 -Notationsbereich oder ein benannter Bereich und der Werteschlüsselwert als Werteliste sein.

 Bereich1 = 'C2: C7'
Bereich2 = 'E2: e7'
BothRangeValues ??= Sampleworksheet.batch_get ([[
    Bereich1,
    Bereich2
]))
Range1Values, Range2Values ??= BotRangeValues
Bereich1UpdatedValues ??= [[int (x [0]) 10] für x in Bereich1Values]
Range2UpdatedValues ??= [[int (x [0]) 20] für x in Bereich2Values]
print (Beispielworksheet.batch_update ([[
    {
        "Bereich": Bereich1,
        "Werte": Bereich1UpdatedValues
    },

    {
        'Range': Bereich2,
        'Werte': Bereich2UpdatedValues
    }
])))

Der obige Code holt zwei Bereiche mit der Funktion "batch_get ()" ab, aktualisiert dann ihre Werte lokal und verwendet dann die Funktion "batch_update ()", um die aktualisierten Werte auf die Google -Bl?tter zurückzulegen. Die Ausgabe dieses Updates sieht so aus:

Zeilen und Spalten l?schen

Bis zu diesem Zeitpunkt haben wir die Daten im Arbeitsblatt eingefügt, gelesen und aktualisiert. Wir k?nnen L?schvorg?nge ausführen, um redundante oder unn?tige Daten aus dem Arbeitsblatt zu entfernen. Die Funktion "Delete_rows ()" und "Delete_colums ()" nimmt die zu l?schende "start_index". Wenn ?end_index“ angegeben ist, l?scht es alle Spalten im Indexbereich von Start und End.

 print (Beispielworksheet.Delete_Columns (4))
print (Beispielworksheet.Delete_rows (6))

Zellen suchen

Mit der Google Sheets -API k?nnen Sie nach Zellen suchen, indem Sie eine Zeichenfolge oder einen regul?ren Ausdruck übereinstimmen. Sie k?nnen Fallsensitive oder Fall-unempfindliche Suchvorg?nge ausführen und die Suche auf bestimmte Zeilen oder Spalten einschr?nken, falls gewünscht. Verwenden Sie diese beiden Arbeitsblattfunktionen, um passende Zellen zu finden:

• Find: Die Funktion ?Find ()“ gibt das erste Ereignis des Spiels zurück. Dadurch werden die Parameter von Suchzeichenfolge oder Regex, "IN_ROW" oder "IN_COLUMN" angezeigt, um die Suche und das Flag "Case_sensitive" einzugrenzen, um den Suchtyp zu steuern. Die Parameter ?In“ nehmen die Zeilen- oder Spaltenpositionen ein (Index 1)
• Findall: Das "findAll ()" ist die fortgeschrittene Stufe der Funktion ?find ()“, in der sie alle übereinstimmungen der Suche zurückgibt.

 Import Re
print (Beispielworksheet.Find ('64 ', in_column = 2))
searchre = re.comPile (r '(a | a)')
print (Beispielworksheet.Findall (Searchre))

Formatierende Zellen

In Excel k?nnen Sie Arbeitsbl?tter auf verschiedene Weise formatieren, einschlie?lich Textherziger, Formatierung, Grenzen, Ausrichtung und zahlreichen Funktionen. Die Google Sheets bieten auch eine Vielzahl von Formatierungsoptionen für Zellen. Die gesamte Liste der Felder ist in der Dokumentation von Google Sheets -Zellen verfügbar.

Sie k?nnen die Funktion format () des GSPREAD -Arbeitsblattobjekts verwenden, um die Zelle oder den Bereich anzugeben, in dem Sie die Formatierung anwenden m?chten. Stellen Sie das Format als JSON -W?rterbuch vor, das alle Formatierungsschlüsselfelder und deren Werte enth?lt.

Der folgende Code wendet Grenzen für alle Zellen der Tabelle an.

 BorderFormatting = {
    "Stil": "Solid",
    "Colorstyle": {"rgbcolor": {"rot": 0, "grün": 0, "blau": 0, "alpha": 1}},
}

drucken(
    Beispielworksheet.Format (
        "A1: D6",
        format = {
            "Grenzen": {{
                "Top": BorderFormating,
                "unten": BorderFormating,
                "links": BorderFormating,
                "rechts": BorderFormating,
            },
        },
    )
)

Wir k?nnen auch Stapelformatierung anwenden, um mehrere Bereiche gleichzeitig zu formatieren. Dies spart viel Zeit mit dem Schreiben verschiedener Formatvorforderungen für jede neue ?nderung. Die Funktion ?batch_format ()“ enth?lt die Liste der W?rterbücher mit zwei wichtigen Schlüssel. Der erste Schlüssel ist der Bereichsschlüssel, der den Bereich der Zelle und den Formatschlüssel definiert, der das Formatierungsw?rterbuch enth?lt.

Lassen Sie uns in unserer Beispieltabelle Folgendes mit der Funktion "batch_format ()" ausführen:

• Wenden Sie Grenzen auf alle Zellen der Tabelle an.
• Fett den Text der ersten Zeile, was darauf hinweist, dass dies die Spalten unserer Tabelle sind.
• Richten Sie alle Daten in der Mitte aus.
• Fügen Sie eine hellblaue Farbe für die Spalten (die erste Zeile) hinzu.

 BorderFormatting = {
    "Stil": "Solid",
    "Colorstyle": {"rgbcolor": {"rot": 0, "grün": 0, "blau": 0, "alpha": 1}},
}
Formate = [
    {
        "Bereich": "A1: D6",
        "Format": {{
            "Grenzen": {{
                "Top": BorderFormating,
                "unten": BorderFormating,
                "links": BorderFormating,
                "rechts": BorderFormating,
            },
            "Horizontalalignment": "Center",
        },
    },
    {
        "Bereich": "A1: D1",
        "Format": {{
            "textFormat": {
                "mutig": wahr,
            },
            "HintergrundColorstyle": {
                "rgbcolor": {"rot": 0,8, "grün": 0,8, "blau": 1, "alpha": 0,8}
            },
        },
    },
]

print (Beispielworksheet.batch_Format (Formate))

Und dies ist der letzte Zustand unserer Tabelle.

Klarer Bereich von Zellen und Arbeitsblatt

Es kann m?glich sein, dass wir den Bereich l?schen m?chten, bevor wir die Operationen ausführen. Zum L?schen der Zellbereiche wird die Funktion ?batch_clear ()“ des Arbeitsblattobjekts verwendet. Dies enth?lt die Liste der Bereiche, die gel?scht werden müssen. Lassen Sie uns Spalte C aus unserer Stichprobentabelle l?schen.

 print (Beispielworksheet.batch_clear (["C1: C6"]))

HINWEIS: L?schen der Funktion l?scht nur die Werte und nicht die angelegte Formatierung.

Das gesamte Arbeitsblatt kann mit der Funktion ?Clear ()“ des Arbeitsblattobjekts gel?scht werden.

 print (Beispielworksheet.Clear ())

Schauen Sie in diesem Artikel über die Verwendung von Python zum Automatisieren von Google Sheets

Einschr?nkungen der Google -API

Mit der GSPREAD -Bibliothek haben wir in Google Sheets viele Vorg?nge ausgeführt. Diese Bibliothek ist nur ein Wrapper, der die von Benutzer gepassten Daten in das akzeptable Format vorbereitet und die API-Anrufe bei Google-Projekten, die den Bl?ttern zugeordnet sind, funktioniert. Es funktioniert, damit der Entwickler die zugrunde liegenden API-Anrufe, Nutzlasten und Antworten nicht verstehen muss. Der Entwickler interagiert nur mit den abstrahierten Funktionen.

• Verst?ndnis des API -Quotenverbrauchs : Entwickler müssen verstehen, wie API -Aufrufe Quoten konsumieren, insbesondere für Produktion und kritische Aufgaben.
• Kostenlose, aber eingeschr?nkte Nutzung : Die Google Sheets -API ist kostenlos, enth?lt jedoch Einschr?nkungen für die Anzahl der API -Anrufe, die get?tigt werden k?nnen.
• H?ufiger Fehler - 429 (?zu viele Anfragen“) : Viele Benutzer begegnen dem 429 -Fehler aufgrund von überschreitenden API -Aufrufgrenzen.
• Beispiel für Quotenlimit : Das aktuelle Kontingent erm?glicht 300 Anforderungen pro Minute pro Projekt.
• überschreitung der Quote : Wenn ein Skript mehr als 300 Anfragen sendet, werden keine zus?tzlichen Anfragen bearbeitet.
• Exponentielle Backoff -L?sung : Ein Wiederholungsmechanismus wie Exponential Backoff, der zuf?llige Wartezeiten verwendet, kann dazu beitragen, diese Einschr?nkungen zu verwalten.

Abschluss

In diesem Leitfaden haben wir ein Google -Service -Konto erstellt, um alle Vorg?nge auszuführen, die man in der Google Sheets -Benutzeroberfl?che ausführen würde. Wir haben viele Funktionen wie das Hinzufügen, Aktualisieren und L?schen von Daten untersucht. Wir haben auch untersucht, wie man Bl?tter und Google Sheets Automation mit Python formatiert.

Die Google Sheets -API bietet viel mehr Funktionen wie das Zusammenführen von Zellen, das Erstellen von geschützten Bereichen, das Verstecken von Zellen, das Hinzufügen von Notizen, Kopienbereichen oder sogar Hinzufügen von Filtern, alle Operationen programmatisch! W?hrend der Dokumentation für die GSPREAD-Bibliothek diese Erkl?rungen fehlen, kann man die offizielle Dokumentation von Google Sheets untersuchen und den API-Referenzabschnitt der GSPREAD-Dokumentation überprüfen, die hochrangige Informationen zu allen in der Bibliothek implementierten Funktionen liefert.

Key Takeaways

• Die Automatisierung von Google Sheets erfordert das Einrichten eines Google Cloud -Projekts, das keine Kosten verursacht. Wenn Sie den Code jedoch nicht optimieren, k?nnen Sie die Quote ersch?pfen, was sp?ter im Code zu Fehlern führt. Ziel ist es, die Stapelfunktion nach M?glichkeit zu verwenden.
• Es gibt viele M?glichkeiten, was mit der Google Sheets -API erreicht werden kann, und die GSPREAD -Bibliothek ist nur ein Ausgangspunkt. Man sollte die offizielle Dokumentation untersuchen, um die in der Bibliothek fehlenden Funktionen zu implementieren (und m?glicherweise einen Beitrag zu tragen).
• Sie k?nnen Google Sheets Automation -Setups verwenden, um Sourcing -Pipelines zu erstellen, die einen Master -Datensatz beibehalten und in eine erweiterte Datenbank wie eine OLAP -Datenbank übertragen. Schlie?en Sie diese Datenbanken an BI-Software wie Tableau an, um ein End-to-End-Projekt abzuschlie?en.
• Google Sheets Automations k?nnen Ihnen helfen, die manuelle Dateneingabe zu beseitigen, indem Workflows eingerichtet werden, die Daten automatisch aktualisieren, importieren oder organisieren und die Effizienz erheblich verbessern.
• Mithilfe von Google Sheets Automations k?nnen Sie wiederkehrende Aufgaben planen, z. B. das Senden von Berichten, die Durchführung von Berechnungen oder die Aktualisierung von Zellen, wodurch die Notwendigkeit repetitiver manueller Aktionen verringert werden.

H?ufig gestellte Fragen

A. Es sollte ein Wiederholungsmechanismus implementiert werden, der versucht, die Anfragen in einiger Zeit erneut zu stellen. Ein solches Beispiel ist der exponentielle Backoff -Algorithmus

A. Ja, Sie k?nnen Aufgaben in Google Sheets mit integrierten Funktionen wie Makros , Google Apps-Skript und Drittanbieter-Tools wie Zapier oder Make (ehemals Integromat) automatisieren. Mit diesen k?nnen Sie sich wiederholende Aufgaben wie Dateneingabe, Formatierung und Berechnungen automatisieren.

A. Ja, Google bietet Google Apps Skript an, eine auf JavaScript-basierte Plattform zur Automatisierung von Aufgaben in Google Workspace Apps (z. B. Bl?tter, Dokumenten, Google Mail). Additionally, tools like Google Cloud Workflows and Zapier integrations can be used for broader automation across Google services.

A. You can set up Google Sheets automations using built-in tools like Macros and Google Apps Script. Macros allow you to record actions and replay them, while Google Apps Script lets you create custom automations using JavaScript. These automations can streamline repetitive tasks such as data entry, formatting, or running specific functions automatically in Google Sheets.

A. Popular Google Sheets automations for data analysis include automated data import from external sources, scheduled reports using Google Apps Script, and conditional formatting to highlight trends. These automations help optimize the data analysis process, making Google Sheets a powerful tool for managing and interpreting large datasets efficiently.

Das obige ist der detaillierte Inhalt vonGoogle Sheets Automation mit Python | Analytics Vidhya. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!