Eine Schritt für Schritt Anleitung, um mit PHP via OAuth2 auf Daten von Google Spreadsheets oder anderen Google Diensten zuzugreifen.

Man kann den Suchmaschinenmarktführer mögen oder auch nicht. Aber die Google Docs bieten etwas, das mit Excel, Word & Co. Stand heute nicht realisierbar ist: Mehrere Personen arbeiten gemeinsam an einem Dokument. In Echtzeit. Zu den gemeinsam verwaltbaren Dokumenten gehören aktuell:

  • Docs (Textdokumente)
  • Slides (Präsentationen)
  • Sheets (Tabellenkalkulationen)
  • Forms (Web-Formulare, bspw. für Befragungen)
  • Drawings (Konkurrenz für Microsofts Bitmap Programm, muahaha)
  • My Maps (Karten)

Natürlich ist der Funktionsumfang der einzelnen Tools nicht mit anderen Tools wie aus den Suiten von Open Office oder MS Office vergleichbar. Aber die echt kollaborative Zusammenarbeit ist ein Killer Feature und eröffnet interessante Use Cases.

Google Docs Daten automatisiert verarbeiten

Docs, Slides & Co. werden in der Regel im Browser bearbeitet. Das wird für viele Anwendungsfälle genügen. Was aber, wenn Daten sowohl im Browser gepflegt als aber auch durch Software automatisiert verarbeitet werden sollen?

Google stellt umfangreiche Schnittstellen-Dokumentationen für verschiedene APIs zur Verfügung, die den Zugriff per .net, Java oder PHP beschreiben. Durch eine unlängst erfolgte Umstellung auf oAuth2 ist jedoch die Authentisierung für den softwaregesteuerten Zugriff komplexer geworden. Deutlich komplexer.

Wie geht dem?

Die Umstellung auf oAuth2 hat es in sich, denn viele selbstgeschriebene Skripte laufen seitdem nicht mehr. Während die alte Authentisierung mit Username-/Password-Kombination vergleichsweise simpel war, erfordert der Zugriff via oAuth2 mehr Aufwand.

Viele Nutzer im Netz suchen nach entsprechenden Lösungen, wobei konkrete Schritt-für-Schritt-Anleitungen, die 100%ig erfolgreich sind, fehlen. Zusätzlich scheint es nur im englischsprachigen Raum überhaupt Hilfe zu geben. Wenn man aber erst einmal genug Quellen in einen Topf geworfen hat, kommt am Ende sogar eine Suppe Lösung raus. Hier Schritt für Schritt, wie es funktionieren kann. (Viele Wege, Rom, blabla)

So geht dem!

Wir legen zu Testzwecken ein Google (Spread)Sheet an.
Test Google Spreadsheet Reiter

Das Spreadsheet heißt “Test-O-Mat” und besteht aus den zwei Worksheets “Argl” und “Wargl”.

