Just Show me a Spreadsheet: A Guide to Keeping Non-Tech Team Members Happy
Your technology may be in the cloud, but your business still runs on spreadsheets
By Nathan Acuff
We all like to think that we leverage the best technology tools to run our business, but deep within virtually every single business is a load-bearing spreadsheet — or a dozen load-bearing spreadsheets. Sometimes they aren’t even buried that deep.
Modern business tools have come a long, long way, and the best tools emphasize integration and aggregation as first-class features. This is great if you’ve invested the time to set things up and have sales, marketing, and C-teams that are comfortable with your business intelligence tools. This post isn’t about that happy path. This is about what happens when an account executive just needs some historical usage and really, really doesn’t want to sign up for yet another tool.
Business data in APM?
We use Instrumental for APM as well as for some business bits. Instrumenting higher-level concepts like usage and feature interaction in the same tool as our crunchy server stats has been a big win for us. As a result, we have some data that we could pull from our database or BI tool, given time to build and run the appropriate queries, but that gets messy quickly when you need to share and iterate on it with less technical team members. So, which tool do the less technical team members love? Spreadsheets.
Give me an example already!
Let’s go back to our account executive who wants to know about some client usage. I’m going to use our other company, DocRaptor, as an example because it has a very clear test/paid line and a very clear 1 document = 1 unit of usage model, but this applies anywhere. I know we have that usage data in Instrumental, so let’s get it out.
We’re going to use Google Sheets excellent custom script facility and the Instrumental Metrics API. Let’s write some code.
First, we set up a few things we’ll need for the query:
var ONE_DAY_IN_SECONDS = 60 * 60 * 24;
var ONE_MONTH_IN_SECONDS = ONE_DAY_IN_SECONDS * 30;
// the instrumental metrics api endpoint
var url = 'https://instrumentalapp.com/api/2/metrics/';// any instrumental metric expression, including wildcards and such
var expression = 'ts_sum(document_enqueued.user_188.test)';
//your project API key, which I’ve hidden on another sheet to make life easy
var api_key = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Keys").getRange(1, 1).getValue();// the parameters Instrumental needs to know what data you want
// Note that javascript thinks in millis while Instrumental thinks in seconds!
var now = new Date();
var start_time = (now.getTime() / 1000) - ONE_MONTH_IN_SECONDS;
var resolution = ONE_DAY_IN_SECONDS;
var duration = ONE_MONTH_IN_SECONDS;
Okay, so far so good. We could pull a lot of that out into arguments, and we will, but let’s keep going and build our request:
var query = "?start=" + start_time + "&resolution=" + resolution + "&duration=" + duration;
var request = url + encodeURIComponent(expression) + query;
var headers = { 'X-Instrumental-Token': api_key };
var response = UrlFetchApp.fetch(request, { headers: headers });var response_data = JSON.parse(response.getContentText());
var metric_data = response_data.response.metrics[0].values;Easy peasy. Now, we just transform Instrumental’s JSON response into a table:
var start = metric_data.start * 1000;
var step = metric_data.resolution * 1000;var results = metric_data.data.map(function(data_point, index) {
// data_point.s is the sum - this is usually, but not always, the value you’re looking for
return [new Date(start + (step * index)), data_point.s];
});
return results;
Okay, what does that look like? I threw a chart on there for fun.
That seemed too easy. What does that same data look like in Instrumental?
Not bad. Okay, now our AE is happy, but can we make their life even easier?
Variables?
What if we could just provide a spreadsheet function so users could specify what they wanted to find? It’s trivial to add some parameters to the custom function we created:
function INSTRUMENTAL_USAGE(user_id, input_start, input_days, input_type) {
var ONE_DAY_IN_SECONDS = 60 * 60 * 24;
var url = 'https://instrumentalapp.com/api/2/metrics/';
var expression = 'ts_sum(document_enqueued.user_' + user_id + '.' + input_type + ')';
var api_key = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Keys").getRange(1, 1).getValue();
var start_time = (input_start.getTime() / 1000);
var resolution = ONE_DAY_IN_SECONDS;
var duration = input_days * ONE_DAY_IN_SECONDS;
var query = "?start=" + start_time + "&resolution=" + resolution + "&duration=" + duration;
var request = url + encodeURIComponent(expression) + query;
var headers = { 'X-Instrumental-Token': api_key };
var response = UrlFetchApp.fetch(request, { headers: headers });
var response_data = JSON.parse(response.getContentText());
var metric_data = response_data.response.metrics[0].values;var start = metric_data.start * 1000;
var step = metric_data.resolution * 1000;
var results = metric_data.data.map(function(data_point, index) {
return [new Date(start + (step * index)), data_point.s];
});
return results;
}
Now, I slap =INSTRUMENTAL_USAGE(B1, B2, B3, B4) into a cell on that page, and, magic:
Now, anyone who can spreadsheet can pull whatever usage data they want into the environment they know and love.
Conclusion
With Google’s custom script feature, you can pull data directly from any tool you use that has a reasonable API and then interact with it just as you would any other data. I kept the Instrumental queries in my example to one result, but this function could easily be extended to pull multiple time series in one call. I don’t really want to use spreadsheets as an alternative UI to Instrumental, but it’s nice to know I could if needed.
