I have many little robot scripts busy saving data to Google Sheets.
Here’s one I made based on a little comment from one of my kid’s teachers. It goes to a very kind URL that returns the data structure at the bottom of the page.
http://api.wunderground.com/api/YOUR_API_KEY/conditions/q/VA/Richmond.json
It’s a pretty nice starter script for anyone wanting to play with an API and Google Scripts. The full Google Script is below and I’ve tried to comment it up pretty thoroughly. I’ll also break it down below for those of you who want to play at home.
Get the Data
var url = 'http://api.wunderground.com/api/YOUR_API_KEY/conditions/q/VA/Richmond.json'; var response = UrlFetchApp.fetch(url); var json = JSON.parse(response.getContentText());
The first variable ‘url’ is the API endpoint we’re getting the data from. Where it says YOUR_API_KEY, you should put your API Key. I recall early on trying to do this stuff I’d get confused when people did different things to indicate that an API Key should go somewhere. People would put it in quotes or surrounded by asterisk or something else. I’d end up confused about what should go there because I had no baseline to judge this against. It’s fairly obvious now but I haven’t forgotten that feeling of basic confusion. Sometimes it helps to be very basic.
Google has a nice UrlFetchApp class that you use in Google Script instead of fetch like you’d use in vanilla javascript land. Since we want to treat the data as JSON we set the response like so var json = JSON.parse(response.getContentText());
Breaking Off the Pieces
Once you’ve got the big JSON object, you’ll parse out various pieces to get just the parts you want. All of the data I want was a child of the current_observation element. So I move from json->current_observation->the_specific_element_I_wanted.
var tempC = json.current_observation.temp_f;
Most of the script is just parsing out the various elements.
Write It Down
Now that I’ve got all the pieces, I mash them back up in a nice array so that I can more easily write them to the various spreadsheet columns. I could also write each one individually but this is easier. I defined theData as an array by setting it equal to []. Then I pushed all the data into the array.
theData.push(obDate,weather,tempC,humidity,windMph,pressure,dewPt,heatIndex,windChill,feels,visibility,percipToday);
The final little bit, sheet.appendRow(theData); appends that data with each element going to its own column and automatically finds the first empty row.
Finally, I used the script’s project trigger to set the script to run every hour.
function getWeather() {
var url = 'http://api.wunderground.com/api/YOUR_API_KEY/conditions/q/VA/Richmond.json';
var response = UrlFetchApp.fetch(url);
var json = JSON.parse(response.getContentText());
//THE GOOGLE SHEETS STUFF
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
//THE DATA PIECES
var theData = []; //the array holder
var obDate = json.current_observation.observation_time_rfc822;
var weather = json.current_observation.weather;
var tempC = json.current_observation.temp_f;
var humidity = json.current_observation.relative_humidity;
var windMph = json.current_observation.wind_mph;
var pressure = json.current_observation.pressure_mb;
var dewPt = json.current_observation.dewpoint_f;
var heatIndex = json.current_observation.heat_index_f;
var windChill = json.current_observation.windchill_f;
var feels = json.current_observation.feelslike_f;
var visibility = json.current_observation.visibility_mi;
var percipToday = json.current_observation.precip_today_in;
//PUSH THE PIECES TO THE ARRAY theData.push(obDate,weather,tempC,humidity,windMph,pressure,dewPt,heatIndex,windChill,feels,visibility,percipToday);
//WRITE TO THE SHEET
sheet.appendRow(theData);
}
The JSON data that comes from the API.
{
"response": {
"version": "0.1",
"termsofService": "http://www.wunderground.com/weather/api/d/terms.html",
"features": {
"conditions": 1
}
},
"current_observation": {
"image": {
"url": "http://icons.wxug.com/graphics/wu2/logo_130x80.png",
"title": "Weather Underground",
"link": "http://www.wunderground.com"
},
"display_location": {
"full": "Richmond, VA",
"city": "Richmond",
"state": "VA",
"state_name": "Virginia",
"country": "US",
"country_iso3166": "US",
"zip": "23218",
"magic": "1",
"wmo": "99999",
"latitude": "37.54999924",
"longitude": "-77.45999908",
"elevation": "60.0"
},
"observation_location": {
"full": "Fan District RVA, Richmond, Virginia",
"city": "Fan District RVA, Richmond",
"state": "Virginia",
"country": "US",
"country_iso3166": "US",
"latitude": "37.554218",
"longitude": "-77.465668",
"elevation": "223 ft"
},
"estimated": {},
"station_id": "KVARICHM156",
"observation_time": "Last Updated on March 6, 2:17 PM EST",
"observation_time_rfc822": "Tue, 06 Mar 2018 14:17:50 -0500",
"observation_epoch": "1520363870",
"local_time_rfc822": "Tue, 06 Mar 2018 14:18:00 -0500",
"local_epoch": "1520363880",
"local_tz_short": "EST",
"local_tz_long": "America/New_York",
"local_tz_offset": "-0500",
"weather": "Rain",
"temperature_string": "45.5 F (7.5 C)",
"temp_f": 45.5,
"temp_c": 7.5,
"relative_humidity": "50%",
"wind_string": "From the South at 4.9 MPH Gusting to 8.0 MPH",
"wind_dir": "South",
"wind_degrees": 180,
"wind_mph": 4.9,
"wind_gust_mph": "8.0",
"wind_kph": 7.9,
"wind_gust_kph": "12.9",
"pressure_mb": "1015",
"pressure_in": "29.98",
"pressure_trend": "-",
"dewpoint_string": "28 F (-2 C)",
"dewpoint_f": 28,
"dewpoint_c": -2,
"heat_index_string": "NA",
"heat_index_f": "NA",
"heat_index_c": "NA",
"windchill_string": "43 F (6 C)",
"windchill_f": "43",
"windchill_c": "6",
"feelslike_string": "43 F (6 C)",
"feelslike_f": "43",
"feelslike_c": "6",
"visibility_mi": "10.0",
"visibility_km": "16.1",
"solarradiation": "123",
"UV": "0.7",
"precip_1hr_string": "0.00 in ( 0 mm)",
"precip_1hr_in": "0.00",
"precip_1hr_metric": " 0",
"precip_today_string": "0.00 in (0 mm)",
"precip_today_in": "0.00",
"precip_today_metric": "0",
"icon": "rain",
"icon_url": "http://icons.wxug.com/i/c/k/rain.gif",
"forecast_url": "http://www.wunderground.com/US/VA/Richmond.html",
"history_url": "http://www.wunderground.com/weatherstation/WXDailyHistory.asp?ID=KVARICHM156",
"ob_url": "http://www.wunderground.com/cgi-bin/findweather/getForecast?query=37.554218,-77.465668",
"nowcast": ""
}
}