How to delete all records from a table using google apps script?

[danielmohuriya]

I have a script that retrieves data from a google photos library using google apps script and writes the data to a airtable base, I have no coding experience and have generated the script through Chat-gpt. The script retrieves all the album names and the number of photos in each albums.
          What I want to achieve is that a script should delete all the existing records in the specified base before writing new records to it, using google apps script. Can someone please help me by modifying the script for me?

Here is the script that i use to retrieve data from google photos library and write the data to an airtable base:

function syncAlbumData() {
  var apiKey = "apiKey-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

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

[Marko_K · Accepted solution]

On a high-level, the script might look like this:

1. Fetch Records: Use a GET request to retrieve all records from the desired Airtable base and table.
2. Delete in Batches: Use DELETE requests to remove these records in batches.

A couple things to consider:

• 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.

Below is an example. Let me know how it goes.

var API_KEY_OR_ACCESS_TOKEN = ".......";

var baseId = `....`;
var tableId = `....`; // Or name.

// Fetch all records from Airtable
var res = UrlFetchApp.fetch(
  `https://api.airtable.com/v0/${baseId}/${tableId}`,
  {
    method: "get",
    headers: {
      Authorization: `${API_KEY_OR_ACCESS_TOKEN}`,
    },
  }
);

var records = JSON.parse(res.getContentText()).records;

var batch = [];

for (var i = 0; i < records.length; i++) {
  batch.push(records[i].id);

  // Batch the requests.
  if (batch.length === 10 || i === records.length - 1) {
    UrlFetchApp.fetch(
      `https://api.airtable.com/v0/${baseId}/${tableId}`,
      {
        method: "delete",
        payload: JSON.stringify({ records: batch }),
        headers: {
          Authorization: `${API_KEY_OR_ACCESS_TOKEN}`,
          "Content-type": "application/json",
        },
      }
    );
    batch = [];

    Utilities.sleep(1000); // Pause for 1 second, due to rate limits.
  }
}

Logger.log("Finished.");

---

Marko at Subsystem

[danielmohuriya · Accepted solution]

Hey Marko, thank you so much for reaching out to help me, the code you provided worked, though not directly, but with the help of your code chat-gpt gave me a new code which works.
The final script that works with the help of your provided 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);
  });
}

 Once again thank you.😊