Script Request: Delete records from Tables

Hi. I have no scripting experience so hoping someone can help. Is there a way to use the scripting block to delete all records from certain tables? I can do this manually, but I have to do this from 8 tables out of 30 in a base that I duplicate regularly. If there was just one button I can click to do this at once, that would be amazing. Bonus if it first asked to confirm that I really want to do this, so someone doesn’t accidentally click it.

Here’s a script that should do this for you. I’d suggest pasting it first into a Scripting Block in a Test base and trying it out to see how it works. Then, if you feel it meets your needs, paste it into a Scripting Block in your live base.

/* 
Script: Delete all Records from Table
Author: Jeremy Oglesby
License: MIT

This script will present you with a table picker to choose a table from which
you would like to delete ALL existing records.

It will then prompt you to confirm that you want to delete all records from
the selected table. Selecting 'Yes' will perform the mass delete and will 
result in the selected table 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.
*/

let table = await input.tableAsync("Choose a Table to clear");
let query = await table.selectRecordsAsync();
let records = query.records;
let recordCount = records.length;

output.markdown(`## You are about to delete ${recordCount} records from ${table.name}.`);
output.markdown(`**Choose wisely!**`);

let areYouSure = await input.buttonsAsync('Are you sure?', ['Yes', 'No']);
if (areYouSure === 'Yes') {
    output.clear();
    
    let recordsToDelete = records.map(record => record.id);
    let recordsDeleted = await batchAnd('Delete', table, recordsToDelete);

    if (recordsDeleted !== null) {
        output.markdown(`## ${recordsDeleted} records deleted from ${table.name}.`);
        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 a table.`);
};

// ********************
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;
}

I’d also suggest naming your Scripting Block in a way that clearly expresses that this Script will mass delete records.

1 Like

I tried this on a test base/table which has 693 records and I got this error.

Error: Request exceeds maximum batch size limit of 50 items
at Mutations._assertMutationUnderLimits (https://cdn.airtableblocks.com/bbnvQSVM77xPXpYP3/bundle.js:1:572466)
at Mutations.applyMutationAsync$ (https://cdn.airtableblocks.com/bbnvQSVM77xPXpYP3/bundle.js:1:571112)
at tryCatch (https://cdn.airtableblocks.com/bbnvQSVM77xPXpYP3/bundle.js:1:1996567)
at Generator.invoke [as _invoke] (https://cdn.airtableblocks.com/bbnvQSVM77xPXpYP3/bundle.js:1:1996172)
at Generator.prototype. [as next] (https://cdn.airtableblocks.com/bbnvQSVM77xPXpYP3/bundle.js:1:1997465)
at tryCatch (https://cdn.airtableblocks.com/bbnvQSVM77xPXpYP3/bundle.js:1:1996567)
at invoke (https://cdn.airtableblocks.com/bbnvQSVM77xPXpYP3/bundle.js:1:1997721)
at https://cdn.airtableblocks.com/bbnvQSVM77xPXpYP3/bundle.js:1:1998206
at new Promise ()
at callInvokeWithMethodAndArg (https://cdn.airtableblocks.com/bbnvQSVM77xPXpYP3/bundle.js:1:1997629)

ah, sorry :confused: I didn’t take into account the request limits on the mass delete.

I’ll try to work on that if I have time (or maybe somebody will beat me to it). The records to be deleted will have to be batched in groups of 50, I think.

Ok. Thanks for your help. I really appreciate the advice.

It’d be great to select multiple tables at once too. I have to do this for 8 tables each time - it’s always the same tables, so I’m happy to have that directly in the code.

1 Like

I’ve updated my Script in the post above to including a batching function to handle more than 50 records for you.

You still have to select each table with the version I’ve posted there. I will post a new version in a new post that can delete records from all 8 tables in a single go for you.

1 Like

This is great! Thank you so much. It’d be great to see the multi-table version too. But I like this a lot.

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!

1 Like

Should the table names be in quotes at the top?

1 Like

I guess so - works great! Thank you again! This is amazing.

1 Like

@Jeremy_Oglesby Great work used part of this for my solution. But got stuck trying to filter records based on cell data. how would you filter records before deleting them.

Hi @Peter_Versaci – glad it was helpful!

I answered a similar question for someone else recently. See here:

This was really helpful @Jeremy_Oglesby - thanks!

I was going to merge this into another script that populates a table, but as you’ve abstracted it so nicely it feels more sensible (and useful) to keep it separate. Cheers!

1 Like

Hi @Jeremy_Oglesby I think I understand your code to delete records, but I’m obviously missing something fundamental. My script does not recognise the text “batchAnd”. Does this need to be predefined somewhere? Or am I missing something?

Here’s my code followed by the error message. I’m trying to delete all the records in mediatable that are linked to a particular film, where the name of the link column in film table is “Media”.

let existing = await film.getCellValue("Media");
let recordsToDelete = existing.map(record => record.id);
let recordsDeleted = await batchAnd('Delete', mediatable, recordsToDelete);

Hi @Peter_Borg,

batchAnd() is a convenience function I wrote for performing record creation, updates, or deletes on more than 50 records at a time.

In order to call it from your script, you have to copy and paste the entire function into your script, because it doesn’t exist in Airtable’s Scripting Block API.

You’ll find the entire async function near the bottom of my script above, but here is a link to where I’ve posted it on its own in the forums, along with an explanation — just copy and paste the entire function into your script (I usually place it at the very bottom):

Scripting Block: A (hopefully) Helpful Batching Function

Thanks @Jeremy_Oglesby, all makes sense now.

1 Like

Hi! I’m new to scripting. I’ve played around and searched around for about a half hour; but i’d like to add the option to choose a view before deleting. Could you help a gal out? I’m referring to your top script :slight_smile: