Comment afficher les scores des phares dans Google Sheets avec une fonction personnalisée

L'automatisation et l'apprentissage automatique ont un énorme potentiel pour nous aider tous en marketing. Mais pour le moment, beaucoup de ces outils sont inaccessibles aux personnes qui ne peuvent pas coder ou qui peuvent coder un peu mais qui ne sont pas vraiment à l'aise avec cela.

Ce qui arrive souvent, c'est qu'il y a une ou deux personnes au bureau qui sont à l'aise avec l'écriture et l'édition de code, puis ces personnes produisent des scripts et des cahiers que tout le monde gère. Le flux de travail ressemble un peu à ceci:

Je vais vous montrer un moyen simple de rationaliser ce flux de travail pour supprimer les étapes où les gens doivent exécuter un script et formater la sortie. Au lieu de cela, ils peuvent exécuter l'automatisation directement à partir de Google Sheets.

L'exemple que je vais vous montrer concerne une fonction personnalisée Sheets qui renvoie le score Lighthouse pour une URL comme dans ce gif:

La méthode que je vais vous montrer n'est pas la seule façon de faire cela, mais elle illustre une technique beaucoup plus générale qui peut être utilisée pour de nombreuses choses, y compris les algorithmes d'apprentissage automatique.

Il y a deux parties:

1. Une application Google Cloud Run qui fera les choses compliquées (dans ce cas, exécutera un test Lighthouse) et qui répondra aux requêtes HTTP.
2. Une fonction personnalisée Appscript qui enverra des requêtes à l'API que vous avez créée à l'étape 1 et renverra les résultats dans la feuille Google.

Applications exécutées dans le cloud

Cloud Run est un service Google qui prend une image docker que vous fournissez et la rend disponible via HTTP. Vous ne payez que lorsqu'une requête HTTP est effectuée, donc pour un service comme celui-ci qui n'est pas utilisé 24h / 24, 7j / 7, il est très bon marché. Le coût réel dépendra de l'utilisation que vous en faites, mais j'estimerais moins de 1 $ par mois pour exécuter des milliers de tests.

