Firing an API request for each row in a table

Queries and Resources
1

I want to fire an API request for each row in a table. To do so I'm following almost exactly the example provided in Scripting Retool. Only difference is that I'm not using a CSV as data source but the table itself.

  1. I Created table1 which holds the values I need to push to the API.

  2. I created a query POSTalldev that POST a JSON. Each key in the JSON is set to

{{table1.data[i].keyname}}

  1. Added a button, as well as a statusText and errorText fields.

  2. I set the event handler of the button to the following JS:

    var rows = table1.data;
    var errors = '';
    var total = rows.length;

    function runQuery (i) {
      // Update the statusText with the current progress
      statusText.setValue(i.toString() + '/' + total.toString())
      
      if (i >= rows.length) {
    console.log('Finished running all queries');
    return;
      }

      console.log('Running query for row', i);

      POSTalldev.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() + ':  ' + error +  '\n\n';
      errorText.setValue(errors);
      runQuery(i + 1);
    }
      });
    }

    runQuery(0);
  1. When I click the button, the query goes through each row one by one as expected, but returns for each row an error:
Found error at line 0:  {"data":{"statusCode":404,"error":"Not Found","message":"Unable to retrieve information about the query "POSTalldev"","data":null},"errorData":"Unable to retrieve information about the query "POSTalldev"","displayOptions":{"hideToast":false},"trigger":"NATURAL_FAILURE"}

Found error at line 1:  {"data":{"statusCode":404,"error":"Not Found","message":"Unable to retrieve information about the query "POSTalldev"","data":null},"errorData":"Unable to retrieve information about the query "POSTalldev"","displayOptions":{"hideToast":false},"trigger":"NATURAL_FAILURE"}
...

I'm 100% sure the endpoint works and the JSON format is valid, so there must be something wrong with the JS. My guess is the row definition is not correct but I'm not sure. Any idea what it could be?

2

Hey @nils could you post a screenshot if your POST query?

3

hi @minijohn here you go:

image
image953×899 54.8 KB

4

Hey @nils,

thanks. Looks ok to me..

Are you on a Retool trial which is ending maybe? Other then that I would ping support in the live-chat to debug this.

5

Hey @nils!

Hmmm, providing the data in table.data is the correct format, everything here looks good at first glance. Just to troubleshoot, could you try moving this code to a JS query and trigger that with the buttons event handler to see if that changes anything here?

6

hi @minijohn @Chris-Thompson Thanks for your help! I was indeed on an ending trial period...stupid me. Now the queries are running fine :slight_smile:

Now I have a last issue I'm running into: I want to make sure I'm not pushing duplicates.
Before each POST call to create reviews recursively (using the JS above), I'd like to check if it is already created. My guess is that I should insert a GET call to the JS and then compare author and description in the GET response with the corresponding keys of the i row of the table1.

Here is the GET response format for a specific boatId:

image
image393×558 19.2 KB

Any suggestion on how to achieve this?

7

Hi @nils!

If you want to trigger your GET query as a part of your script you can do so with an await statement, then you can check for duplicates in a way similar to what you did here (I'll use your query from that post as an example).

At the top part of your script you can declare another variable var reviewData = await GETdev.trigger(); and then modify the runQuery function with the following code:

function runQuery(i){

  // first part of function..

  var author = table1.data[i].author;
  var description = table1.data[i].description; 
  if(reviewData.data.filter(x => author == x.author && description == x.description).length>0){
    console.log('Skipping duplicate post', i);
  } else {
    console.log('Running query for row', i);
    POSTalldev.trigger(/*...*/);
  }
}

To avoid duplicate posts that result from multiple clicks it might be best to move this script into a separate query (or queries) and then disable the button until the query has finished!

1 Like