getRange variable range

Question

I am trying to use a google sheet to rank a list of elements. This list is continually updated, so it can be troublesome to update the list if i already have hundreds of elements ranked and need to rank 10 new ones. Rather than having to re-rank some of the previously ranked elements every time (whether manually or using formulas), i thought it easier to write a macro that would re-rank for me.

1 - element A

2 - element B

3 - element C

new element: element D

For instance if i wanted element D to be ranked 2nd, i would need to change element B to 3 and element C to 4. This is tedious when doing hundreds of elements.

Here is my code so far but I get stuck with the getRange lines. Rankings are in column A.

 function RankElements() { 

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getActiveSheet();
  var r = s.getActiveCell();
  var v1 = r.getValue();
  var v2 = v1 + 1 
  var v3 = v2 + 1
  var lastRow = s.getLastRow();
  s.getRange(1,v2).setValue(v2);
  s.getRange(1,v3).autoFill(s.getRange(1,v3+":"+1,lastRow), SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
  s.getRange(1,v3+":"+1,lastRow).copyTo(s.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  s.getFilter().sort(1, true);
};

Answer 1

You can do the following:

• Iterate through all values in column A.
• For each value, check if (1) ranking is equal or below the new one, and (2) it's not the element that is being added.
• If both these conditions are met, add 1 to the current ranking.

It could be something like this:

function RankElements() { 
  const sheet = SpreadsheetApp.getActiveSheet();
  const cell = sheet.getActiveCell();
  const row = cell.getRow();
  const newRanking = sheet.getActiveCell().getValue();
  const firstRow = 2;
  const columnA = sheet.getRange(firstRow, 1, sheet.getLastRow() - 1).getValues()
                       .map(row => row[0]); // Retrieve column A values
  for (let i = 0; i < columnA.length; i++) { // Iterate through column A values
    if (columnA[i] >= newRanking && (i + firstRow) != row) {
      sheet.getRange(firstRow + i, 1).setValue(columnA[i] + 1); // Add 1 to ranking
    }
  }
  sheet.getFilter().sort(1, true);
};