Get your Google PageSpeed score using VBA

If you are a SEO junkie like me then you cannot find enough ways to improve your website's loading speed. Checking your PageSpeed score is one way to do that.

Naturally I try to leverage VBA wherever I can. In this case, Google has an API for their online PageSpeed tool which we can call in VBA.

I created a sample workbook to demonstrate how you might use the API. It's a very basic implementation, returning only the PageSpeed score for a given website. You will need an API key from Google to use their online APIs. I have an API key but in the examples below it is left out. Visit the API Documentation to find out how to get one. Once you have it I believe you can use the same API key for any of their online APIs.

Code to Fetch PageSpeed Score

First we declare the base URL for our queries:

Const GPS_BASEURL As String = _
"https://www.googleapis.com/pagespeedonline/v1/runPagespeed?"

Unfortunately Google only returns a JSON response so we'll need some special parsing instead of handling the MSXML DOM. I did find what I consider to be an elegant solution, if I do say so myself. I wrote a very simple function that takes the URL and the API key and returns the raw PageSpeed score.

Function GetPageSpeedScore(url As String, apiKey As String) As Long

  Dim psurl As String
  Dim xml As Object
  Dim start_part As String
  Dim score As Long

  ' build URL and send XMLHTTP request
  psurl = GPS_BASEURL & "url=" & url & "&key=" & apiKey
  Set xml = GetMSXML
  
  If xml Is Nothing Then
    Exit Function
  End If
  
  With xml
    .Open "GET", psurl, False
    .send
  End With

  ' parse JSON response
  start_part = Split(xml.responsetext, "score"": ")(1)
  score = CLng(Split(start_part, ",")(0))

  GetPageSpeedScore = score
End Function

For the GetMSXML function visit MSXML Object Library Routines.

Using the Split function, we take the second element (the index starts at zero) to find the position of the score value. We use the Split function on the remaining string and take the first element. I found this technique in some PHP code and I like it better than using Instr and Mid, so I'll try to use it more often from now on.

PageSpeed workbook screenshot

There are some things the code doesn't do, for example it doesn't validate the URL or the API key. You could also hardcode the API key into the backend instead of needing to pass it in every time. I did that so you could reuse the function in any VBA environment.

I could see this becoming a more full featured tool, for example the entire JSON response could be parsed and returned. VBA has no native methods for parsing JSON, however, so it would require either an external library or some custom code.

Download sample workbook