Automation - Exceed quota 30 table queries, what to do?

[Emilie]

Hello,

I have this javascript that i want to add in my automation in order to transpose a table but i always have this error message "Exceed quota 30 table queries". How to solve it?

// Get the "Forecast artikel" table and the "Transposed" table
const tableForecastArtikel = base.getTable("Forecast Artikel");
const tableTransposed = base.getTable("Transposé");

// Retrieve all records from the "Forecast artikel" table
const queryForecastArtikel = await tableForecastArtikel.selectRecordsAsync();

// Array to store updates
let updates = [];

// Iterate through records in the "Forecast artikel" table
for (const recordForecastArtikel of queryForecastArtikel.records) {
// Get the article name from the "Name" column of "Forecast artikel"
const articleName = recordForecastArtikel.getCellValue("Name");

// Iterate through columns like "Tag 1," "Tag 2," "Tag 3," and so on in "Forecast artikel"
for (let i = 1; i <= 31; i++) { // Make sure to adjust the upper limit based on the number of columns in "Forecast artikel"
const columnName = `Tag ${i}`;
const cellValue = recordForecastArtikel.getCellValue(columnName);

if (!isNaN(cellValue)) {
// Retrieve corresponding records in "Transposed" based on "Name" and "articleName"
const queryTransposed = await tableTransposed.selectRecordsAsync({
filterByFormula: `AND({Name} = '${columnName}', FIND('${articleName}', Name) > 0)`
});

// Update corresponding records in "Transposed" with values from "Forecast artikel"
for (const recordTransposed of queryTransposed.records) {
try {
const transposedName = recordTransposed.name;
const transposedNameParts = transposedName.split(' ');
const lastPart = transposedNameParts[transposedNameParts.length - 1];

if (lastPart === i.toString()) {
updates.push({
id: recordTransposed.id,
fields: {
[articleName]: cellValue,
}
});
}
} catch (error) {
console.error(`Error during update: ${error.message}`);
// Continue with other records in case of an error
}
}
}
}
}

// Split the "updates" array into batches of 30 updates and perform the updates
while (updates.length > 0) {
const batchUpdates = updates.slice(0, 30);
updates = updates.slice(30);

// Use the "updateRecordsAsync" function to update records in batches
await tableTransposed.updateRecordsAsync(batchUpdates);
}

console.log("Data update in the 'Transposed' table from 'Forecast artikel' completed successfully.");

Thank you !

Emilie

[kuovonne]

Can you share where this script came from? Is there a reason you cannot ask the original author of the script?

Does this script produce the results you want when running in scripting extension? If not, I recommend getting the script to produce the desired results in scripting extension first, as scripting extension does not have a limit on the number of queries.

The reason for the error about exceeding 30 queries is because you call selectRecordsAsync inside of a loop, and the loop runs for every record in the table, and there probably are over 30 records in your table. One way of dealing with this issue is to query the other table once (outside of the loop) and then build a hash table. (Bill French had written some excellent posts over the years about using hash tables.)

However, I think this script has issues that go beyond the limit of 30 queries. 

Please report back! I find writing scripts to be very empowering and enjoy learning about other people’s coding journeys. 

[Emilie]

Hello, I am the author of this script and this script works perfectly as I want in the extension scripting of Airtable that's why I wanted to automate it because it is much better. But I do not have so much knowledge about programming that's why I do not know how to make it work in the automation because yes my script exceed much more than 30 queries (maybe 200?).

Thanks for the feedback!

[dilipborad]

@kuovonne is right.

Then you should need to run it on Extension Script because if script exceed much more than 30 queries (maybe 200?) then it will be also exceeded execution time limit for 30 seconds.

You can share more details in message and contact for help in script. I can help you. 👍

[kuovonne]

Since the script works in Scriptung Extension, I suggest you use the technique of moving the select query to before the loop, so it is performed only once total, instead of with every loop. You will have to use JavaScript to find the record you want from the query results inside the loop. There are several ways to do this. Bill has written posts about using a hash table. If you don’t feel up to building and using a hash table, you can do an array find.

I’m curious about your use of filterByFormula. When I last looked at the scripting documentation, scripting extension did not support filterByFormula. Can you tell more about your choice to use it and how it works for you?

[kuovonne]

@dilipborad Can you explain why you recommend sticking with scripting extension instead of refactoring the script so that it can run faster with fewer queries?

Is there a reason you feel that this script cannot be refactored to run as an automation script? (If so, I’d like to understand your reasons.)

Or are you suggesting that she hire you to refactor the script to run as an automation script? (If so, that’s totally okay too.)

[dilipborad]

@kuovonne  Thanks for clarifying.

As per my experience when we process more than 100 rows using Automation script most of the time it's timeout due to an exceeded execution time limit of 30 seconds.

If the extension script is working fine then run it on the automation script as you said.

No matter if she hires me or not, Just try to fix the error.

[kuovonne]

@dilipborad Thanks for the explanation that your recommendation is based on personal experience. I guess we have different experiences. Building a hash (which I learned from Bill’s posts from a few years ago) has helped me write better scripts. 

[Emilie]

Thanks for the help !

[dilipborad]

Hello @kuovonne,

Thanks in advance but can you share that Bill’s posts(link)?

I would also like to learn it.