Home > Software engineering >  Script to fetch users and ID from an API, then list them, one by one in G-sheet A10:B
Script to fetch users and ID from an API, then list them, one by one in G-sheet A10:B

Time:08-17

Guilded just (pre)released an API, and I wish to use it to retrieve the list of users, and userid from my server, and have it in a Google-sheet. It seems that I get the information (I see it in my log), but I'm struggling with the output. All I managed was to get the first user and ID.

Here is my script, with the attempt of having all users|userid, one by row.

I have a test file here : https://docs.google.com/spreadsheets/d/1ZUZ6utQO6cW_BAy6cMACxX2H1_zrZAIE0CTtTmdN9nc/edit?usp=sharing

​Thank you for any help you could provide,

Nyl,

[edit] I believe that my for (var i = 0; i < data.length; i ) is not correct, as it returns nothing if my logger.log is inside it. It might be due to the payload, but I don't see how to fix it.

CodePudding user response:

Here is a script that will work.

data is an object. Within data is an array of members so your indexing the wrong variable.

Also I would collect all the member data into an array and use a single setValues()

function Get_Members() {
  var ss = SpreadsheetApp.getActiveSpreadsheet ();
  var url = "https://www.guilded.gg/api/v1/servers/Gjk9vPgl/members";
  var key = ss.getRange('test!B1').getValue();
  var rowCounter = 10;
  var startColumn = 1;
  
  var params = {
    method: "GET",
    headers: { Authorization: key },
    contentType: "application/json",
  };

  var dataJSON = UrlFetchApp.fetch(url, params);
  var data = JSON.parse(dataJSON)

  console.log(data);

  let members = [];

  for (var i = 0; i < data.members.length; i  ){
    
    members.push([data.members[i].user.name,data.members[i].user.id]);
  }

  ss.getActiveSheet().getRange(10,1,members.length,2).setValues(members);
  
}

Execution log

8:40:06 AM  Notice  Execution started
8:40:06 AM  Info    { members: 
   [ { user: [Object], roleIds: [Object] },
     { user: [Object], roleIds: [Object] },
     { user: [Object], roleIds: [Object] },
     { user: [Object], roleIds: [Object] },
     { user: [Object], roleIds: [Object] } ] }
8:40:06 AM  Notice  Execution completed
  • Related