nodejs recursively call same api and write to excel file sequentially

Question

I need to call an API recursively using request promise after getting result from API need to write in an excel file , API sample response given below

{
"totalRecords": 9524,
"size": 20,
"currentPage": 1,
"totalPages": 477,
"result": [{
        "name": "john doe",
        "dob": "1999-11-11"
    },
    {
        "name": "john1 doe1",
        "dob": "1989-12-12"
    }

]
}

Now I want to call this API n times, here n is equal to totalPages, after calling each API I want to write response result to the excel files. First write page 1 response result to excel then append page 2 response result to excel file and so on.. I have written some sample code given below

function callAPI(pageNo) {
var options = {
  url: "http://example.com/getData?pageNo="+pageNo,
  method: 'GET',
  headers: {        
    'Content-Type': 'application/json'
  },
  json: true
}   
return request(options)
}

callAPI(1).then(function (res) {
   // Write res.result to excel file      
}).catch(function (err) {
// Handle error here
})

But facing problem calling recursively API and maintaining sequentially like write page 1 result first to excel file then page 2 result append to excel and so on.. Any code sample how to achieve in nodejs

Answer 1

You want to do something like this:

function getAllPages() {
    function getNextPage(pageNo) {
        return callAPI(pageNo).then(response => {
            let needNextPage = true;
            if (pageNo === 1) {
                // write to file
            } else {
                // append to file
            }

            if (needNextPage) {
                return getNextPage(pageNo+1);
            } else {
                return undefined;
            }
        });
    }

    return getNextPage(1);
}

Obviously change that 'needNextPage' to false to stop the recursion when you're done

Answer 2

So you want to do 477 requests in sequence? How long do you wanna wait for this to finish? Even in paralell, this would be still too long for me.

Best: write an API that can return you a batch of pages at once. Reducing the number of requests to the backend. Maybe something like http://example.com/getData?pages=1-100 and let it return an Array; maybe like

[
  {
    "totalRecords": 9524,
    "currentPage": 1,
    "totalPages": 477,
    "result": [...]
  },

  {
    "totalRecords": 9524,
    "currentPage": 2,
    "totalPages": 477,
    "result": [...]
  },
  ...
]

or more compact

{
  "totalRecords": 9524,
  "totalPages": 477,
  "pages": [
    {
      "currentPage": 1,
      "result": [...]
    },

    {
      "currentPage": 2,
      "result": [...]
    },
    ...
  ]
}

Sidenote: writing the size of the results array into the json is unnecessary. This value can easily be determined from data.result.length

---

But back to your question

Imo. all you want to run in sequence is adding the pages to the sheet. The requests can be done in paralell. That already saves you a lot of overall runtime for the whole task.

callApi(1).then(firstPage => {
  let {currentPage, totalPages} = firstPage;

  //`previous` ensures that the Promises resolve in sequence, 
  //even if some later request finish sooner that earlier ones.
  let previous = Promise.resolve(firstPage).then(writePageToExcel);

  while(++currentPage <= totalPages){
    //make the next request in paralell
    let p = callApi(currentPage);

    //execute `writePageToExcel` in sequence
    //as soon as all previous ones have finished
    previous = previous.then(() => p.then(writePageToExcel));
  }
  return previous;
})
.then(() => console.log("work done"));

or you wait for all pages to be loaded, before you write them to excel

callApi(1).then(firstPage => {
  let {currentPage, totalPages} = firstPage;

  let promises = [firstPage];
  while(++currentPage < totalPages)
    promises.push(callApi(currentPage));

  //wait for all requests to finish
  return Promise.all(promises);
})
//write all pages to excel
.then(writePagesToExcel)
.then(() => console.log("work done"));

or you could batch the requests

callApi(1).then(firstPage => {
  const batchSize = 16;
  let {currentPage, totalPages} = firstPage;

  return Promise.resolve([ firstPage ])
    .then(writePagesToExcel)
    .then(function nextBatch(){
      if(currentPage > totalPages) return;

      //load a batch of pages in paralell
      let batch = [];
      for(let i=0; i<batchSize && ++currentPage <= totalPages; ++i){
        batch[i] = callApi(currentPage);
      }

      //when the batch is done ...
      return Promise.all(batch)
        //... write it to the excel sheet ...
        .then(writePagesToExcel)
        //... and process the next batch
        .then(nextBatch);
    });
})
.then(() => console.log("work done"));

But don't forget to add the error handling. Since I'm not sure how you'd want to handle errors with the approaches I've posted, I didn't include the error-handling here.

---

Edit:

can u pls modify batch requests, getting some error, where you are assigning toalPages it's not right why the totalPages should equal to firstPage

let {currentPage, totalPages} = firstPage;
//is just a shorthand for
let currentPage = firstPage.currentPage, totalPages = firstPage.totalPages;
//what JS version are you targeting?

This first request, callApi(1).then(firstPage => ...) is primarily to determine currentIndex and totalLength, as you provide these properties in the returned JSON. Now that I know these two, I can initiate as many requests in paralell, as I'd want to. And I don't have to wait for any one of them to finish to determine at what index I am, and wether there are more pages to load.

and why you are writing return Promise.resolve([ firstPage ])

To save me some trouble and checking, as I don't know anything about how you'd implement writePagesToExcel.
I return Promise.resolve(...) so I can do .then(writePagesToExcel). This solves me two problems:

1. I don't have to care wether writePagesToExcel returns sync or a promise and I can always follow up with another .then(...)

2. I don't need to care wether writePagesToExcel may throw. In case of any Error, it all ends up in the Promise chain, and can be taken care of there.

So ultimately I safe myself a few checks, by simply wrapping firstPage back up in a Promise and continue with .then(...). Considering the amounts of data you're processing here, imo. this ain't too much of an overhead to get rid of some potential pitfalls.

why you are passing array like in resolve

To stay consistent in each example. In this example, I named the function that processes the data writePagesToExcel (plural) wich should indicate that it deals with multiple pages (an array of them); I thought that this would be clear in that context.

Since I still need this seperate call at the beginning to get firstPage, and I didn't want to complicate the logic in nextBatch just to concat this first page with the first batch, I treat [firstPage] as a seperate "batch", write it to excel and continue with nextBatch

Answer 3

function callAPI(pageNo) {
  var options = {
    url: "http://example.com/getData?pageNo="+pageNo,
    method: 'GET',
    headers: {        
      'Content-Type': 'application/json'
    },
    json: true
  }   
  return request(options)
}

function writeToExcel(res){console.log(res)} //returns promise.


callAPI(1).then(function (res) {
   if(res){
       writeToExcel(res).then(() => {
          var emptyPromise = new Promise(res => setTimeout(res, 0));
          while(res && res.currentPage < res.totalPages){
             emptyPromise = emptyPromise.then(() => {
               return callAPI(res.currentPage).then(function (res){
                   if(res){
                     writeToExcel(res)
                   }
               });
             }
          }
          return emptyPromise; 
       });
   }
}).catch(function (err) {
// Handle error here
})