Google Spreadsheet ranking values

Question

Im currently setting up a decision matrix with Google Spreadsheet and I want to implement a ranking feature for the results. I wouldnt do this, if there were only 4 options, but in my case there are like 32 options available...

I tried to do this with the google script api, but the result is kinda disappointing:

function Ranking() {
var sheet = SpreadsheetApp.getActiveSpreadsheet();  
var alldata = sheet.getDataRange().getValues();
var data = [];
var j = 8;
for (var i = 0; i <= 31; i++) {
data [i] = alldata[18][j];
j= j+2;
}
var sorted = data.slice().sort(function(a,b){return b-a});
var ranks = data.slice().map(function(v){return sorted.indexOf(v)+1});
sheet.getRange("I20").setValue(ranks[0]);
sheet.getRange("K20").setValue(ranks[1]);
sheet.getRange("M20").setValue(ranks[2]);
sheet.getRange("O20").setValue(ranks[3]);
sheet.getRange("Q20").setValue(ranks[4]);
sheet.getRange("S20").setValue(ranks[5]);
sheet.getRange("U20").setValue(ranks[6]);
sheet.getRange("W20").setValue(ranks[7]);
sheet.getRange("Y20").setValue(ranks[8]);
sheet.getRange("AA20").setValue(ranks[9]);
sheet.getRange("AC20").setValue(ranks[10]);
sheet.getRange("AE20").setValue(ranks[11]);
sheet.getRange("AG20").setValue(ranks[12]);
sheet.getRange("AI20").setValue(ranks[13]);
sheet.getRange("AK20").setValue(ranks[14]);
sheet.getRange("AM20").setValue(ranks[15]);
sheet.getRange("AO20").setValue(ranks[16]);
sheet.getRange("AQ20").setValue(ranks[17]);
sheet.getRange("AS20").setValue(ranks[18]);
sheet.getRange("AU20").setValue(ranks[19]);
sheet.getRange("AW20").setValue(ranks[20]);
sheet.getRange("AY20").setValue(ranks[21]);
sheet.getRange("BA20").setValue(ranks[22]);
sheet.getRange("BC20").setValue(ranks[23]);
sheet.getRange("BE20").setValue(ranks[24]);
sheet.getRange("BG20").setValue(ranks[25]);
sheet.getRange("BI20").setValue(ranks[26]);
sheet.getRange("BK20").setValue(ranks[27]);
sheet.getRange("BM20").setValue(ranks[28]);
sheet.getRange("BO20").setValue(ranks[29]);
sheet.getRange("BQ20").setValue(ranks[30]);
sheet.getRange("BR20").setValue(ranks[31]);
}

As you can see, the code is really sloppy. Thats probably because I have never done javascript before and my coding knowledge is very basic in general.

Is there a way to implement such a feature directly in the spreadsheet, without the need of the script api?

Thanks in advance for the help!

score 0 · Answer 1 · answered Apr 19 '13 at 14:45

0

for(i=0;i<numRanks;i++)
    sheet.getRange(20, i*2+9).setValue(ranks[i]);

Basically you just iterate a for loop over the twentieth row every other column.

Function documentation here.

answered Apr 19 '13 at 14:45

Jason Nichols

3,739
3
29
49

score 0 · Answer 2 · edited May 23 '17 at 12:11

Jason's answer is clear & short - but it's not efficient because of the number of calls to the Spreadsheet Service.

For background about this concern, see Google Apps Script Best Practices. You may also want to look at What is faster: ScriptDb or SpreadsheetApp?.

You've already got an Array of ranks; if you transpose that into a two-dimensional array with the values in rows, then you can write it all out in one setValues() operation.

sheet.getRange("I20:BR20").setValues(transpose([ranks]));

Here's a transpose function, from Google Spreadsheet Script - How to Transpose / Rotate Multi-dimensional Array?.

function transpose(a)
{
  return Object.keys(a[0]).map(function (c) { return a.map(function (r) { return r[c]; }); });
}

There is still iteration happening, but it's in the transpose function now, instead of around the setValue() call, so this will run much quicker.

Google Spreadsheet ranking values

2 Answers2