API Download PWS Data to JSON then Import to Google Spreadsheet

  • 1
  • Idea
  • Updated 8 months ago
Like many PWS owners I've been grappling with how to access my years worth of PWS data in light of the recent API changes. I decided to write a bash script that would download and combine a range of days into a single JSON file. Here's the script:

#!/bin/bash

STARTDATE=20150101
PWS=EnterYourPWSStationID
APIKEY=EnterYourAPIKey

    for i in {0..365}
    do
         NEXTDATE=$(date -j -f %Y%m%d -v+"$i"d $STARTDATE +'%Y%m%d')

curl "https://api.weather.com/v2/pws/history/hourly?stationId=${PWS}&format=json&units=e&date=${NEXTDATE}&apiKey=${APIKEY}" >> ~/Downloads/PWSHistoricalData.json

sleep 1s
done         
#

Just enter your start date, PWS ID and API Key in the variables at the top and you're good to go. The example above would download 365 days worth of data starting January 1, 2015. I have the sleep 1 second command between each date download so you don't exceed the maximum 30 API calls in a given minute.

Once you have your combined JSON file it does have some extra JSON metadata added from each day that won't parse correctly. Open the file in a text editor and do a find for ]}{"observations":[ then replace with a comma in all instances (it should find it 364 times). I was going write a code snippet in the script using sed to find and replace the string, but gave up with all the special characters needed in my find.

Now that you've got a correctly formatted JSON file we need to parse it into a human-readable format. I like Google Spreadsheets, and found this article (https://medium.com/@paulgambill/how-to-import-json-data-into-google-spreadsheets-in-less-than-5-minutes-a3fede1a014a) that shows a custom script you can add to a Google Spreadsheet that will allow you to import a JSON file and have it parse directly inside of a Google Sheet. Your JSON file does need be uploaded to a publicly accessible web server (or you can use Drive or Dropbox with a public URL). Then in the top left cell of your sheet just enter your new import json function like this:
=IMPORTJSON("http://yourserver.org/PWSHistoricalData.json","","")

You can use the same IMPORTJSON function to query data directly from wunderground too, albeit you can only query a single day.
=IMPORTJSON("https://api.weather.com/v2/pws/history/all?stationId=YOURPWS&format=json&units=e&date=20150101&apiKey=YOURAPIKEY","","")

I was originally going to do this with 5-minute increments on the data, but that would yield an enormous file (too large to ingest into Google Spreadsheets). Anyhow, hopefully some of you may find this helpful.
Photo of Wade Gibson

Wade Gibson

  • 7 Posts
  • 0 Reply Likes

Posted 8 months ago

  • 1
Photo of Wade Gibson

Wade Gibson

  • 7 Posts
  • 0 Reply Likes
You may need to sleep 2s just to be safe. I noticed that occasionally the servers responded/downloaded quicker than expected so I had a few instances where I exceed the 1-minute threshold.
Photo of Tom

Tom

  • 52 Posts
  • 21 Reply Likes
Victoria-Any status update on the csv? This is a lot of effort (and still fairly technical for an average user) to accomplish what used to be a very simple task.

I know you personally throw api calls around but the average user is not as capable as you are.
Photo of Victoria Gardner

Victoria Gardner, Official Rep

  • 658 Posts
  • 93 Reply Likes
It's in the list of desired changes.

--Victoria