API zum Lesen von Daten aus einem Google Sheet

02. September 2021

Google Sheets kann ein großartiger Ort zum Speichern von Inhalten für eine Website sein, da es strukturiert und leicht zu aktualisieren ist (insbesondere für Nicht-Programmierer).

Es gibt eine sehr nützliche, aber leider derzeit wenig dokumentierte, um nicht zu sagen obskure, Möglichkeit, eine API zum Lesen von Daten aus Google Sheets zu erstellen, die keine Authentifizierung oder komplizierte Berechtigungen erfordert. Außerdem wird es sofort aktualisiert, wenn die Tabelle bearbeitet wird, ohne Verzögerung.

Dass Google seit Mitte August 2021 die Zugriffs-Methode geändert hat, trägt ja auch nicht zur Transparenz bei. Die aktuell funktionierende Methode (ab August 2021) ist im Folgenden beschrieben:

Vorgehen

Öffnen Sie das Google Sheet und geben Sie die Tabelle frei (Schaltfläche in der oberen rechten Ecke), damit jeder mit dem Link sie anzeigen kann, ohne sich anzumelden.
Kopieren Sie die Tabellen-ID. Das ist die lange zufällige Zeichenfolge in der URL der Tabelle vor "/edit". Achten Sie darauf, den gesamten zufälligen Teil der URL zwischen zwei Schrägstriche zu kopieren.
Wir verwenden diese URL, um die Daten der Tabelle abzurufen, und ersetzen tabellen_id durch Ihre Tabellen-ID aus dem vorherigen Schritt:

https://docs.google.com/spreadsheets/d/tabellen_id/gviz/tq?tqx=out:json

Dieser Endpunkt gibt nicht ganz JSON zurück, aber er gibt einen Funktionsaufruf zurück, der JSON umschließt. So erhalten Sie die gewünschten Daten mit der JavaScript "fetch" API:

const tabellen_id = '...'
fetch(`https://docs.google.com/spreadsheets/d/${tabellen_id}/gviz/tq?tqx=out:json`)
    .then(res => res.text())
    .then(text => {
        const json = JSON.parse(text.substr(47).slice(0, -2))
    })

Sie werden feststellen, dass die ersten 47 Zeichen des Textes abgeschnitten werden (das ist der Anfang des Funktionsaufrufs) und dann die letzten beiden Zeichen (das ist das Ende des Funktionsaufrufs,);).

console.log (text) zeigt Ihnen die vollständige Zeichenfolge an, die die API zurückgibt.

Jetzt können Sie die resultierende json-Variable überprüfen, um zu sehen, was die API zurückgibt. Die Überschriften Ihrer Tabelle befinden sich unter json.table.cols, und die Zeilen befinden sich unter json.table.rows in derselben Reihenfolge, in der die Spalten in json.table.cols zurückgegeben werden.

Andere Blätter in Ihrer Tabelle lesen

Sie können am Ende der URL "&sheet = Name anderes Blatt" hinzufügen, um Daten von einem anderen Blatt / Tab zu erhalten.

Optionaler Schritt: Formatieren der Daten

Für eine einfachere Verarbeitung, lohnt es sich die Daten der obigen API neu zu formattieren, bevor sie verwendet werden.

Anbei ein Beispiel zum Formatieren der Daten welche mit der fetch-Methode in JavaScript erhalten werden (Dokumentation):

fetch("https://spreadsheets.google.com/feeds/list/HIER_IHRE_SPREADSHEET_ID/1/public/values?alt=json")
  .then(res => res.json())
  .then(json => {
    const data = [] /* Dieses Array wird mit dem Inhalt der Tabellenzeilen gefüllt  */

    const rows = json.feed.entry

    for(const row of rows) {
      const formattedRow = {}

      for(const key in row) {
        if(key.startsWith("gsx$")) {

          / * Die tatsächlichen Zeilennamen aus Ihrer Tabelle 
            * sind im Format "gsx$title".  
            * Daher müssen wir Schlüssel in diesem Objekt finden 
            * die mit "gsx$" beginnen und das dann entfernen,
            * um den tatsächlichen Zeilennamen zu erhalten
            * / 

          formattedRow[key.replace("gsx$", "")] = row[key].$t

        }
      }

      data.push(formattedRow)
    }

    console.log(data) /* neu formatierten Daten hier weiterverwenden */
  })