How To Create an Numeric Virtual Column

[deltai]

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]

Wouldn't changing TABLE[VIRTUALCOLUMN] to SLICE[id] work?

Unless your slice changes the order of [_ROWNUMBER] (skipping some is no issue), this should work.

[deltai]

Hi,

I´m using Slice and Row Filter = AND(ISNOTBLANK(USERSETTINGS(USUÁRIO)),ISBLANK([DATA_HORA_ENCERRAMENTO])). But this just filter a view.

Let me show you my APP (using in Browser):

[TeeSee1]

Yes. This expression works in a view of a slice.

The slice filters the rows that start with either 2 or 3.

The virtual col expression is COUNT(SELECT(SL_T3[id],[_RowNumber]<=[_THISROW].[_rownumber]))

[deltai]

Tks...

But it not works for me....i´m using exactly this way....

My view needs to be like Google Sheets lines....and number
sequence...always.

This form:
1
2
3
4
5
6
until the end of number os views.

[deltai]

I´m using same formula, but the view it´s the same.

[TeeSee1]

Create a vc that copies [_ROWNUMBER] and show it to see what is going on.

[deltai]

Sorry...may bad. I forgot to say.....

This view is sorting by 2 columns (data / hour) and other column.

That is the problem, what de VC (sequencial number) do not show like I want.

Any other solution?

[TeeSee1]

In a view with sort configured, I cannot think of a way to do this.

Besides it may be nice to look at the ordered numbers, why do you need to show them? Maybe there is another way to satisfy your business requirements.

[deltai]

Thank´s...

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.

[deltai]

Problem still unsolved 😕

[TeeSee1]

The first row edited from 12/CLOSED to Z12/WIP

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.

Hope someone can suggest a better approach..

[jaichith]

How did you get Rectangular Add button in top ?

[deltai]

Oh...thank´s

But dont understand very well how do it.

Could you explain or show me formula?

[TeeSee1]

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.

[deltai]

Frind,

Is it possible show me your code?

Thanks

[TeeSee1 · Accepted solution]

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);
  });
}

[deltai]

Thanks....i will study about.

Now, is it possible to obtain the index of the rows that are being displayed in the view as a result of the SORT performed?

[TeeSee1]

I suppose if you pass the filtering and sorting conditions to the function, it can be done.

[deltai]

Ok. But if not use Script...using only appsheet formula to get this information...is it possibile?

[TeeSee1]

I really did not understand your question after all...

Are you asking how you can get [Order SL] in my last demo app? If so, you can write an expression similar to my initial response, using [ORD] in place of [_RowNumber].

[deltai]

Sorry for my bad explain,

No. I think direct in appsheet.

[deltai]

It´s ok.

I know GAS.... i have many google sheets using that.

But to use Script on AppSheet is it necessary to pay or not?

Tanks

[TeeSee1]

This is what I see in the help doc. You should contact your Google rep for details to be sure.