Quite embarrassing really, but I tested creating a database from a spreadsheet with a modified script from this example:
function loadDatabaseFromSheet() {
var sheet = SpreadsheetApp.openById('my_key').getSheetByName('Payroll');
var data = sheet.getDataRange().getValues();
data = data.splice(0, 9); // **this is where I went wrong**
var keys = data[0]; // **this actually corresponds with a blank row**
var db = ScriptDb.getMyDb();
for (var row = 1; row < data.length; row++) {
var rowData = data[row];
var item = {};
for (var column = 0; column < keys.length; column++) {
item[keys[column]] = rowData[column]; // **null values used to name attributes???**
}
db.save(item);
}
}
So the Javascrit noob should have used data.splice(0, 9) rather than data = data.splice(0, 9). I wanted to remove the first nine rows from the array, not retain them.
Anyway, it seems as it stands the database is not repairable or erasable. I can't retrieve any records; any time I try to invoke .next() or .hasNext() I get a server error thrown, for example with this script from here:
function deleteAll() {
var db = ScriptDb.getMyDb();
while (true) {
var result = db.query({}); // get everything, up to limit
if (result.getSize() == 0) {
break;
}
while (result.hasNext()) {
db.remove(result.next());
}
}
}
.getSize() does work, but the script returns a server error when it gets to the line with .hasNext(), presumably because of the faulty object. I also tried a batch remove:
function batchRemove() {
var db = ScriptDb.getMyDb();
var result = db.query({});
db.removeBatch(result, false);
}
And I get:
Cannot find method removeBatch($Proxy799,boolean). (line 114)
By the way, I can successfully save a new record to the database, and retrieve that record with a query, but if the query returns anything else in the database that was created with the flawed script, that's when I get the failure when I try to work with the resulting objects.
So I understand my error and promise to take more care in the future, but does anyone know how I can "clean out" my database to start again?
