How to batch delete records from a airtable base using google apps script?

Question

I have a script that retrieves Album names and NO. of albums in each album using google apps script, and I want to delete all the records in a table before writing new records to that table. Here is the script that I use to retrieve data from google photos library and write it to a table in google apps script:

function syncAlbumData() {
  var apiKey = ""; //paste your api or access token here
  var baseId = ""; //paste your base id here
  var tableName = "tab3"; //paste your table name here

  // Get albums from Google Photos
  var albums = [];
  var nextPageToken = null;
  do {
    var pageData = getGooglePhotosAlbums(nextPageToken);
    if (pageData.albums && Array.isArray(pageData.albums)) {
      albums = albums.concat(pageData.albums);
    }
    nextPageToken = pageData.nextPageToken;
  } while (nextPageToken);

  // Prepare records for Airtable
  var records = [];
  albums.forEach(function(album) {
    var record = {
      "fields": {
        "Album Name": album.title,
        "Number of Photos": album.mediaItemsCount
      }
    };
    records.push(record);
  });

  // Write records to Airtable
  writeRecordsToAirtable(records, apiKey, baseId, tableName);
}

function getGooglePhotosAlbums(pageToken) {
  var options = {
    method: "GET",
    headers: {
      "Authorization": "Bearer " + ScriptApp.getOAuthToken()
    },
    muteHttpExceptions: true
  };

  var url = "https://photoslibrary.googleapis.com/v1/albums";
  if (pageToken) {
    url += "?pageToken=" + pageToken;
  }

  var response = UrlFetchApp.fetch(url, options);
  var data = JSON.parse(response.getContentText());

  return data;
}

function writeRecordsToAirtable(records, apiKey, baseId, tableName) {
  var url = "https://api.airtable.com/v0/" + baseId + "/" + tableName;
  var headers = {
    "Authorization": "Bearer " + apiKey,
    "Content-Type": "application/json"
  };

  // Batch the records into groups of 10
  var batchSize = 10;
  var batchedRecords = [];
  while (records.length > 0) {
    batchedRecords.push(records.splice(0, batchSize));
  }

  // Send requests for each batch
  batchedRecords.forEach(function(batch) {
    var payload = {
      "records": batch
    };

    var options = {
      "method": "POST",
      "headers": headers,
      "payload": JSON.stringify(payload)
    };

    UrlFetchApp.fetch(url, options);
  });
}

Since I am new to coding and don't know how to code I used Chat-gpt to generate the above script and I used chat-gpt to solve the issue but wasn't successful. Can some one please help me to include a condition in the above script to delete all the existing records in the table before writing new records to that table.

score 0 · Answer 1 · answered Jun 15 '23 at 04:22

With the help of Marko_K in Airtable cummunity and by using chat-gpt, I got a script that works fine and does exactly what I want.

A couple things to consider:(Words by Marko_K)

Rate Limit: Airtable API allows 5 requests per second per base. Exceeding this will result in a 30-second timeout (429 status code).
Batch Sizes: Up to 10 records deleted per request.

The script is :

function deleteRecordsInBatches() {
  var airtableApiKey = "api-or-accesstoken"; //paste your api or access token here
  var baseId = "base-id"; //paste your base id here
  var tableName = "tab3"; //paste your table name here


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

How to batch delete records from a airtable base using google apps script?

1 Answers1