カスタム機能を使用してGoogleスプレッドシートに灯台スコアを表示する方法

公開: 2020-11-19

自動化と機械学習は、私たち全員のマーケティングに役立つ大きな可能性を秘めています。しかし、現時点では、これらのツールの多くは、コーディングできない人や少しコーディングできるが、それほど快適ではない人にはアクセスできません。

よくあることは、オフィスにはコードの記述と編集に慣れている1人か2人の人がいて、これらの人が他の人が実行するスクリプトとノートブックを作成することです。ワークフローは次のようになります。

このワークフローを合理化して、スクリプトを実行して出力をフォーマットする必要がある手順を削除する簡単な方法を紹介します。代わりに、Googleスプレッドシート内から直接自動化を実行できます。

ここで紹介する例は、次のgifのようなURLの灯台スコアを返すスプレッドシートカスタム関数の例です。

これを行う方法はこれだけではありませんが、機械学習アルゴリズムなど、さまざまな用途に使用できるはるかに一般的な手法を示しています。

2つの部分があります：

複雑な処理を実行し（この場合はLighthouseテストを実行し）、HTTPリクエストに応答するGoogle CloudRunアプリケーション。
手順1で作成したAPIにリクエストを送信し、結果をGoogleスプレッドシートに返すAppscriptカスタム関数。

クラウド実行アプリケーション

Cloud Runは、提供されたDockerイメージを取得し、HTTP経由で利用できるようにするGoogleサービスです。 HTTPリクエストが行われたときにのみ料金が発生するため、24時間年中無休で使用されていないこのようなサービスの場合は非常に安価です。実際の費用は使用量によって異なりますが、何千ものテストを実行するのに月額1ドル未満と見積もっています。

最初に行う必要があるのは、HTTPリクエストを行うときにLighthouse分析を実行するDockerイメージを作成することです。幸いなことに、GithubでLighthouse監査をプログラムで実行する方法を示すドキュメントがいくつかあります。リンクされたコードは、HTTP経由で応答を返すのではなく、分析をファイルに保存しますが、これは、次のようなExpressアプリですべてをラップすることで簡単に修正できます。

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

このコードをindex.jsとして保存します。

次に、上記のアプリケーションとDockerfileをインストールする方法を説明するpackage.jsonというファイルも必要になります。これにより、すべてをDockerでラップできます。すべてのコードファイルは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" ]

Dockerイメージを作成すると、次のように自分のコンピューターでローカルにテストできます。

最初に画像を開始します。

 docker run -p 8080:8080 lighthouse-sheets

そして、それが機能するかどうかをテストします。

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

または、ブラウザでlocalhost：8080？url = https％3A％2F％2Fwww.example.comにアクセスします。多くのJSONが表示されるはずです。

次のステップは、画像をGoogleContainerレジストリにプッシュすることです。私にとって、これは単純なコマンドです。

 docker push gcr.io/MY_PROJECT_ID/lighthouse-sheets

ただし、これを行う前に、最初にDocker認証を設定する必要がある場合があります。別の方法は、Google CloudBuildを使用してイメージを作成することです。認証を機能させることができない場合、これはあなたにとってよりうまくいくかもしれません。

次に、このDockerイメージを使用してCloudRunサービスを作成する必要があります。

Cloud Runを開き、「サービスの作成」をクリックします

名前を付けて設定を調整します。 サービスに名前を付け、他のいくつかの設定を構成する必要があります。

サイトのほとんどのオーディエンスが住んでいる場所に近い地域を選択するのが最善です。東京の英国サイトのサイト速度を確認しても、オーディエンスと同じ結果は得られません。

Googleスプレッドシートからこのサービスを呼び出すには、認証されていない呼び出しを許可する必要があります。他の人がサービスを使用できないようにサービスをロックダウンして保護することを心配している場合は、（たとえば）HTTPリクエストのAPIシークレットなどからチェックする必要があります。

次に、前に作成したコンテナを選択する必要があります。 名前を覚えている場合は入力するか、「選択」をクリックしてメニューから選択します。

次に、さらに構成を行う必要があるため、[詳細設定を表示]をクリックします。

Lighthouseテストの実行には256Mb以上が必要なため、メモリ割り当てを増やす必要があります。ここでは1GiBを選択しましたが、サイトによっては2GiBの最大許容量が必要になる場合があります。

同時実行性を1に減らすと、サービスの信頼性が向上することがわかりました。これは、GoogleがHTTPリクエストごとに新しいコンテナを自動的に開始することを意味します。欠点は、これには少し多くの費用がかかることです。

「作成」をクリックすると、CloudRunサービスの準備がまもなく完了します。

URLを使用して簡単にテストできます。例えば：

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

または、ブラウザでhttps://lighthouse-sheets-public-v4e5t2rofa-nw.a.run.app?url=https%3A%2F%2Fwww.example.comにアクセスしてください。

次のステップは、Googleスプレッドシート内から新しいAPIを使用できるようにAppscriptを作成することです。

新しいGoogleスプレッドシートを開き、Appscriptエディターを開きます。

これにより、Googleスプレッドシートのカスタム関数をコーディングできる新しいタブが開きます。

ここでの重要なアイデアは、AppscriptUrlFetchApp関数を使用してAPIへのHTTPリクエストを実行することです。これを行うためのいくつかの基本的なコードは次のようになります。

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

最後の行は、全体的なパフォーマンススコアをシートに返します。あなたはそれを編集して何か他のものを返すことができます。たとえば、SEOスコアを取得するには、代わりにresult.categories.seo.scoreを使用します。

または、次のようなリストを返すことで、結果の複数の列を返すことができます。

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

ファイルを保存すると、GoogleスプレッドシートでLIGHTHOUSEというカスタム関数を使用できるようになります。

これを開始する最も簡単な方法は、サンプルのGoogleスプレッドシートをコピーしてから、コードを自分で更新して独自のAPIをポイントし、最も関心のあるLighthouseの結果を返すことです。

スプレッドシートのノウハウを強化する

このメソッドの優れている点は、Dockerコンテナーでラップできるものなら何でも機能し、30秒以内に結果を返すことができることです。残念ながら、Googleスプレッドシートのカスタム関数にはタイムアウトがあるため、大規模な深層学習アルゴリズムをトレーニングするのに十分な時間はありませんが、それでもできることはたくさんあります。

GoogleSheetsアドオンのForecastForgeにも非常によく似たプロセスを使用していますが、Lighthouseスコアを返す代わりに、入力した数値に応じて機械学習を利用した予測を返します。

検索マーケティングにはスプレッドシートに非常に優れた人がたくさんいるので、この種の可能性は本当にエキサイティングです。スプレッドシートの知識をすべて活用し、機械学習で強化できる場合に、彼らが何ができるかを知りたいと思います。

この記事で表明された意見はゲスト著者の意見であり、必ずしも検索エンジンランドではありません。 スタッフの作者はここにリストされています。