I need to loop through a table of mysql table and then call out to an API to get additional data to add it to my table.
For example; I have a table that has these fields:
ID, name, API data
The "ID" and "name" fields both come from the MySQL table and are displaying fine. I have manually added in an "API Data" column at the end of the table.
Now, I need to write a script to loop through all of the table data, grab the ID, and then make an API call with that ID. The results of the single API call will then be added to the current row in the "API Data" column.
How do I write a script to do this? Or is there a better way of getting this data? Thanks!
(p.s. bonus question - is the A.I. code helper coming to the scripting area in retool soon? I see it's available in the workflow and mysql areas. But, not in the scripting areas)
First get the data from your MySQL query using a resource query. Run it to test it.
Then in a js script, loop through the data and trigger another resource REST query (i named it as example apiResourceQueryName ) to call the API endpoint:
getRows.data.forEach(row => {
// Trigger the API for each row
apiResourceQueryName.trigger({
additionalScope: {
id: row.id,
name: row.name
}
})
After that, the response from the API is to be used to trigger another resource query to update the row in your database.
Without more information about the API it’s difficult to give a better answer to your specific question. What API are you using?
Depending on the API and the amount of rows, you might need to throttle it so you are not spamming the endpoint with requests.
Sometimes bulk updates should be used instead for better performance.
Also, if there’s a graphql api, it could be better to request all data from the api in one call.
Got it. One more question - how do I update the row on the correct column with the API data? It will just be a string. I know the name of the column and it's location in the table. Thanks!
I ended up re-writing my sql to just have it on the column at the end, hide it and then reference it for the total at the bottom instead.
For the scripting areas for the AI, I am referring to the event handler areas (and really anywhere you would need to write scripts). It would be great to have the same AI tool as workflows in there (as well as have the options for JS or Python). Thanks!
I dont see that on our roadmap yet, but I'll request it