Introduction
It has been more than a year since our last post about the GAS language. It’s time to return to this subject and learn how to pull COVID-19 data from an external API into Google Sheets. Considering the increasing number of APIs that appear online, along with the rising demand for cloud data, such functionality will be beneficial for your projects.
You should not forget that Google Apps Script is a JavaScript look-alike language. Therefore, it allows us to fetch the data directly into Google Sheets, without the need for a “middle man,” as it was, for example, the C# application in the Face Detection in Excel article. A side note: Microsoft seems to adopt this JavaScript approach by promoting the Office Scripts in their new/upcoming Office releases. Hence, the functionality presented here can also be useful in the upcoming versions of Excel.
COVID-19 API
The API that we will use in this example returns data for the COVID-19 virus by country. It is a free API that can be accessed without an API key. More information about the API can be found on this site.
A few comments about the COVID-19 virus: We are living in extraordinary times. Most countries in the world have applied some sort of lockdown. At the time of writing this article on March 29 April 15, the virus has infected more than 2 million people, while more than 130,000 have already died.
It is really terrifying what we currently live in. Apart from the obvious health concerns, it is hard to predict the economic and other consequences of the virus in our lives in the upcoming months. I guess that the only thing that we can do is be patient and be healthy. Without health, we can’t do anything.
GAS code
Below is the GAS code that does the following: Based on the selected country (from a dropdown list), it retrieves the slug value from the COVID-19 API and then uses that slug value to retrieve the daily confirmed, recovered, and death data for the selected country. The data are written in columns A to D. In contrast, the latest data are written in column J. Finally, column K contains some simple formulas that calculate the percentage of active, recovered, and death cases.
/* Start of the GAS code. */
/*
--------------------------------------------------
Written By: Christos Samaras
Date: 29/03/2020
Last Updated: 09/05/2020
E-mail: [email protected]
Site: https://myengineeringworld.net
--------------------------------------------------
*/
function getCovidData()
{
/*
-------------------------------------------------------------------------------------------------
Gets the external COVID-19 data based on country selection and updates the sheet data and chart.
-------------------------------------------------------------------------------------------------
*/
// Get the active sheet.
let sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
// Check if it is exists.
if(!sheet)
{
Browser.msgBox('Could not retrieve the active sheet!');
return;
}
// Clear the contents (main data and latest data).
sheet.getRange('A2:D1000').clearContent();
sheet.getRange('J2:J3').clearContent();
sheet.getRange('J5:J6').clearContent();
// Get the selected country.
let country = sheet.getRange('G1').getValue();
// Ensure that is not empty.
if(isBlank(country))
{
Browser.msgBox('The input country cannot be empty!');
return;
}
// Get the slug from the country's name.
let slug = getCountrySlug(country);
// Ensure that is not empty.
if(isBlank(slug))
{
Browser.msgBox('Cannot find the slug for the input country!');
return;
}
// Write the country data in the sheet.
getCovidDataByCountry(sheet, slug);
// Update the chart's title, and series colors and line widths.
let chart = sheet.getCharts()[0];
chart = chart.modify().setOption('title', 'COVID-19 Data: ' + country || 'Empty')
.setOption('series.0.color', '#236267')
.setOption('series.1.color', '#789E35')
.setOption('series.2.color', '#AA3E39')
.setOption('series.0.lineWidth', 4)
.setOption('series.1.lineWidth', 4)
.setOption('series.2.lineWidth', 4)
.build();
sheet.updateChart(chart);
// Auto-fit the columns A to D.
sheet.autoResizeColumns(1, 4);
}
function getCovidDataByCountry(sheet, slug)
{
/*
-------------------------------------------------------------------------------
Gets the external COVID-19 data based on the input slug and updates the sheet.
-------------------------------------------------------------------------------
*/
// Create the required API urls based on input slug.
let confirmedUrl = 'https://api.covid19api.com/total/country/' + slug + '/status/confirmed';
let recoveredUrl = 'https://api.covid19api.com/total/country/' + slug + '/status/recovered';
let deathsUrl = 'https://api.covid19api.com/total/country/' + slug + '/status/deaths';
// Perform the requests and get the JSON data. Convert them into arrays.
let dates = getResponseJsonData(confirmedUrl).map(c => [new Date(c.Date)]);
let confirmed = getResponseJsonData(confirmedUrl).map(c => [c.Cases]);
let recovered = getResponseJsonData(recoveredUrl).map(r => [r.Cases]);
let deaths = getResponseJsonData(deathsUrl).map(d => [d.Cases]);
// Ensure that there are no empty arrays.
if(dates.length == 0 || confirmed.length == 0 || recovered.length == 0 || deaths.length == 0)
return;
// Write the data into the sheet (columns A to D).
sheet.getRange(2, 1, dates.length).setValues(dates);
sheet.getRange(2, 2, confirmed.length).setValues(confirmed);
sheet.getRange(2, 3, recovered.length).setValues(recovered);
sheet.getRange(2, 4, deaths.length).setValues(deaths);
// Write the latest data into the sheet (column J).
sheet.getRange('J2').setValue(dates[dates.length - 1]);
sheet.getRange('J3').setValue(confirmed[confirmed.length - 1]);
sheet.getRange('J5').setValue(recovered[recovered.length - 1]);
sheet.getRange('J6').setValue(deaths[deaths.length - 1]);
}
function getCountrySlug(countryName)
{
/*
--------------------------------------------------
Returns the slug based on the input country name.
--------------------------------------------------
*/
// The API url for the countries.
const countriesUrl = 'https://api.covid19api.com/countries';
// Get the country data.
let data = getResponseJsonData(countriesUrl);
// Ensure that there are no empty data.
if(data.length == 0)
return;
// Search the input country and return the slug for that country.
let country = data.find(d => d.Country.startsWith(countryName))
// Return the slug for the found country.
return country == null? null: country['Slug'];
}
function getResponseJsonData(url)
{
/*
----------------------------------------------------------
Performs the request and converts the response into JSON.
----------------------------------------------------------
*/
// Check that the URL is not empty.
if(isBlank(url))
return;
// Fetch the data.
let response = UrlFetchApp.fetch(url);
// Get the response text.
let json = response.getContentText();
// Convert to JSON.
return JSON.parse(json);
}
function isBlank(str)
{
/*
--------------------------------------------------------
Checks if the input string is blank, null or undefined.
--------------------------------------------------------
*/
return (!str || /^\s*$/.test(str));
}
/* End of the GAS code. */Note that the retrieved data are usually from the previous day. Hence, if today is March 29 April 15, the data that the GAS code pulls from the API are from March 28 April 14.
Sample spreadsheet in Google Sheets
The above functions are included in this Google Sheets file. I have added a trigger in that file that fires every time the file is opened. You can learn more about triggers here.
Note that the file is in the View only mode. To enable editing, go to File → Make a copy. Once the file is copied on your Drive, you can use it whenever you want.
Read also
Geocoding & Reverse Geocoding Functions In Google Sheets
Triggers & Events In Google Sheets
Page last updated: 15/04/2020
