So zeigen Sie Leuchtturm-Scores in Google Sheets mit einer benutzerdefinierten Funktion an

Automatisierung und maschinelles Lernen haben ein enormes Potenzial, um uns allen im Marketing zu helfen. Aber im Moment sind viele dieser Tools für Leute unzugänglich, die nicht oder nur ein bisschen codieren können, aber nicht wirklich damit vertraut sind.

Was häufig passiert, ist, dass ein oder zwei Personen im Büro sind, die mit dem Schreiben und Bearbeiten von Code vertraut sind, und diese Personen dann Skripte und Notizbücher erstellen, die alle anderen ausführen. Der Workflow sieht ungefähr so ​​aus:

Ich werde Ihnen eine einfache Möglichkeit zeigen, diesen Workflow zu optimieren, um die Schritte zu entfernen, bei denen Benutzer ein Skript ausführen und die Ausgabe formatieren müssen. Stattdessen können sie die Automatisierung direkt in Google Sheets ausführen.

Das Beispiel, das ich Ihnen zeigen werde, bezieht sich auf eine benutzerdefinierte Sheets-Funktion, die den Lighthouse-Score für eine URL wie in diesem GIF zurückgibt:

Die Methode, die ich Ihnen zeigen werde, ist nicht die einzige Möglichkeit, dies zu tun, aber sie veranschaulicht eine viel allgemeinere Technik, die für viele Dinge verwendet werden kann, einschließlich Algorithmen für maschinelles Lernen.

Es gibt zwei Teile:

1. Eine Google Cloud Run-Anwendung, die die komplizierten Aufgaben erledigt (in diesem Fall einen Lighthouse-Test) und auf HTTP-Anforderungen reagiert.
2. Eine benutzerdefinierte Appscript-Funktion, die Anforderungen an die in Schritt 1 erstellte API stellt und die Ergebnisse in Google Sheet zurückgibt.

Cloud-Anwendungen ausführen

Cloud Run ist ein Google-Dienst, der ein von Ihnen bereitgestelltes Docker-Image aufnimmt und über HTTP verfügbar macht. Sie zahlen nur, wenn eine HTTP-Anfrage gestellt wird. Für einen Dienst wie diesen, der nicht rund um die Uhr genutzt wird, ist er daher sehr günstig. Die tatsächlichen Kosten hängen davon ab, wie oft Sie es verwenden, aber ich würde weniger als 1 US-Dollar pro Monat für die Durchführung von Tausenden von Tests schätzen.

