- Create a new Google Sheets file
- Get a PageSpeed API Key
- Go here https://developers.google.com/speed/docs/insights/v5/get-started
- Click on “Get a key”
- Go to Extensions → Apps Script
- Remove all the code and copy-paste the following instead
- Within the App Script project replace key value with the API key
- Save your project and go back to your sheet
- Create a drawing and assign script analyzeUrlsWithPageSpeed
- Click on Run. It's done.
The code to paste in Apps Script inside Google Sheets:
function analyzeUrlsWithPageSpeed() {
// Enter the name of the sheet and the column where the URLs are located
var sheetName = "Sheet1";
var urlColumn = "URL";
// Enter your PageSpeed Insights API key
var apiKey = "ADD YOUR KEY HERE";
// Get the sheet and range of URLs
var sheet = SpreadsheetApp.getActive().getActiveSheet();
var urls = sheet.getRange(2, 1, sheet.getLastRow()-1, 1).getValues();
// Define the required column headers
var requiredHeaders = ["LCP (ms)", "FID (ms)", "CLS (ms)", "FCP (ms)", "Performance Score"];
// Find the first empty column to the right of the existing data
var numColumns = sheet.getLastColumn();
var headersRow = sheet.getRange(1, 1, 1, numColumns).getValues()[0];
// Get the indices of the columns for each metric
var lcpIndex = headersRow.indexOf("LCP (ms)") + 1;
var fidIndex = headersRow.indexOf("FID (ms)") + 1;
var clsIndex = headersRow.indexOf("CLS (ms)") + 1;
var fcpIndex = headersRow.indexOf("FCP (ms)") + 1;
var perfIndex = headersRow.indexOf("Performance Score") + 1;
// Loop through each URL and analyze it with PageSpeed Insights
for (var i = 0; i < urls.length; i++) {
var url = urls[i];
if (sheet.getRange(i + 2, lcpIndex).getValue() == "") {
var pageSpeedUrl = "https://www.googleapis.com/pagespeedonline/v5/runPagespeed?url=" + encodeURIComponent(url) + "&key=" + apiKey;
try{
var response = UrlFetchApp.fetch(pageSpeedUrl);
var data = JSON.parse(response.getContentText());
// Get the metrics from the response
var fcp = data["loadingExperience"]["metrics"]["FIRST_CONTENTFUL_PAINT_MS"]["percentile"];
var fid = data["loadingExperience"]["metrics"]["FIRST_INPUT_DELAY_MS"]["percentile"];
var cls = data["loadingExperience"]["metrics"]["CUMULATIVE_LAYOUT_SHIFT_SCORE"]["percentile"]/100;
var lcp = data["loadingExperience"]["metrics"]["LARGEST_CONTENTFUL_PAINT_MS"]["percentile"]
var perf = data["lighthouseResult"]["categories"]["performance"]["score"] * 100
// Write the metrics to the sheet
sheet.getRange(i + 2, lcpIndex).setValue(lcp);
sheet.getRange(i + 2, fidIndex).setValue(fid);
sheet.getRange(i + 2, clsIndex).setValue(cls);
sheet.getRange(i + 2, fcpIndex).setValue(fcp)
sheet.getRange(i + 2, perfIndex).setValue(perf);
} catch{Logger.log("error")}
}
}}
