Help

Re: Error: Exceeded quota of 30 table queries per script invocation.

903 1
cancel
Showing results for 
Search instead for 
Did you mean: 
LoicEspinasse
4 - Data Explorer
4 - Data Explorer

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

 

3 Replies 3

Automation scripts can only perform up to 30 selectRecord queries, as the error message is telling you. 

It looks like your script is performing 5 queries for each record that you want to create, in addition to two other queries. That means you won't be able to create more than 5 records.

Can you provide more background on how you got this script and your level of scripting ability? One suggestion is to use a hash table for looking up data.

The script includes some unusual and inconsistent ways of writing things. Although there are many different ways to do things in code, this script does not look like it was written by a single human.

I also suggest breaking this script up into two scripts, each running in its own action.. 

ScottWorld
18 - Pluto
18 - Pluto

@LoicEspinasse 

Since you are trying to bring in records via a custom API, you might be better served by using a tool like DataFetcher or Make’s HTTP module.

Hello @kuovonne, thx for your faster reply. 
I'm not very good in javascript, this code was build thanks to several informations find in the web. 

I gonna try  to breaking this script up like you said.