How can I compare the values in one column to the values in other columns?

Question

If I have a spreadsheet where people go on and rank songs (each song has its own row and each user has their own column), how can I write a script to iterate over each user column and then compare the individual user's rating of each song to the column that is the input?

The goal is to output the column with rankings most similar to the input column's rankings (ideally this should be an iterative recursive function with space complexity of O(1) and time complexity of O(log(n)).

I would think to make a function that produces a formula to be used like =FINDMOSTSIMILAR(<USER>) that outputs the value of the first row in the output column, but I'm not sure where to begin. I have some experience in JavaScript and I know Google Apps Script is based on JS, but I don't know how to do this function in GAS.

Spreadsheet

Answer 1

I tried to give you a start. Go to sheet, click Tools > Script Editor. Paste this code.

To find similar user, what this code does is, between input user and any other user, calculate differences for every song ratings and add them to produce a value. Do this for every user in sheet. Then find the lowest of differences and output which user produces this value. This logic is separated in compare function and you can change it to suit your needs.

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  // Or DocumentApp or FormApp.
  ui.createMenu('Find Similar User')
      .addItem('Similar User', 'showPrompt')
      .addToUi();
}

// this configuration is based on current sheet names and formatting
// if sheet names change, change needed here
// if track title and user columns change, change needed here
var config = {
  // sheet name: [ col of track title, col of 1st user, col of last user ]
  "UV6": [6, 9, 28],
  "Midnight Underground": [7, 11, 28],
  "Furious Fap February": [8, 12, 23],
  "March Masturbation Madness": [7, 11, 31]
};

function showPrompt() {
  var ui = SpreadsheetApp.getUi(); // Same variations.

  var result = ui.prompt(
    'Find similar user.',
      'Enter user name:',
      ui.ButtonSet.OK_CANCEL);

  // Process the user's response.
  var button = result.getSelectedButton();
  var text = result.getResponseText();
  if (button == ui.Button.OK) {
    // User clicked "OK".
    text = text.trim();
    if (!text) return;
    FINDMOSTSIMILAR(text);
  } else if (button == ui.Button.CANCEL) {

  } else if (button == ui.Button.CLOSE) {

  }
}

function FINDMOSTSIMILAR(username) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var aSheet = ss.getActiveSheet();
  var aSheetName = aSheet.getName();

  // get positions from config
  if (!config[aSheetName]) return;
  var userNamesColsStart = config[aSheetName][1];
  var userNamesColsSpan = config[aSheetName][2] - config[aSheetName][1] + 1;
  var titleColStart = config[aSheetName][0];

  // read user names
  var users = aSheet.getRange(1,userNamesColsStart,1,userNamesColsSpan).getValues()[0];
//  Logger.log(users);

  // read title col
  var tArr = aSheet.getRange(2,titleColStart,aSheet.getLastRow()-2,1).getValues();
//  Logger.log(tArr);

  // data structure
  var DS = {};
  users.forEach(function(h, i) {
    var obj = {};
    var colValues = aSheet.getRange(2,userNamesColsStart+i,aSheet.getLastRow()-2,1).getValues();
    tArr.forEach(function(v, i) {
      obj[i] = colValues[i][0];
    });
    DS[h] = obj;
  });
//  Logger.log(DS);

  var target = DS[username];
  delete DS[username];

  var results = [];

  Object.keys(DS).forEach(function(user) {
    var obj = {};
    obj.prop = username+'__'+user;
    obj.value = compare(target, DS[user]);
    results.push(obj);
  });

  // sort based on difference values
  results.sort(comp);
  Logger.log(results);

  // user with lowest difference is answer
  var similarUser = results[0].prop.split('__')[1];
  Logger.log(similarUser);

  try {
    ss.getActiveCell().setValue([similarUser]);
  } catch(e) {}

}

function comp(a, b) {
  if (a.value < b.value) return -1;
  else if (a.value > b.value) return 1;
  else return 0;
}

// this takes a difference of two users ratings for the same song
// accumulate all the differences for all songs into a single value
// change here how to compare 2 song ratings
function compare(target, user) {
  var v = 0;
  Object.keys(target).forEach(function(key, i) {
    v += Math.abs(target[key] - user[key]);
  });
  return v;
}