Skip to main content

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

});

}

 

 

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

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


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

Reply


Terms & Conditions