Counting uniques: updated

I previously posted a script I wrote to count uniques. It’s kind of a crappy script. It can only draw values from an array (as opposed to multiple selected values in the sheet) and it just leaves 2 arrays, 1 with each unique value, and a corresponding array with the count of each respective value. So, 2 arrays of numbers. Not very useful unless you are 100% aware of and confident about what you’re doing, and I certainly am not. So here’s a better script.

Use this in a google apps script bound to a spreadsheet. With the onOpen function, a menu will appear in the spreadsheet itself and you can run the function from within the document (as opposed to running it from the bound code.)

This will log each value and its corresponding count pretty clearly. Unfortunately, logs can only be viewed from the bound script editor, so I will update this code with something that will show an alert or HTML file within the spreadsheet so one can run the function and get the results without being forced to open the script editor.

function onOpen(){
var ui = SpreadsheetApp.getUi();
ui.createMenu('Counting')
 .addItem('Count instances', 'countSelected')
.addToUi();
}

function countSelected(){
var sheet = SpreadsheetApp.getActive().getActiveSheet();
var range = sheet.getActiveRange();
var row = range.getRow();
var col = range.getColumn();
var lastRow = range.getLastRow()
var numRows = range.getNumRows();

var rclr = 'Start row: ' + row + ', Column: ' + col + ', Last row: ' + lastRow + ', Number of rows: ' + numRows;
Logger.log(rclr)

var selectedVals = []
for(var a = 0; a < numRows; a++){
var thisRow = row + a;
var thisValue = sheet.getRange(thisRow, col).getValue();
selectedVals.push(thisValue)
}

var arr = selectedVals;
arr.sort();
var uniques = [];
var instCount = [];
var prev = '';
var arrCount = arr.length;
for(var a = 0; a < arrCount; a++){
if (arr[a] !== prev ) {uniques.push(arr[a]); instCount.push(1)}
else {instCount[instCount.length-1]++}
var prev = arr[a];}


for (var b = 0; b < uniques.length; b++){
 var variable = uniques[b];
 var instanceCount = instCount[b];
 Logger.log('There are ' + instanceCount + ' instances of the variable ' + variable);
 }
}