Als erstes müssen wir ein Docker-Image erstellen, das die Lighthouse-Analyse durchführt, wenn wir eine HTTP-Anfrage an das Unternehmen senden. Zum Glück gibt es einige Dokumentationen, die zeigen, wie ein Lighthouse-Audit programmgesteuert auf Github durchgeführt wird. Der verknüpfte Code speichert die Analyse in einer Datei, anstatt die Antwort über HTTP zurückzugeben. Dies lässt sich jedoch leicht beheben, indem Sie das Ganze in eine Express-App wie die folgende einbinden:

 const express = require('express'); const app = express(); const lighthouse = require('lighthouse'); const chromeLauncher = require('chrome-launcher'); app.get('/', async (req, res) => { // Check that the url query parameter exists if(req.query && req.query.url) { // decode the url const url = decodeURIComponent(req.query.url) const chrome = await chromeLauncher.launch({chromeFlags: ['--headless', '--no-sandbox','--disable-gpu']}); const options = {logLevel: 'info', output: 'html', port: chrome.port}; const runnerResult = await lighthouse(url, options); await chrome.kill(); res.json(runnerResult.lhr) } }); const port = process.env.PORT || 8080; app.listen(port, () => { console.log(`Listening on port ${port}`); });

Speichern Sie diesen Code als index.js.

Dann benötigen Sie auch eine Datei namens package.json, in der beschrieben wird, wie die obige Anwendung installiert wird, sowie eine Docker-Datei, damit wir alles in Docker zusammenfassen können. Alle Codedateien sind auf Github verfügbar.

 package.json { "name": "lighthouse-sheets", "description": "Backend API for putting Lighthouse scores in Google sheets", "version": "1.0.0", "author": "Richard Fergie", "license": "MIT", "main": "index.js", "scripts": { "start": "node index.js" }, "dependencies": { "express": "^4.17.1", "lighthouse": "^6.3" }, "devDependencies": {} } Dockerfile # Use the official lightweight Node.js 10 image. # https://hub.docker.com/_/node FROM node:12-slim # Our container needs to have chrome installed to # run the lighthouse tests RUN apt-get update && apt-get install -y \ apt-transport-https \ ca-certificates \ curl \ gnupg \ --no-install-recommends \ && curl -sSL https://dl.google.com/linux/linux_signing_key.pub | apt-key add - \ && echo "deb https://dl.google.com/linux/chrome/deb/ stable main" > /etc/apt/sources.list.d/google-chrome.list \ && apt-get update && apt-get install -y \ google-chrome-stable \ fontconfig \ fonts-ipafont-gothic \ fonts-wqy-zenhei \ fonts-thai-tlwg \ fonts-kacst \ fonts-symbola \ fonts-noto \ fonts-freefont-ttf \ --no-install-recommends \ && apt-get purge --auto-remove -y curl gnupg \ && rm -rf /var/lib/apt/lists/* # Create and change to the app directory. WORKDIR /usr/src/app # Copy application dependency manifests to the container image. # A wildcard is used to ensure copying both package.json AND package-lock.json (when available). # Copying this first prevents re-running npm install on every code change. COPY package*.json ./ # Install production dependencies. # If you add a package-lock.json, speed your build by switching to 'npm ci'. # RUN npm ci --only=production RUN npm install --only=production # Copy local code to the container image. COPY . ./ # Run the web service on container startup. CMD [ "node", "--unhandled-rejections=strict","index.js" ]

Erstellen Sie das Docker-Image und testen Sie die Dinge wie folgt lokal auf Ihrem eigenen Computer:

Starten Sie zuerst das Bild:

 docker run -p 8080:8080 lighthouse-sheets

Und dann testen, ob es funktioniert:

 curl -v "localhost:8080?url=https%3A%2F%2Fwww.example.com"

Oder besuchen Sie localhost: 8080? Url = https% 3A% 2F% 2Fwww.example.com in Ihrem Browser. Sie sollten viel JSON sehen.

Der nächste Schritt besteht darin, Ihr Bild in die Google Container-Registrierung zu verschieben. Für mich ist dies ein einfacher Befehl:

 docker push gcr.io/MY_PROJECT_ID/lighthouse-sheets

Möglicherweise müssen Sie jedoch zuerst die Docker-Authentifizierung einrichten, bevor Sie dies tun können. Eine alternative Methode ist die Verwendung von Google Cloud Build, um das Bild zu erstellen. Dies funktioniert möglicherweise besser für Sie, wenn die Authentifizierung nicht funktioniert.

Als Nächstes müssen Sie einen Cloud Run-Dienst mit diesem Docker-Image erstellen.

Öffnen Sie Cloud Run und klicken Sie auf "Service erstellen".

Benennen und Einstellungen anpassen. Sie müssen Ihrem Dienst einen Namen geben und einige andere Einstellungen konfigurieren:

Wählen Sie am besten eine Region aus, in der sich die meisten Zuschauer Ihrer Websites befinden. Wenn Sie die Geschwindigkeit der Website für eine britische Website aus Tokio überprüfen, erhalten Sie nicht die gleichen Ergebnisse wie bei Ihrem Publikum.

Damit Sie diesen Dienst von Google Sheets aus aufrufen können, müssen nicht authentifizierte Aufrufe zulässig sein. Wenn Sie sich Sorgen machen, den Dienst zu sperren und zu sichern, um zu verhindern, dass andere Personen ihn verwenden, müssen Sie dies tun, indem Sie beispielsweise ein API-Geheimnis in der HTTP-Anforderung oder ähnliches überprüfen.

Als nächstes müssen Sie den Container auswählen, den Sie zuvor erstellt haben. Sie können den Namen eingeben, wenn Sie sich daran erinnern, oder auf „Auswählen“ klicken und ihn aus dem Menü auswählen.

Klicken Sie dann auf "Erweiterte Einstellungen anzeigen", da weitere Konfigurationen erforderlich sind.

Sie müssen die Speicherzuordnung erhöhen, da für die Ausführung von Lighthouse-Tests mehr als 256 MB erforderlich sind. Ich habe hier 1 GB gewählt, aber für einige Websites benötigen Sie möglicherweise die maximale Zulässigkeit von 2 GB.

Ich habe festgestellt, dass das Reduzieren der Parallelität auf 1 die Zuverlässigkeit des Dienstes verbessert. Dies bedeutet, dass Google für jede HTTP-Anfrage automatisch einen neuen Container startet. Der Nachteil ist, dass dies etwas mehr Geld kostet.

Klicken Sie auf "Erstellen" und Ihr Cloud Run-Dienst wird in Kürze verfügbar sein.

Sie können es anhand der URL schnell testen. Zum Beispiel:

 curl -v "https://lighthouse-sheets-public-v4e5t2rofa-nw.a.run.app?url=https%3A%2F%2Fwww.example.com"

Oder besuchen Sie https://lighthouse-sheets-public-v4e5t2rofa-nw.a.run.app?url=https%3A%2F%2Fwww.example.com in Ihrem Browser.

Der nächste Schritt besteht darin, Appscript zu schreiben, damit Sie Ihre neue API in Google Sheets verwenden können.

Öffnen Sie ein neues Google Sheet und öffnen Sie den Appscript-Editor.

Dadurch wird eine neue Registerkarte geöffnet, in der Sie Ihre benutzerdefinierte Google Sheets-Funktion codieren können.

Die Schlüsselidee hierbei ist die Verwendung der Appscript UrlFetchApp-Funktion, um die HTTP-Anforderung an Ihre API auszuführen. Ein grundlegender Code dafür sieht folgendermaßen aus:

 function LIGHTHOUSE(url) { const BASE_URL = "https://lighthouse-sheets-public-v4e5t2rofa-nw.a.run.app" var request_url = BASE_URL+"?url="+encodeURIComponent(url) var response = UrlFetchApp.fetch(request_url) var result = JSON.parse(response.getContentText()) return(result.categories.performance.score * 100) }

In der letzten Zeile wird die Gesamtleistungsbewertung in das Blatt zurückgegeben. Sie können es bearbeiten, um etwas anderes zurückzugeben. Um beispielsweise den SEO-Score zu erhalten, verwenden Sie stattdessen result.categories.seo.score.

Sie können auch mehrere Ergebnisspalten zurückgeben, indem Sie eine Liste wie die folgende zurückgeben:

[result.categories.performance.score, result.categories.seo.score]

Speichern Sie die Datei, und in Ihrem Google Sheet steht eine benutzerdefinierte Funktion namens LIGHTHOUSE zur Verfügung.

Der einfachste Weg, um damit zu beginnen, besteht darin, mein Beispiel-Google Sheet zu kopieren und dann den Code selbst zu aktualisieren, um auf Ihre eigene API zu verweisen und die Lighthouse-Ergebnisse zurückzugeben, an denen Sie am meisten interessiert sind.

Erweitern Sie Ihr Tabellenkalkulations-Know-how

Das Tolle an dieser Methode ist, dass sie für alles funktioniert, was in einen Docker-Container verpackt werden kann, und innerhalb von 30 Sekunden ein Ergebnis zurückgibt. Leider haben benutzerdefinierte Funktionen von Google Sheets eine Zeitüberschreitung, sodass Sie nicht lange genug Zeit haben, um einen massiven Deep-Learning-Algorithmus zu trainieren. Sie haben jedoch noch viel zu tun.

Ich verwende einen sehr ähnlichen Prozess für mein Google Sheets-Addon Forecast Forge, aber anstatt einen Lighthouse-Score zurückzugeben, wird eine Prognose für maschinelles Lernen für alle Zahlen zurückgegeben, die Sie eingeben.

Die Möglichkeiten für solche Dinge sind wirklich aufregend, weil wir im Suchmaschinenmarketing viele Leute haben, die sehr gut mit Tabellenkalkulationen umgehen können. Ich möchte sehen, was sie tun können, wenn sie ihr gesamtes Tabellenkalkulationswissen nutzen und es durch maschinelles Lernen erweitern können.

Die in diesem Artikel geäußerten Meinungen sind die des Gastautors und nicht unbedingt Search Engine Land. Die Autoren der Mitarbeiter sind hier aufgelistet.