Schedule your task in serverless style using Google App Script

Sometimes you want to automate a task which you do daily manually but you don’t have a server or a separate machine. If that is the problem then don’t worry Google App Script there to save you.

Our company has a lot of websites(products) and we try hard to give the best consumer experience in every aspect. That aspect might be UX or Customer care or UI or Performance or etc. We want to measure these or else we might have done a mistake accidentally due to that consumer is suffering. Usually, it happens a lot with respect to Performance because we release regularly. To measure this google provided a great tool that is PageSpeed Insights. Now it’s powered by Lighthouse.

We got a task like this measure every day what is the performance score of each important URL and if there is any drop inform the concern team. I know a bit about Google App Script so I thought why not I do this task using it. I am going to cover some part of that task.

Before we start there are great tutorials here about Google App Scripts. I learned a lot from them.

In this tutorial, we will cover how we can create a scheduler task which reads each URL present in spreadsheet and checks what is the Page Speed performance score today, sends mail to us and stores that score in the spreadsheet. Without wasting much time let’s start.

I hope you have a Gmail account. Open Gmail and go to your google drive or open this URL. Create a Blank Spreadsheet (New -> Google Sheets -> Blank spreadsheet)

Give a name to it. Now enter a couple of URLs for which you want to calculate Page Speed score like this.

Now interesting stuff going to start. We need to write a script to do our daily job. For that, we need the Script editor. To open it click on Tools then click on Script editor.

Now script for this spreadsheet will be opened in the new tab like this

Give a name to it and put a proper name to function also. We will write the code here. Now our job is to retrieve one by one URL from the spreadsheet
and get its page speed score.

For that first of all, we need to know about its API. For that, we need to read a bit here. Go to the Get Started section. Here the concept is simple we need to hit below URL with 2 get parameters(url, key).

https://www.googleapis.com/pagespeedonline/v5/runPagespeed

So the final URL will be

https://www.googleapis.com/pagespeedonline/v5/runPagespeed?url=https://developers.google.com&key=yourapikey

So now we need to create a key for ourselves. In that page, you can see GET A KEY button. Please click on it then you will get a popup like below

Now click on the dropdown then you will get an option “+ Create a new project”. Now choose/click on this option.

Enter a name for the project which you want to keep and then click on NEXT.

Now you will get the API key in the popup and one project is created at developers.google.com end.

So now choose a URL for which you want to see the PageSpeed score. Here for example purpose, we will consider it as developers.google.com & we got key already. So let’s hit the URL. You will get output like this.

We don’t need all these details we just need a score as it displays at the PageSpeed Insights UI.

If you observe the whole JSON which we got through the API URL which is like

https://www.googleapis.com/pagespeedonline/v5/runPagespeed?url=https://developers.google.com&key=yourapikey

You will see a section “performance”. There you get a score which is similar to it. We will take that only consideration.

This URL/API is like GraphQL API. So instead of getting this big/fat response, we can get the precise response too for that we need to add a couple of things in the URL so that this API understand what exactly we want.

Here we need desktop/mobile sore so for example, we will consider the only desktop this thing we can send as strategy parameter and we need only the score which is under performance, categories, lighthouseResult for this we need to send our requirement in fields parameter. I got to know about this by experimenting bit here. Now the API url will become like this

https://www.googleapis.com/pagespeedonline/v5/runPagespeed?url=https://developers.google.com&strategy=desktop&fields=lighthouseResult/categories/performance/score&key=yourapikey

Now you will get output like this

Now everything set. We need to start writing our code. Let’s go back to our script editor. Let’s write the code for reading URL one by one.

function getPageSpeedScoreAndMailToMe() {
  //Get current SpreadSheet
  var spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
  //Get the Sheets in SpreadSheet
  var sheets = spreadSheet.getSheets();
  for(var sheetIndex = 0; sheetIndex < sheets.length; sheetIndex++) {
    //Get a sheet
    var sheet = sheets[sheetIndex];
    var sheetRange = sheet.getDataRange();
    //Get the data in the sheet
    var sheetData = sheetRange.getValues();
    //We want to skip the header that's why starting from row 1
    for(var rowIndex = 1; rowIndex < sheetData.length; rowIndex++) {
      var row = sheetData[rowIndex];
      Logger.log(row);
    }
  }
}

This code prints each row in the spreadsheet. This code will run by clicking on the play button.

