In this post, I demonstrate how to Call a JSON Web Service and Populate a Google Sheet with a table of raw data.
How to Video:
Video Notes:
- Web Service Used In Video Ergast Developer API
- Web Service tool used in Video Postman
Code in Video:
function getData() {
// DECLARE SHEET
var ss = SpreadsheetApp.getActiveSpreadsheet();
var mainSheet = ss.getSheetByName("MAIN");
// GET CATEGORY VALUE
var race_year = mainSheet.getRange(3, 2).getValue();
var race_num = mainSheet.getRange(2, 2).getValue();
var race_laps = mainSheet.getRange(1, 2).getValue();
//CLEAR QUESTION AND ANSWER
mainSheet.getRange('A7:DC27').clear();
//INSERT LAP NUMBERS
for (var j = 1; j <= race_laps; j++)
{
mainSheet.getRange(7, j + 1).setValue([j]);
SpreadsheetApp.flush();
}
// URL
var URL_STRING = "http://ergast.com/api/f1/" + race_year + "/" + race_num + "/drivers.json"
console.log('URL_STRING ' + URL_STRING);
//CALL API
var response = UrlFetchApp.fetch(URL_STRING);
var json = response.getContentText();
var data = JSON.parse(json);
var drivers = [];
//GET DRIVERS
for (var i = 0; i < data.MRData.DriverTable.Drivers.length; i++)
{
var drivers_Array = data.MRData.DriverTable.Drivers;
//STORING DRIVERS
drivers.push(drivers_Array[i].driverId);
mainSheet.getRange(8 + i, 1).setValue([drivers_Array[i].driverId]);
SpreadsheetApp.flush();
}
for (var j = 1; j <= race_laps; j++)
{
// URL
var URL_STRING = "http://ergast.com/api/f1/" + race_year + "/" + race_num + "/laps/" + j + ".json";
console.log('URL_STRING ' + URL_STRING);
//CALL API
var response = UrlFetchApp.fetch(URL_STRING);
var json = response.getContentText();
var data = JSON.parse(json);
var laptime_array = data.MRData.RaceTable.Races[0].Laps[0].Timings;
for (var y = 0; y < drivers.length; y++)
{
var driver_id = drivers[y];
console.log('laptimes ' + laptime_array.length);
//GET LAP TIMES
for (var i = 0; i < laptime_array.length; i++)
{
if (laptime_array[i]["driverId"] == driver_id)
{
var laptime = laptime_array[i]["time"];
const time_array = laptime.split(":");
const seconds = time_array[0]*60+(+time_array[1]);
mainSheet.getRange(8 + y, j + 1).setValue([seconds]);
SpreadsheetApp.flush();
break;
}
}
}
}
}
Related Posts
![Back Up Google Drive Files](https://codewithcurt.com/wp-content/uploads/2023/04/Back-Google-Drive-Files-WS-150x150.png)
![Embed Chart Google Sheets](https://codewithcurt.com/wp-content/uploads/2022/11/Embed-Chart-GS-WS-150x150.png)
![Search Google Drive Google Sheets](https://codewithcurt.com/wp-content/uploads/2022/11/Search-Google-Drive-GS-WS-150x150.png)
![Create Custom Menu Google Sheets](https://codewithcurt.com/wp-content/uploads/2022/10/Create-Custom-Menu-GS-WS-150x150.png)
![Get and Set Value Google Sheets](https://codewithcurt.com/wp-content/uploads/2022/07/Get-and-Set-Value-GS-WS-150x150.png)
![Create Data Validation Google Sheets](https://codewithcurt.com/wp-content/uploads/2022/04/Create-Data-Validation-GS-WS-150x150.png)
![Stock Trend Analysis Google Sheets](https://codewithcurt.com/wp-content/uploads/2022/04/Stock-Trend-Analysis-GS-WS-150x150.png)
![Scrape Amazon Website Googe Sheets](https://codewithcurt.com/wp-content/uploads/2022/03/Scrape-Amazon-Website-GS-WS-150x150.png)
![Alert Popups on Google Sheets](https://codewithcurt.com/wp-content/uploads/2022/03/Alert-Popups-on-GS-WS-150x150.png)
![Combine Multiple Spreadsheets Google Sheets](https://codewithcurt.com/wp-content/uploads/2022/02/Combine-Multiple-Spreadsheets-GS-WS-150x150.png)
![Last Row and Column Google Sheets](https://codewithcurt.com/wp-content/uploads/2022/04/Last-Row-and-Column-GS-WS-150x150.png)
![Time and Distance Map Google Sheets](https://codewithcurt.com/wp-content/uploads/2022/02/Time-and-Distance-GS-WS-150x150.png)
![Create Dependent Dropdown Google Sheets](https://codewithcurt.com/wp-content/uploads/2022/01/Create-Dependent-Dropdown-GS-WS-150x150.png)
![Call Rest API Google Sheets](https://codewithcurt.com/wp-content/uploads/2022/01/Call-Rest-API-GS-WS-150x150.png)
![Google Doc From Apps Script](https://codewithcurt.com/wp-content/uploads/2021/12/Google-Doc-From-Apps-Script-WS-150x150.png)
![How to Clear, Delete, Insert, and Replace Row Google Sheets](https://codewithcurt.com/wp-content/uploads/2021/12/How-to-Clear-Delete-Insert-and-Replace-Row-GS-WS-150x150.png)
![Email Last Update Google Sheet](https://codewithcurt.com/wp-content/uploads/2021/12/Email-Last-Update-GS-WS-150x150.png)
![Scrape Website Google Sheet](https://codewithcurt.com/wp-content/uploads/2021/02/Scrape-Website-GS-WS-150x150.png)
![Query Function Google Sheets](https://codewithcurt.com/wp-content/uploads/2021/02/Query-Function-WS-150x150.png)
![Copy Row Google Sheet to Google Sheet](https://codewithcurt.com/wp-content/uploads/2021/02/Copy-Row-Sheet-to-Sheet-WS-150x150.png)