Re: Script Request: Delete records from Tables
0
6048
2
Alright, I’ve got a multi-table solution for you, @Dimitri_Zakharov
Here’s the script:
/*
* Script: Delete all Records from Multiple Tables
* Author: Jeremy Oglesby
* License: MIT
*
* This script allows you to define tables (by supplying their names), from
* which to delete all records. Useful for quickly clearing out all tables
* defined in a single button click.
*
* It will then prompt you to confirm that you want to delete all records from
* the defined tables. Selecting 'Yes' will perform the mass delete and will
* result in the defined tables being completely emptied of records.
*
* Mass deletions performed by this script can be recovered from the trash bin
* for 7 days after running the script if they are deleted by mistake.
*/
const Tables = function(){
// ** DEFINE ALL OF YOUR TABLE NAMES HERE **
// ** WITH A COMMA AFTER ALL BUT THE LAST **
this.names = [
"Table 1",
"Table 2",
"Table 3",
"Table 4",
"Table 5",
"Table 6",
"Table 7",
"Table 8"
];
this.numberOfTablesDefined = this.names.length;
};
// ****************************************************************
// ******* NOTHING BELOW HERE SHOULD NEED TO BE CHANGED *******
// ****************************************************************
let tables = new Tables();
const tablesToClear = []
let totalRecordsToDelete = 0;
let tablesQueried = 0;
// Get records from each table and add to array of tables to clear
while (tablesQueried < tables.numberOfTablesDefined) {
let tableName = tables.names[tablesQueried]
let tableToAdd = base.getTable(tableName);
let queryToAdd = await tableToAdd.selectRecordsAsync();
let recordsToAdd = queryToAdd.records;
if (recordsToAdd.length > 0) {
totalRecordsToDelete += recordsToAdd.length;
const table = {table: tableToAdd, records: recordsToAdd};
tablesToClear.push(table);
}
tablesQueried++
}
output.markdown(`## You are about to delete ${totalRecordsToDelete} records from ${tablesToClear.length} tables.`);
output.markdown(`**Choose wisely!**`);
let areYouSure = await input.buttonsAsync('Are you sure?', ['Yes', 'No']);
if (areYouSure === 'Yes') {
let totalRecordsDeleted = 0;
for (let table of tablesToClear) {
let recordsToDelete = table.records.map(record => record.id);
let recordsDeleted = await batchAnd('Delete', table.table, recordsToDelete);
if (recordsDeleted !== null) {
totalRecordsDeleted += recordsDeleted;
}
};
if (totalRecordsDeleted !== null) {
output.clear();
output.markdown(`## ${totalRecordsDeleted} records deleted from ${tablesToClear.length} tables.`);
output.markdown(`These records can be restored from the trash bin within the next 7 days if you didn't mean to delete them.`);
};
};
if (areYouSure === 'No') {
output.clear();
output.markdown(`## No records deleted.`);
output.markdown(`Run the script again to delete records from your tables.`);
};
// ***************************************************
// ****************** FUNCTIONS ******************
// ***************************************************
async function batchAnd(action, table, records) {
let recordsActedOn = records.length;
switch (action) {
case 'Update':
while (records.length > 0) {
await table.updateRecordsAsync(records.slice(0, 50));
records = records.slice(50);
};
break;
case 'Create':
while (records.length > 0) {
await table.createRecordsAsync(records.slice(0, 50));
records = records.slice(50);
};
break;
case 'Delete':
while (records.length > 0) {
await table.deleteRecordsAsync(records.slice(0, 50));
records = records.slice(50);
}
break;
default:
output.markdown(`**Please use either 'Update', 'Create', or 'Delete' as the "action" parameter for the "batchAnd()" function.**`);
recordsActedOn = null;
}
return recordsActedOn;
}
The delete actions happen in batches of 50, so you will see continuous “50 records moved to trash” messages pop up at the bottom of your base while it runs.
This run took about 15 seconds:
Restoring all records from a mistaken run with thousands of records will be a bit painful, though, since each batch of 50 records deleted will have to be restored individually:
Thus the “Are you sure?” prompt!