You can view logs once it ran. It’s at View menu.

When you run this code you need to give permission to access for spreadsheets. You will get like this

Click on Review Permissions then

Click on your email id then

Click on Advanced. We are at the development stage so our script is not verified so this is common.

Click on your script link. Here Page Speed Log Script is our script that’s why we get the link like Go to Page Speed Log Script (unsafe)

Then click Allow button. Now your script works like a charm. You can check logs.

It’s time to get the score for each URL that can be done through this code

//Prepare the URL and get the score
function getPageSpeedScoreForAURL(url, strategy) {
  var apiKey = 'yourapikey';
  var strategyKey = '&strategy=';
  var fieldsKeyAndValue = '&fields=lighthouseResult/categories/performance/score&key=';
  var pageSpeedBaseUrl = 'https://www.googleapis.com/pagespeedonline/v5/runPagespeed?url=';
  
  var url = pageSpeedBaseUrl + url + strategyKey + strategy + fieldsKeyAndValue + apiKey;
  var response = UrlFetchApp.fetch(url);
  var speedScore = JSON.parse(response).lighthouseResult.categories.performance.score * 100;
  Logger.log(strategy + ' speed score of ' + url + ' ' + speedScore);
  
  return speedScore;
}

Now everything set we just need to connect the dots and write code for sending mail.

function getPageSpeedScoreAndMailToMe() {
  //Get current SpreadSheet
  var spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
  //Get the Sheets in SpreadSheet
  var sheets = spreadSheet.getSheets();
  for(var sheetIndex = 0; sheetIndex < sheets.length; sheetIndex++) {
    //Get a sheet
    var sheet = sheets[sheetIndex];
    var sheetRange = sheet.getDataRange();
    //Get the data in the sheet
    var sheetData = sheetRange.getValues();
    //We want to skip the header that's why starting from row 1
    for(var rowIndex = 1; rowIndex < sheetData.length; rowIndex++) {
      var row = sheetData[rowIndex];
      //URL present in first column of every row
      var siteURL = row[0];
      //Get the scores
      var desktopPageSpeedScore = getPageSpeedScoreForAURL(siteURL, 'desktop');
      var mobilePageSpeedScore = getPageSpeedScoreForAURL(siteURL, 'mobile');
      //Set the scores in respective columns
      sheet.getRange(rowIndex+1, 2).setValue(desktopPageSpeedScore);
      sheet.getRange(rowIndex+1, 3).setValue(mobilePageSpeedScore);
      //Send mail to the respective people with the scores
      MailApp.sendEmail('yourmailid@maildomain.com', 'Page Scores for URL ' + siteURL, 'Desktop PageSpeed Score: ' + desktopPageSpeedScore + '\nMobile PageSpeed Score: ' + mobilePageSpeedScore);
    }
  }
}

//Prepare the URL and get the score
function getPageSpeedScoreForAURL(url, strategy) {
  var apiKey = 'yourapikey';
  var strategyKey = '&strategy=';
  var fieldsKeyAndValue = '&fields=lighthouseResult/categories/performance/score&key=';
  var pageSpeedBaseUrl = 'https://www.googleapis.com/pagespeedonline/v5/runPagespeed?url=';
  
  var url = pageSpeedBaseUrl + url + strategyKey + strategy + fieldsKeyAndValue + apiKey;
  var response = UrlFetchApp.fetch(url);
  var speedScore = JSON.parse(response).lighthouseResult.categories.performance.score * 100;
  Logger.log(strategy + ' speed score of ' + url + ' ' + speedScore);
  
  return speedScore;
}

It’s time to run the code. Now we are accessing mail service too so it will ask again for permissions just like when it asked for spreadsheet service permission.

Congrats!!! Your code is ready and you will get the mail too once you run it.

Only one thing left you to need to make it as a Cron job or Scheduler job. To do that you need to click the Timer button.

Now a separate tab will be opened. There you need to add the trigger. For this project, we didn’t put a name so you will get as untitled project don’t worry we can edit later.

Click on Add Trigger. You will get a popup

We need to make it time trigger so we will change the Select event source section to Time-driven. Then select Select type of time based trigger to Day timer. Then select Select time of day according to your comfort.

Now Click on Save. Now you will get like this

Congrats!!! Your Scheduler job ready. You can’t get everything for free, Google put some conditions around this. You will find those details here.

Peace. Happy Coding.