Hello there,
I'm a recent customer of Airtable, i really love this solution but i have some issues with scripting automation.
When i run my code on scripting app i don't have any problem but when i try to create an automation like a button on click i receive this error message -> Error: Exceeded quota of 30 table queries per script invocation.
I checked the forum but i don't find the real solution of this issue. Could we help me ? Thx a lot
My code :
// Clean all table
let table = base.getTable("fetch_articles");
let query = await table.selectRecordsAsync();
let recordsToDelete = query.records;
let batchedRecords = [];
while (recordsToDelete.length > 0) {
let batch = recordsToDelete.slice(0, 50);
batchedRecords.push(batch);
recordsToDelete = recordsToDelete.slice(50);
}
for (let i = 0; i < batchedRecords.length; i++) {
await table.deleteRecordsAsync(batchedRecords[i]);
}
// Run code
function btoa(token) {
var Base64={_keyStr:"ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/=",encode:function(e){var t="";var n,r,i,s,o,u,a;var f=0;e=Base64._utf8_encode(e);while(f<e.length){n=e.charCodeAt(f++);r=e.charCodeAt(f++);i=e.charCodeAt(f++);s=n>>2;o=(n&3)<<4|r>>4;u=(r&15)<<2|i>>6;a=i&63;if(isNaN(r)){u=a=64}else if(isNaN(i)){a=64}t=t+this._keyStr.charAt(s)+this._keyStr.charAt(o)+this._keyStr.charAt(u)+this._keyStr.charAt(a)}return t},decode:function(e){var t="";var n,r,i;var s,o,u,a;var f=0;e=e.replace(/[^A-Za-z0-9\+\/\=]/g,"");while(f<e.length){s=this._keyStr.indexOf(e.charAt(f++));o=this._keyStr.indexOf(e.charAt(f++));u=this._keyStr.indexOf(e.charAt(f++));a=this._keyStr.indexOf(e.charAt(f++));n=s<<2|o>>4;r=(o&15)<<4|u>>2;i=(u&3)<<6|a;t=t+String.fromCharCode(n);if(u!=64){t=t+String.fromCharCode(r)}if(a!=64){t=t+String.fromCharCode(i)}}t=Base64._utf8_decode(t);return t},_utf8_encode:function(e){e=e.replace(/\r\n/g,"\n");var t="";for(var n=0;n<e.length;n++){var r=e.charCodeAt(n);if(r<128){t+=String.fromCharCode(r)}else if(r>127&&r<2048){t+=String.fromCharCode(r>>6|192);t+=String.fromCharCode(r&63|128)}else{t+=String.fromCharCode(r>>12|224);t+=String.fromCharCode(r>>6&63|128);t+=String.fromCharCode(r&63|128)}}return t},_utf8_decode:function(e){var t="";var n=0;var r=c1=c2=0;while(n<e.length){r=e.charCodeAt(n);if(r<128){t+=String.fromCharCode(r);n++}else if(r>191&&r<224){c2=e.charCodeAt(n+1);t+=String.fromCharCode((r&31)<<6|c2&63);n+=2}else{c2=e.charCodeAt(n+1);c3=e.charCodeAt(n+2);t+=String.fromCharCode((r&15)<<12|(c2&63)<<6|c3&63);n+=3}}return t}}
return Base64.encode(token)
}
const fetchArticles = async function () {
const username = "myUsername";
const password = "myPassword";
const headers = new Headers({
'Authorization': "Basic " + btoa(`${username}:${password}`)
});
let now = new Date();
now.setDate(now.getDate() - 1);
now.setHours(0, 0, 0, 0);
let allArticles = [];
let page = 1;
while (true) {
let apiUrl = `https://www.herault-tribune.com/wp-json/wp/v2/article?per_page=50&page=${page}&after=${now.toISOString()}&status=draft,future,publish`;
let article = await fetch(apiUrl, { headers })
let data = await article.json();
allArticles.push(...data);
if (data.length < 50) {
// If the number of returned posts is less than the specified per_page value,
// it means we have fetched all the available posts, so we can stop fetching.
break;
}
page++;
}
return allArticles;
};
const data = await fetchArticles();
console.log(data)
// Function to get name by ID
const getIds = async function (dataObj, tableName) {
// DATA TABLE
let table = base.getTable(`${tableName}`);
let records = await table.selectRecordsAsync();
if (Array.isArray(dataObj)) {
// If dataObj is an array, map the values to get the corresponding record ids
let ids = dataObj.map((item) => {
for (let record of records.records) {
if (record.getCellValue("id") === item) {
return record.id;
}
}
});
// Remove undefined values from the locationsIds array
ids = ids.filter((id) => id !== undefined);
// If no matching records are found, return null
return ids.length ? ids : null;
} else {
// Look for a matching record in the table
for (let record of records.records) {
if (record.getCellValue("id") === dataObj) {
return [record.id];
}
}
// If no matching record is found, return null
return null;
}
}
// CREATE RECORDS ARRAY
let createArr = [];
const importTable = base.getTable('fetch_articles');
// Get existing record IDs from the "test-import" table
const existingRecords = await importTable.selectRecordsAsync({ fields: ["id"] });
const existingRecordIds = existingRecords.records.map((record) => record.getCellValue("id"));
for (let obj of data) {
// Check if the record already exists in the "test-import" table
if (existingRecordIds.includes(obj.id)) {
console.log(`Record with ID ${obj.id} already exists in the "test-import" table. Skipping...`);
continue;
}
let userId = await getIds(obj.author, "Users");
let locationsIds = await getIds(obj.location, "Villes");
let communesIds = await getIds(obj.location, "Intercommunalités");
let parentCategoriesIds = await getIds(obj.type_article, "parent_categories");
let childCategoriesIds = await getIds(obj.type_article, "child_categories");
let fields = {
"id": obj.id,
"title": obj.title.rendered,
"date": obj.date,
"Users": userId ? [{ "id": userId[0] }] : null,
"lien": obj.link,
"statut": { name: obj.status },
"Villes ( id )": locationsIds ? locationsIds.map(id => ({ id })) : null,
"Intercommunalités ( id )": communesIds ? communesIds.map(id => ({ id })) : null,
"Catégories ( id )": parentCategoriesIds ? parentCategoriesIds.map(id => ({ id })) : null,
"Sous Catégories ( id )": childCategoriesIds ? childCategoriesIds.map(id => ({ id })) : null,
};
createArr.push({ fields });
}
// CREATE RECORDS
while (createArr.length) {
await importTable.createRecordsAsync(createArr.slice(0, 50));
createArr = createArr.slice(50)
}
