Sending Metric Data from Google Sheets

A quick guide on how to use Google's App Script Editor to take data from a Google Sheet and send it to TelemetryTV

Gersham Meharg avatar
Written by Gersham Meharg
Updated over a week ago

Google Sheets is a powerful tool for collecting and managing data, and in this guide we're going to show you how to extrapolate that data and send it to TelemetryTV as a metric

(This guide uses Google Sheets as a simple Metrics Collection Agent).


To put it simply: we are going to use triggers to trigger scripts in the Google Sheets Script Editor. These will gather data from your spreadsheet and send it to TelemetryTV.

Our Example:

Click the above image to view our example. If you create your own copy, you'll be able to view the scripts we used to send this mock data as well. Unfortunately, at this time copying the sheet won't copy over the triggers necessary for automation, so if you'd like to run tests with this mockup you'll have to follow the instructions later on in this guide.

In our mockup Google Sheet, we'll be sending the top player and stat for points, rebounds, and assists. We'll show two methods: where the data is sent on a timer, and where the data is sent when relevant cells are edited.

Good Practice:

In the same way that you label columns, rows, tables, and other data sets on a spreadsheet, it's a good idea to do the same for specific cells/ranges you will be using in a script. This is not a required step, but will make the code easier to read and, if the positions of the data you want to send gets moved, you won't have to change the code at all, just update values in the named range.

Open the 'Data' menu, then click 'Named ranges...' Click '+ Add a range' in the newly opened dropdown to add each range you'd like to name.

Scripts and the Script Editor

In order to gather and send our stats to TelemetryTV, we need to write a couple scripts. It is not necessary to use Google's in-app Script Editor, but it is the simplest way and is more than suitable for the example we're running through in this guide.
The scripts are available if you copy the sheet we've provided, and if your sheets require more complicated scripts, you can get more info from the Google Apps Script API Reference. We'll also include our scripts as text at the bottom of this guide.

To get to the Script Editor, open the 'Tools' menu and click 'Script editor'

Gathering Data

Method One: When a cell is edited
When a Google Sheet is edited, it fires a specific trigger (that we'll set up later), that runs a user-specified function.

Notes: The trigger passes an event 'e' to our function. From that event, we can grab the cell that was edited, and the sheet the cell is from. We check if that cell is within one of the relevant ranges we named earlier, and if it is, we grab that data and send it. (We'll get to our sendData function later).

Our function to check to see if a cell is within range.

Method Two: On a set time interval
When sending on a set time interval, you can more comfortably send larger batches of data. This method also pairs nicely with metric buckets if you'd like to aggregate the data for viewing in some kind of line chart widget.

Notes: Instead of being passed an event, we grab our sheet using the line:

SpreadsheetApp.getActiveSheet()

This is fine for our example, but if your project and/or spreadsheet has multiple sheets you might have to use a function that opens a specific sheet.


Sending the Data to TelemetryTV

Notes: Our sendData function is fairly straightforward.  We use Google's built-in UrlFetchApp.fetch() to make the request after formatting the data.
(Make sure to replace the API token in the Authorization header with
your own).


Triggers

Now that we've set up our scripts, we need to automate them! As we touched on earlier, triggers are what we use to tell Google Sheets to run our scripts. 

From within the Script Editor, open the 'Edit' menu, and click 'Current project's triggers to access the trigger screen.

We'll create two triggers: one to run scripts when the spreadsheet is edited, and another to run a script once per day.

Click the  '+ Add Trigger' button to add triggers.

On the left: we add the 'Spreadsheet - On edit' trigger, and tie it to our 'sendCellDataOnEdit' function.
On the right: we add the the 'Time-driven' trigger, and trie it to our 'sendSheetDataAtTime' function.


And that's it!

We've created triggers to run scripts that gather our data, then send it to TelemetryTV!
Below, we'll add our functions again as plain text for quick copying.

function sendData(data) {
  var url = 'https://metrics.telemetrytv.com/metrics'
 
  var headers = {'Authorization': 'Token abcdef0123456789'}; // Use your API token
  var formattedData = JSON.stringify(data)
  var options = {
    'method':      'post',
    'contentType': 'application/json',
    'headers':     headers,
    'payload':     formattedData,
  };
 
  var response = UrlFetchApp.fetch(url, options);
}

function sendSheetDataAtTime() {
  var sheet = SpreadsheetApp.getActiveSheet()
   
  var data = {
    '$top_points': sheet.getRange('topPoints').getValue(),
    '$top_points_player': sheet.getRange('topPointsPlayer').getValue(),
    '$top_rebounds': sheet.getRange('topRebounds').getValue(),
    '$top_rebounds_player': sheet.getRange('topReboundsPlayer').getValue(),
    '$top_assists': sheet.getRange('topAssists').getValue(),
    '$top_assists_player': sheet.getRange('topAssistsPlayer').getValue()
  }
 
  sendData(data)
}

function sendCellDataOnEdit(e) {
  var sheet = e.source
  var cell = e.range
  var data
 
  if (isCellWithinRange(cell,sheet.getRange('points'))) {
    data = {
      '$top_points': sheet.getRange('topPoints').getValue(),
      '$top_points_player': sheet.getRange('topPointsPlayer').getValue()
    }
  } else if (isCellWithinRange(cell,sheet.getRange('rebounds'))) {
    data = {
      '$top_rebounds': sheet.getRange('topRebounds').getValue(),
      '$top_rebounds_player': sheet.getRange('topReboundsPlayer').getValue()
    }
  } else if (isCellWithinRange(cell,sheet.getRange('assists'))) {
    data = {
      '$top_assists': sheet.getRange('topAssists').getValue(),
      '$top_assists_player': sheet.getRange('topAssistsPlayer').getValue()
    }
  } else {
    return
  }
 
  sendData(data)
}

function isCellWithinRange(cell, range) {
  var cellCol = cell.getColumn()
  var cellRow = cell.getRow()
 
  var rangeColStart = range.getColumn()
  var rangeColEnd = rangeColStart + range.getWidth() - 1
  var rangeRowStart = range.getRow()
  var rangeRowEnd = rangeRowStart + range.getHeight() - 1
 
 
   return ( cellCol >= rangeColStart &&
            cellCol <= rangeColEnd   &&
            cellRow >= rangeRowStart &&
            cellRow <= rangeRowEnd )
}
Did this answer your question?