The most effective method to show Lighthouse Scores in Google Sheets with a custom function

Computerization and AI can possibly help we all in showcasing. However, right now a ton of these instruments are unavailable to individuals who can’t code or who can code a piece yet aren’t generally that OK with it.

What regularly happens is that there winds up being a couple of individuals in the workplace who are alright with composing and altering code and afterward these individuals produce contents and journals that every other person runs. The work process looks a cycle this way:

I will show you a basic method to smooth out this work process to eliminate the means where individuals need to run a content and organization the yield. Rather they can run the computerization straightforwardly from inside Google Sheets.

The model I will show you is for a Sheets custom capacity that profits the Lighthouse score for a URL like in this gif:

The strategy I will show you isn’t the main method of doing this, yet it represents a substantially more broad procedure that can be utilized for some, things, including AI calculations.

* There are two sections: *

A Google Cloud Run application that will do the confounded stuff (for this situation run a Lighthouse test) and that will react to HTTP demands.

An Appscript custom capacity that will make solicitations to the API you made in sync 1 and return the outcomes into the Google Sheet.

Cloud run applications

Cloud Run is a Google administration that takes a docker picture that you give and makes it accessible over HTTP. You possibly pay when a HTTP demand is made, so for a help like this that isn’t being utilized every minute of every day it is modest. The real cost will rely upon the amount you use it, however I would appraise under $1 every month to run a great many tests.

The primary thing we have to do is make a Docker picture that will play out the Lighthouse investigation when we make a HTTP solicitation to it. Fortunately for us there is some documentation telling the best way to run a Lighthouse review programatically on Github. The connected code spares the investigation to a record instead of restoring the reaction over HTTP, yet this is anything but difficult to fix by enveloping the entire thing by an Express application like this:

Save this code as index.js.

At that point you will likewise require a document called package.json which depicts how to introduce the above application and a Dockerfile so we can envelop everything with Docker. All the code documents are accessible on Github.

Assemble the docker image and afterward you can test things locally on your own PC like this:

First start the image:

And then test to see if it works:

Or visit localhost:8080? in your browser. You should see a lot of JSON.

The next step is to push your image to the Google Container registry. For me, this is a simple command:

However, you may need to arrangement the docker confirmation first before you can do this. An elective technique is the utilization Google Cloud Build to make the picture; this may turn out better for you in the event that you can’t get the validation working.

Next you have to make a Cloud Run administration with this docker picture.

Open Cloud Run and click “Create service”

Name and adjust settings. You must give your service a name and configure a few other settings:

It is ideal to pick an area that is near where the greater part of the crowd for your destinations live. Checking the site speed for a UK site from Tokyo won’t give you similar outcomes as what your crowd get.

With the goal for you to call this administration from Google Sheets it must permit unauthenticated summons. In case you’re stressed over securing a lot the administration to keep others from utilizing it you should do this by (for instance) checking from an API mystery in the HTTP solicitation or something to that effect.

Next you should choose the holder you made before. You can type in the name in the event that you recall it or snap “Select” and pick it from the menu.

Then click “Show Advanced Settings” because there is further configuration to do.

You have to expand the memory assignment since Lighthouse tests need more than 256Mb to run. I have picked 1GiB here however you may require the greatest stipend of 2GiB for certain locales.

I have discovered that lessening the simultaneousness to 1 improves the dependability of the administration. This implies Google will consequently begin another compartment for every HTTP demand. The drawback is that this costs marginally more cash.

Snap “Create” and your Cloud Run administration will be prepared in a matter of seconds.

You can give it a quick test using the URL. For example:

Or visit in your browser.

The next step is to write some Appscript so you can use your new API from within Google Sheets.

Open a new Google Sheet and the open up the Appscript editor.

This will open a new tab where you can code your Google Sheets custom function.

The key idea here is to use the Appscript UrlFetchApp function to perform the HTTP request to your API. Some basic code to do this looks like this:

The last line restores the general presentation score into the sheet. You could alter it to restore something different. For instance to get the SEO score use result.categories.seo.score all things considered.

Or then again you can restore numerous segments of results by restoring a rundown like this:

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

Spare the record and afterward you will have a custom capacity accessible in your Google Sheet called LIGHTHOUSE.

The simplest method to begin with this is to duplicate my model Google Sheet and afterward update the code yourself to point at your own API and to restore the Lighthouse results you are generally keen on.

published by Shantun Parmar

Originally published at on November 17, 2020.




Hello! I’m Shantun Parmar. Software Developer with over 3 years of experience specializing in Backend development.

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium


Introduction to Microservices

Using Functional Selenium C# Tests for Load Testing w/ Microsoft Visual Studio

10 Things I Learned After 6 Months of Mob Programming

Change Java version on Mac 11 BigSur & persist it

Caught in Time 除暴 Oglądaj Online cały film

My first attempt with Terraform on GCP

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store


Hello! I’m Shantun Parmar. Software Developer with over 3 years of experience specializing in Backend development.

More from Medium

So you have just started using the Bitbucket git repository what should you pick up next?

The first Step. An isLoading report of self taught software development.

What is an API?

How I crack GSoC 2022