Help

Unable to delete 10 records at a time. Can anyone solve this problem?

Solved
Jump to Solution
1405 6
cancel
Showing results for 
Search instead for 
Did you mean: 
danielmohuriya
5 - Automation Enthusiast
5 - Automation Enthusiast

I have a script that runs in google apps script and deletes all the records in a airtable table one by one, since I am new to coding and don't know how to code can anyone help me to modify the script to delete the records in batches of 10. I used chat-gpt to generate the script.
The script that deletes airtable records is:

function deleteRecordsInBatches() {
  var airtableApiKey = "API_KEY/ACCESS_TOKEN"; //Paste your apiKey or accessToken here
  var baseId = "BASE_ID"; //Paste your baseId here
  var tableName = "tab3"; //Paste your tableName

  // Number of records to delete in each batch
  var batchSize = 10;

  var tableUrl = "https://api.airtable.com/v0/" + baseId + "/" + tableName;
  var headers = {
    "Authorization": "Bearer " + airtableApiKey,
    "Content-Type": "application/json"
  };

  // Fetch the initial set of records
  var recordsToDelete = fetchRecords(tableUrl, headers);
  
  while (recordsToDelete.length > 0) {
    var batchRecords = recordsToDelete.splice(0, batchSize);

    // Delete records in the current batch
    deleteBatchRecords(batchRecords, tableUrl, headers);
    
    // Sleep for 1 second to avoid rate limit
    Utilities.sleep(0);
    
    // Fetch the next set of records
    recordsToDelete = fetchRecords(tableUrl, headers);
  }
}

function fetchRecords(tableUrl, headers) {
  var options = {
    "method": "GET",
    "headers": headers,
    "muteHttpExceptions": true
  };

  var response = UrlFetchApp.fetch(tableUrl, options);
  var result = JSON.parse(response.getContentText());

  return result.records;
}

function deleteBatchRecords(records, tableUrl, headers) {
  var options = {
    "method": "DELETE",
    "headers": headers,
    "muteHttpExceptions": true
  };

  records.forEach(function(record) {
    var recordUrl = tableUrl + "/" + record.id;
    UrlFetchApp.fetch(recordUrl, options);
  });
}

 

1 Solution

Accepted Solutions
danielmohuriya
5 - Automation Enthusiast
5 - Automation Enthusiast

With the help of  @kuovonne  and @Stephen_Orr1  and chat-gpt, I was able to generate the answer.

Problem: The original script I provided deleted records one by one instead of in batches of 10, as required.

Solution: To modify the script and make it delete records in batches of 10, the following changes were made:

Introduced a batchSize variable: Added a batchSize variable to specify the number of records to delete in each batch. In this case, the value is set to 10.

Modified the fetchAllRecords function: Updated the fetchAllRecords function to retrieve all records from the Airtable table. The function now uses pagination to fetch records in chunks, considering the possibility of more than 100 records. The function keeps making requests with the appropriate offset until all records have been retrieved.

Updated the deleteBatchRecords function: Modified the deleteBatchRecords function to send a single request to delete multiple records at once. Instead of making individual requests to delete each record, the function builds a payload with the IDs of the records to delete and sends a single HTTP DELETE request to the Airtable API. This significantly improves efficiency by reducing the number of API calls.

Additional Features: In addition to the batch deletion functionality, the following extra features were added:

Rate Limit Handling: To avoid hitting the rate limit of the Airtable API, a sleep function (Utilities.sleep(1000)) was introduced after each batch deletion. It pauses the execution for 1 second to ensure compliance with the rate limit restrictions.

Error Handling (not fully implemented): The script currently lacks error handling code, which would be essential in a production-ready solution. It is important to check the response from the API and handle any errors appropriately, such as rate limit exceeded or network issues. The commented-out sections in the deleteBatchRecords function indicate places where you can add error handling logic based on the specific requirements of your application.

The updated script should now delete records from the Airtable table in batches of 10, with added rate limit handling and improved efficiency. You can post this solution on Stack Overflow as an answer to a question, but make sure to customize it further based on the specific needs of the asker and add appropriate error handling mechanisms.

The updated script is:

function deleteRecordsInBatches() {
  var airtableApiKey = "API_KEY/ACCESS_TOKEN"; //Paste your apiKey or accessToken here
  var baseId = "BASE_ID"; //Paste your baseId here
  var tableName = "tab3"; //Paste your tableName

  // Number of records to delete in each batch
  var batchSize = 10;

  var tableUrl = "https://api.airtable.com/v0/" + baseId + "/" + tableName;
  var headers = {
    "Authorization": "Bearer " + airtableApiKey,
    "Content-Type": "application/json"
  };

  // Fetch all records to be deleted
  var allRecords = fetchAllRecords(tableUrl, headers);
  
  while (allRecords.length > 0) {
    var batchRecords = allRecords.splice(0, batchSize);

    // Delete records in the current batch
    deleteBatchRecords(batchRecords, tableUrl, headers);
    
    // Sleep for 1 second to avoid rate limit
    Utilities.sleep(1000);
  }
}

