2025-02-26

Google Sheets in DuckDB lesen und schreiben

Alex Monahan, Archie Wood

Tabellenkalkulationen sind überall

Gibt es für Data-Leute etwas Polarisierenderes als Tabellenkalkulationen? Warten Sie, antworten Sie nicht – wir haben keine Zeit, schon wieder über führende und nachgestellte Kommas zu sprechen…

Tatsache ist: Tabellenkalkulationen sind überall. Schätzungen zufolge gibt es über 750 Millionen Tabellenkalkulationsnutzer, gegenüber nur 20 bis 30 Millionen Programmierern. Das umfasst alle Sprachen zusammen!

Es gibt eine Reihe von Wegen, wie eine Tabellenkalkulation einen Data-Workflow verbessern kann. Blasphemie, sagen Sie! Nun, stellen Sie sich vor, Ihre Datenbank könnte diese Tabellenkalkulationen tatsächlich lesen und schreiben. Tabellenkalkulationen sind oft der beste Ort, um Daten manuell zu editieren, und sie bieten außerdem hochgradig anpassbares Pivoting für Self-Serve-Analytik.

Jetzt können Sie DuckDB nutzen, um die Lücke zwischen Data-Leuten und Business-Leuten nahtlos zu überbrücken! Mit einem einfachen In-Browser-Authentifizierungsflow oder einem automatisierbaren Private-Key-Datei-Flow können Sie sowohl aus Google Sheets abfragen als auch hineinladen.

Die GSheets-Extension wurde ursprünglich von Archie aus dem Team bei Evidence geschrieben, hat seitdem aber signifikante Beiträge von Alex und Michael bekommen.

Einstieg in die GSheets-Extension

Die ersten Schritte sind, die gsheets-Community-Extension zu installieren und sich bei Google zu authentifizieren.

INSTALL gsheets FROM community;
LOAD gsheets;

-- Authenticate with a Google Account in the browser (default)
CREATE SECRET (TYPE gsheet);

Als Teil des Befehls CREATE SECRET öffnet sich ein Browserfenster und erlaubt Login und das Kopieren eines temporären Tokens, das dann zurück in DuckDB eingefügt wird.

Beispiele für das Lesen aus Sheets

Jetzt, da Sie authentifiziert sind, kann DuckDB jedes Sheet abfragen, auf das Ihr Google-Konto Zugriff hat. Das umfasst alle öffentlich verfügbaren Sheets wie das unten, also führen Sie es ruhig aus!

FROM 'https://docs.google.com/spreadsheets/d/1B4RFuOnZ4ITZ-nR9givZ7vWVOTVddC3VTKuSqgifiyE/edit?gid=0#gid=0';

Kopieren Sie die URL des abzufragenden Sheets, wenn Sie das gewünschte Sheet innerhalb der Workbook ansehen. Der Query-String-Parameter gid ist die ID dieses konkreten Sheets.

Es gibt zwei Wege, zusätzliche Parameter zu übergeben:

1. Sie ans Ende der URL als Query-String-Parameter anhängen oder
2. die Tabellenfunktion read_gsheet nutzen und sie als getrennte SQL-Parameter angeben.

Das Repository-README hat eine Reihe von Beispielen, und einige stehen unten!

Query-String-Parameter müssen nach einem ? stehen. Jeder Parameter ist als key=value-Paar formatiert, und mehrere werden mit & getrennt.

Ein konkretes Sheet und einen Range lesen

Standardmäßig liest die GSheets-Extension alle Daten auf dem ersten Sheet in der Workbook. Die Parameter sheet und range (oder ihre Query-String-Äquivalente) erlauben gezielte Reads.

Um zum Beispiel nur die ersten 3 Zellen auf dem Sheet We <3 Ducks zu lesen, sind diese beiden Statements äquivalent:

-- The sheet with the gid of 0 is named 'We <3 Ducks' (because of course it is!)
FROM read_gsheet(
    'https://docs.google.com/spreadsheets/d/1B4RFuOnZ4ITZ-nR9givZ7vWVOTVddC3VTKuSqgifiyE/edit',
    sheet = 'We <3 Ducks',
    range = 'A1:A3'
    );

FROM 'https://docs.google.com/spreadsheets/d/1B4RFuOnZ4ITZ-nR9givZ7vWVOTVddC3VTKuSqgifiyE/edit?gid=0#gid=0&range=A1:A3';

