CODEWITHSUNDEEP
javascriptapiloopsgoogle-apps-scriptgoogle-sheets
willy
willy

Reputation: 43

How do I loop through all responses from API and pass them to Google Sheet using Javascript?

I have this code that gets project ID from google sheet if they meet a criteria (between two dates) and uses it for an API to get the users in the project. After, getTeam function append the response (Team) to a sheet. My challenge is that I am not able to get all the users appended to the sheet in the getTeam function. I did not structure well my loops in readDates I will help in structuring the code to get all the users to a sheet.

function readDates() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getActiveSheet();

    var range1 = sheet.getRange("C2:C" + sheet.getLastRow()).getValues();
    var searchString = "Project";
    var ahead = nextweek()
    var behind = lastweek()
    var team_array = [];

    for (var i = 0; i < range.length; i++) {
        if (range1[i][0] >= behind && range1[i][0] <= ahead) {


                var lastRow = sheet.getRange(2 + i, 1, 1, 8).getValues();
                var dateval = lastRow[0][2]
                var data = {

                    'project_id': lastRow[0][3] // There are 6 projects ID meeting criteria, each project has 7 users

                };


                var options = {
                    method: 'get',
                    headers: {
                        Authorization: 'Bearer ' + token
                    }
                };

                var url = tknurl + Endpoint + data.project_id + '/users?auth=' + token
                var response = UrlFetchApp.fetch(url, options);
                var team = JSON.parse(response);
                var content = team.data;
                team_array.push(content);
            }

        }
   return(content) 
    }

getTeam function. Where I am only getting users for one project, instead of all 6 projects

function getTeam() {
var ss = SpreadsheetApp.getActive().getSheetByName('Team');
var Pro_data = readDates()

for (var j = 0; j < Pro_data.length; j++) {

ss.getRange(2 + j, 1).setValue(Pro_data[j].id);
ss.getRange(2 + j, 2).setValue(Pro_data[j].first_name);
ss.getRange(2 + j, 3).setValue(Pro_data[j].last_name);
ss.getRange(2 + j, 4).setValue(Pro_data[j].display_name);
ss.getRange(2 + j, 5).setValue(Pro_data[j].email);
ss.getRange(2 + j, 6).setValue(Pro_data[j].user_type_id);
ss.getRange(2 + j, 7).setValue(Pro_data[j].role);

    }

}

Upvotes: 0

Views: 171

Answers (2)

Ricardo Cunha
Ricardo Cunha

Reputation: 2075

You are returning only the first result in readDates function. Your return instruction is inside the loop, you need to aggregate all the results somewhere, you can use an array before executing the return.

var contents = [];
for (var i = 0; i < range.length; i++) {
     ...
     contents.push(content);
     } //close if2
   } //close if1
}//close for
return contents;

Upvotes: 1

ziganotschka
ziganotschka

Reputation: 26836

The return statement finishes the execution of a function and exits it

  • You use return(content) inside the for loop
  • As a consequence the function will be exited after the first iteration
  • Instead, you should create an array to which you append within each iteration the data for each project
  • Return the array containing all data after the exiting the for loop.

Sample:

  ...
  var myArray = [];
  for (var i = 0; i < range1.length; i++) {
    ...
    var content = team.data;
    myArray.push(content);
    ...    
  }
  return content; 
  ...

Side note:

You can replace the nested if statement

if (range1[i][0] >= behind) {
  if (range1[i][0] <= ahead) {
    ...
  }
}

through a combined one:

if (range1[i][0] >= behind && range1[i][0] <= ahead) {
  ...
}

Upvotes: 1

Related Questions