Hi all,
I am newish to Retool and learning a lot. My main weakness is JS. I have a really simple request for help, from which I think I will understand enough to move forward. I have been through the getting started tutorial and read forum posts, but am not able to achieve my goal.
Required Outcome
Loop through a table of Jira email addresses and retrieve their accountID. The API call works fine in a single call, but I want to loop and store in a table (+ other stuff later of course but this is a starting point)
This is my current setup. The JS query actually succeeds (note progress 120/128) but if I create a table to store results based on loopAccountIDs I get 'The selected data source is empty or could not be converted to an array'
Any help extremely appreciated. I am not a js guy (obv) but am used to being able to debug and not sure there is such a feature in retool
Thanks
Nick
Would need to see a bit more of the code/queries to be able to pinpoint what's happening here but...
If you look in the bottom right of your screenshot there's a bug icon with a red dot on it - click that and it may shed some light on what's happened.
(As an aside can I suggest you edit your screenshots to hide email addresses or accountIDs or any other potentially sensitive data, just in case)
Thanks @dcartlidge
I am using this JS query to loop through 'theTeam' table. The query runs successfully, and 'table2' is populated with the results of each query call (just one row).
What I want to do is save the results of each call to a new row. I am not sure how to achieve this from the query below:
var rows = theTeam.data;
var errors = "";
var total = rows.length;
function runQuery(i) {
// Update the Status text
statusLoop.setValue("Progress: " + (i.toString() + "/" + total.toString()));
if (i >= rows.length) {
console.log("Finished running all queries");
return;
}
console.log("Running query for row", i);
getAccountID.trigger({
additionalScope: { i: i }, // This is where we override the `i` variable
// You can use the argument to get the data with the onSuccess function
onSuccess: function (data) {
runQuery(i + 1);
},
onFailure: function (error) {
// Update the Errors text
errors += "Found error at line " + i.toString() + ": " + error + "\n\n";
errorsLoop.setValue(errors);
runQuery(i + 1);
},
});
}
runQuery(0);
'table2' (top right) is set to be the result of the Jira query, so I understand it will always just show the result of each query as the loop proceeds.
Can I achieve adding a row with the result of the query from within this JS? Or is there an additional step/process required?
Thanks
Nick
Hi all,
I found a related post (and sample app). I am able to add a new row to a different table using the following:
// see https://docs.tryretool.com/docs/scripting-retool for docs!
const rowsToSubmit = theTeam.data;
successfulSubmissions.setValue([])
failedSubmissions.setValue([])
isBulkSubmitting.setValue(true)
function submitSingleRow(row) {
//let email_address = row.email_address;
return getAccountID.trigger({ additionalScope: { row} })
}
async function bulkSubmit(rows) {
const successes = []
const errors = []
for (let i = 0; i < rows.length; i++) {
const row = rows[i]
console.log('Running query for row', row);
try {
const res = await submitSingleRow(row)
successes.push(Object.assign(row, { res }))
successfulSubmissions.setValue(successes)
} catch (err) {
errors.push(Object.assign(row, { err }))
failedSubmissions.setValue(errors)
}
}
}
bulkSubmit(rowsToSubmit)
.then(() => {
isBulkSubmitting.setValue(false)
utils.showNotification({ title: 'Finished bulk submit!', notificationType: 'success' })
})
The new table's source is 'successfulSubmissions.value'. However, i'm not sure how I pass the correct parameter to my API request from within this JS. The request is:
GET https://;ourorganisation'.atlassian.net/rest/api/3/user/search/?query=
So what I need to do is pass in 'email_address' from the table I'm looping through. If I just set the query={{theTeam.data[i].email_address, I of course get back a 'res' within the results which just contains the results from the first email in the table.
Any help hugely appreciated!
Nick
Hi all,
I was able to get the behaviour I needed following this post
I'm now facing a different issue with a similar query - this one returns nested objects and I'm not sure how to access them in a table.This is the JS I'm using:
return Promise.all(
theTeam.data.map((row) => {
return getTimeLogsFromJS.trigger({
additionalScope: {
accountId: row.jira_account_id, from: fromTo.value.start, to: fromTo.value.end,
},
});
})
).then((results) => {
successfulSubmissions.setValue(results)
});
I then try to access successfulSubmissions using:
table data source = {{successfulSubmissions.value}}
field source = {{item[i].self}}
or {{successfulSubmissions.value[i].results[i].self}}
Screenshot below. I want to have a row in the table for each element of value[i].results[i]."keys"
Hey @nickaus!
What happens if you use something like the following:
const queryResponses = await Promise.all(
theTeam.data.map((row) => {
return getTimeLogsFromJS.trigger({
additionalScope: {
accountId: row.jira_account_id,
from: fromTo.value.start,
to: fromTo.value.end,
},
});
})
);
const formattedData = queryResponses.flatMap(queryData => queryData.results);
return formattedData;
With that, you'd also want to use your JavaScript query as your table's data source instead of the temp state. It will hopefully pick up the values a bit better and let you use more of the table UI to interact with your data.
To explain the script a bit: queryResponses should match what you posted in the screenshot. Using queryData => queryData.results should extract the results array from each response. Combining that with flatMap you should then get you a nice flat array.
Can you let me know if that works?
Hi @Kabirdas,
Thanks for your reply and apologies in the delay.
That approach would be helpful as I need to run a Query JSON with SQL process on completion.
However I get this message when attempting to run it:
getTempoTimeLogs_New: queryResponses.flatMap is not a function
Any other suggestions I could try?
Regards,
Nick
Oh geez, I forgot an await 
sorry! You'll need to write const queryResponses = await Promise.all(...) instead of const queryResponses = Promise.all(...).
I've edited the post above to include it, does that work better?
Hi @Kabirdas
Thanks! I tried it and it did work. I've changed approach a few times over the duration of this chat, most recently I was getting 429 rate limit errors from Tempo, so switched to using https://api.tempo.io/4/worklogs? and passing in from/to and projectId. Attempting to use POST to https://api.tempo.io/4/worklogs/search? or GET https://api.tempo.io/4/worklogs/user/... resulted in too many requests when looping through our whole team.
Tempo's rate limits are not well documented I found.
I tried using debounce and throttle but did not seem to have much luck.
I have had to switch to non-Retool work for a bit but will come back and relook at it.
Thanks again for your responses. Really accelerating my learning curve in Retool!
Nick
Good to hear that worked!
It's possible to batch requests in the same script as well! The lodash _.chunk function is particularly helpful for that:
const delay = (ms) => new Promise((resolve) => setTimeout(resolve, ms));
const batches = _.chunk(theTeam.data, batchSize);
const collectedResponses = [];
for (let i in batches) {
console.log("Running batch " + i);
// ---- this is basically the same as above, just replacing theTeam.data with batches[i]
const queryResponses = await Promise.all(
batches[i].map((row) => {
return getTimeLogsFromJS.trigger({
additionalScope: {
accountId: row.jira_account_id,
from: fromTo.value.start,
to: fromTo.value.end,
},
});
})
);
const formattedData = queryResponses.flatMap(
(queryData) => queryData.results
);
// ----
collectedResponses.push(...formattedData);
await delay(200); //optional
}
return collectedResponses;
(Edited in line with the post below)
1 Like
Wow @Kabirdas you're the best!
I had to make just two minor tweaks:
- declaring
const batchSize = 10;
- Changing
batch[i] to batches[i]
After that it works perfectly 
. Well also after I realised I needed a new token 
.
This is the full working script for anyone else who may find this useful in future:
const delay = (ms) => new Promise((resolve) => setTimeout(resolve, ms));
const batchSize = 10;
const batches = _.chunk(theTeam.data, batchSize);
const collectedResponses = [];
for (let i in batches) {
console.log("Running batch " + i);
// ---- this is basically the same as above, just replacing theTeam.data with batch[i]
const queryResponses = await Promise.all(
batches[i].map((row) => {
return getTimeLogsFromJS.trigger({
additionalScope: {
accountId: row.jira_account_id,
from: fromTo.value.start,
to: fromTo.value.end,
},
});
})
);
const formattedData = queryResponses.flatMap(
(queryData) => queryData.results
);
// ----
collectedResponses.push(...formattedData);
await delay(200); //optional
}
return collectedResponses;
Thanks again @Kabirdas I have an internal demo of this POC I've been working on tomorrow for which you've been a huge help 
Regards,
Nick
1 Like
Hi @Kabirdas,
I'm managing to get some traction internally (for actually a few different potential apps built in Retool) 
I've been trying to work out how to adapt the solution you provided to call an API endpoint passing in a date as the additional scope, but not having much luck so far.
This time I'm looking at FreshDesk (which we use for a couple of Service Desks we run). The API part is quite simple:
GET https://ourcompany.freshdesk.com/api/v2/search/tickets/ ?query="created_at:%27{{dateRange.value.start}}%27"&page=1
What I'm trying to achieve is to make that call for each date between a range on a Date Range component, and save the results to a DB. There will be an initial 'catch up' to grab all the historical data, then I would run it either daily or weekly to get all Tickets between the date range.
I've reviewed some JS date range loop solutions such as this but am not sure how to incorporate this into the structure in your solution. I will need to use the const delay = (ms) => new Promise((resolve) => setTimeout(resolve, ms)); part for rate limiting reason (and pagination), but wanted to try and make sure I use a good solution here, as we will need to also grab timelogs (time spent on each ticket). The logic here is approximately the same, but the volume higher (1 ticket may have 10 or up to even 50 timelogs for example).
Any pointers would be hugely appreciated!
Regards,
Nick
It looks like the result of getDatesInRange could be used instead of theTeam.data as that's the array you'd be iterating over. When the date is passed through additionalScope it will automatically be converted to a string which might not be the ideal format:
So you might want to use something like date.toISOString() (docs):
Bonus: Since this looks like a useful utility function you may want to use elsewhere, you can either define it in the JavaScript query itself or with preloaded JS. If you end up using a Workflow at some point though it will need to be defined in the Workflow itself for the moment.
Thanks again @Kabirdas,
You're right. date.toISOString() was w needed.
I had been using a dummied up table as a placeholder/workaround, but now have this working to generate an array of dates (in the required format of yyyy-MM-dd):
function createDateRangeArray(startDate, endDate) {
let currentDate = new Date(startDate);
const endDateObj = new Date(endDate);
const data = [];
while (currentDate <= endDateObj) {
const formattedDate = currentDate.toISOString().split('T')[0];
data.push({ "date": formattedDate });
currentDate.setDate(currentDate.getDate() + 1);
}
return data;
}
const startDate = dateRange.value.start;
const endDate = dateRange.value.end;
const data = createDateRangeArray(startDate, endDate);
return data;
I will work on combining this with the API calls I need to make when I get time to come back to this one.
Regards,
Nick
