LinkedIn
Twitter
There always seems to be a ton of questions asking how to generate sequential numbering in the community. I thought I would give my own example of how I deal with this. I used to do it with onChange() events in the spreadsheet, but now that App Script Tasks has been released this whole process became a lot smoother.
Here is a link to a simple spreadsheet that keeps track of the Previous Number in a sequence and the Next Number in the sequence. It allows you to set a Prefix and a Suffix to the number so you can customise it easily...or add your own flavour! All of this stuff is done within the sheet using simple formulas.
Google Sheet Clicking this link will make a copy of the sheet in your own Google Drive. It will have its own unique 'sheetid' and you will need to take note of that to use in the App Script Task later in this post.
So, you've copied the sheet and had a look...pretty simple yeah? Ok, lets create a brand new Google App Script project. Just click the link https://script.new/ and you'll get the standard new script project window with an empty function. This is also saved in your Google Drive...don't forget to give it a name in the top left so it's not called 'Untitled Project' any more!
Copy and paste the code below over the top of the empty script project. That's right, get rid of that empty function as well as it's not needed.
//sheetid will be the ID portion of the SerialNumbers Sheet you copied to your Drive
//example https://docs.google.com/spreadsheets/d/1lIM7jvmqZRP8iHeBcpgwW2dWdFB-QucKbr0K9SlBBLc/edit#gid=0 is the full URL format
//of a document on Google Drive. The ID portion you need is the part between spreadsheets/d/ and /edit#gid. In this example it would
//be 1lIM7jvmqZRP8iHeBcpgwW2dWdFB-QucKbr0K9SlBBLc
function getNextNumber(sheetid) {
var ss = SpreadsheetApp.openById(sheetid);
var indexer = ss.getSheetByName("SerialNumbers");
var nextNumber = indexer.getRange("B10").getDisplayValue();
var oldIndex = indexer.getRange("A10").getValue();
var newIndex = oldIndex + 1;
indexer.getRange("A7").setValue(oldIndex);
indexer.getRange("A10").setValue(newIndex);
return nextNumber
}
Save the project and we're ready to use it in Appsheet!
Here is a Bot definition. The event will be when a new row is added to whichever table you wish to store the new sequenced number. When you have set up the task to be Call a Script, you need to click the Apps Script Project empty field so we can point Appsheet to our newly saved Project.
I called mine 'DemoProject'. What did you call yours?
Choose your project name and then Authorize it. You will choose your Google Acount email that is the same one you are using for Appsheet. Don't be alarmed at the requested permissions...Just Allow them.
Now we choose the function to run (there's only one in this Project) and choose the Return Value toggle.
You need to 'plug in' the sheetid for your particular copy of the SerialNumbers file you copied at the beginning. This is enclosed in double quotes the same as in any text value. If I use the example from within the project code it would be "1lIM7jvmqZRP8iHeBcpgwW2dWdFB-QucKbr0K9SlBBLc"
We're almost there!
The Apps Script Task will return the generated number and we need to use that number to store in the field of the record that needs it (the newly added row), so we add a new step below the 'GetNumber' step...You can call this anything.
As you can see, it's a simple 'Run a data action' of 'Set row values'. I have chosen my column that I want the number inserted into. However, the value is slightly truncated! It looks like a dereferenced expression and it's full value is [GetNumber].[Output]
[GetNumber] is referring to the Step Name previously in the process and not the function name used in the script. If for instance I'd named my step 'ReturnNewNumber' then I would be referring to the number returned as [ReturnNewNumber].[Output] instead.
That's it! You're done! Once you have completed these stages you should have a newly sequenced number flowing into your newly added records.
I hope you have enough courage to try it out and maybe have a bit more of a delve into what you can do with App Script.
