Generate an auto incrementing key with scriptDB

NOTE: ScriptDB is now deprecated. Please take a look at Database abstraction with google apps script for alternatives.


Sometimes you need to create a unique identification for each object. ScriptDB automatically assigns a unique objectId for each object, so you can just use this. However, if you really do want to create a unique numeric ID that increments with each save, you can try this using this function to save to your scriptdb. 

</p>
<p>
function incremenKeyAndSave(db,ob,key,proto) {
// get a lock on shared resource
    var lock = LockService.getPublicLock();
    lock.waitLock(10000);
//now get the latest value for the given key
    var top = db.query(proto || {}).sortBy(key, db.DESCENDING, db.NUMERIC).limit(1);
// will add one to the latest
    ob[key] = top.hasNext() ? (top.next()[key]) + 1: 1;
// save and reslease lock
    var results = db.save(ob);
    lock.releaseLock();
    return results;
}
Page Content hide
1 Walkthrough
2 Lock the database
3 Arguments
4 Get the current highest value
5 Increment
6 Save and return
7 Test

Walkthrough

 

Lock the database

We’re using the lock service. This will protect this section of code being run by multiple users at the same time while the next id is being calculated. We’ll hold this for a maximum of 10 seconds.  

Arguments

 

db – the scriptDB to use. We’ll pass this instead of using ScriptDb.getMyDb(). This will allow you to use the same function in a library to access multiple scriptDb. ob – the object to write. key – the name of the key to auto increment.  proto – an optional object that identifies the objects of this type (there may be more than one object type in this scriptDb). I always use a siloId to segregate all types of data.   

Get the current highest value

 

Do a query on the current scriptDb content, returning a single record – sorting it in descending order will give us the current highest value.

var top = db.query(proto || {}).sortBy(key, db.DESCENDING, db.NUMERIC).limit(1);

Increment 

 

The first record ever will receive  id 1. Otherwise, it will increment the current value of ob[key]. 

// will add one to the latest
ob[key] = top.hasNext() ? (top.next()[key]) + 1: 1;

Save and return

// save and release lock
var results = db.save(ob);
lock.releaseLock();

Test

Here’s an example, writing 5 records to an existing database

function testIncrement() {
  var x = 5;
  var db = ScriptDb.getMyDb();
  while (x--&gt;0) {
    incremenKeyAndSave ( db, {someData:'something',siloId:'test'}, 'someKey', {siloId:'test'} );
  }
  var results = db.query ({}).sortBy('someKey',db.NUMERIC);
  while (results.hasNext()) {
    Logger.log(results.next());
  }
}

and the results..

[14-02-16 14:42:47:629 GMT] {siloId=test, someData=something, someKey=1.0} [14-02-16 14:42:47:629 GMT] {siloId=test, someData=something, someKey=2.0} [14-02-16 14:42:47:630 GMT] {siloId=test, someData=something, someKey=3.0} [14-02-16 14:42:47:630 GMT] {siloId=test, someData=something, someKey=4.0} [14-02-16 14:42:47:631 GMT] {siloId=test, someData=something, someKey=5.0} [14-02-16 14:42:47:631 GMT] {siloId=test, someData=something, someKey=6.0} [14-02-16 14:42:47:631 GMT] {siloId=test, someData=something, someKey=7.0} [14-02-16 14:42:47:632 GMT] {siloId=test, someData=something, someKey=8.0} [14-02-16 14:42:47:632 GMT] {siloId=test, someData=something, someKey=9.0} [14-02-16 14:42:47:633 GMT] {siloId=test, someData=something, someKey=10.0}

For more like this, see  From VBA to Google Apps Script . Why not join our forum, follow the blog or follow me on Twitter to ensure you get updates when they are available.