Der Weg zu den Daten mit PHP: Schritt für Schritt

  1. Verzeichnis anlegen. Wir legen irgendwo ein passendes Verzeichnis für unser Testprojekt an, bspw. im Home-Verzeichnis des eingeloggten Users und wechseln hinein.
    $ mkdir ~/oauth && cd ~/oauth
  2. Composer installieren. Wir brauchen den PHP Dependency Manager “Composer”. Dieser lässt sich am einfachsten mit dem folgenden Befehl auf der Kommandozeile installieren. Das setzt natürlich eine funktionsfähige lokale PHP-Installation voraus.
    $ curl -sS https://getcomposer.org/installer | php

    Wenn die Installation erfolgreich verläuft, sieht das in etwa so aus:

    #!/usr/bin/env php
    All settings correct for using Composer
    Downloading...
    
    Composer successfully installed to: <Pfad>/oauth/composer.phar
    Use it: php composer.phar
    $
    

    Es wurde eine composer.phar Datei erstellt – ein PHp ARchive, das nach dem nächsten Schritt zum Einsatz kommt.

  3. composer.json erstellen. Für die Arbeit mit den Google APIs werden bestimmte PHP-Libraries benötigt. Der im vorherigen Schritt installierte Dependency Manager entledigt uns der Bürde, diese Libraries manuell zu verwalten. Aber er muss wissen, welche Libraries wir genau benötigen. Das bekommt er über eine projektbezogene composer.json Datei mitgeteilt. Dafür im aktuellen Verzeichnis eine composer.json Datei mit folgendem Inhalt anlegen.
    {
      "require": {
        "asimlqt/php-google-spreadsheet-client": "2.2.*",
        "google/apiclient": "1.0.*@beta"
      }
    }
    
  4. Libraries installieren. Mit dem folgenden Befehl wird der Composer gestartet. Er lädt alle benötigten Libraries von den zugehörigen Servern und installiert die Library-Dateien im aktuellen Verzeichnis.
    $ php composer.phar install

    Wenn’s gut läuft, sieht das wie folgt aus.

    Loading composer repositories with package information
    Installing dependencies (including require-dev)
      - Installing asimlqt/php-google-spreadsheet-client (2.2.4)
        Downloading: 100%
    
      - Installing google/apiclient (1.0.6-beta)
        Downloading: 100%
    
    Writing lock file
    Generating autoload files
    $
    

    Verzeichnis und Dateien nach den ersten Schritten Nach der erfolgreichen Ausführung des Composers sollte sich ein neues Unterverzeichnis “vendor” im aktuellen Verzeichnis befinden. Zusätzlich die Dateien composer.phar (Composer Dependency Manager), composer.lock (Finger weg!) und composer.json.

  5. Credentials für oAuth2 Authentisierung erstellen. Wir benötigen Authentisierungs-Credentials, die mit der Google Developers Console generiert werden. Dies erfordert, wie alles bei Google, einen Google Account. Falls noch nicht vorhanden, Google Account anlegen und dann bei der Google Developers Console registrieren. Nach dem Login empfängt uns eine karge Benutzeroberfläche.
    Google Developers Console
    Zur Erstellung der Credentials für unser PHP-Skript wie folgt vorgehen.

    1. Im Hauptmenü “APIs & auth” wählen. Dort “Credentials”.
    2. Der Datenbereich zeigt ein Drop-Down-Menü. Dort “Service account” wählen.
      Credentials Service Account
    3. Es folgt eine Auswahl zum gewünschten Keytype. “P12” anklicken und den Button “Create” drücken. Der Browser speichert daraufhin ein Key-Datei “API Project-xxxxxxxxxx.p12”. Diese in’s Projektverzeichnis kopieren. Gleichzeitig wird ein Passwort im Browser angezeigt. Dieses notieren, wir brauchen es später noch.
    4. Im Datenbereich wird daraufhin eine Übersicht aller Credentials angezeigt. Gibt es mehrere Service Account Credentials, so steht unser neu angelegter ganz unten in der Liste. Die zugehörige E-Mail-Adresse anklicken und die auf dem folgenden Screen angezeigte lange, kryptische Client ID sowie die ebenso lange und ebenso kryptische E-Mail-Adresse notieren. Brauchen wir später noch.
      Credentials Service Account

    Mehr gibt es in der Developers Console nicht zu tun.

  6. Spreadsheet freigeben. Das Google Spreadsheet “Test-O-Mat” muss für den soeben angelegten Service Account freigegeben werden, damit über diesen auf das Spreadsheet zugegriffen werden kann. Dazu verwenden wir die zuvor notierte E-Mail-Adresse aus der Google Developers Console: Im Google Spreadsheet oben rechts auf den Share/Teilen Button klicken. Daraufhin öffnet sich ein Popup-Fenster mit Eingabefeld. Dort die E-Mail-Adresse hineinkopieren, die Option “Notify people via E-Mail” abwählen und “Send” drücken.
  7. PHP Code für den Spreadsheet Zugriff erstellen. Now for the fun part, falls das noch nicht genug Spaß war. Eine frische index.php im Projektverzeichnis anlegen und im Editor öffnen. Dort den folgenden Kode einfügen und G_CLIENT_ID, G_CLIENT_EMAIL sowie G_CLIENT_KEY_PATH durch die eigenen Credentials ersetzen. Ggfs. ist auch das Passwort durch das eigene zu ersetzen, aber ich glaube, dass beim P12-Keyfile stets “notasecret” gewählt wird.
    setApplicationName( 'Test-O-Mat' );
    $client->setClientId( G_CLIENT_ID );
    $client->setAssertionCredentials( new Google_Auth_AssertionCredentials(
        G_CLIENT_EMAIL,
        array( 'https://spreadsheets.google.com/feeds', 'https://docs.google.com/feeds' ),
        file_get_contents( G_CLIENT_KEY_PATH ),
        G_CLIENT_KEY_PW
    ) );
    $client->getAuth( )->refreshTokenWithAssertion( );
    $objToken = json_decode( $client->getAccessToken( ) );
    $accessToken = $objToken->access_token;
    
    $serviceRequest = new GoogleSpreadsheetDefaultServiceRequest( $accessToken );
    GoogleSpreadsheetServiceRequestFactory::setInstance( $serviceRequest );
    
    $spreadsheetService = new GoogleSpreadsheetSpreadsheetService( );
    $spreadsheetFeed = $spreadsheetService->getSpreadsheets( );
    echo "
    ";
    foreach( $spreadsheetFeed as $spreadsheet ) {
      echo $spreadsheet->getTitle( )."n";
      $worksheetFeed = $spreadsheet->getWorksheets( );
      foreach( $worksheetFeed as $worksheet ) {
        echo "|n+-" . $worksheet->getTitle( ) . "n";
      }
    }
    echo "</pre>";
    ?>
    

    Das Skript authentisiert sich via OAuth2, startet dann einen Service Request ggü. Google Spreadsheets, holt sich eine Liste aller(!) Spreadsheets, die mit der (kryptischen) E-Mail-Adresse geteilt wurden, iteriert über diese Liste und gibt für jedes Spreadsheet die Namen aller enthaltenen Worksheets aus.

    Wir haben nur das Spreadsheet "Test-O-Mat" für die E-Mail-Adresse freigegeben. Daher sollte der Browser eine Ausgabe wie die Folgende produzieren.

    Test-O-Mat
      |
      +-Argl
      |
      +-Wargl
    