Die Google-Sheets-API überspringt hilfreicherweise leere Zeilen am Ende eines Datensatzes oder leere Spalten rechts. Geben Sie ruhig einen etwas größeren range an, wenn Ihre Daten wachsen können! Außerdem kann der range als Satz von Spalten angegeben werden (z. B. D:X), um freundlicher zu einer variablen Zahl von Zeilen zu sein.

Datentypen

Die Extension sampelt die erste Datenzeile im Sheet, um die Datentypen der Spalten zu bestimmen. (Wir haben Pläne, dieses Sampling zu verbessern, und sind offen für Beiträge!) Um diesen Schritt zu überspringen und die Datentypen in SQL zu definieren, setzen Sie den Parameter all_varchar auf true. Das Beispiel unten zeigt außerdem, dass die volle URL nicht nötig ist – nur der Google-Workbook-Identifikator.

FROM read_gsheet(
    '1B4RFuOnZ4ITZ-nR9givZ7vWVOTVddC3VTKuSqgifiyE',
    sheet = 'We <3 Ducks',
    range = 'A:A',
    all_varchar = true
    );

Es ist auch möglich, Daten ohne Header-Zeile abzufragen, indem der Parameter header auf false gesetzt wird. Spalten bekommen Default-Namen und können in SQL umbenannt werden.

Beispiele für das Schreiben in ein GSheet

Eine weitere Schlüsselfähigkeit der GSheets-Extension ist, die Ergebnisse jeder DuckDB-Query in ein Google Sheet zu schreiben!

Standardmäßig wird das gesamte Sheet durch die Ausgabe der Query ersetzt (einschließlich einer Header-Zeile für Spaltennamen), beginnend in Zelle A1 des ersten Sheets. Unten stehen Beispiele, die dieses Verhalten anpassen!

-- Here you will need to specify your own Sheet to experiment with!
-- (We can't predict what folks would write to a public Sheet...
-- Probably just memes, but there is always that one person, you know?)
COPY (FROM range(10))
TO 'https://docs.google.com/spreadsheets/d/...'  (
    FORMAT gsheet
);

In ein konkretes Sheet und einen Range schreiben

Wie beim Lesen können sowohl Query-String-Parameter als auch SQL-Parameter genutzt werden, um in ein konkretes sheet oder einen range zu schreiben. Ebenso haben die SQL-Parameter Vorrang. Diese Beispiele sind äquivalent:

COPY (FROM range(10))
TO 'https://docs.google.com/spreadsheets/d/...?' (
    FORMAT gsheet,
    sheet 'The sheet name!',
    range 'A2:Z10000'
);

COPY (FROM range(10))
TO 'https://docs.google.com/spreadsheets/d/...?gid=123#gid=123&range=A2:Z10000' (
    FORMAT gsheet
);

Der boolesche Parameter header kann außerdem genutzt werden, um festzulegen, ob die Spaltennamen geschrieben werden sollen oder nicht.

Überschreiben oder Anhängen

Manchmal ist es hilfreich, andere Daten in einem Sheet vor dem Kopieren nicht zu löschen. Das ist besonders praktisch, wenn in konkrete Ranges geschrieben wird. Vielleicht können die Spalten C und D von DuckDB kommen und der Rest Tabellenkalkulationsformeln sein. Es wäre toll, einfach nur die Spalten C und D zu leeren!

Um dieses Verhalten anzupassen, übergeben Sie diese booleschen Parameter an die Funktion COPY. OVERWRITE_SHEET ist der Default, bei dem das gesamte Sheet vor dem Kopieren geleert wird. OVERWRITE_RANGE leert nur den angegebenen Range.

Sind beide auf false gesetzt, werden Daten angehängt, ohne dass andere Zellen geleert werden. Typischerweise ist es beim Anhängen nicht erwünscht, die Spaltenheader in der Ausgabe zu haben. Hilfreicherweise ist der Parameter header im Append-Fall standardmäßig false, er kann aber bei Bedarf angepasst werden.

-- To append, set both flags to false.
COPY (FROM range(10))
TO 'https://docs.google.com/spreadsheets/d/...?gid=123#gid=123&range=A2:Z10000' (
    FORMAT gsheet,
    OVERWRITE_SHEET false,
    OVERWRITE_RANGE false
    -- HEADER false is the default in this case!
);

Automatisierte Workflows

