Google Trends in Spreadsheets anzeigen

In Google Sheets hat man über fertige Funktionen bereits Zugriff auf einige Services von Google.

So kann man direkt aus eine Spreadsheets heraus Texte übersetzen, Aktienkurse abrufen oder RSS-Feeds auslesen. Weitere Google Services (z.B. YouTube, Analytics oder Gmail) kann man direkt über den Script-Editor einbinden.

Für Google Trends gibt es allerdings keine solche Einbindung. Es gibt nicht einmal eine öffentliche Api.

Da ich aber trotzdem Trends zu Unternehmen in meinen Sheets anzeigen möchte, musste ich einen anderen Weg finden.

In diesem Beitrag soll es darum gehen, wie man mit überschaubarem Aufwand Google Trends in Google Spreadsheets einbindet.

Für die Lesefaulen gibt es ein Video am Ende des Beitrags

Disclaimer vorweg: Dieser Beitrag verwendet inoffizielle APIs. Wie nachhaltig die Lösung ist und ob Google den Zugriff auf die Schnittstellen limitiert / unterbindet, wird die Zukunft zeigen. Die Verwendung der im Beitrag verwendeten Scripte erfolgt auf ein eigene Gefahr.

Zugriff auf die Daten von Google Trends

Ich war echt überrascht, dass es scheinbar keine offizielle API zu einem so verbreiteten Service wie Google Trends gibt.

Auf der Suche nach anderen Lösungen, bin ich dann auf das google-trends-api Projekt gestoßen. Die für Node.js entwickelte Bibliothek deckt alles ab, was ich für meine Anwendungsfälle brauche.

Alles klar! Fix nen Node.js Server ins Netz stellen, der die Bibliothek über HTTP-Anfragen zur Verfügung stellt.

Als Server nutze ich Googles App-Engine. Die ist, bis zu einem bestimmten Grad, kostenlos nutzbar und bietet ein schnelles und stabiles Node.js Hosting an.

Der „Server“ besteht eigentlich nur aus zwei Dateien. Einer Beschreibungsdatei für die App-Engine (app.yaml) und dem eigentlichen Servercode (app.js).

Als Bibliotheken installiere ich fix google-trends-api und express, hier die package.json, damit man die Abgängigkeiten direkt über „npm install“ installieren kann:

{
  "name": "gtrends",
  "description": "Simple gtrends api",
  "version": "0.0.1",
  "private": true,
  "license": "MIT",
  "author": "Tobi Sell",
  "engines": {
    "node": ">=8.0.0"
  },
  "scripts": {
    "start": "node app.js"
  },
  "dependencies": {
    "express": "^4.16.3",
    "google-trends-api": "^4.9.0"
  }
}

Neben den Abhängigkeiten braucht es noch die app.js. Ich habe hier erstmal nur den „interestOverTime“ -Endpunkt umgesetzt, die anderen lassen sich aber auf die gleiche Art und Weise umsetzen.

'use strict';

const express = require('express');
const googleTrends = require('google-trends-api');

const app = express();

app.get('/interestOverTime', (req, res) => {
  googleTrends.interestOverTime({keyword: req.query.keyword})
      .then(function(results){
        res
            .status(200)
            .header('Content-Type', 'application/json')
            .send(results)
            .end();
        console.log('These results are awesome', results);
      });
});

// Start the server
const PORT = process.env.PORT || 8080;
app.listen(PORT, () => {
  console.log(`App listening on port ${PORT}`);
  console.log('Press Ctrl+C to quit.');
});

module.exports = app;

Und als letztes die app.yaml, die besteht nur aus einer Zeile.

runtime: nodejs10

Wer will, kann den Server lokal direkt benutzen, indem er „npm start“ ins Terminal eingibt.

Node.js App auf App-Engine hosten

Um unsere „Api“ auf der App-Engine zu hosten, muss in der Cloud-Console ein neues Projekt angelegt werden.

In dem Projekt kann man dann unter „App-Engine“ eine neue node.js Umgebung anlegen und einen Serverstandort wählen.

Um das Projekt in die App-Engine hochzuladen, müssen die Google Cloud-Tools installiert sein. Dann kann man den Befehl:

gcloud app deploy

im Terminal ausführen. Nach wenigen Minuten sollte der Service in der App-Engine installiert sein und kann über

gcloud app browse

im Browser geöffnet werden.

Einbindung in Google Sheets

Der letzte Schritt besteht daraus, die neue Api als Funktion in Google-Sheets bereit zu stellen. Dafür ruft man den Script-Editor unter Tools->Script-Editor auf.

Dort wird dann folgender Code eingefügt:

/**
 * Get google trends stats
 * @param {string} keyword
 * @return value
 * @customfunction
 */
function gTrend(term) {
  var resp=[];
  var url="https://{{APP-URL}}/interestOverTime?keyword="+term;
  var body=UrlFetchApp.fetch(url).getContentText();
  var data=JSON.parse(body);
  var timelineData=data["default"].timelineData;
  for (var i in timelineData) {
    var tdata=timelineData[i];
    resp.push(tdata.value[0]);
  }
  return resp;
}

function testGTrends(){
  gTrend("mobfish");
}

{{APP-URL}} müsst ihr durch die URL eurer App-Engine Instanz ersetzen.

Anschließend lässt sich die Funktion wunderbar zusammen mit der SPARKLINE-Funktion von Google-Sheets nutzen:

Wie versprochen, hier noch die Video-Version für Lesefaule: