Solved: How To Create an Numeric Virtual Column - Page 2

deltai · 10-21-2023 05:31 PM

I need to create a numeric column (Virtual Column) starting with the number 01 and ending with the number of existing views.
This needs to be done in ascending order.
I can't base it on ROWNUMBER as it won't be in order.
I use SLICE with SELECT to filter the VIEW.
The preview is always changing the quantity displayed as orders are created or completed.

For example:
At the moment I have 18 orders in the view and therefore the column must have numbers from 01 to 18.

Number 01 for the first line, 02 for the second line, 03 for the third line.....on the final line.

I even managed to develop a formula that I got from this community, but it doesn't meet the need.

COUNT(SELECT(TABLE[VIRTUALCOLUMN], [_RowNumber] <= [_THISROW].[_RowNumber]))

TeeSee1

This is the code I used for the demo. This is set in the column (ORD) of the gSheet to indicate the new row order. The argument passed to this function is a cell from the column ('sort_val' in my demo) that contains the values of concatenated cells that you want to sort.

const shENUM1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("ENUM1");
function rowOrder(val) {
  const lR = shENUM1.getLastRow();
  let range = shENUM1.getRange(2,4,lR-1,1);
  let values = range.getValues();

  values.sort(
   comp
  );

  for(let i = 0; i < values.length; i++){
    if(values[i][0] == val) {
      return i;
    }
  }
}

function comp(e1, e2) {
 return e1[0] == e2[0] ? 0 : e1 < e2 ? -1 : 1;
}

This is the refresh function to force recalculation. You can call functions only in a project that is not bound to a spreadsheet. (this is borrowed from here)

function reCalc() {
  const ss = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/#######/edit')
  var customFunctions = ["rowOrder"]; // a list of custome function names you want to refresh

  var temp = Utilities.getUuid();
  customFunctions.forEach(function (e) {
    ss.createTextFinder("=" + e).matchFormulaText(true).replaceAllWith(temp);
    ss.createTextFinder(temp).matchFormulaText(true).replaceAllWith("=" + e);
  });
}

View solution in original post