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.
The numbered column is a user request as it will be migrated from Google Sheets to APPSheet.
In Google Sheets, the user uses the numbers in each line to indicate tasks to their work team and wants it to be the same in the APP.
This is “a solution” but I am not sure if you want to implement this.
Basically you calculate the required order (it is like RowNumber but sorting is taken into consideration) in Apps Script (col ORD) and use it to again calculate the correct order in the slice.
Here ENUM1 is everything and ENUM1 Sort is a slice that takes the rows with an even [_RowNumber] , sorted by [STATUS] and [Label].
[ORDER SL] is the final info you are looking for.
I also had to implement a recalculate function which is called by a Bot on change events because Spreadsheet cells do not automatically recalculate custom functions when new rows are added or data edited.
I created a custom function using Javascript/Apps Script.
I just tried ‘one’ way to do it but unless you learn to do it by yourself, I do not recommend doing it at all because otherwise you cannot maintain/manage it.
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);
});
}