Fazit

Wenn die Ausgabe wie zuletzt gezeigt war, dann ist es Zeit für einen Glückwunsch-Schokohasen. Die Authentisierung läuft, der Zugriff auf beliebige Spreadsheets ist möglich. Lesen und Schreiben von Daten is left as an excercise to the reader.

Join the conversation!

  • Vielen Dank für deine Anleitung. Interessant wäre noch wie man das auf seiner Website hinbekommt, die man z.B. bei 1und1 hosten läßt. Da habe ich keine Konsole.

    Eine Ahnung wie das geht?

    • Hallo Bernd,
      leider kann ich Dir da nur sehr bedingt weiterhelfen. Du könntest Dich an den Support von 1und1 wenden und denen mal erörtern, was Du machen möchtest. Vielleicht haben die die nötigen Libraries dafür schon installiert?! Einen Versuch wäre es wert.
      Viel Erfolg!
      Axel

  • Hallo Axel – DAnke für den Walk-through.
    Hat bis fast zuletzt alles geklappt. Bei Aufruf der index.php kriege ich jetzt allerdings
    “Fatal error: Class ‘GoogleSpreadsheetDefaultServiceRequest’ not found in /home/oauth/index.php on line 22”
    und kann mir keinen Reim darauf machen.

  • “Lesen und Schreiben von Daten is left as an excercise to the reader.”

    Lieber Axel, soweit hat jetzt alles geklappt – kannst Du mich in die Richtung einer Referenz für die unter PHP nutzbaren Funktionen für obige Vorgänge schubsen?

  • Hi Peter,
    eine Referenz habe ich spontan nicht zur Hand. Aber ich kann Dir ein Code-Schnipsel posten, wie ich etwas gelesen und geschrieben habe:

    $worksheet = $worksheetFeed->getByTitle( );
    $listFeed = $worksheet->getListFeed( );
    $rows = $listFeed->getEntries( );

    count( $rows) gäbe Dir die Menge der im Sheet vorhandenen Zeilen.

    $headers = $rows[ 0 ]->getValues( ); // Zellen der ersten Zeile des Sheets lesen

    Im Array $headers sind alle Zellen der ersten Zeile. count( $headers) gibt Dir die Anzahl der Spalten.

    Damit lässt sich dann schon lesend über alle Zeilen und Spalten (=alle Zellen) iterieren.

    Jetzt muss ich mal suchen …
    Ach ja, schreiben mit “update”, also so

    $rows[ $i ]->update( $columns );

    Damit wird die ganze Zeile $i neu geschrieben.

    Hoffe, das hilft Dir schonmal weiter!

    Viele Grüße
    Axel

    • Danke für den kleinen Lehrgang! Wer wie ich nur ein bisschen php kann, den überfordern die Massen an API-Dokumentationen von Google schnell. Dabei will ich doch nur eine Tabelle aus einem RSS-Feed laufend aufdatieren…

      • Hi Peter,
        gerne! Hoffe, Du bekommst das mit deinem RSS-Feed hin! Drücke Dir die Daumen!
        VGvAxel

  • Entschuldige, wenn ich Dich mit Beschlag belege hier, aber wer einmal hilft… ich bin verwirrt: Es muss doch irgendwo ein Verzeichnis/eine Referenz der Funktionen geben, welche die Library zur Verfügung stellt?

    Ferner: $rows[ $i ]->update( $columns ); gibt mir, wenn ich für $i eine neue Zeile nehme, einen null-Fehler aus und wenn ich eine existierende nehme, einen umfangreichen Fehler aus: ($columns muss ein Array sein, richtig?):

    Fatal error: Uncaught exception ‘Google\Spreadsheet\Exception’ with message ‘Error in Google Request’ in /home/swissrep/public_html/oauth/vendor/asimlqt/php-google-spreadsheet-client/src/Google/Spreadsheet/DefaultServiceRequest.php:242 Stack trace: #0 /home/swissrep/public_html/oauth/vendor/asimlqt/php-google-spreadsheet-client/src/Google/Spreadsheet/DefaultServiceRequest.php(163): Google\Spreadsheet\DefaultServiceRequest->execute(Resource id #10) #1 /home/swissrep/public_html/oauth/vendor/asimlqt/php-google-spreadsheet-client/src/Google/Spreadsheet/ListEntry.php(87): Google\Spreadsheet\DefaultServiceRequest->put(‘https://spreads…’, ‘update(Array) #3 {main} thrown in /home/swissrep/public_html/oauth/vendor/asimlqt/php-google-spreadsheet-client/src/Google/Spreadsheet/DefaultServiceRequest.php on line 242

    • Hi Peter,

      ich habe mir das größtenteils aus anderen (via Google gefundenen) Quellen zusammengereimt und -geschustert. Die API wird aber scheinbar hier https://developers.google.com/sheets/api/ beschrieben.

      Mit update() lässt sich IMO keine neue Zeile anlegen (daher vermute ich den Null-Fehler), sondern nur eine bestehende aktualisieren. Ja, $columns muss ein Array sein. Wobei ich meine, mich zu erinnern, dass $columns ein assoziativer Array sein muß, also in der Form $columns( “a”=>”wert”, “b”=>”wert”, …) wobei a, b usw. die Spaltenüberschriften sind. Ich glaube, ohne Spaltenüberschriften funktioniert das nicht.

      Ich hoffe, das hilft Dir. Die Fehlermeldung ist ja leider überhaupt nicht hilfreich…

      Viele Grüße
      Axel

    • Hi Peter,
      ein anderer User erhält gerade genau diese Fehlermeldung. Könntest Du hier noch Deine Lösung teilen?
      Vielen Dank und VG
      Axel

  • Danke, Axel – ich sehe, Du bist gleich vorgegangen wie ich: Stundenlanges Wühlen in Github und Stackoverflow bringt bruchstückhafte “Klarheit”. Sehr schade, dass IT-Experten generell zu faul sind, Dokumentationen zu erstellen, mit denen auch Laien sich einarbeiten könnten. Vielen Dank für Deine Hilfe.

    • Jau, da war schon einiges zu suchen, bis es dann endlich funktionierte. Naja, ich tröste mich dann mit “der Weg ist das Ziel” über die Mühen hinweg. 😉 Viele Grüße, Axel

  • Hi Axel, die Fehlermeldung wurde zwar schon gepostet, finde aber die Lösung nicht dazu und verstehe diese auch nicht.
    Wenn du mir die Lösung nochmal schreiben könntest, wäre ich dir sehr dankbar.

    PHP Fatal error: Uncaught Error: Class ‘GoogleSpreadsheetDefaultServiceRequest’ not found in /mnt/c/xampp/htdocs/phpTest2/index.php:22
    Stack trace:
    #0 {main}
    thrown in /mnt/c/xampp/htdocs/phpTest2/index.php on line 22

    Viele Grüße

    drag

    • Hi drag,
      den Fehler hatte Peter gemeldet, aber leider nicht beschrieben, wie er ihn wegbekommen hat. Vielleicht liest er hier mit und könnte das noch posten?
      VGxl

Leave a Reply

Your email address will not be published. Required fields are marked *