function fetchAllRecords(tableUrl, headers) {
  var allRecords = [];
  var offset = null;
  
  do {
    var options = {
      "method": "GET",
      "headers": headers,
      "muteHttpExceptions": true
    };
    
    var url = tableUrl;
    
    if (offset) {
      url += "?offset=" + encodeURIComponent(offset);
    }
    
    var response = UrlFetchApp.fetch(url, options);
    var result = JSON.parse(response.getContentText());
    
    var records = result.records;
    
    if (records && records.length > 0) {
      allRecords = allRecords.concat(records);
    }
    
    offset = result.offset;
  } while (offset);
  
  return allRecords;
}

function deleteBatchRecords(records, tableUrl, headers) {
  var options = {
    "method": "DELETE",
    "headers": headers,
    "muteHttpExceptions": true
  };

  var recordIds = records.map(function(record) {
    return record.id;
  });
  
  var payload = {
    "records": recordIds
  };
  
  var payloadString = JSON.stringify(payload);

  var batchUrl = tableUrl + "?records[]=" + recordIds.join("&records[]=");
  
  var response = UrlFetchApp.fetch(batchUrl, options);

  // Check the response and handle any errors if necessary
  // ...

  // Optional: Log the response for debugging
  // Logger.log(response.getContentText());
}

 

See Solution in Thread

6 Replies 6

Thank you for explaining your coding background and how you obtained this code. Thank you also for formatting the code in a way that is legible. 

It looks like the code is putting records into batches, and then passing the batch to the deleteBatchRecords() function, which then proceeds to process each record in the batch individually. 

If you are interested in learning to code, I recommend looking at the documentation for deleting multiple records.

The code currently getting all records for a specified table, then batching these records into batches of 10 records, then running a single API call per record in this batch. As you pointed out, this is ignoring the "batch into 10 records" logic as to Airtable, this looks like one API call per record regardless.

Instead, you'll need to loop through the records returned in your initial fetch and grab 10 records every loop to use as a query parameter with the https://airtable.com/developers/web/api/delete-multiple-records API call.

