Querying an API multiple times and creating an array with the results

Queries and Resources
,
1

Hey guys and gals, this is my first post in the community so be gentle with me :).

I am trying to get usage logs from an API and have no issue making the query itself.
The issue is that in order to get monthly data I need to send an individual request for each month.

It seems silly to create a query for each month by subtracting the month using moment.js and im sure there is a more dynamic way to go about this.

Essentially I want to make 6 request for this month and the previous 5 months, as an end result im looking to generate an array containing the month and the usage like so in order for me to use the data in a bar chart:

[
    {
        "month": "January 2022",
        "minutes": 125000000
    },
    {
        "month": "December 2021",
        "minutes": 850000
    },
    {
        "month": "November 2021",
        "minutes": 950000
    },
    {
        "month": "October 2021",
        "minutes": 950000
    },
    {
        "month": "September 2021",
        "minutes": 950000
    },
    {
        "month": "August 2021",
        "minutes": 700000
    }
]

Any idea on what would be the best way to go about this ?

2

Hi EyalGershon

Great question! And welcome to the community!

I believe you could do something similar to what was asked in this community post: http://community.retool.com/t/how-to-loop-through-an-array-and-hit-an-api-for-each-value-in-a-column/4291/6

The idea is to loop over an array of items (this could be months) and then for each item in the array trigger the query you have already written. The month would then be dynamic and passed in as an 'additionalScope'. If you scroll to the bottom of the community post you will see a great overview on how to use this in a JavaScript Code query.

1 Like
3

That did help but this is a little different, I dont want to run this after a button has been clicked but I would like this to run on page load.

I setup my script:

image
image584×606 27.7 KB

This is my query:

image
image2493×474 90.3 KB

this returns null:
image

I have the script setup to run on page load but seems as if it is not iterating and returning a response.
Unless im missing some sort of return call ?

Also, how do I aggregate the data eventually?

4

Hey @EyalGershon,

In your query you'll need to remove the quotes from index as you want that to interpolate from the additionalScope you're passing.

For the Query I think something like this would work (just wrote this up and didn't test but that's the gist).

Create a new JS query with:

(async () => {
  var indexes = 6;
  var apiData = []

  for (let i = 1; i <= indexes; i++) {
    await historicUsage.trigger({
      additionalScope: {
        index: i,
      },
      onSuccess: (c) => {
        apiData.push(c)
      }
    });
  }

  return apiData
})();

Hope that helps

2 Likes
5

Thank you so much, yeah what you did was much more efficient code wise in terms of iterating.

What confused me was the fact that the query shows an error but when I run the js it works :slight_smile:

image
image2118×375 88.7 KB

This is my code for anyone out there trying to do something similar:

var table = [
  {
    index: 0
  },
  {
    index: 1
  },
  {
    index: 2
  },
  {
    index: 3
  },
  {
    index: 4
  },
  {
    index: 5
  },
  {
    index: 6
  }
];

const promises = table.map((row) => {
  return dynamicUsage.trigger({
    additionalScope: {
      index: row.index
    },
  });
});

return Promise.all(promises);
6

Good to hear :slight_smile:

Yeah, when using interpolated variables the query will always complain because the variable is not defined.

1 Like