I have a tool that uses a CSV file to fill in URL parameters for a restapi and pull some data, then insert it into a corresponding Airtable table. I used the "Run an API request for each row in a table" to write the script.
Currently I have 14 queries, 1 query makes the API call and gets the data, the rest all insert data into Airtable. Ideally, I want the 13 other queries to finish before the Get_Data API call is triggered again
Currently i'm accomplishing this with an await delay() of 30 seconds, but some records take even longer, and it takes loads of time to complete.
I've attempted working with promises and await but i'm not very familiar with JS at all so i'm wondering what is the best way to accomplish this?
Hi @joelime! In case you haven't already looked in our docs, we have some info on Scripting Retool > Promises and Async Queries. You could also explore these docs on Async/await for further reading. And, last but not least, there's an existing community thread that discusses best practices for synchorous JavaScript queries.
Hope this helps, and let me know if there's anything else I can help with!
Hey Alina! I've attempted to follow the documents and the thread you provided but it seems i'm missing something crucial. According to this doc you provided, an async function always returns a promise. I'm not sure if i'm understanding correctly but that would mean the promise is resolved when the function hits the return line, right?
I set the await keyword within the OnSuccess function as I want the second query to trigger on succes, however I want the second query to finish inserting all its rows into Airtable before it triggers again. I tried doing it this way, but no dice.
Hey @joelime!
Looks like you're on the right track here, are you already using Promise.all with those other 13 queries? If not you might want to check it out! (There's an example of its usage in Retool in this post.)
Hey Kabirdas, I have not used Promise.all. I took a look at the example, I just have a question of where in my script I would add this in? Would it be within my original function? Or would I have to create a separate function?
You can include it in your script! You just need to have a reference to them in an array, something like this should work:
const rows = table1.data;
let errors = "";
const additionalQueries = [query3, query4, query5]; // --------Reference your queries here
function runQuery(i) {
if (i >= rows.length) {
console.log("Finished running all queries");
return;
}
console.log("Running query for row", i);
query1.trigger({
additionalScope: { i: i },
onSuccess: async function () {
const triggeredQueries = additionalQueries.map(query => query.trigger({additionalScope: {i}})); // --------Trigger your queries here
await Promise.all(triggeredQueries); // ----------Wait for your queries to finish before moving on
runQuery(i + 1);
},
onFailure: function (error) {
// Update the Errors text
errors += "Found error at line " + i.toString() + ": " + error + "\n\n";
runQuery(i + 1);
},
});
}
runQuery(0);
Hey Kabridas, I tried incorporating your example into my script:
var rows = fileButton1.parsedValue[0];
var errors = '';
var total = rows.length;
var console = '';
const additionalQueries = [Insert_Assesments_By_Row]; // --------Reference your queries here
async function runQuery (i) {
// Update the statusText with the current progress
text1.setValue(i.toString() + '/' + total.toString())
if (i >= rows.length) {
console +='Finished running all queries';
textArea1.setValue(console)
return;
}
console +='Getting data for row'+' '+i +'\n';
textArea1.setValue(console)
Get_Data.trigger({
additionalScope: { i: i }, // This is where we override the `i` variable from step 2!
// You can use the argument to get the data with the onSuccess function
onSuccess: async function(data) {
console +='Got data for row'+' '+i +'\n';
const triggeredQueries = additionalQueries.map(query => query.trigger({additionalScope: {i:i}})); // --------Trigger your queries here
await Promise.all(triggeredQueries); // ----------Wait for your queries to finish before moving on
runQuery(i + 1);
},
onFailure: function(error) {
// Update the errorsText with all the errors encountered
console += 'Found error at line '+' '+ i.toString()+'\n';
textArea1.setValue(console)
runQuery(i + 1);
}
});
}
runQuery(0);
It seems that when it reaches this line await Promise.all(triggeredQueries); // ----------Wait for your queries to finish before moving on
It is triggering the query, however it does not seem to wait for it to finish running before triggering the first query again.
Is there something I may be missing?
do you mind sharing the code for Insert_Assessments_By_Row? And are you seeing the same behavior with one of your REST queries?
Sure! It's just the same script from here but with a different source for the number of rows.
var rows = Assesments_Data.data;
var errors = '';
var total = rows.length;
const delay = async ms => new Promise(resolve => setTimeout(resolve, ms))
async function runQuery (i) {
// Update the statusText with the current progress
text3.setValue(i.toString() + '/' + total.toString())
if (i >= rows.length) {
console.log('Finished running all queries');
return;
}
console.log('Running query for row', i);
Insert_Assesments_Airtable.trigger({
additionalScope: { i: i }, // This is where we override the `i` variable from step 2!
// You can use the argument to get the data with the onSuccess function
onSuccess: function(data) {
runQuery(i + 1);
},
onFailure: function(error) {
// Update the errorsText with all the errors encountered
errors += 'Found error at line ' + i.toString();
text4.setValue(errors);
runQuery(i + 1);
}
});
}
runQuery(0);
Thanks for posting that! It looks like, even though you're defining runQuery as an async function, since you're not returning or calling await on anything at the top level of that query it doesn't know to wait and will say it's finished almost instantly while the async function you've defined runs in the background. You can compare that to doing something like:
const insertQueries = Assements_Data.data.map((row, index) => Insert_Assessments_Airtable.trigger({additionalScope: {i : index }}));
return Promise.all(insertQueries);
In which case the query should wait for the Promise to resolve before saying it's complete.
Hey Kabridas, sorry i'm not sure i'm following. Are you saying to do something like this for Insert_Assessments_By_Row?
If I'm understanding your situation correctly you might be able to just copy that code directly as your query (the red lines are because it's done in an environment that doesn't have the proper references):
If you return a promise from a JS query it will run until that promise is either resolved or rejected. Since every time you run .trigger on a query it actually returns a promise, we can call .map on your table to create an array of the individual query promises. We then return Promise.all(insertQueries) since that is itself a promise that completes when all the Insert_Assessments_Airtable queries have completed.
That worked perfectly, thank you so much!
