Using PageSpeed Insights API with Excel VBA (VBA PageSpeed)

Google API are very useful and easy to use. Especially since queries are sent via GET and results are provided in JSON which is easy to scrape, from which data can be easily extracted. I always like to play with Google API as Google has such a vast range of different API addressing almost any area of the Internet.

From time to time I intend to share different examples of uses of the Google API using VBA as I think it may be of particular interest to some of my audiences. So let’s jump right in to the VBA PageSpeed guide.

What is the Google PageSpeed Insights API?

The Google PageSpeed Insights API runs an analysis on the page at the specified URL, and returns:

PageSpeed scores
Information on the URL resources (JS, CSS images)
Lists suggestions to make that page faster, and other information.

As you can see this information may be of particular interest to SEO experts, website admins or just a regular blogger like myself. There are literally multitudes of websites out living off this single Web API by sharing SEO and website stats with their users. Why use the middle man when you can easily use this Web API call yourself, and more easily even in Excel!

If you want to play around with the Google PageSpeed Insights API directly go here:
https://developers.google.com/apis-explorer/#p/pagespeedonline/v2/pagespeedonline.pagespeedapi.runpagespeed

The code (VBA PageSpeed)

Let’s start with defining some useful objects we will need for the procedure:

Enum Strategy
    Desktop
    Mobile
End Enum

Type PageSpeed
    responseCode As Long
    speedScore As Long
    'PageStats
    pageStats_numberResourses As Long
    pageStats_numberHosts As Long
    pageStats_totalRequestBytes As Long
    pageStats_numberStaticResources As Long
    pageStats_htmlResponseBytes As Long
    pageStats_cssResponseBytes As Long
    pageStats_imageResponseBytes  As Long
    pageStats_javascriptResponseBytes As Long
    pageStats_otherResponseBytes  As Long
    pageStats_numberJsResources As Long
    pageStats_numberCssResources As Long
End Type

Strategy – this enumeration lists the two possible options. Mobile – analyzing the URL for mobile devices, and Desktop – analyzing the URL for desktop devices. Pretty straightforward.

PageSpeed – this is the (partial) result of the API call. In this example I am only extracting the general pagespeed information like:

Response code – read here how to interpret these codes. Generally you would prefer this to be 200 (OK)
Speed Score – how well the URL scores in terms of speed. Read more here.
Other – number of page resources, JS/CSS/image files etc.

I intentionally ignored the other tons of info provided by the Google PageSpeed API response. You can always extract this data yourself by building up the VBA code and creating a regular expression that will match what you need.

The GetPageSpeed function

