ScriptDB is a JavaScript object database for Google Apps Script. Each script project gets a database, which the script can use to save, update, and search JavaScript object data.

Data format

ScriptDB stores all data as maps — that is, as JavaScript objects composed of key-value pairs. The values in a map can be strings, numbers, arrays, or other nested maps, but not methods or Date objects. For example, you might have an object representing an employee record:

{
  type: 'employee',
  employee_id: 1,
  name: {
    first: 'Fatima',
    initial: 'S',
    last: 'Pauli'
  },
  address: {
    street: '4076 Washington Avenue',
    city: 'Jackson',
    state: 'MS',
    zip: '39201'
  },
  department_id: 52
}

Here, the type is an employee, the employee's id is 1, the person's name is "Fatima S. Pauli", and so on. There are no "magic" keys. That is, all key names and values are entirely user-defined.

ScriptDB throws an exception if you attempt to save an object that contains a function or a Date. The section on dates later in this guide provides tips for properly saving date information with ScriptDB.

Connecting to ScriptDB

The first API call you need to learn is how to access a database. Start by calling ScriptDb.getMyDb(), which returns a ScriptDbInstance. The database can be accessed from the current script or a library that is included in the current script.

Saving data

Now that you have access to the database, you can save objects via the method db.save(item). For example:

var db = ScriptDb.getMyDb();
var item = {
  type: 'employee',
  employee_id: 1,
  name: {
    first: 'Fatima',
    initial: 'S',
    last: 'Pauli'
  },
  address: {
    street: '4076 Washington Avenue',
    city: 'Jackson',
    state: 'MS',
    zip: '39201'
  },
  department_id: 52
};
var record = db.save(item);

Loading by ID

When you save an object, ScriptDB automatically gives it the method getId(). The ID is an opaque, immutable string value. If you know an ID, you can load the object with a call to db.load(id):

var record = db.save(item);
var id = record.getId();

// Then, next execution, load it based on that ID.
var record = db.load(id);

Object IDs can be used as foreign key values from other objects. Depending on your needs, you may want to load more than one object, such as a case where you have an array of IDs:

var records = db.load([id_1, id_2, id_3]);

Here, id_1, id_2, and id_3 are variables which hold the string values of the IDs (specifically, the IDs here are not literals).

Modifying data

To make changes to an object, you must have an object that has been fetched from the database by a call to db.load(id) or db.query(query), or returned from db.save(item). For example, starting from the examples above, you could modify the data you just saved:

record.foo = 'bar';
db.save(record);

Alternatively, you could query and modify those objects. For example:

var record = db.query({employee_id: 29}).next();
record.name.first = 'Fred';
db.save(record);

Deleting data

To delete objects from the database, use remove(item) or removeById(id):

db.remove(record);

Or, if you have object ID available:

var id = record.getId();
db.removeById(id);

Bulk operations

If you know in advance that there are a number of objects you want to work on all at once, ScriptDB also allows for bulk operations. For example, there's db.saveBatch(items, atomic).

var saveResults = db.saveBatch(arrayOfObjects, false);

The false argument at the end is the atomicity flag; currently, false is the only valid value. Because there is a possibility, although rare, of some objects being saved, but not others, the array returned by this call may contain MutationResult objects that indicate failure to save the corresponding item in arrayOfObjects. You can use the method db.allOk(mutateResults) to quickly check for success. For example:

var result = db.saveBatch(arrayOfObjects, false);
if (db.allOk(result)) {
  // Everything went swimmingly, proceed.
} else {
  // Partial or no success.
  for (var i = 0 ; i < result.length; i++) {
    var item = result[i];
    if ((typeof item.success) == 'function' && !item.success()) {
      // Do something with failed object.
    }
  }
}

The same general pattern goes for db.removeBatch(items, atomic) and db.removeByIdBatch(ids, atomic), save that the final if statement can simply be:

if (!item.success()) {
  // Do something with failed object.
}

Except as otherwise noted, the content of this page is licensed under the Creative Commons Attribution 3.0 License, and code samples are licensed under the Apache 2.0 License. For details, see our Site Policies.