La première chose que nous devons faire est de créer une image Docker qui effectuera l'analyse Lighthouse lorsque nous lui adresserons une requête HTTP. Heureusement pour nous, il existe une documentation montrant comment exécuter un audit Lighthouse par programme sur Github. Le code lié enregistre l'analyse dans un fichier plutôt que de renvoyer la réponse via HTTP, mais cela est facile à résoudre en enveloppant le tout dans une application Express comme celle-ci:

 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}`); });

Enregistrez ce code sous le nom index.js.

Ensuite, vous aurez également besoin d'un fichier appelé package.json qui décrit comment installer l'application ci-dessus et d'un Dockerfile afin que nous puissions tout encapsuler dans Docker. Tous les fichiers de code sont disponibles sur Github.

 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" ]

Créez l'image docker et vous pouvez ensuite tester les choses localement sur votre propre ordinateur comme ceci:

Commencez par démarrer l'image:

 docker run -p 8080:8080 lighthouse-sheets

Et puis testez pour voir si cela fonctionne:

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

Ou visitez localhost: 8080? Url = https% 3A% 2F% 2Fwww.example.com dans votre navigateur. Vous devriez voir beaucoup de JSON.

L'étape suivante consiste à pousser votre image vers le registre Google Container. Pour moi, c'est une commande simple:

 docker push gcr.io/MY_PROJECT_ID/lighthouse-sheets

Mais vous devrez peut-être configurer l'authentification du docker avant de pouvoir le faire. Une autre méthode consiste à utiliser Google Cloud Build pour créer l'image; cela pourrait mieux fonctionner pour vous si vous ne parvenez pas à faire fonctionner l'authentification.

Ensuite, vous devez créer un service Cloud Run avec cette image docker.

Ouvrez Cloud Run et cliquez sur "Créer un service"

Nommez et ajustez les paramètres. Vous devez donner un nom à votre service et configurer quelques autres paramètres:

Il est préférable de choisir une région proche de l'endroit où vit la plupart de l'audience de vos sites. Vérifier la vitesse du site pour un site britannique de Tokyo ne vous donnera pas les mêmes résultats que ceux obtenus par votre public.

Pour que vous puissiez appeler ce service à partir de Google Sheets, il doit autoriser les appels non authentifiés. Si vous craignez de verrouiller et de sécuriser le service pour empêcher d'autres personnes de l'utiliser, vous devrez le faire (par exemple) en vérifiant à partir d'un secret API dans la requête HTTP ou quelque chose du genre.

Ensuite, vous devez sélectionner le conteneur que vous avez créé précédemment. Vous pouvez taper le nom si vous vous en souvenez ou cliquez sur «Sélectionner» et choisissez-le dans le menu.

Cliquez ensuite sur «Afficher les paramètres avancés» car il reste une configuration à effectuer.

Vous devez augmenter l'allocation de mémoire car les tests Lighthouse nécessitent plus de 256 Mo pour s'exécuter. J'ai choisi 1GiB ici mais vous pourriez avoir besoin de l'allocation maximale de 2GiB pour certains sites.

J'ai constaté que réduire la concurrence à 1 améliore la fiabilité du service. Cela signifie que Google démarrera automatiquement un nouveau conteneur pour chaque requête HTTP. L'inconvénient est que cela coûte un peu plus d'argent.

Cliquez sur «Créer» et votre service Cloud Run sera bientôt prêt.

Vous pouvez lui faire un test rapide en utilisant l'URL. Par exemple:

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

Ou visitez https://lighthouse-sheets-public-v4e5t2rofa-nw.a.run.app?url=https%3A%2F%2Fwww.example.com dans votre navigateur.

L'étape suivante consiste à rédiger un certain Appscript afin de pouvoir utiliser votre nouvelle API à partir de Google Sheets.

Ouvrez une nouvelle feuille Google et ouvrez l'éditeur Appscript.

Cela ouvrira un nouvel onglet dans lequel vous pourrez coder votre fonction personnalisée Google Sheets.

L'idée clé ici est d'utiliser la fonction Appscript UrlFetchApp pour exécuter la requête HTTP vers votre API. Un code de base pour faire cela ressemble à ceci:

 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) }

La dernière ligne renvoie le score de performance global dans la feuille. Vous pouvez le modifier pour renvoyer autre chose. Par exemple, pour obtenir le score SEO, utilisez à la place result.categories.seo.score.

Ou vous pouvez renvoyer plusieurs colonnes de résultats en renvoyant une liste comme celle-ci:

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

Enregistrez le fichier et vous aurez une fonction personnalisée disponible dans votre feuille de calcul Google appelée PHARE.

Le moyen le plus simple de commencer est de copier mon exemple de feuille Google, puis de mettre à jour le code vous-même pour qu'il pointe vers votre propre API et de renvoyer les résultats Lighthouse qui vous intéressent le plus.

Améliorez votre savoir-faire en matière de feuilles de calcul

L'avantage de cette méthode est qu'elle peut fonctionner pour tout ce qui peut être enveloppé dans un conteneur Docker et renvoyer un résultat dans les 30 secondes. Malheureusement, les fonctions personnalisées de Google Sheets ont un délai d'expiration, vous n'aurez donc pas assez de temps pour entraîner un algorithme d'apprentissage en profondeur massif, mais cela vous laisse encore beaucoup de choses à faire.

J'utilise un processus très similaire pour mon addon Google Sheets Forecast Forge, mais au lieu de renvoyer un score Lighthouse, il renvoie une prévision alimentée par l'apprentissage automatique pour les nombres que vous y mettez.

Les possibilités pour ce genre de choses sont vraiment excitantes car dans le Search Marketing, nous avons beaucoup de gens qui sont très bons avec les feuilles de calcul. Je veux voir ce qu'ils peuvent faire lorsqu'ils peuvent utiliser toutes leurs connaissances en matière de feuilles de calcul et les améliorer avec l'apprentissage automatique.

Les opinions exprimées dans cet article sont celles de l'auteur invité et pas nécessairement de Search Engine Land. Les auteurs du personnel sont répertoriés ici.