Public Function GetPageSpeed(url As String, analysisStrategy As Strategy, filterThirdPartyResources As Boolean) As PageSpeed
    Dim callURL As String, pageSpeedRes As PageSpeed, objHTTP As Object
    callURL = "https://www.googleapis.com/pagespeedonline/v2/runPagespeed?url=http%3A%2F%2F" & url & "&filter_third_party_resources=" & IIf(filterThirdPartyResources, "true", "false") & "&strategy=" & IIf(analysisStrategy = Desktop, "desktop", "mobile")
    Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
    objHTTP.Open "GET", callURL, False
    objHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
    objHTTP.send ("")
    If InStr(objHTTP.responseText, """SPEED""") = 0 Then GoTo ErrorHandl
    '---Extract PageSpeed results---
    On Error Resume Next
    Dim regex As Object: Set regex = CreateObject("VBScript.RegExp"): regex.Global = False
    regex.Pattern = """responseCode"".*?([0-9]+)": Set matches = regex.Execute(objHTTP.responseText)
    pageSpeedRes.responseCode = CLng(matches(0).SubMatches(0))
    regex.Pattern = """score"".*?([0-9]+)": Set matches = regex.Execute(objHTTP.responseText)
    pageSpeedRes.speedScore = CLng(matches(0).SubMatches(0))
    regex.Pattern = """numberResources"".*?([0-9]+)": Set matches = regex.Execute(objHTTP.responseText)
    pageSpeedRes.pageStats_numberResourses = CLng(matches(0).SubMatches(0))
    regex.Pattern = """numberHosts"".*?([0-9]+)": Set matches = regex.Execute(objHTTP.responseText)
    pageSpeedRes.pageStats_numberHosts = CLng(matches(0).SubMatches(0))
    regex.Pattern = """totalRequestBytes"".*?([0-9]+)": Set matches = regex.Execute(objHTTP.responseText)
    pageSpeedRes.pageStats_totalRequestBytes = CLng(matches(0).SubMatches(0))
    regex.Pattern = """numberStaticResources"".*?([0-9]+)": Set matches = regex.Execute(objHTTP.responseText)
    pageSpeedRes.pageStats_numberStaticResources = CLng(matches(0).SubMatches(0))
    regex.Pattern = """htmlResponseBytes"".*?([0-9]+)": Set matches = regex.Execute(objHTTP.responseText)
    pageSpeedRes.pageStats_htmlResponseBytes = CLng(matches(0).SubMatches(0))
    regex.Pattern = """cssResponseBytes"".*?([0-9]+)": Set matches = regex.Execute(objHTTP.responseText)
    pageSpeedRes.pageStats_cssResponseBytes = CLng(matches(0).SubMatches(0))
    regex.Pattern = """imageResponseBytes"".*?([0-9]+)": Set matches = regex.Execute(objHTTP.responseText)
    pageSpeedRes.pageStats_imageResponseBytes = CLng(matches(0).SubMatches(0))
    regex.Pattern = """javascriptResponseBytes"".*?([0-9]+)": Set matches = regex.Execute(objHTTP.responseText)
    pageSpeedRes.pageStats_javascriptResponseBytes = CLng(matches(0).SubMatches(0))
    regex.Pattern = """otherResponseBytes"".*?([0-9]+)": Set matches = regex.Execute(objHTTP.responseText)
    pageSpeedRes.pageStats_otherResponseBytes = CLng(matches(0).SubMatches(0))
    regex.Pattern = """numberJsResources"".*?([0-9]+)": Set matches = regex.Execute(objHTTP.responseText)
    pageSpeedRes.pageStats_numberJsResources = CLng(matches(0).SubMatches(0))
    regex.Pattern = """numberCssResources"".*?([0-9]+)": Set matches = regex.Execute(objHTTP.responseText)
    pageSpeedRes.pageStats_numberCssResources = CLng(matches(0).SubMatches(0))
    Set regex = Nothing: Set objHTTP = Nothing
    '---Return result---
    GetPageSpeed = pageSpeedRes
    Exit Function
ErrorHandl:
End Function

Ok. Now let’s put it to the test:

Sub TestPageSpeed()
    Dim res As PageSpeed
    res = GetPageSpeed("www.google.com", Desktop, True)
    Debug.Print "Responsecode: " & res.responseCode
    Debug.Print "Speedscore: " & res.speedScore
    Debug.Print "Number of resources: " & res.pageStats_numberResourses
    Debug.Print "Number of hosts: " & res.pageStats_numberHosts
    Debug.Print "Total request bytes: " & res.pageStats_totalRequestBytes
    Debug.Print "Number of static resources: " & res.pageStats_numberStaticResources
    Debug.Print "HTML response bytes: " & res.pageStats_htmlResponseBytes
    Debug.Print "CSS response bytes: " & res.pageStats_cssResponseBytes
    Debug.Print "Image response bytes: " & res.pageStats_imageResponseBytes
    Debug.Print "JS response bytes: " & res.pageStats_javascriptResponseBytes
    Debug.Print "dOther response bytes: " & res.pageStats_otherResponseBytes
    Debug.Print "Number of JS resources: " & res.pageStats_numberJsResources
    Debug.Print "Number of CSS resources: " & res.pageStats_numberCssResources
End Sub

The results for Google (obviously speed score = 99!!!).