Auto - sequential numbers, using Googlesheet formula - no duplicate!

“To get the auto-sequential number” without duplicates is one of the frequent question and ask here in the community, but there is no native and perfect solutions available for the time being.
This is not a perfect solutions, and I m not strongly recommend to implement this tricks into your production app as I m not sure how it will be stable in terms of the performence, but at least, my quick test showed positive result to generate the sequential number dynamically, using Googlesheet formula.

Please bear in mind the row ID is generated “uniqueid()” expression. Apart from row key, i generate the sequential number ID fields, where the value will be auto filled by Googlesheet formula. So this is backend task. When the app user add new row at the same time, those concurrent request to backend data base, i.e. Googlesheet will NOT conflict with others. It should be servived basis “first in first come”? Even the app user delete the row, the given sequencial Id stay same, and unaffected.

The blog from Googlesheet specialist, Mr. Ben Collins inspire me to test this with Appsheet, and hopefully it will work.

Once the sequential number is generated, then pass the same value to appsheet action and workflow to generate another sequential something, such as multi digit sequential ID and numbering using Concact expression in Appsheet.

Again, in general, Appsheet is not recommend to user Array formula in spreadsheet, so unexpected miss behaivior might happen. The use of this trick is at your own risk, please.

https://mailchi.mp/benlcollins/auto-generate-id-column?e=e7d994617f

6 Likes

Based on my sample app, the required app formula for sequential number is simply like this.

image

for your guidance.

2 Likes

For this sample and screenshot as above, I placed the expression into B2 cell.

From master of Appsheet (@Aleksi) who gave a great advice and tips once again. We are able to push the same expression into B1 cell which is column name row. Read table on Appsheet editor. It wont take the spreadsheet expression, but take as a column value (name). Then app is ready to go.

Working like a charm.

Thank you Aleksi for the tips.