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