I usually use Python to transform WebPageTest or Page Speed Insights JSON results to CSVs for custom visualization.
But I was enthralled by Rick Viscomi’s “WebPageTest sorcery using Google Sheets” which essentially lets you work with JSON directly. I couldn’t get his solution to work right off the bat. But once I did, I agree: pulling JSON from the Web and working with it directly in Google Sheets is indeed magical 🪄!
So hopefully this tutorial helps save you some headaches.
1. Run a test in WebPageTest
First off, you need to run a test in WebPageTest and wait for the results.
Once the results page appears, grab the test ID, which is the hash in the address bar URL.
https://www.webpagetest.org/result/210630_AiDcXH_a17de95b32287d0a8c9c23afb0cee637/
# '210630_AiDcXH_a17de95b32287d0a8c9c23afb0cee637' is the test ID.2. Set up Google Sheets
Create or open a spreadsheet in Google Sheets, then rename it.
You can add a header row for the Test ID and the metric (i.e. JSON path) that you want to look up.
If you want to paruse the results JSON for metrics, just click on “View JSON result” in the WebPageTest results view. It’ll open up a page such as this:
https://www.webpagetest.org/jsonResult.php?test=210630_AiDcXH_a17de95b32287d0a8c9c23afb0cee637&pretty=1
3. Add a Custom Function
With Custom Functions, you can fetch content from the Web and perform whatever computations you can dream of.
In your Google Sheet, select the menu item Tools > Script editor.
Delete any code in the script editor and replace it with this:
/**
* Fetches a metric from WebPageTest.
*
* @param {string} testId The WebPageTest ID of the test.
* @param {string} metric The name of the metric (as it appears in the results).
* @return The value of the metric in the test results.
* @customfunction
*/
function WEBPAGETEST_RESULT(testId, metric) {
const results = JSON.parse(UrlFetchApp.fetch(`https://webpagetest.org/result/${testId}/?f=json`).getContentText());
return eval(`results.data.median.firstView${metric.replace(/^([^\[]+)/, ".$1")}`);
}Important:
Make sure you include JSDoc meta information – the text at the beginning of the snippet that looks like a CSS comment – so that the custom function appears in Sheet’s autocomplete. Without it, you won’t find your function again that easily!
Rename the Apps Script document, e.g. wpt, then click on the Save icon.
4. Use a Custom Function
Now go back to your Google Sheet and refresh it.
Select an empty cell where you want to display a metric value and enter =web and you’ll see the custom function WEBPAGETEST_RESULT appear in the autocomplete menu.
Now for the first argument (testId), select the test ID and add a $ before the column number. For the second argument (metric) select the cell containing the path to the metric whose value you want to display (e.g. [“chromeUserTiming.LargestContentfulPaint”]) and add a $ before the row number.
Then hit Enter to use the function. While it’s running, you’ll see “Loading…” in the cell.
Now you can copy and paste the cell containing the function for additional metrics, e.g. ["bytesIn"] or add additional test IDs and display the values for those.
MAGIC!
Keep in mind
As mentioned above, don’t forget to include the JSDoc meta info above the custom function. I spent hours trying to figure out why I couldn’t call the function in my Sheet when it was missing.
Also, every time you close the Sheet and reopen it, the functions will re-run. That means you’re repeatedly pulling data from the net. Depending on your WebPageTest setup, that could be up to 10 MB! That’s not only wasteful, it’s also slow.
You can reduce data transfer and speed things up by adding some parameters to the URL to fetch in the custom function (thanks to Tim Kadlec for that tip!):
// Instead of this: fetch(`https://webpagetest.org/result/${testId}/?f=json`)
fetch(`https://webpagetest.org/result/${testId}/?f=json&average=0&standard=0&runs=0&console=0&lighthouse=0&rv=0&requests=0`);I could get the JSON response down to 6.7 KB doing that 😎.