Help

Script Request: Delete records from Tables

Topic Labels: Extensions
10249 20
cancel
Showing results for 
Search instead for 
Did you mean: 
Dimitri_Zakharo
6 - Interface Innovator
6 - Interface Innovator

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.

20 Replies 20

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.

Dimitri_Zakharo
6 - Interface Innovator
6 - Interface Innovator

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.

Dimitri_Zakharo
6 - Interface Innovator
6 - Interface Innovator

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.

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.

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:
image


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:
image

Thus the “Are you sure?” prompt!

Dimitri_Zakharo
6 - Interface Innovator
6 - Interface Innovator

Should the table names be in quotes at the top?

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

Peter_Versaci
4 - Data Explorer
4 - Data Explorer

@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:

Russell_Bishop
4 - Data Explorer
4 - Data Explorer

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!

Peter_Borg
6 - Interface Innovator
6 - Interface Innovator

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);

Screen Shot 2020-06-13 at 5.13.37 pm

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

Peter_Borg
6 - Interface Innovator
6 - Interface Innovator

Thanks @Jeremy_Oglesby, all makes sense now.

Caroline_Riteno
6 - Interface Innovator
6 - Interface Innovator

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 :slightly_smiling_face:

Here’s the script I put together to solve my problem:

let table = base.getTable("Tasks");

