CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Musaddiq
Musaddiq

Reputation: 39

API to Google sheets - unable to fetch data

I am trying to fetch data via API to Google sheets, I am able to get "NewConfirmed" and other few fields but not able to get "Countries" data. Please help.

function Covid19() {
  
  // Call the COVID19 API
  var response = UrlFetchApp.fetch("https://api.covid19api.com/summary");
  
  // Parse the JSON reply
  var json=response.getContentText();
  var data=JSON.parse(json);
  var sheet = SpreadsheetApp.getActiveSheet();
  var i = 2;

  for each (var info in data)
    {
      sheet.getRange(i,1).setValue([info['NewConfirmed']]);
      sheet.getRange(i,2).setValue([info['Country']]);
     
      i = i + 1;
  }

Upvotes: 0

Views: 149

Answers (1)

ziganotschka
ziganotschka

Reputation: 26836

If you log data, you will see

{Countries=[{CountryCode=AX, TotalRecovered=0.0, NewDeaths=0.0, Slug=ala-aland-islands, Country=ALA Aland Islands, NewRecovered=0.0, Date=2020-04-21T12:32:01Z, NewConfirmed=0.0, ...

Thus, in order to retrieve Country and NewConfirmed you need to define

var data=JSON.parse(json).Countries and then you have to iterate through all entries within a loop.

Sample based on your code:

function Covid19() {
  var response = UrlFetchApp.fetch("https://api.covid19api.com/summary");
  var json=response.getContentText();
  var data=JSON.parse(json).Countries;
  var sheet = SpreadsheetApp.getActiveSheet();
  for(var i = 0; i < data.length; i++)
    {
      sheet.getRange(i+2,1).setValue([data[i]['NewConfirmed']]);
      sheet.getRange(i+2,2).setValue([data[i]['Country']]);
  }
}

Sidenote:

It is ot good practive to use getRange(...).setValue(..) during each loop iteration. It would be better to write the data into an array and assign the array with all the data to the sheet after finishing iteration.

Upvotes: 1

Related Questions