Art of the Smart

Metrics Collection From "Copied" Google Sheets

Categories: [coding], [spreadsheets]
Tags: [apps-script], [spreadsheets]

To gather metrics from copied spreadsheets in your org, follow this pattern to retain tight permissions while still automatically gathering usage data.

Got another good pattern for you Apps Script users out there. Suppose you want to create a tool in your company that does something and you distribute it by having users make a copy of it. They’re going to have their own local version that you do not have access to.

So how do you collect usage metrics? Sure, you can measure URL usage to see how many copies are made, and you can use Google Analytics on the tool’s documentation, but nothing compares to knowing how many times they opened it or did certain actions.

Enter this handy solution.

To do this, you’ll need three things:

1. Your tool - which has scripts embedded to dispatch metrics messages. Only your user will have access to their copy of your tool.
2. A metrics sheet - that collects and analyzes the data you want. Only you have access to this sheet.
3. A form - that is visible to your audience, who’s sole job is to collect metrics from #1 and pipe them to #2.

We do it this way for two main reasons. The first is that the alternative, urlFetchApp operates outside the confines of your Google Workspaces domain. So it can’t write data directly via Webapp or other web hooks. It’s gotta be within the GSuite space. The second is that you want to keep your metrics sheet more private. Sure, the user’s copy of your tool could write directly to your metrics sheet, but then that user will also have read access to it. That symmetry of access isn’t always OK.

This method prioritizes (a) letting a workbook you don’t own (b) write data to a workbook they don’t own. It comes with a big caveat though; it requires permissions. So if your tool is just a spreadsheet template, you still need the user to accept some scripting permissions.

To set it up, you’d begin by adding code like this from StackOverflow user Louis Teo, which lets you inject triggers and other scripting into your copy-able Spreadsheet Tool.

function scriptSetup() {
  createOnEditFunction();
}

function createOnEditFunction() {
  const ss = SpreadsheetApp.getActive();
  ScriptApp.newTrigger('onEdit')
    .forSpreadsheet(ss)
    .onOpen()
    .create();
}

function onEdit(e) {
  //your stuff here
}

Then you’d create a new form with settings like multiple submissions, no editing, public form with private results. All vanilla stuff.

Then in your code where you want to actually dispatch the metrics, you’d add code like this from StackOverflow user Ozan. It lets you programmatically create and submit a Form response containing the metrics information you want to aggregate.

var formID = row[24]; 
var form = FormApp.openById(formID);
var formResponse = form.createResponse();
// get items of form and loop through
var items = form.getItems();
for (index = 0; index < a.length; ++index) {
  var item = items[index]
  // Cast the generic item to the text-item class. You will likely have to adjust this part. You can find the item classes in the documentation. https://developers.google.com/apps-script/reference/forms/item-type.
  if (item.getType() == 'TEXT') {
    var textItem = item.asTextItem();
    var itemresponse = textItem.createResponse('Core Teachers');
    formResponse.withItemResponse(itemresponse);  
  }
}
formResponse.submit();