let view = table.getView(“ :curly_loop: Closed”);
let query = await view.selectRecordsAsync();
let records = query.records;
{

let recordsToDelete = records.map(record => record.id);
let recordsDeleted = await batchAnd('Delete', table, recordsToDelete);

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

Hi Jeremy! I am working on importing and managing data of device details on the cloud with API. I am a beginner and don’t have experience with coding. I was wondering if you can help me because my team is struggling with the data management of our clients while waiting on developing new tools (released in 5 weeks). Old tool is broken at the moment…

We have airtable that can download (to airtable) and upload (to the server) info for the devices (4 fields) on airtable sheet. Using a script for that. I also saw this script of yours that can delete all data from airtable sheet, but is it possible to use this script with the modification that deletes corresponding data on the server using api?

For example:

  1. I download a list of devices with info on 4 fields. (name, code, mac address, NFC number)
  2. Copy selected devices or rows in airtable (not all, just ones that I want to delete) to the new airtable sheet.
  3. Click the button in the new sheet to use scripts to delete all corresponding devices for the server using api.

I would really appreciate your help or forward me to the place where I can find similar scripts. Thank you.

EDIT:

So far I have this script for updating records but it asks me to select a specific record or row in the table, I would like to not ask me, just replace/update all records in the table to server - checking them and update them or delete them.

 let auth_headers = new Headers();
auth_headers.append('Accept',"application/json, text/plain, */*");
auth_headers.append('Content-Type',"application/json, text/plain, */*");

let UpdateYesNo = await input.buttonsAsync('Are you sure you want to update this record?', [
    {label: 'Yes', variant: 'primary'},
    {label: 'Cancel'},
]);

if (UpdateYesNo === 'Yes'){
    let tableId = cursor.activeTableId;
    let table = base.getTable(tableId);
    let record = await input.recordAsync('', table);
    output.clear()
    if (table.name === 'LOCATIONS-DOWNLOAD'){
        await updateLocation(record);
    }else if(table.name === 'LOCATORS-DOWNLOAD'){
        await updateLocator(record);
    }else if(table.name === 'TAGS-DOWNLOAD'){
        await updateTag(record);
    }else if(table.name === 'ASSETS-DOWNLOAD'){
        await updateAsset(record);
    };
}else{
    output.markdown('_Canceled_')
}



// _________________________________________________________________________________________
// FUNCTION FOR LOGIN TO THE WEBSITE
async function login(){  
    output.markdown('Please wait...')
    let table = base.getTable('CREDENTIALS');
    let records = await table.selectRecordsAsync();
    let record = records.getRecord(records.recordIds[0]);
    let username = record.getCellValue("Username");   
    let password = record.getCellValue("Password");
    let login_url = 'https://api.servername.com/login';
    let creds = {"username": username, "password": password};
    let apiResponse = await fetch(login_url, {method:"POST", body:JSON.stringify(creds)});
    let user_data = await apiResponse.json();
    if (apiResponse.status === 200){
        let token = user_data['Token'];
        auth_headers.append('token',token); 
        return true        
    }else{
        output.markdown(`* ${user_data['message']}*`);
        return false
    }
}


async function updateLocation(record){
    let login_status = await login()
    if (login_status){
        let url = "https://api.servername2.com/location/"
        let location_type
        let payload = {}
        if (record.getCellValue('Location Type').name === 'Active'){location_type = 0}else{location_type =1}
        payload['LocationId'] = record.getCellValue('ID');
        payload['LocationName'] = record.getCellValue('Location Name')
        payload['LocationCode'] = record.getCellValue('Location Code')
        payload['LocationType'] = location_type
        let apiResponse = await fetch(url, {method:"PUT", headers:auth_headers, body: JSON.stringify(payload)});
        let responsText = await apiResponse.text()
        let feedback =  responsText.replace(/["':{}]/g, "").replace('message','');
        if (feedback === 'OK'){
            output.clear()
            output.markdown('Record updated successfuly.')
        }else{
            output.markdown(feedback)
        }
    }
};

async function updateLocator(record){
    let login_status = await login()
    if (login_status){
        let url = "https://api.servername2.com/locator/" 
        let payload = {}
        let locator_type
        if (record.getCellValue('Type').name === 'Wi-Fi'){locator_type = 3}else if(record.getCellValue('Type').name === 'Mobile'){locator_type =1}else{locator_type=2} 
        payload['LocatorName'] = record.getCellValue('Locator Name');
        payload['LocatorId'] = record.getCellValue('MAC')
        payload['LocatorType'] = locator_type
        payload['LocatorSerialNumber'] = record.getCellValue('Serial Number')
        payload['LocatorImsi'] = record.getCellValue('Locator IMSI')       
        let apiResponse = await fetch(url, {method:"PUT", headers:auth_headers, body: JSON.stringify(payload)});
        let responsText = await apiResponse.text()
        let feedback =  responsText.replace(/["':{}]/g, "").replace('message','');
        if (feedback === 'OK'){
            output.clear()
            output.markdown('Record deleted successfuly.')
        }else{
            output.markdown(feedback)
        }
    }
};

async function updateTag(record){
    let login_status = await login()
    if (login_status){
        let url = "https://api.servername2.com/tag/" 
        let tag_type
        if (record.getCellValue('Type').name === 'Mobile'){tag_type = 1}else {tag_type=2}                    
        let payload =  {"TagId": record.getCellValue('MAC'), "NFCId": record.getCellValue('NFC'), "TagType": tag_type};
        let apiResponse = await fetch(url, {method:"PUT", headers:auth_headers});
        let responsText = await apiResponse.text()
        let feedback =  responsText.replace(/["':{}]/g, "").replace('message','');
        if (feedback === 'OK'){
            output.clear()
            output.markdown('Record deleted successfuly.')
        }else{
            output.markdown(feedback)
        }
    }
};

async function updateAsset(record){
    let login_status = await login()
    if (login_status){
        let url = "https://api.servername2.com/asset/" + record.getCellValue('Asset ID')
        let apiResponse = await fetch(url, {method:"PUT", headers:auth_headers});
        let responsText = await apiResponse.text()
        let feedback =  responsText.replace(/["':{}]/g, "").replace('message','');
        if (feedback === 'OK'){
            output.clear()
            output.markdown('Record deleted successfuly.')
        }else{
            output.markdown(feedback)
        }
    }
};

Hello years later! Is there any way to run this script through Zapier like on a schedule?