Jun 14, 2023 05:29 AM
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);
});
}
Solved! Go to Solution.
Jun 14, 2023 11:00 AM - edited Jun 14, 2023 11:04 AM
On a high-level, the script might look like this:
A couple things to consider:
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
Jun 14, 2023 12:33 PM
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.😊
Jun 14, 2023 11:00 AM - edited Jun 14, 2023 11:04 AM
On a high-level, the script might look like this:
A couple things to consider:
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
Jun 14, 2023 12:33 PM
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.😊