NOTE: ScriptDB is now deprecated. Please take a look at Database abstraction with google apps script for alternatives. 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; } WalkthroughLock the databaseWe'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. Argumentsdb - 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 valueDo 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); IncrementThe 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(); TestHere's an example, writing 5 records to an existing database function testIncrement() { var x = 5; var db = ScriptDb.getMyDb(); while (x-->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. |
Services > Desktop Liberation - the definitive resource for Google Apps Script and Microsoft Office automation > From VBA to Google Apps Script > Using scriptDB >