Script for Airtable Scripting block to delete blank records

deleteBlankRecords.js

/*******************************************************************************
Title: Delete Blank Records for Airtable scripting block
Author: Kuovonne Vorderbruggen
Website: kuovonne.com
Date Created: March 6, 2020
Version: 1.1
Copyright (c) 2020 by Kuovonne Vorderbruggen
Usage License: MIT License

## Description
This script is designed for use in the Scripting Block of an
(Airtable)[airtable.com] database.

It deletes up to 50 blank records from a user-selected table.

## Airtable Requirements
- Scripting Block installed
- Editor or higher permissions

## How To Use This Script
1. Open an Airtable base with the Scripting Block installed.
2. Copy and paste this script into the code editor.
3. Run the script and follow the instructions.

This script requires no customization.
This script requires no changes to the structure of the database.
********************************************************************************
*/
/*******************************************************************************
  THIS FILE CAN BE USED "AS IS" WITH NO EDITS
*******************************************************************************
*/


/*******************************************************************************
Delete blank records (up to batch limit) in a user selected table
********************************************************************************
*/
const batchLimit = 50; // batch limit matches Airtable Scripting API batch limit
let table = await input.tableAsync('Pick a table');
let {blankRecordIds, reachedBatchLimit} = await gatherBlankRecords(table);
let response = await deleteRecords(blankRecordIds, reachedBatchLimit);
output.inspect(response);
output.markdown("Done");

async function gatherBlankRecords(table) {
  let reachedBatchLimit = false;
  // get list of non-computed fields (don't need to check for values in computed fields)
  let nonComputedFields = table.fields.filter((field) => { return (! field.isComputed)});
  let primaryFieldId = table.fields[0].id;
  let nonComputedFieldIds = nonComputedFields.map((field) => { return field.id});
  // get all the records in the table, returning only non-computed fields
  // sort by primary field, as blank records should appear first
  let queryResult = await table.selectRecordsAsync({
    sorts: [{field: primaryFieldId, direction: 'asc'}],
    fields: nonComputedFieldIds
  });
  // pick out blank records until reach batch limit or the end of the records
  let blankRecordIds = [];
  for (let record of queryResult.records) {
    let recordIsBlank = true;
    // check if this record has a value for any non-computed field
    for (let fieldId of nonComputedFieldIds) {
      if (record.getCellValue(fieldId) !== null) {
        recordIsBlank = false;
        break; // don't need to check other fields for this record, move on ot next record
      }
    }
    if (recordIsBlank) {
      // add blank record to list of blank records and check for batch limit
      blankRecordIds.push(record.id);
      if (blankRecordIds.length >= batchLimit) {
        reachedBatchLimit = true;
        break; // reached the batch limit so can stop checking other records
      }
    };
  }
  return {blankRecordIds, reachedBatchLimit};
}

async function deleteRecords(blankRecordIds, reachedBatchLimit = null) {
  // tell user number of blank records and show preview
  if (blankRecordIds.length == 0) {
    output.markdown(`# No blank records found.`);
    return ("no records to delete");
  } else if (blankRecordIds.length == 1) {
    output.markdown(`# Found 1 blank record to delete.`);
  } else if (reachedBatchLimit) {
    output.markdown(`Found ${batchLimit} blank records to delete (batch limit). There may be more.`);
  } else {
    output.markdown(`# Found ${blankRecordIds.length} blank records to delete`);
  }
  output.inspect(blankRecordIds);
  // ask for confirmation
  let userResponse = await input.buttonsAsync('Delete records?', [
    {label: 'Abort', value: 'Abort'},
    {label: 'Delete', value: 'Delete', variant: 'danger'}
  ]);
  // Evaluate user decision
  if (userResponse == "Delete") {
    // do the delete
    await table.deleteRecordsAsync(blankRecordIds);
    return "Deleted records";
  } else {
    return "Aborted";
  }
}