After a lot of testing, I finally figured out why AppSheet was resetting my numero_factura back to zero.
What happens is this: when a bot starts running, AppSheet takes a kind of “snapshot” of the row at that moment. Then my Apps Script assigns the invoice number directly in the spreadsheet, but AppSheet doesn’t detect that external change.
So when the bot continues and later reaches a Set Values step to update any other field, AppSheet rebuilds the row using that old snapshot (the one from before the script wrote the number). Since in that snapshot numero_factura was still 0, AppSheet simply overwrites the number and puts the 0 back.
In other words, the script isn’t deleting anything — the bot is rewriting the row with outdated data.
And that’s why the invoice number kept disappearing.
I have a similar use case where I am generating sequential numbers but it does not support offline mode since the ID is generated from a Bot.
However, I am using [_RowNumber]-1 to retrieve the last generated number, and in case of several rows being added at the same time, I created a loop using Actions that will keep checking if the previous row ID has been generated, in case its blank, it will loop again until eventually all IDs have been generated.
It’s a flawed solution because if there’s a timeout or any other issues, no IDs will be generated. I have another scheduled Bot to cover those cases, in case any of the IDs are blank, it will do the loop again but at a delay of course, depending on the time of its scheduled execution.
Hi its multi user yes I thing your problem is easy to solve for example both user can bill generate then submit the bill then bill no generated other wise its not a multi user, just use the logical apply otherwise not getting solutions
I believe the issue will be if two or more users simultaneously add their respective rows, the respective bots will pick up the same previous serial number to add 1 to it as their source of previous truth will be same. Essentially the same issue of multiple users adding based on identical previous data point and hence duplicating the number.
Even if more urers will add rows (invoices) in the same time they will be added in separate rows. Bot should find last row of logged user (to avoid adding invoice no. to other user invoice) and generate that no. basing on previous row (of all users). Bots are working sequentially so even if more users will add invoices in the same time invoice nos. will be different.
Why are you not returning the generated number from the script back to Appsheet and then set the value? I think that would help with your described issue of the snapshot in time and setting the number to 0 again. What is your script actually doing? Is it using SpreadsheetApp to set the row value for your invoice?
Are you using the LockService in your script? If you are running the application as the user instead of the creator then you may need to use getPublicLock() instead of the getScriptLock().
The way I worked around this was not using AppSheet but a specific program (those of which abound) to produce the invoice and comply with the electronic invoice details, sending it to the domestic revenue service, etc.
What I have is:
The user in the AppSheet app produces an order and marks it to be invoiced.
An app bot reads that change and sends data to an intermediary gsheet which receives it in the column format needed by the invoicing program.
A python script “reads” the gsheet, formats some fields and produces a local file which is automatically imported into the invoicing program.
That turns it into a pre-invoice, sends it to the revenue service and, upon confirmation of reception, provides the confirmation number to its invoice.
The app reads the invoices table from the invoicing program DB which provides the required legal numbers to the initial Order.
Effective! The whole process takes less than 1 minute and can be still improved.
There is an advantage: One app can invoice in different locations or countries.
My solution to a very similar problem involved creating a table for system properties and then using a bot to update the row in that table for the number increase. We have sequential numbers for work orders. When an action is run to create a new order, a bot is triggered and the value in the system property table is increased by one. On very rare occasions we still have a duplicate, but correcting it is as simple as reviewing the system table and adjusting one of the duplicate numbers to cover the skipped value. With an invoice that may not be a suitable solution though.
Hey, I actually solved it using GAS. No matter how many people trigger the bot at the exact same second, it always gives me a sequential number—never repeats. And if something goes wrong, I’ve set it up so it won’t give out any number or run the bot, and at the same time it sends an email alert letting me know there was an error.
I’m still testing it because I just implemented it these past few days. It hasn’t failed yet. The only thing that could go wrong would be hitting the request limit on GAS.