Side note - I know Airtable rest API docs are intended to be language agnostic but it would be so nice if Javascript examples were provided next to curl... It would immediately help answer questions like these and encourage more people to use the API (and doesn't Airtable want that? :D).

Instead, you'll need to loop through the records returned in your initial fetch and grab 10 records

The script already puts the records in batches of 10.

var batchRecords = recordsToDelete.splice(0, batchSize);

 

it would be so nice if Javascript examples were provided next to curl... It would immediately help answer questions like these and encourage more people to use the API (and doesn't Airtable want that? :D).

A previous version of the API documentation did include JavaScript examples, and maybe even other languages. However, I think the JavaScript examples assumed that you had the library, so it wasn't very useful to me, as my first foray into using the API was via PHP. I imagine that Airtable decided that language specific examples were too much to maintain, especially since there was the possibility of using a library versus raw calls.

I totally get that seeing more examples would be helpful. On the other hand, I also understand the difficulties in writing documentation. In these cases I tend to use a mixture of reading documentation and searching for example code on the internet. Once you understand how to implement a fetch request in JavaScript, the documentation is sufficient. Getting to that point of knowing how to implement a fetch request is a whole different matter (and relates to whether or not you want to learn to code), and I currently think that self-teaching JavaScript via ChatGPT is problematic.

It would immediately help answer questions like these and encourage more people to use the API (and doesn't Airtable want that? :D).

I think it is debatable whether or not Airtable actually wants users to be using the API. An increase in API use doesn't necessarily cause an increase in paid subscribers. And there seems to be no way to purchase higher rate limits than 5 requests per second. SaaS platforms that really encourage API usage tend to have the ability to buy much higher rate limits. I think Airtable is more focused on increasing people who use Airtable directly (either through the app itself or via interfaces).


@kuovonne wrote:
Instead, you'll need to loop through the records returned in your initial fetch and grab 10 records

The script already puts the records in batches of 10.

I suppose I could have worded this better. "Instead" here refers to the query parameter part as in "instead, construct a query parameter that includes a full batch of record IDs, per the docs."

 

A previous version of the API documentation did include JavaScript examples, and maybe even other languages. However, I think the JavaScript examples assumed that you had the library, so it wasn't very useful to me, as my first foray into using the API was via PHP.

I thought I remembered this being available... glad I'm not crazy! My first experience with server-side scripting was also in PHP building a health flexible spending account web app for employees of my university. Moment of silence for the LAMP stack... 

Now that Node.js has native fetch(), importing libraries won't be necessary like it used to be. Examples would essentially be the same for browser or server JS.

 

I currently think that self-teaching JavaScript via ChatGPT is problematic.

I agree with this for the most part. ChatGPT gets some things wrong and straight up lies about the expected output of code in my experience. Maybe one day this won't be the case. I do think it is a fantastic tool for explaining complex code or strange syntax to newbies. It can aid in learning a language (yet not best practices) as long as you are inquisitive and want to know "why" and not just the means to an end.

To your last point, I can see this being a factor. And selfishly, I wouldn't want it to become such a major cost or opportunity for Airtable that it warrants being a paid feature (ahem Reddit!!). I also like empowering people with tech though and this stuff (like most things) is doable with the desire to learn. Better docs will increase users, then Airtable can offer paid higher RPS once they're hooked 🙂

danielmohuriya
5 - Automation Enthusiast
5 - Automation Enthusiast

@kuovonne and @Stephen_Orr1 thank you so much for reaching out to help!!

danielmohuriya
5 - Automation Enthusiast
5 - Automation Enthusiast

With the help of  @kuovonne  and @Stephen_Orr1  and chat-gpt, I was able to generate the answer.

Problem: The original script I provided deleted records one by one instead of in batches of 10, as required.

Solution: To modify the script and make it delete records in batches of 10, the following changes were made:

Introduced a batchSize variable: Added a batchSize variable to specify the number of records to delete in each batch. In this case, the value is set to 10.

Modified the fetchAllRecords function: Updated the fetchAllRecords function to retrieve all records from the Airtable table. The function now uses pagination to fetch records in chunks, considering the possibility of more than 100 records. The function keeps making requests with the appropriate offset until all records have been retrieved.

Updated the deleteBatchRecords function: Modified the deleteBatchRecords function to send a single request to delete multiple records at once. Instead of making individual requests to delete each record, the function builds a payload with the IDs of the records to delete and sends a single HTTP DELETE request to the Airtable API. This significantly improves efficiency by reducing the number of API calls.

Additional Features: In addition to the batch deletion functionality, the following extra features were added:

Rate Limit Handling: To avoid hitting the rate limit of the Airtable API, a sleep function (Utilities.sleep(1000)) was introduced after each batch deletion. It pauses the execution for 1 second to ensure compliance with the rate limit restrictions.

Error Handling (not fully implemented): The script currently lacks error handling code, which would be essential in a production-ready solution. It is important to check the response from the API and handle any errors appropriately, such as rate limit exceeded or network issues. The commented-out sections in the deleteBatchRecords function indicate places where you can add error handling logic based on the specific requirements of your application.

The updated script should now delete records from the Airtable table in batches of 10, with added rate limit handling and improved efficiency. You can post this solution on Stack Overflow as an answer to a question, but make sure to customize it further based on the specific needs of the asker and add appropriate error handling mechanisms.

The updated script is:

function deleteRecordsInBatches() {
  var airtableApiKey = "API_KEY/ACCESS_TOKEN"; //Paste your apiKey or accessToken here
  var baseId = "BASE_ID"; //Paste your baseId here
  var tableName = "tab3"; //Paste your tableName

  // Number of records to delete in each batch
  var batchSize = 10;

  var tableUrl = "https://api.airtable.com/v0/" + baseId + "/" + tableName;
  var headers = {
    "Authorization": "Bearer " + airtableApiKey,
    "Content-Type": "application/json"
  };

  // Fetch all records to be deleted
  var allRecords = fetchAllRecords(tableUrl, headers);
  
  while (allRecords.length > 0) {
    var batchRecords = allRecords.splice(0, batchSize);

    // Delete records in the current batch
    deleteBatchRecords(batchRecords, tableUrl, headers);
    
    // Sleep for 1 second to avoid rate limit
    Utilities.sleep(1000);
  }
}

function fetchAllRecords(tableUrl, headers) {
  var allRecords = [];
  var offset = null;
  
  do {
    var options = {
      "method": "GET",
      "headers": headers,
      "muteHttpExceptions": true
    };
    
    var url = tableUrl;
    
    if (offset) {
      url += "?offset=" + encodeURIComponent(offset);
    }
    
    var response = UrlFetchApp.fetch(url, options);
    var result = JSON.parse(response.getContentText());
    
    var records = result.records;
    
    if (records && records.length > 0) {
      allRecords = allRecords.concat(records);
    }
    
    offset = result.offset;
  } while (offset);
  
  return allRecords;
}

function deleteBatchRecords(records, tableUrl, headers) {
  var options = {
    "method": "DELETE",
    "headers": headers,
    "muteHttpExceptions": true
  };

  var recordIds = records.map(function(record) {
    return record.id;
  });
  
  var payload = {
    "records": recordIds
  };
  
  var payloadString = JSON.stringify(payload);

  var batchUrl = tableUrl + "?records[]=" + recordIds.join("&records[]=");
  
  var response = UrlFetchApp.fetch(batchUrl, options);

  // Check the response and handle any errors if necessary
  // ...

  // Optional: Log the response for debugging
  // Logger.log(response.getContentText());
}