Help

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

Topic Labels: Automations
1419 15
cancel
Showing results for 
Search instead for 
Did you mean: 
Emilie
4 - Data Explorer
4 - Data Explorer

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

15 Replies 15

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
4 - Data Explorer
4 - Data Explorer

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!

@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. 👍

 

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?

@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.)

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

 

@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
4 - Data Explorer
4 - Data Explorer

Thanks for the help !

Hello @kuovonne,

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

I would also like to learn it.

I don’t have links to Bill’s post. It was before the forum changed software platforms. And I am on my phone and the searching this forum on mobile is lousy. 

@MaddieJ 
This could be a useful exercise for you. How many of the articles written by Bill French about this topic can you find? He has posted about this topic multiple times? Bonus points if you can find the thread where Bill first teaches me the technique. Extra bonus points if you can find the thread where I explain to Bill why we have to use square brackets when using a variable as a key name in the hash map. This was really useful content that could continue to help people, if only they could find it. 

NR
5 - Automation Enthusiast
5 - Automation Enthusiast

In case anyone else reads this thread and goes on the hunt for one of the articles that @kuovonne mentioned above like I did.
https://community.airtable.com/t5/show-tell/filtering-with-loops-in-javascript-how-to-avoid-insanity...

Wow, @NR THANK YOU for finding that thread. It brings back so many memories. 

Would you mind sharing how you found it? Did you happen to pick just the right keywords? Were you using search functions that are not available on mobile?

NR
5 - Automation Enthusiast
5 - Automation Enthusiast

I am afraid I didn't use any advanced techniques, this was actually my first time on the forum.

I didn't try to search since I was not really sure of the keywords to use from the original article. Since the thing I was sure was the author (Bill French), I looked for his profile page (https://community.airtable.com/t5/user/viewprofilepage/user-id/30391). Then looked at the titles of his articles from a couple of years ago.

The irony of all this. In 1999 two fellers in a garage figured out how to make stuff findable. Yet, I had to write this and we continue to struggle to find anything on this community. This is a crime against all who would spend great effort to help others. A real shame.

https://community.airtable.com/t5/show-tell/it-s-2023-and-i-can-t-find-me/td-p/140263