Google Sheets Automation mit Python | Analytics Vidhya

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

Q1. Was kann getan werden, um den Fehler „429: Zu viele Anfragen“ zu vermeiden?

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

Q2.Kannt, dass Sie Automatisierung in Google Sheets durchführen?

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.

Q3. Hat Google ein Automatisierungstool?

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.

Q4. How can I set up Google Sheets automations to save time on repetitive tasks?

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.

Q5. What are some popular Google Sheets automations for data analysis?

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!