Mit Tabellenkalkulationen zu arbeiten, ist großartig für Ad-hoc-Arbeit, kann aber auch mächtig sein, wenn es in automatisierte Prozesse eingebettet ist. Wenn Sie eine Interaktion mit Google Sheets planen wollen, wird eine Schlüsseldatei mit einem privaten Schlüssel statt der In-Browser-Authentifizierungsmethode benötigt.

Nachdem Sie diese Schlüsseldatei haben, muss der persistente private Schlüssel alle 30 Minuten in ein temporäres Token umgewandelt werden. Dieser Prozess ist jetzt mit dem Secret-Provider key_file automatisiert. Legen Sie das Secret mit einem Befehl wie unten an und zeigen Sie auf die von Google exportierte JSON-Datei.

CREATE OR REPLACE PERSISTENT SECRET my_secret (
    TYPE gsheet,
    PROVIDER key_file,
    FILEPATH 'credentials.json'
);

Beim Anlegen des Secrets wird der private Schlüssel in DuckDB gespeichert und ein temporäres Token erzeugt. Das Secret kann im Speicher gehalten oder optional mit dem Keyword PERSISTENT auf Platte persistiert werden (unverschlüsselt). Das temporäre Token wird ebenfalls im SECRET gecacht und neu erzeugt, wenn es älter als 30 Minuten ist.

Das schaltet die Nutzung der GSheets-Extension in Pipelines frei, etwa GitHub Actions (GHA) oder anderen Orchestratoren wie dbt. Best Practice ist, die Datei credentials.json als Secret in Ihrem Orchestrator zu speichern und sie in eine temporäre Datei zu schreiben. Ein Beispiel-GHA-Workflow steht hier, der dieses Python-Skript nutzt, um ein Sheet abzufragen.

Die Extension weiterentwickeln

Die Google-Sheets-Extension ist ein gutes Beispiel dafür, wie DuckDBs Extension-GitHub-Template und CI/CD-Workflows auch Nicht-C++-Experten erlauben, zur Community beizutragen! Mehrere der Leute, die bisher beigetragen haben (danke!!), einschließlich der Autoren dieses Beitrags, sind keine klassischen C++-Programmierer. Die Kombination aus einem tollen Template, Beispielen aus anderen Extensions und ein bisschen Hilfe von ein paar LLM-getriebenen „Junior Devs“ hat es möglich gemacht. Wir ermutigen Sie, Ihrer Extension-Idee eine Chance zu geben und auf Discord nachzufragen, wenn Sie Hilfe brauchen!

Roadmap

Es gibt ein paar weitere spaßige Features, über die wir für die Extension nachdenken – wir sind offen für PRs und Collaborators!

Wir möchten eine bessere Heuristik zur Erkennung von Datentypen beim Lesen aus einem Sheet nutzen. Das DuckDB-Typsystem ist fortgeschrittener als Sheets, deshalb wäre es vorteilhaft, präziser zu sein.

Die GSheets-Extension in DuckDB-Wasm zum Laufen zu bringen, würde In-Browser-Anwendungen erlauben, Sheets direkt abzufragen – kein Server nötig! Mehrere http-Funktionen brauchen etwas Anpassung, um in einer Browserumgebung zu funktionieren.

Der OAuth-Flow, der das browserbasierte Login antreibt, könnte nützlich sein, um sich bei anderen APIs zu authentifizieren. Wir fragen uns, ob vielleicht eine generische OAuth-Community-Extension möglich wäre. Es gibt derzeit keine konkreten Pläne dafür, aber wenn jemand interessiert ist, bitte melden!

Schlussgedanken

Bei MotherDuck (wo Alex arbeitet) läuft diese Extension in Produktion für mehrere interne Data-Pipelines! Wir haben automatisierte Exporte von Forecasts aus unserem Warehouse in Sheets und laden laufend manuell gesammelte Customer-Support-Daten in unser (MotherDuck-getriebenes) Data Warehouse. Deshalb enthalten unsere KPI-Dashboards Kontext von Leuten, die direkt mit Kunden sprechen!

Michael Harris hat ebenfalls zur Extension beigetragen (danke!), und Definite hat GSheets-Scheduled-Jobs für mehrere Kunden in Produktion gebracht!

Wie nutzen Sie Google Sheets in Ihrem Datenanalyse-Workflow, und wie kann DuckDB helfen? Wir würden gerne Ihre Ideen auf BlueSky, LinkedIn oder X / Twitter hören!

Jetzt automatisieren Sie dieses Sheet mit etwas SQL!