0

Using Google Forms, I am collecting data for teachers to use in determining students who qualify for Title 1 services.

The form feeds the data to Google Sheets, and I want to automate the process of summing scores by category and then ranking them in each category.

I have this code that copies the summaries from one column to another, but then I need to rank the column and that's where I'm stuck. Can someone help me understand how to rank the data?

I think I may need a for loop - but I'm stuck.

function CopyRankData() 
{
  var spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
  var SourceSheet=spreadSheet.getSheetByName('Ranking')
  var srcRange = SourceSheet.getRange('D1:D31');
  var destSheet = spreadSheet.getSheetByName('Ranking');
  var destRange = destSheet.getRange('I1:I31');
  srcRange.copyTo(destRange);
  --Need to Rank the column ---[Link to spreadsheet][1]
}
Oleg Valter is with Ukraine
  • 9,619
  • 8
  • 37
  • 59
Karen
  • 157
  • 2
  • 12
  • 1
    Precisely, what do you mean by ranking the data? – Cooper Mar 04 '19 at 23:27
  • 1
    I want to write the code in the function above to perform this calculation: =RANK(M2,$M$2:$M$8,1). We are getting the student scores and by ranking them so we can quickly see who needs the most help. Give someone a rank or place within a grading system. – Karen Mar 06 '19 at 13:14
  • 1
    I would add Google spreadsheets to your list of tags. I don’t know about other volunteers here but I don’t deal with cell functions very much. In my own work I do everything in code so I don’t really know how to evaluate cell functions so I can’t help you – Cooper Mar 06 '19 at 15:40

1 Answers1

0

The below is pretty a simple utility that can correctly emulate RANK formula (including ascending and descending ranking). If you need to make it sort by another column of the range or use another comparator, it is easy to change and make it more versatile.

What you need to do is sort a copy of the target column values by some criteria, get the resulting index, and increment it by 1 since Array indices are 0-based.

/**
 * @summary ranks a range by first column
 * @param {number[][]} range
 * @param {(1|0)} [ascending]
 * @return {number[][]}
 * @customfunction
 */
const rankRange = (values, ascending = 0) => {

    try {

        const rows = values.map(v => v).sort((a, b) => {
            const aParsed = parseFloat(a[0]);
            const bParsed = parseFloat(b[0]);
            return ascending ?
                aParsed - bParsed :
                bParsed - aParsed;
        });

        return values.map((row) => {
            return [rows.findIndex(r => r[0] === row[0]) + 1];
        });

    } catch (error) {
        console.warn(`failed to rank column: ${error}`);
        return [[]];
    }
};

Make sure that the V8 runtime is enabled in your project for it to work (or transpile it to ES5 syntax before using).

Performance note - usage of map(v => v) to shallow copy values is preferential. If you expect the number of items to be high, use slice() (thanks to TheMaster's comment, I decided to investigate what the performance hit is). Run the interactive snippet below to see the difference:

const rng = document.querySelector("input[type=range]");

const arr = [];

const bench = (func, lbl) => {
  console.time(lbl);
  func();
  console.timeEnd(lbl);
}

rng.addEventListener("change", () => {
  const {
    value
  } = rng;

  const toAdd = new Array(+value).fill(42);

  arr.length = 0;
  arr.push( ...toAdd );

  bench(() => arr.slice(), "slice");
  bench(() => arr.map(v => v), "map");
});
.rng {
  width: 100%
}
<input class="rng" type="range" min="1" max="1e5" step="1">
Oleg Valter is with Ukraine
  • 9,619
  • 8
  • 37
  • 59