Thursday, January 03, 2019

Pack the Umbrella? Adding Weather Data to Google Sheets

Suppose you're planning an epic two week vacation that has you zipping around the globe. Wouldn't it be ideal to see the weather forecast for each day of your trip in a central location? That would simplify packing, no?

I thought so, and so I built the following custom function in Google Sheets:

/**
 * Look up a weather related attribute in a specific location/date
 *
 * @param {attr} weather attribute to look up
 * @param {location} of the weather to look up
 * @param {date} of the weather forcase
 * @return weather value
 * @customfunction
 */
function weather(attr, location,date) {
  var url = "http://code.benjisimon.com/singular-weather-service/?" +
                                   "loc=" + encodeURIComponent(location) + "&" +
                                   "date=" + encodeURIComponent(date) + "&" +
                                   "attr=" + encodeURIComponent(attr);
 
  if(attr == "debug") {
    return url;
  } else {
    var response = UrlFetchApp.fetch(url);
    return response.getContentText();
  }
}

This function is powered by the Singular-Weather-Service, a super easy to use Weather API.

With this function defined, it's now possible to create a spreadsheet that looks like this:

You can see a live version of this spreadsheet here.

Recall that the Singular-Weather-Service handles future dates by taking a 5 year historic average. This means that as your trip approaches, the weather data turns from historic average into prediction and finally into historic data.

This may all seem a bit excessive. However, I've found that when planning a trip, be it a multi-city extravaganza or a weekend getaway, creating a spreadsheet to record flight, hotel, things-to-do and other trip details is quite useful. Being able to add live weather to this spreadsheet makes it even more so.

Wednesday, January 02, 2019

A Hacker Friendly Weather Tool - The Singular Weather Service

I recently posted the start of a simple weather web service. And now I've got the rest of the code written.

Actual weather data is being pulled from the coder friendly DarkSky.net API. The code to do so is straightforward:

function forecast($loc, $time, $attr) {
  $key = DARKSKY_API_KEY;
  $url = "https://api.darksky.net/forecast/$key/{$loc['lat']},{$loc['lng']},$time";
  $ttl = $time < time() ? true : (60*60*6);

  if($time > (time() + (60*60*24*10))) {
    $ref_times = historic_times($time, 5);
    $result    = false;
    foreach($ref_times as $t) {
      $f  = forecast($loc, $t, $attr);
      if(is_numeric($f)) {
        $result = $result === false ? $f : (($result + $f) / 2);
      } else {
        $result = ($result == false ? $f : ("$result\n$f"));
      }
    }
    return $result;
  } else {
    $response = curl_get($url, ['exclude' => 'hourly,currently,flags'], ['ttl' => $ttl]);
    $data = $response['daily']['data'][0];
    return $attr === 'all' ? json_encode($data, JSON_PRETTY_PRINT) : $data[$attr];
  }
}

My code does have a few notable features:

  • To make my Darksky API quote last, I'm being careful to cache whatever data I can. If you ask for a historic forecast, the result is cached forever. If you ask for a forecast in the next 10 days, the results are cached for 6 hours.
  • The goal of my API is to return a single attribute, say temperatureHigh, however if you pass in the attribute all the entire forecast is returned as a JSON string. This is useful for debugging.
  • If you ask for the weather for a date more than 10 days into the future the code gets clever and returns a historic average of the last 5 years. Therefore, you can ask the system for any date and you'll get a sane response.

Here are some examples of the API in use:

# Quick access to one weather attribute. Note that any date allowed
# by strotime is legal.
$  curl -s "http://code.benjisimon.com/singular-weather-service/?loc=Arlington,VA&date=today&attr=temperatureHigh" ; echo
45.06

# See all attributes
$  curl -s "http://code.benjisimon.com/singular-weather-service/?loc=Arlington,VA&date=2019-01-02&attr=all" ; echo
{
    "time": 1546405200,
    "summary": "Light rain overnight.",
    "icon": "rain",
    "sunriseTime": 1546432095,
    "sunsetTime": 1546466329,
    "moonPhase": 0.9,
    "precipIntensity": 0.0042,
    "precipIntensityMax": 0.0375,
    "precipIntensityMaxTime": 1546488000,
    "precipProbability": 0.4,
    "precipType": "rain",
    "temperatureHigh": 45.06,
    "temperatureHighTime": 1546459200,
    "temperatureLow": 41.6,
    "temperatureLowTime": 1546491600,
    "apparentTemperatureHigh": 42.43,
    "apparentTemperatureHighTime": 1546459200,
    "apparentTemperatureLow": 37.09,
    "apparentTemperatureLowTime": 1546484400,
    "dewPoint": 33.69,
    "humidity": 0.73,
    "pressure": 1024.09,
    "windSpeed": 3.12,
    "windGust": 21.31,
    "windGustTime": 1546488000,
    "windBearing": 116,
    "cloudCover": 0.83,
    "uvIndex": 2,
    "uvIndexTime": 1546444800,
    "visibility": 10,
    "ozone": 273.7,
    "temperatureMin": 38.01,
    "temperatureMinTime": 1546430400,
    "temperatureMax": 45.06,
    "temperatureMaxTime": 1546459200,
    "apparentTemperatureMin": 36.13,
    "apparentTemperatureMinTime": 1546437600,
    "apparentTemperatureMax": 42.75,
    "apparentTemperatureMaxTime": 1546405200
}

# A month of high temps from the command line!
# This was on run on 2 Jan 2019. Therefore, some of these temps are predictions,
# some are historic and some are historic averages.
$ for d in `seq 1 31` ; do\
   echo -n "High Temp: $d Jan 2019: " ; \
   curl -s "http://code.benjisimon.com/singular-weather-service/?loc=Arlington,VA&date=2019-01-$d&attr=temperatureHigh" ; \
   echo "F" ; done
High Temp: 1 Jan 2019: 61.72F
High Temp: 2 Jan 2019: 45.06F
High Temp: 3 Jan 2019: 45.84F
High Temp: 4 Jan 2019: 49.45F
High Temp: 5 Jan 2019: 49.93F
High Temp: 6 Jan 2019: 53.85F
High Temp: 7 Jan 2019: 45.58F
High Temp: 8 Jan 2019: 53.14F
High Temp: 9 Jan 2019: 42.83F
High Temp: 10 Jan 2019: 44.97F
High Temp: 11 Jan 2019: 50.52F
High Temp: 12 Jan 2019: 41.88F
High Temp: 13 Jan 2019: 46.69F
High Temp: 14 Jan 2019: 41.600625F
High Temp: 15 Jan 2019: 43.900625F
High Temp: 16 Jan 2019: 41.5675F
High Temp: 17 Jan 2019: 41.451875F
High Temp: 18 Jan 2019: 34.704375F
High Temp: 19 Jan 2019: 44.614375F
High Temp: 20 Jan 2019: 49.164375F
High Temp: 21 Jan 2019: 36.860625F
High Temp: 22 Jan 2019: 29.885625F
High Temp: 23 Jan 2019: 32.618125F
High Temp: 24 Jan 2019: 31.099375F
High Temp: 25 Jan 2019: 39.45875F
High Temp: 26 Jan 2019: 37.8025F
High Temp: 27 Jan 2019: 44.05F
High Temp: 28 Jan 2019: 28.1475F
High Temp: 29 Jan 2019: 30.96F
High Temp: 30 Jan 2019: 35.040625F
High Temp: 31 Jan 2019: 45.15125F

Check out the code for the project here and feel free to experiment with the server over at http://code.benjisimon.com/singular-weather-service/.

LinkWithin

Related Posts with Thumbnails