Search our website

From the blog

Tracking Server Response Times with the Google Spreadsheets API and asp.net

Author · Steve Gourley
Published · Oct 25, 2012
Last modified · Mar 20, 2024
Category · Developer
Read time · 3 min

We've been noticing some speed issues with our mapserv machines recently and are trying to figure out the issue. In an effort to have a little insight into the when, where, and why's, we created a very rudimentary tracking tool that has quite a bit of potential and was fun to make. This will hopefully give us and our hosting and networking groups a better idea of what could be the root cause.

The abridged version of what we did was to create a console application scheduled to run every 30 minutes to request a page and report the response times to a google spreadsheet. We then created a chart in the spreadsheet that can be published on our wiki and visually consulted.

Google makes it really easy to use their app API's. They've developed asp.net assemblies that you can import via NuGet to get up and running quickly. Pair that with the documentation and tutorials and it's really easy to get started. The NuGet package that I used for this example is the Google.GData.Spreadsheets. My packages.config is below in the gist.

The first speed bump I encountered was choosing how to authenticate my application. Google recommends OAuth2 but that doesn't really fit my needs as far as I understand. I want this application to run on my behalf without any human interaction necessary to authorize it. I chose to use ClientLogin even though it is deprecated.

ClientLogin is pretty simple and allowed me access to my Google Drive documents in a few minutes.

Next I created a Spreadsheet through a browser to capture the data on our page response times. I named this document something useful so I could query for it through the SpreadSheetFeed. I also created a header row of values so when I insert rows, the data would map to the correct cell. If you do not create a header row you will get a 400 bad request responses when trying to insert. Therefore, if you wanted to create your spreadsheet via code, you'd have to use the Cell feed to create the header row, then use the spreadsheet feed to insert your values. For the sake of speed, I created the sheet and the header row manually. This gotcha was the only non trivial step when using this API. The error message was not very helpful and the documentation wasn't super clear.

After that, I was able to create a chart through the browser and publish it. It gives you a script tag you can inject or an image you can place to view online that is always up to date.

Sample Code