Google Sheets heißt zwar hierzulande Google Tabellen, aber vielleicht ist die englischsprachige Variante doch geläufiger.
Gemeint ist quasi „Excel im Browser“, was teil von Google Docs ist.
Zumindest ich fange bei datengetriebenen Projekten meistens in einer Tabellenverarbeitung damit an, erste Daten aufzubereiten.
Die in Google Sheets erzeugte Tabelle lässt sich einfach in eine MySQL, SQLite (oder beliebige andere Datenbank) exportieren, denn es gibt einen CSV-Export mittels Link.
Wie es mit PHP geht, zeige ich hier.
Inhaltsverzeichnis
CSV-Export per Magic Link mit Google Sheets
Um ein Tabellenblatt aus Google Sheets programmatisch importieren zu können, benötigt man zunächst den entsprechenden Link.
Ich nutze hier die „Freigeben“-Funktion von Google Sheets.
Damit sollte auch schon die wesentliche Einschränkung klar sein: Sensible Daten verarbeitet man auf diesem Weg lieber nicht.
In meinem Fall aggregiere ich Zahlenmaterial aus öffentlichen Quellen, bereite sie in einer Tabelle bei Google Docs auf und binde die Daten anschließend in mein Web-Projekt ein.
Sollte jemand (Lese-)Zugriff auf meine Tabelle bei Google erhalten, ist mir das egal.
Also weiter zum CSV-Feed.
Rechts oben kann die Tabelle freigegeben werden und anschließend „Jeder, der über den Link verfügt“ ausgewählt werden, woraufhin man einen Link wie diesen erhält:
Der angezeigte Link ist noch nicht der CSV-Feed, jedoch die Basis dafür.
Folgend der Link zu einem kleine Beispiel-Spreadsheet auf Google Docs, welches ich im Folgenden verwenden werde.
https://docs.google.com/spreadsheets/d/1USaikKWXoe6EbddpUMijdSr1JItk2Dp4Y1tB7W-O2Ns/edit?usp=sharingDas ruft die Kurse der beiden Tech-Aktien Apple (AAPL) und Intel (INTC) in einem zwölfmonatigen Zeitraum jeweils zum Monatsersten mit der GOOGLEFINANCE-Funktion ab.
Für das Beispiel hier eigentlich völlig egal, aber da ich die GOOGLEFINANCE-Funktion für mein privates Wertpapier-Dashboard nutze, kam mir dieses Beispiel in den Sinn.
Falls man mehrere Tabellenblätter hat, öffnet man in Google Sheets das Tabellenblatt, welches man mittels CSV exportieren und später in die Datenbank einlesen möchte.
In der Browser-Adresszeile prüft man die URL – ganz am Ende steht ein Parameter gid.
Die gid dient zur Identifizierung des Tabellenblatts und wir brauchen diesen, um die URL zum CSV-Feed zu konstruieren. In meinem Beispiel ist die gid 0.
Mit dem ursprünglichen Freigeben-Link und der gid lässt sich folgende CSV-Feed-URL konstruieren:
https://docs.google.com/spreadsheets/d/1USaikKWXoe6EbddpUMijdSr1JItk2Dp4Y1tB7W-O2Ns/export?format=csv&gid=0CSV von Google Sheets per PHP in MySQL oder SQLite importieren
Damit sind die Google Sheets-Spezifika eigentlich bereits erledigt.
Wir haben nun einen CSV-Feed, der sich einlesen lässt.
Für die weitere Bearbeitung nutze ich im Beispiel PHP und SQlite, aber es führen bekanntlich viele Wege nach Rom 🙂
In der SQlite-Datenbank erstelle ich zunächst eine Tabelle für den Import – im Beispiel mit folgendem CREATE-Statement:
CREATE TABLE "tech_stocks" (
"date" TEXT,
"apple" REAL,
"intel" REAL
);
Nicht wirklich sinnvoll – aber es geht ja nur darum, zu zeigen, wie sich der CSV-Feed aus der Google-Tabellenverarbeitung in eine Datenbank wie MySQL oder SQLite einlesen lässt.
Nachdem die Datenbank vorbereitet ist, lässt sich mit folgendem PHP-Script das Tabellenblatt von Google Docs abrufen und einlesen:
//CSV Feed Google Sheet
$feed = 'https://docs.google.com/spreadsheets/d/1USaikKWXoe6EbddpUMijdSr1JItk2Dp4Y1tB7W-O2Ns/export?format=csv&gid=0';
// DB Setup
$dbFile = __DIR__.'/data.db';
$dbFile = 'sqlite:/'.$dbFile;
$dbHandler = new PDO($dbFile) or exit;
$dbHandler->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
if (!$dbHandler) {
echo 'Problem Opening DB Connection';
exit;
}
// Delete Old Data
$query = $dbHandler->prepare('DELETE from tech_stocks');
$query->execute();
// Get CSV and Convert to Array
if(!$data = convert_csv($feed))
{
echo 'Problem Processing CSV-Feed';
exit;
}
// Iterate through Array and Insert to DB
foreach ($data as $row)
{
$query = $dbHandler->prepare('INSERT INTO tech_stocks(date,apple,intel) values (:date,:apple,:intel);');
$query->bindValue(':date',$row['date']);
$query->bindValue(':apple',$row['apple']);
$query->bindValue(':intel',$row['intel']);
if(!$query->execute()) {
echo 'Problem Inserting Data Row in DB';
exit;
}
}
//Parse CSV to Array using fgetcsv
function convert_csv($feed = '')
{
if (!$handle = fopen($feed, 'r'))
return FALSE;
$header = array();
$data = array();
while (($row = fgetcsv($handle, 1000, ',')) !== FALSE)
{
if(empty($header))
$header = $row;
else
$data[] = array_combine($header, $row);
}
fclose($handle);
return $data;
}
Anwendungszenarien
Zunächst mal: Wann lohnt sich der der Einsatz von Google Sheets überhaupt, um im Hintergrund eine Datenbank zu „füttern“?
Zwei Anwendungsszenarien sehe ich:
- Dateneingabe und Datenaufbereitung
- Zusammenarbeit
Die Eingabe von Daten in Google Sheets ist m.E. nutzerfreundlicher als in gängigen Tools zur Datenbankadministration und Formatierung. Formeln sowie Funktionen erleichtern die Aufbereitung der Daten.
Weiterhin lässt sich gemeinsam, also im Team, an einem Spreadsheet arbeiten.
Für die Verarbeitung sensibler Daten taugt das Konstrukt hingegen nicht.
Stichwort sensible Daten: Bei dem hier beschriebenen, einfachen Weg kommt verschärfend noch hinzu, dass der Export darauf basiert, einen öffentlichen Link zu erstellen.
Das bedeutet: Wer den Link zur Tabelle hat (oder ihn errät), hat auch Zugriff auf die Daten.
Als Alternative wäre hier z.B. die Nutzung der Google Sheets API denkbar.
Es gibt aber genug Szenarien, in denen man Daten aus ohnehin öffentlichen Quellen zusammenträgt, um diese später aufbereitet auf einer ebenfalls öffentlichen Webseite darzustellen.
In diesem Fall ist es vermutlich akzeptabel, dass die Tabelle auf Google Docs öffentlich einsehbar ist.
